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
