MyBatis how can I generate different sql for different database backend

Question

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>

Bogdan · Accepted Answer · 2016-01-30 20:51:34Z

5

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>

See the relevant pieces of documentation here and here.

answered Jan 30, 2016 at 20:51

Bogdan

24.7k3 gold badges74 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dyng Over a year ago

Thank you for your suggestion, it works like a charm. But as a side node, for user of mybatis-spring, setting databaseIdProvider in mybatis-config.xml seems have no effect (I haven't found why), rather setting databaseIdProvider in SqlSessionFactoryBean works.

Gio Italy · Accepted Answer · 2024-02-08 17:25:29Z

0

to add databaseIdProvider in Mybatis XML configuration

   <databaseIdProvider type="DB_VENDOR">
    <property name="Oracle" value="oracle"/>
    <property name="H2" value="h2"/>
</databaseIdProvider>

answered Feb 8, 2024 at 17:25

Gio Italy

1611 silver badge7 bronze badges

Collectives™ on Stack Overflow

MyBatis how can I generate different sql for different database backend

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related