Failure trying to catch an UncategorizedSQLException

To store information in our PostgreSQL database we use Spring and Hibernate as I mentioned earlier in my posts. Some of our tables in the database also have GIS information with them. That’s why we are also using PostGIS extension on our database. This GIS information (Geometry) is also reflected in the Hibernate objects we use to persist our data.
We have experienced that this Geometry data can be invalid, in which case the database (JDBC) is throwing an UncategorizedSQLException. This is the case, for example, when a Polygon has a different start and ending point.
In our application we have two kinds of exceptions: the CustomException (thrown and handled by ourselves) and all other exceptions not handled by us. With this distinction between the exceptions I can decide what to do when an exception occurs, for example, when storing data in the database: is it our CustomException, then it is most probably a problem with this instance and I will move to process the next instance. If there is thrown another Exception, then there is something seriously wrong and I will stop the processing.
This may sound good in theory but it appeared that when an instance with invalid geometry was stored in the database, the whole process was stopped because there occured an UncategorizedSQLException. The code we used to store our Hibernate object is this:

 public void insert(final CustomObject e) throws CustomException {
      try {
      } catch (UncategorizedSQLException ex) {
         // We assume the geometry was invalid
         throw new CustomSystemException(CustomErrorMessage.CSM_SYS019, ex);
      } catch (DataAccessException ex) {
         throw new SystemException(ex);

While this code was working fine in our unit tests it kept failing in our application. After some research we discoverd the (logical, like always) reason for this: since we are using Hibernate to persist our data it is Hibernate that determines when the data is posted to the database. And tests showed that this is done when a commit is fired. And that is the big difference between our real-world application and our unit test. In our application we perform a lot of other steps before we fire the commit. So the code has already passed the try-catch before the database is throwing the UncategorizedSQLException. In our unit test we just called this insert and performed a commit right-away, so the exception is thrown inside the try-catch.
So that was the problem but now we had to think about the solution. We came up with 3 options:

  1. Use AOP to act on every thrown UncategorizedSQLException with a ThrowsAdvice and make it a CustomException.
  2. To call flush() after each insert() and update() so the data is posted in the database right away.
  3. Perform the validation of the geometry ourselves before we send it to the database.

So first we try the flush() because this one was the easiest to implement. While this gave the behaviour we wanted we didn’t like the fact that we decided when to flush the data instead of letting Hibernate decide this. This could be giving performance issues at some time so we decided to investigate the other solution too.
The second attempt was the AOP option. Because I haven’t used this before (besides the default out-of-the-box Spring functionlity) I was curious how to get it to work. Well, I got the ThrowsAdvice working for every exception that was thrown in our code, but not for the UncategorizedSQLException. I guess that that is caused by the fact that this exception is thrown by AOP itself, and therefor can not be ‘catched’ by another AOP, but I am not sure about this yet.
Finally, because of time-pressure I decided to go for the last option. We were doing all kinds of validations already on the Geometry so I added the one for the polygons. This is performance wise the best solution and I am also very sure what the implications are for the rest of the application.
But someday when I have more time, I am definitely having another look at this AOP thing, because that part is worth some more investigation.

About Pascal Alma

Pascal is a senior IT consultant and has been working in IT since 1997. He is monitoring the latest development in new technologies (Mobile, Cloud, Big Data) closely and particularly interested in Java open source tool stacks, cloud related technologies like AWS and mobile development like building iOS apps with Swift. Specialties: Java/JEE/Spring Amazon AWS API/REST Big Data Continuous Delivery Swift/iOS
This entry was posted in Hibernate, Spring Framework and tagged , . Bookmark the permalink.