JPA exception handling (unique constraint, name too long) sql layer vs java layer?

Question

I'm wondering how most applications are enforcing a unique ID. (e.g. username for any website registering)

One way of my server-side implementation could be:

Query the database every time I got a new ID. The query is composed in Java code with JPA. Such as:

//compose query
myQuery.where(...);
myQuery.from(...);
...
TypedQuery<> search= entityManager.createQuery(myQuery);
//get result
search.getResultList();

This feels too expensive to me. And it's problematic.

a) I'm not sure when/how this query is gonna be optimized. Since I may not have the type of index on the database column that would optimize this specific query.

b) When JPA persist my entity to database, db is gonna check again for the unique constraint (if I put one on the column). So the same unique check happened twice.

c) Multi-threading, thread A gets all the IDs from db and start to compare uniqueness. During this time thread B persists the id. When A is done comparing and persist the id, it would hit db unique constraint again. Which makes this check useless.

So, I hope to only use the database layer unique check and I believe it has more optimization power. But here comes to the problem:

1. The unique constraint gonna throw RollbackException only when you do entityManager.commitTransaction(). It is so difficult to catch a RollbackException and extract the actually cause (ORA-00001: unique constraint (constraint_name) violated) and surface that out to my end user. How should I do it?
2. The same issue, for ORA-12899: value too large for column. For same reason I don't want to check the length in my java code. So I could hope if in the future I update my db column length it could automatically reflect this to end user.

Answer 1

You could write custom aspects/interceptors around your service methods to intercept all the calls and returned results including thrown exceptions. Then you can react on exceptions and create appropriate error messages.

Easier solution in your case would be to explicitly flush the persistence context and catch such kinds of database constraint violations immediately:

User user = ...
user.setUsername(username);
...
entityManager.persist(user)
try {
  entityManager.flush(); // Force synchronization with database
} catch (SomeException e) {
  if (<cause of the exception is database constraint violation>) {
    throw new ValidationException("Username already exists.");
  } else {
    throw e;
  }
}