Executing JPQL query in PersistenceException catch clause re-throws PersistenceException

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("[email protected]", "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 ("[email protected]). 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: [email protected] | 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?

Solution

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.