Developing against H2 Database: Lesson Learned

While my production database is MS SQL Server, I have been using H2 database for rapid local development. I wrote my DDL scripts as “Transact-SQL” as possible so that I don’t need to maintain multiple DDL scripts for each database. So far, it has been working out great. My web development runs against H2’s file-based database because I want my data to persist until I manually wipe them off.

That said, I also realized that H2 does behavior a little differently from MS SQL Server. For example, I have the following Hibernate’s HQL query:-

public Collection<Request> getRequestsWithMatchingStatus(Collection<String> statusNames) {
    return sessionFactory.getCurrentSession()
            .createQuery("from Request r where r.status.name in (:statusNames)")
            .setParameterList("statusNames", statusNames)
            .list();
}

If statusNames is an empty collection, H2 will work just fine. However, MS SQL Server will throw the following exception:-

Caused by: java.sql.SQLException: Incorrect syntax near ')'.
	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)

My test cases didn’t catch this error because they run against H2’s in-memory database.

Of course, the fix is as easy as performing a check on the collection size first:-

public Collection<Request> getRequestsWithMatchingStatus(Collection<String> statusNames) {
    if (statusNames.isEmpty()) {
        return new ArrayList<Request>();
    }</code>
<code>
</code>
<code>    return sessionFactory.getCurrentSession()
            .createQuery("from Request r where r.status.name in (:statusNames)")
            .setParameterList("statusNames", statusNames)
            .list();
}

That said, H2 database allows me to do local development really fast, but I also need to make sure I test it against MS SQL Server from time to time before deploying into production.