aspose file tools*
The moose likes JDBC 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
Bookmark "Issues around unit testing MySQL DAO code using HSQLDB." Watch "Issues around unit testing MySQL DAO code using HSQLDB." New topic
Author

Issues around unit testing MySQL DAO code using HSQLDB.

Emanuel Kadziela
Ranch Hand

Joined: Mar 24, 2005
Posts: 186
Hi,

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.

Thanks
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30973
    
159

Emanuel,
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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
 
subject: Issues around unit testing MySQL DAO code using HSQLDB.