I'm using mybatis-spring 1.2.3 together with Spring4 to create a web application. The main data storage is MySQL in production environment, but I also use in-memory database H2 in unit testing.
MyBatis works well with both of MySQL and H2 in testing and production, but I come across a problem that one day I need to use force index(idx1) in a query to MySQL, which will cause a syntax error in unit testing as H2 hasn't supported force index. As the result, the unit testing is totally broken.
I want to know is there any way that MyBatis can handle such a situation? (type of database differs in testing and production, and their support of SQL grammar are 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>
MyBatis provides multi-db vendor support that allows you to structure your SQL differently depending on the database vendor that you use. So you could wrap the problematic code in a test such as:
<if test="_databaseId == 'mysql'">
force index(idx1)
</if>