Java + MySQL integrity violation handling

Question

Hello, I write Java program using JDBC (mysql database). When I violate mysql integrity (f.e. I try to insert same primary key value) I catch SQL exception. Should I write it in way it may never happen (f.e. at first boolean function checking whether primary key value isn't already in DB and then calling insert), or is it okay to handle it just by exception? Example :

catch (SQLException ex) {ex.printStackTrace(); showSomeErrorDialog(); }

Answer 1

There are two possible answers :

• if you know that your application is designed to avoid this kind of behaviour, use the exception
• if your application can make these errors often, use a test.

Answer 2

There are indeed basically two ways to achieve this:

1. Test if record exists before inserting --inside the same transaction.

2. Determine if SQLException#getSQLState() of the catched SQLException starts with 23 which is a constraint violation as per the SQL specification. It can namely be caused by more factors than "just" a constraint violation. You should not amend every SQLException as a constraint violation.

   public static boolean isConstraintViolation(SQLException e) {
       return e.getSQLState().startsWith("23");
   }
   

I would opt for the first one as it is semantically more correct. It is in fact not an exceptional circumstance. You namely know that it is potentially going to happen. But it may potentially fail in heavy concurrent environment where transactions are not synchronized (either unawarely or to optimize performance). You may then want to determine the exception instead.

That said, you normally shouldn't get a constraint violation on a primary key. In well designed datamodels which uses technical keys as primary keys they are normally to be managed by the database itself. Isn't the field supposed to be an unique key?