MyBatis如何为不同的数据库后端生成不同的SQL [英] MyBatis how can I generate different sql for different database backend

问题描述

我将mybatis-spring 1.2.3和Spring4一起使用来创建Web应用程序.在生产环境中，主要的数据存储是MySQL，但是在单元测试中，我也使用了内存数据库H2.

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在测试和生产中都可以与MySQL和H2一起很好地工作，但是我遇到一个问题，有一天我需要在对MySQL的查询中使用force index(idx1)，这将在作为H2的单元测试中引起语法错误不支持force index.结果，单元测试被完全破坏了.

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.

我想知道MyBatis有什么方法可以处理这种情况? (数据库的类型在测试和生产中有所不同，并且它们对SQL语法的支持也不相同.)

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.)

这是我的映射器文件:

<?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提供了多数据库供应商支持，使您可以根据所使用的数据库供应商来不同地构造SQL.因此，您可以将有问题的代码包装在测试中，例如:

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>

在此处参阅相关文档此处.

这篇关于MyBatis如何为不同的数据库后端生成不同的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！