File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Issues around unit testing MySQL DAO code using HSQLDB. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Issues around unit testing MySQL DAO code using HSQLDB." Watch "Issues around unit testing MySQL DAO code using HSQLDB." New topic

Issues around unit testing MySQL DAO code using HSQLDB.

Emanuel Kadziela
Ranch Hand

Joined: Mar 24, 2005
Posts: 187

I am not sure if anyone has resolved this issue successfully, but any help would be greatly appreciated.

Our system setup:
Spring/JPA/Hibernate with MySQL in production and HSQLDB in tests.
Allow me to preempt the first wave of comments about running the tests on exactly the same setup as production. We do that in our acceptance, performance and full-stack tests. This is about unit tests, and for various reasons (including continuous integration), we need to run some sort of an in-memory database for the unit tests in order to remove external server dependencies (unfortunately, as far as I know, MySQL does not have an embedded, in-memory database option).

As long as we use JPA and JPQL only, we are fine, because Hibernate resolves the dialects for us. However, as soon as we began hybridizing our daos to include direct JDBC SQL queries, we began running into all sorts of problems surrounding the more limited and strict SQL supported in HSQLDB and the relatively lax and forgiving SQL in MySQL.

I have tried switching HSQLDB to both H2 and Derby without success - failures included: 1. same SQL issues as HSQLDB, 2. additional SQL issues not even encountered in HSQLDB, 3. transactional and referential integrity issues due to our complex, custom management of sessions and transactions (which we had to implement to alleviate optimistic locking problems in a very highly concurrent system with lots of writes).

I suppose one obvious solution would be to rewrite the SQL queries to run fine in HSQLDB, because then they would likely work fine in MySQL too. Unfortunately, many of the queries are quite complex and leverage a lot of the non-standard SQL features provided by MySQL, so it would be a very difficult if not impossible task, and would ultimately discourage our developers from writing unit tests.

I've searched the web for some ideas or solutions and there is very little out there. I wonder if anyone has run into this problem and found a reasonable solution.

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

While this thread is perfectly appropriate in the testing forum, I think you are getting to get better answers in the JDBC forum. Moving it there.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: Issues around unit testing MySQL DAO code using HSQLDB.
jQuery in Action, 3rd edition