MyBatis, how can I generate different sql for different database backend

Question

I am using mybatis-spring 1.2.3 along with Spring4 to build a web application. The main data warehouse is MySQL in the production environment, but I also use the H2 database in unit testing.

MyBatis works well with MySQL and H2 during testing and production, but I run into the problem that one day I need to use force index(idx1) in a MySQL query, which will lead to a syntax error in unit testing as H2 does not support force index . As a result, unit testing is completely broken.

I want to know if there is a way that MyBatis can handle this situation? (The type of database is different during testing and production, and their support for SQL grammar is not identical.)

Here is my mapper file:

 <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="myproject.mapper.UserMapper"> <select id="getGameUsersForDate" resultType="myproject.dao.domain.GameUser"> select * from game_user force index(idx1) where game_id in <choose> <when test="gameIds.size() > 0"> <foreach item="gameId" collection="gameIds" open="(" separator="," close=")"> #{gameId} </foreach> </when> <otherwise> (null) </otherwise> </choose> and uid in <choose> <when test="uids.size() > 0"> <foreach item="uid" collection="mids" open="(" separator="," close=")"> #{mid} </foreach> </when> <otherwise> (null) </otherwise> </choose> and `date` = #{date} </select> </mapper> 

Answer 1

MyBatis provides multi-db provider support that allows you to structure your SQL differently depending on the database provider you are using. This way you can wrap the problematic code in a test, for example:

 <if test="_databaseId == 'mysql'"> force index(idx1) </if> 

See relevant snippets of documentation here and here .