Integration testing native SQL query when using Hibernate

Question

I use Hibernate and widely adopted Hibernate Query Language to define queries in my DAOs.

Anyway sometimes HQL isn't capable of performing specific task compared to native SQL query.

For example the following Postgres expression is not "convertible" to HQL:

my_date > current_date - interval '10 year'

This means that in some case I'm writing native queries. Considering that I'm using another database for integration testing (http://hsqldb.org/) which doesn't reflect the syntax of the Postgres expression above. This results in test exception during DAO methods using such native query.

How do you handle such cases? I can just think of following scenarios:

Never use native query and try to build everything in HQL (possible?)
Don't test methods which use such queries (unhappy)
Use same database both for production and development (performance problem)

Other, more interesting solution? Thanks

You're using another database for unit testing? I think you're misunderstanding the purpose of unit testing. You shouldn't be hitting a database at all. When you start to hit a database, you're now using integration testing. Unit tests are intended to cover one particular unit of code (generally a class), and should mock any and all external dependencies that the class may have. — Colin M
– Colin M, Commented Jul 12, 2013 at 13:41
@ColinMorelli so how do you test DAOs in unit tests? How do you mock away the database? — Uwe Plonus
– Uwe Plonus, Commented Jul 12, 2013 at 13:47
Maybe one solution would be to extend some hibernate/jpa classes (for instance the EntityManager) to alter the SQL before sending it to HSQLDB. — mael
– mael, Commented Jul 12, 2013 at 13:51
@Wizche I think you're missing the point. If you separate unit and integration testing, then your question is answered. Your unit tests don't talk to your database. Your integration tests should absolutely talk to a replica of your production environment. So, you simply talk to a development PostgreSQL database. Then your problem is avoided entirely. Testing against a different environment with different systems somewhat defeats the purpose of testing. You should always aim to emulate production. — Colin M
– Colin M, Commented Jul 12, 2013 at 13:53
@Wizche No, you should use an exact copy of your production schema (without the data). Your integration tests should insert any test data that needs to be inserted, and then subsequently make your DAO call to ensure that the proper data is returned. In either case, you should be querying against Postgres, since that's what you have in production — Colin M
– Colin M, Commented Jul 12, 2013 at 14:03

gerrytan · Accepted Answer · 2013-07-12 16:02:27Z

4

Normally the purpose of integration testing is to test against (very) similar environment to production, hence IMO you should use the same database engine. For unit testing however using HSQLDB is fine. In which case unfortunately the classes that has dependency to Postgres couldn't be unit tested, you have to wait until integration testing to detect bugs.

On a side note however the result of the Postgres SQL you mentioned can be achieved by performing the date arithmetic on Java.

Calendar currentDateCal = Calendar.getInstance();
currentDateCal.add(Calendar.YEAR, -10);
Date currentDate = currentDateCal.getTime(); // bind this currentDate object into your HQL parameter

answered Jul 12, 2013 at 16:02

gerrytan

41.2k12 gold badges88 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Colin M Over a year ago

You shouldn't be talking to a database at all in a unit test, it should be testing a specific portion of your code with no external dependencies.

Wizche Over a year ago

Thanks gerry, although many people are completely against using a different database for "whatever" test, I think u are right, If I have dependency on the database syntax, I can either use the same database or adapt the query.

Collectives™ on Stack Overflow

Integration testing native SQL query when using Hibernate

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related