• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Issues around unit testing MySQL DAO code using HSQLDB.

Ranch Hand
Posts: 187
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.

author & internet detective
Posts: 40188
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
Nothing up my sleeve ... and ... presto! A tiny ad:
Thread Boost feature
    Bookmark Topic Watch Topic
  • New Topic