Pragmatic Programmer Issues

How to extend SQLErrorCodesFactory

I found some interesting feature in springframework. Spring offers you to map sql error code to exception type. By default there is in springframework distribution file called sql-error-codes.xml which has definition for common databases ( DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase).

This keys are taken form java.sql.DatabaseMetaData databaseProductName property. Each driver should provide proper implementation. This post will provide you the way how to add another databases or extends definition to more sql codes.

As we read javadoc, the default file is bundled in spring.jar and it’s location is “org/springframework/jdbc/support/sql-error-codes.xml" so first solution is to simple extract this file, add what we want, and than put it back. This solution has many drawbacks e.g. when we upgrade we must repeat our steps once again. If we keep reading javadoc we read

Reads the default file in this package if not overridden by a file in the root of the class path (for example in the “/WEB-INF/classes” directory).

So the second option is to provide file called ‘sql-error-codes.xml’ in the root path. Lets start coding.
Our App.java is very simple.

public class App {
public static void main(String[] args) {
SQLErrorCodesFactory errorCodes = SQLErrorCodesFactory.getInstance();
SQLErrorCodes errorCode = errorCodes.getErrorCodes(“MySQL”);
}
}

sql error codes default

If we run this in debug mode we can see something like this. This is default SQL error codes definition for MySQL database. Now we add the ‘sql-error-codes.xml’. I’ve just copied MySQL bean from default file and added some codes to badSqlGrammarCodes. This time the result look like this:

sql error codes override

And here is execution log, interesting lines in bold:

Sep 2, 2008 9:54:46 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
Sep 2, 2008 9:54:46 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [sql-error-codes.xml]
Sep 2, 2008 9:54:46 PM org.springframework.beans.factory.support.DefaultListableBeanFactory registerBeanDefinition
INFO: Overriding bean definition for bean 'MySQL':
    replacing [Generic bean: ... defined in class path resource [org/springframework/jdbc/support/sql-error-codes.xml]]
    with [Generic bean: ...  defined in class path resource [sql-error-codes.xml]]
Sep 2, 2008 9:54:46 PM org.springframework.jdbc.support.SQLErrorCodesFactory 
INFO: Found custom sql-error-codes.xml file at the root of the classpath
Sep 2, 2008 9:54:46 PM org.springframework.jdbc.support.SQLErrorCodesFactory 
INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]

The point to remember is that our definition didn’t override all the definition but only definition with the same bean id. This is not so clear in SQLErrorCodesFactory javadoc, which suggest that we override all beans.

We can use this mechanism if we have different database, or if we want to extend or change sql code mapping to exception. The application is really simple but you can
download it, and try by yourself.

Bye.

Categories