Executing JPQL query in PersistenceException catch clause re-throws PersistenceException

Question

I am trying to achieve some error handling in my DAO in my JavaEE application, using JPA.

I have the situation where someone (a user) might try to enter duplicates into my DB. My plan is to attempt to persist my entity, eg. user("test@test.com", "Test", "password"). If this fails with a PersistenceException I am thinking that I can check for duplicate entries on the unique columns like username ("Test") and email ("test@test.com). If I find a duplicate I'd like to check which column it failed for and notify the user accordingly.

My attempt is as follows:

getEntityManager().persist(entity);
try {
    getEntityManager().flush();
} catch (PersistenceException ex) {
    List<T> duplicates = findDuplicate(entity);
    if (duplicates.size() > 0) {
        // Notify user
    } else {
        // Probably pass exception forwards
    }
}

The Entity manager is injected into the class with:

@PersistenceContext(unitName = "RecruitmentPU")
protected EntityManager mEM;

The getEntityManager() simply return this member. The class it self is annotated as @Stateless.

To find a duplicate I basically just do this:

String column = myEntity.getUniqueColumn(); // returns the name of the column
Object uniqueValue = myEntity.getUniqueValue(); // returns the value of the unique column

Query query = getEntityManager().createQuery(
        "SELECT e FROM TestEntity e WHERE " + column + " = :identifier",
        TestEntity.class
);

query.setParameter("identifier", uniqueValue);
List<T> entries = null;
try {
    entries = (List<T>) query.getResultList(); // Here the exception is re-thrown
} catch(Exception ex) {
    System.out.println("Caught something... \n" + ex.getMessage());
}

The entity also has an ID column which is annotated @GeneratedValue(strategy = GenerationType.IDENTITY). There is also a @ManyToOne attribute that was removed when I simplified the code. When I test this I get the following output:

Info:   Caught something... 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.3.qualifier): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'Test' for key 'username_UNIQUE'
Error Code: 1062
Call: INSERT INTO test.test (email, username, role) VALUES (?, ?, ?)
        bind => [3 parameters bound]
Query: InsertObjectQuery(ID: 0 | email: test@test.com | username: Test | password: ********)

At the moment I'm letting the container handle transactions, but I'm having a hunch that I'm getting these problems because I'm trying to query the database before the first transaction is finished (or something similar).

Is the flaw in the strategy or the implementation? And what steps could I take to start solving this?

Ah, that is a getter for an injected member. I inject the entity manager as so `@PersistenceContext(unitName = "RecruitmentPU") protected EntityManager mEM;` into the same class (which is marked as `@Stateless`). I've updated the question with the info. — Christian Eriksson, Feb 15 '17 at 07:13
Do users have an additional field, called `ID` which is an `auto-invrement` field? — Janez Kuhar, Feb 15 '17 at 11:30
I use the Entity to get which column is unique and the value of this column for the Entity instance, added the basics to the question. Yes the User Entity has an autogenerated ID column and it also has a ManyToOne relationship on a column that is not shown in the simplified example. — Christian Eriksson, Feb 15 '17 at 12:21

score 2 · Accepted Answer · edited May 23 '17 at 10:29

2

You don't want to continue any transaction after an exception has occurred.

I suggest you switch the order of operations, like so:

query DB for records with unique keys that are equal to the unique key of the entity you would like to persist,
persist your entity.

edited May 23 '17 at 10:29

Community

1
1

answered Feb 15 '17 at 12:47

Janez Kuhar

3,705
4
22
45

This is probably the right answer, however doesn't this force me to query the DB twice for every time I try to add things to the database? I was hoping to keep the number of queries down (basically cut the number in half) by trying to insert first and then check what went wrong in the few instances that this might happen. Are there any solutions to this? – Christian Eriksson Feb 15 '17 at 13:05
Then simply just `persist()`. The exception will tell you what went wrong. – Janez Kuhar Feb 15 '17 at 13:12
It is indeed a possible solution, and probably what I'll end up doing. However, I played around a bit and what about using `EntityManager.clear()`? Clearing the PersistenceContext seem to enable me to query for duplicates in the catch clause. Afterwards the transaction is toast so I'll have to discard it. But it seems to enable me to check if the insert would fail on multiple columns as opposed to one (which is what the exception would tell me). Am I doing something that could come back and bite me later? – Christian Eriksson Feb 15 '17 at 14:37
http://stackoverflow.com/questions/13886608/when-to-use-entitymanager-clear – Janez Kuhar Feb 15 '17 at 14:44
Thanks for your patience! I'll see if it's possible to reconsider and go with your first suggestion, alternatively I'll just have the transaction fail and deal with the exception as per the second suggestion. – Christian Eriksson Feb 15 '17 at 15:07

Executing JPQL query in PersistenceException catch clause re-throws PersistenceException

1 Answers1