Binding variables in date INTERVAL functions - H2 DB

While exploring in-memory H2 DB, I came across a strange issue where the bind variable for a date INTERVAL function was throwing JdbcSQLSyntaxErrorException, "Syntax error in SQL statement", expected "string". The same query was working with MySQL but strangely it was failing while switching to H2 DB.

Simplified form of the query in question is:

SELECT ID FROM BLOG WHERE CREATED_DATE >= NOW() - INTERVAL ? DAY;

First suggestion given by StackOverflow was to use the "MySQL Compatibility Mode".

H2 DB by default supports the ANSI SQL standard, but it is not guaranteed that the features will work with all the supported database engines. Hence they have come up with compatibility modes, if you are switching from MySQL, choose MySQL compatibility mode so that the same code and query will continue to work for H2 db too.

Changing the compatibility mode is simple, just append ;MODE=MySQL to the connection string.

Did switching to MySQL solved the issue?

Sadly, switching to MySQL compatibility mode did not work. Like the suggestions from Internet, I was also suspecting compatibility issue between the databases. After spending couple of more minutes, started looking into DB2 spec where it was clearly mentioned, INTERVAL is a literal, and you cannot parameterize a literal. This is applicable for all database engines but strangely MySQL was supporting parameterization, hence this issue never popped up 😃

What's next?

I had two options, one is to re-write the query to support both the database engines and the other to keep two different queries; one for H2 and the other for MySQL. I went with the first option, considering maintainability and to avoid duplicate code. Again I had to spent considerable amount of time to come up with a solution which will work with both the database engines. Finally figured out I can use the function TIMESTAMPADD which is an alias for DATEADD in H2, which works both in MySQL and H2😊 The modified query will look something like this:

SELECT ID FROM BLOG WHERE CREATED_DATE >= TIMESTAMPADD(DAY, -?, NOW());

Also you can consider CAST(? AS INTEGER) if you are using databases other than MySQL, little tricky but serves the purpose! So the query will look like:

SELECT ID FROM BLOG WHERE CREATED_DATE >= NOW() - CAST(? AS INTEGER) * INTERVAL 1 DAY;

Switching between MySQL and H2 with Spring Boot and MyBatis

Not related but some people asked me about this before, how can we switch between database providers in a Spring boot and MyBatis project. With the above example, say you want to keep the queries separate, you can achieve this by having a VendorDatabaseIdProvider bean in your Spring boot configuration.

Creating a VendorDatabaseIdProvider

In your Spring boot project, build a org.apache.ibatis.mapping.VendorDatabaseIdProvider bean

@Bean
VendorDatabaseIdProvider databaseIdProvider() {
        VendorDatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties properties = new Properties();
        properties.put("MySQL", "mysql");
        properties.put("H2", "h2");
        databaseIdProvider.setProperties(properties);
        return databaseIdProvider;
}

Now you can use the providerId in your mapper files.

<select id="selectRecentBlogs" parameterType="map" resultMap="BlogMap" databaseId="mysql">
SELECT ID FROM BLOG WHERE CREATED_DATE >= NOW() - INTERVAL ? DAY
</select>

<select id="selectRecentBlogs" parameterType="map" resultMap="BlogMap" databaseId="h2">
SELECT ID FROM BLOG WHERE CREATED_DATE >= TIMESTAMPADD(DAY, -?, NOW())
</select>

That's it. Now based on the database provider, MyBatis will pick the right selector and execute it against your database engine.

Still having isssues with your query, feel free to post it in the comment box below, we are here to help!