1

I'm using Spring Jdbc 3.0.5 RELEASE, and we are using DB2 database as per our client's requirement. We want to run our tests against HSQL DB bcz we dont want to disturb the DB2 primary keys(which are implemented by creating sequences) with our tests.

And I've used "jdbc:embedded-database" tag in ApplicationContext.xml related to tests to create the required tables and inserted dummy data for tests (which ran successfully) .

But now obviously we are facing issues with the syntax of the queries we wrote in DB2 Dialect in the DAO Layer.

What is the best possible solution to overcome this problem?

And is there any 'Hibernate Criteria' like API that supports Spring jdbc to make our queries related to Joins(which are more painful when running against HSQL) run on any db

Thanks in advance

Varun Tamiri
  • 11
  • 2
  • Please dont say 'switch to Hibernate' which is a know solution to me !.... – Varun Tamiri Aug 03 '12 at 09:05
  • 1
    It doesn't have to be Hibernate. Any JPA implemention would do. But if you've written literal JDBC queries then they will *of course* not be database independent. You'll have to rewrite them for the other database. Or write a second set of queries, and add a layer that chooses the correct set based upon the underlying database. But since you'd have to rewrite them anyways, you might was well rewrite them in something like EJBQL and switch to using JPA instead of JDBC. – aroth Aug 03 '12 at 09:16
  • As of now we changed the queries not to use any DB2 specific functions but switching to JPA based implementation will be much better option – Varun Tamiri Aug 12 '12 at 00:44

1 Answers1

0

JPA is a great solution as it gives you a standard interface for database operations, allowing you to swap engine implementations (Hibernate, Toplink, etc.) fairly freely.

If you need to then you can always mix and match JPA and JDBC for the situations where JPA doesn't quite give you enough flexibility.

You should find that specifying the dialect of the underlying database boils down to a configuration choice when setting up your persistence context. This ought to remove some of the differences between the various databases in your JPQL.

As ever, avoid vendor-specific extensions for maximum portability.

Rich Cowin
  • 668
  • 1
  • 8
  • 17