This is the issue that I keep running into on various projects, so even if it seems obvious I think it is worth spending a post on it.

Some OR mapping frameworks tend to use batch update operations when dealing with the database. The “problem” that this approach introduces is that if any problem occurs during the update, a java.sql.BatchUpdateException exception will be thrown instead of the regular java.sql.SQLException. If we don’t handle BatchUpdateException properly the message it will give us will not be very useful.

In Spring-Hibernate based web applications this is something that is often seen. For example, the following log entry is not helping you much in solving the problem:

org.springframework.dao.DataIntegrityViolationException:
Hibernate operation: Could not execute JDBC batch update; SQL [];
Batch entry 0 [..... the original query goes here .....] was aborted.
Call getNextException() to see the cause.

To avoid this kind of problems we need to take a special care of SQL exceptions (and all related exceptions).

Take a look at the following code snippet for example:

if (exception instanceof SQLException) {
   while(exception != null) {
         logger.info(exception); // Log the exception
         // Get cause if present
         Throwable t = exception.getCause();
         while(t != null) {
               logger.info("Cause:" + t);
               t = t.getCause();
         }
         // procees to the next exception
         exception = exception.getNextException();
   }
}

If you put the snippet like this into your exception-handling routine, you will get the actual SQL error in the log file. For example:

org.postgresql.util.PSQLException:
 ERROR: null value in column "comment" violates not-null constraint

This is much more informative message then we had before and it will certainly help you solve the issue faster.

While we are talking about SQLException, you could be interested in the following blog posts:

Here Lance Andersen talk about enhancements of SQLException in JDBC 4.0 and here Cameron Purdy talks about some design flaws of the SQLException

Enjoy your reading.