Hibernate spring unit testing with mySQL in memory database

Question

I just saw an article that we can use HSQLDB to do this but I was thinking of testing my DAOs using mySQL in memory database since behavior varies from database to database. Can someone tell me how I can do this?

Thanks

Answer 1

I'm using H2 for unit/integration testing and also for development - way faster and doesn't depend on anything external. MySQL is my production database. It's like a perfect marriage...

... until concurrency kicks in, and databases start behaving differently.

Each database has its own transaction isolation policies - for the most part, tests work on MySQL but not on H2. For instance, H2 only supports table-locking with a timeout of just 1,000ms—thus causing some threads to fail and therefore my tests unstable.

There are ways on how H2 can be configured separately (check out H2's transaction isolation levels), however I've yet to test the MVCC option.

I'll keep you posted.

PS. Moral of the story - create concurrent integration tests!

---

Update: It works! I have created the data source as follows:

final DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("org.h2.Driver");
ds.setUrl("jdbc:h2:~/testdb;MVCC=TRUE");
ds.setUsername("sa");
ds.setPassword("sa");       
return ds;

The MVCC flag works like a charm, however I had some problems using in-memory h2:mem since it is volatile with each connection (therefore, the schema is wiped out whenever Hibernate reattempts the connection). I had to retreat to a file-based database (which is not a big problem for me).