Which in-memory Java database is closest to MySQL & SqlServer for the purpose of unit testing?

Question

I am looking for an in-memory database to use in unit-testing my data access layer. In production, most of my classes will be running against MySQL 5.1 but some will have read access to Microsoft SQL Server.

In the past, I've had issues with differences dialects between different databases tripping up unit testing (resulting once in using AspectJ to mangle the queries before execution!!!) so I'd like to avoid that as much as possible.

So, I was wondering which Java in-memory database is closest in behaviour to MySql & SQL Server? My main concern is mostly with regards to MySQL since we use it the most and -AFAIK- it has the most non-standard syntax. This is for unit testing so scalability, performance, efficiency, etc are not important.

Answer 1

I actually use a 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 runs before every test and if it cannot connect to the database, it will skip the test without causing a failure via assumeNoException. This way, if you need to build on a system that is not configured for MySQL you can, but if your developers configure their system correctly, it will use an installed instance of MySQL. I know this is not a direct answer to your question, but thought it may be useful anyway...