views:

100

answers:

2

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

+3  A: 

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.

Peter Tillemans
Hi Peter, thank you very much for reminding me about *BATIS. I was using iBATIS some years ago and I liked it a lot. I just checked iBATIS website and saw that it is retired. After I checked myBATIS and saw that it is not very good with Spring. So, it seems not the best choise for me.
MinimeDJ
I saw that the Spring support was broken with iBatis 3. However myBatis.org also supports iBatis 2.3.5 which presumably has still Spring support.
Peter Tillemans
+1  A: 

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.

seanizer