Which in-memory Java database is closest to MySQL and SqlServer for unit testing?

Question

I am looking for a database in memory that will be used to unit test my level of data access. In production, most of my classes will work against MySQL 5.1, but some of them will have read access to Microsoft SQL Server.

In the past I had problems with dialect differences between different databases dropping unit testing (as a result of using AspectJ once to block requests before execution !!!), so I would like to avoid this as much as possible.

So, I was wondering which in-memory Java database is closest to the behavior on MySql and SQL Server? My main problem is mainly with MySQL, because we use it the most and -AFAIK- it has the most non-standard syntax. This is for unit testing, so scaling, performance, efficiency, etc. Not important.

Answer 1

I really use the MySQL database in my unit tests to ensure correctness. When using JUnit, you can do something like this:

@Before public void initialize() { try { dataSource.getConnection().close(); logger.info( "got a connection, so we can assume the configuration is correct..." ); } catch ( Exception e ) { logger.warn( "skipping tests as no connection is available. check spring config: {}", e.getMessage() ); e.printStackTrace(); assumeNoException( e ); } } 

This is done before each test, and if it cannot connect to the database, it will skip the test without causing a failure through assumeNoException . Thus, if you need to build a system that is not configured for MySQL, you can, but if your developers set up their system correctly, it will use the installed instance of MySQL. I know this is not a direct answer to your question, but I thought it might be useful anyway ...