Java + MySQL integrity violation handling

Question

I am writing a Java program using JDBC (mysql database). When I violate the integrity of mysql (for example, I try to insert the same primary key value), I catch an SQL exception. Should I write it in a way that will never happen (for example, first the boolean function checks if the primary key value is not in the database yet, and then calls the insert), or is it ok to handle it only by exception? Example:

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

Answer 1

There are really two ways to achieve this:

• Check if the record exists before insertion - into the same transaction.

• Determine that the SQLException#getSQLState() caught SQLException begins with 23 , which is a violation of the restriction in accordance with the SQL specification . This can be caused by more factors than the “just” violation of the restriction. You should not change every SQLException as a violation of the constraint.

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

I would choose the first, because it is semantically more correct. In fact, this is not an exceptional circumstance. You know that this will potentially happen. But this can lead to failure in a complex parallel environment where transactions are not synchronized (either informally or optimized). Then you can define an exception.

However, you should usually not violate the primary key constraint. In well-designed datamodels that use technical keys as primary keys, they are usually managed by the database itself. Shouldn't the field be a unique key ?

Answer 2

There are two possible answers:

• if you know that your application is designed to avoid this behavior, use an exception
• If your application can often make these errors, use a test.

Answer 3

As already noted, there are two possible approaches: one must test, then insert / update, otherwise handle SQL Exception. Both of these approaches have their drawbacks:

• The caveat "Test before insertion" is that each transaction will have additional queries that will affect performance. This is a particularly big problem when there are few such erroneous transactions.
• The caveat for "evaluating SQL exceptions" is that such exception messages are database specific. These messages, in most cases, do not provide specific information, except that there is a restriction on the violation.

So, I will propose an approach that is a hybrid of the two .

• Do not perform the test before inserting.
• Let the database throw an exception.
• Throw SQL Exception.
• In the stream of exceptions (catch block), make additional requests to generate very specific error messages to indicate to clients what exactly failed (unique key, primary key, foreign key, specific columns, etc.).

This may require a few extra lines of code, but it definitely improves performance and generates friendly error messages.

Answer 4

catch (SQLException se)

  { if(se.getErrorCode()==1062) System.out.print(" unique error"); } 

when you try to insert a line that violates your db restrictions, then SQLException throws an error code representing your violated restriction.

function in detail http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/sqlcode_function.htm