Pattern for working with different database dialects via JDBC

Question

I'm creating an application that has to work with different databases (Oracle, MSSQL, MySQL...) through JDBC. I have to work via JDBC because my application calls stored procedures in these databases.

What is the best aproach for building such applications? Are there any frameworks for this?

Important: The solution must nicely deal with Spring Framework.

---

I am thinking about Hibernate, since it is robust ORM solution and it has a buildin support for stored procedures: http://docs.jboss.org/hibernate/stable/core/reference/en/html_single/#sp_query

Please, provide me with your oppinions about my current choise.

---

Best regards, Max

Answer 1

I would give myBatis a good look. It handles all the pain associated with JDBC and transactions and mapping resultsets to Java objects or hashes.

It also plays nice with SQL and stored procedure by separating them from the Java code and configuring them in XML configuration files. This works in practice a lot better because it is easier to copy queries from XML to an interactive SQL browser and vice versa.

To connect to multiple datasets you need to create an SqlSessionFactory for each datasource.

Answer 2

Hibernate is usually the standard option (and the one I'd choose). I prefer using JPA over Hibernate, but that's not an option if you need Stored Procedures. But regarding the comment about iBatis:

While I have no experience with iBatis myself, it seems the Spring Support for iBatis is not bad:

From the Spring Reference, chapter 13.6: iBATIS SQL Maps:

The iBATIS support in the Spring Framework much resembles the JDBC support in that it supports the same template style programming, and as with JDBC and other ORM technologies, the iBATIS support works with Spring's exception hierarchy and lets you enjoy Spring's IoC features.

Transaction management can be handled through Spring's standard facilities. No special transaction strategies are necessary for iBATIS, because no special transactional resource involved other than a JDBC Connection. Hence, Spring's standard JDBC DataSourceTransactionManager or JtaTransactionManager are perfectly sufficient.