What the Spring template should really do

Spring provides a wonderful mechanism to standard the way that connections to an underlying database are managed. The JDBC Template abstracts the developer from having to understand any particular vendor, and provides a neat way to translate vendor specific SQL errors into a standardised format.

It doesn’t go far enough. SQL can no longer be treated as a standard, but instead should be viewed as a recommendation. The differences in SQL dialects between vendors is unbelievable, and inexcusable. However there isn’t a standardised translation service available. In an earlier example I outlined the different SQL statements required to allow MySQL and Oracle to update the same row in the database:

MySQL

UPDATE TABLE1(date, username)
VALUES ("2009-01-01", "fred");

Oracle

UPDATE TABLE1(date, username)
VALUES(TO_DATE("01-JAN-2009"), "fred")

Clearly this it is undesirable to have to write these different SQL statements in an application, there is too much scope for error and poor testing to let a faulty statement through. We could use Hibernate, represent the whole thing in objects, mapping files and HQL, but that’s the programming equivalent of taking a sledgehammer to a nut. It would work (usually) but it will certainly not be pretty, nor light weight.

What is really needed is a translator, similar to the concept of Hibernate’s dialects. The translator could be part of the template, either determined automatically from the database, or wired in by configuration. Developers would then simply execute statements on the template as normal, with it handling the conversion to the relevant dialect for the database:

template.update(
    "UPDATE TABLE1(date, username) "
    + "VALUES (\"2009-01-01\", \"fred\");");

This would make the JDBC template a universal fit for accessing databases.