mybatis动态sql的使用(以及特殊查询)

虽然动态sql 和 映射xml的关系很密切，但是这里还是把它分出来把。

文章目录

• ​​if​​
• ​​choose、when、otherwise​​
• ​​trim、where、set​​

• ​​where​​
• ​​update语句的实际例子​​

• ​​foreach​​

• ​​foreach遍历list​​
• ​​foreach遍历数组​​

• ​​script​​
• ​​bind​​

• ​​date日期处理(CDATA的使用)​​
• ​​日期格式化的兼容处理​​

• ​​resultType的使用​​
• ​​resultMap和resultType 报错对比​​

• ​​其他​​

• ​​update的时候一定要当心​​
• ​​mybatis中入参日期，数据库字段是date的写法​​
• ​​if的判断​​

• ​​string的写法​​

• ​​Integer的写法​​
• ​​Boolean类型的写法​​

• ​​特殊查询​​

• ​​isNull的特殊查询​​

• ​​其他​​

• ​​sql语句中不要有分号​​

if

demo1：

<if test="author != null and author.name != null">
  AND author_name like #{author.name}
</if>

choose、when、otherwise

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

还是上面的例子，但是策略变为：传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找的情形。若两者都没有传入，就返回标记为 featured 的 BLOG（这可能是管理员认为，与其返回大量的无意义随机 Blog，还不如返回一些由管理员挑选的 Blog）。

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

trim、where、set

where

where标签可以解决一个臭名昭著的问题，就是如果后面条件都不成立，where就是多余的了：

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

prefixOverrides 属性会忽略通过管道符分隔的文本序列（注意此例中的空格是必要的）。上述例子会移除所有 prefixOverrides 属性中指定的内容，并且插入 prefix 属性中指定的内容。

用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。比如：

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

这个例子中，set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。

来看看与 set 元素等价的自定义 trim 元素吧：

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

注意，我们覆盖了后缀值设置，并且自定义了前缀值。

update语句的实际例子

<update id="updateUser" parameterType="UserBean">
  update t_user set
  <trim prefix="set" suffixOverrides=",">
    <if test="user.name != null">
      user.name = #{user.name},
    </if>
    <if test="user.sex != null">
      user.sex = #{user.sex},
    </if>
    <if test="user.age != null">
      user.age = #{user.age},
    </if>
  </trim>
  WHERE id=#{id}
</update>

foreach

foreach遍历list

例如list为invoiceList,多条件并列，可以这样写。 但是有点需要注意，就是如果list.size>0 ，但是item的数据不合规，会多出一个and，所以要用代码控制item数据的合规。

<if test="invoiceList != null and invoiceList.size() > 0">
  and
  <foreach collection="invoiceList" index="index" item="item" open="(" close=")" separator=" or ">
    <if test="item.invoiceCode != null  and item.invoiceCode !='' and item.invoiceNumber != null  and item.invoiceNumber !='' ">
      (info.INVOICE_CODE=#{item.invoiceCode} and info.INVOICE_NUMBER =#{item.invoiceNumber})
    </if>
  </foreach>
</if>

foreach遍历数组

要注意数组是length，list是size()。
而且不要少了and。

<if test="types!= null and types.length >0 ">
    and type in
    <foreach collection="types" open="(" separator="," close=")" item="type">
        #{type}
    </foreach>
</if>

注：使用foreach的时候，如果list为空，最好不执行这个mapper，因为很容易报错。

script

这个感觉不如在xml中写着舒服。
要在带注解的映射器接口类中使用动态 SQL，可以使用 script 元素。比如:

@Update({"<script>",
 "update Author",
 "  <set>",
 "    <if test='username != null'>username=#{username},</if>",
 "    <if test='password != null'>password=#{password},</if>",
 "    <if test='email != null'>email=#{email},</if>",
 "    <if test='bio != null'>bio=#{bio}</if>",
 "  </set>",
 "where id=#{id}",
 "</script>"})
void updateAuthorValues(Author author);

bind

这个用的比较少，我就没用过，耳目一新啊。
bind 元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文。比如：

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

date日期处理(CDATA的使用)

日期范围：
一种是直接写死,代码量最少。
​​​create_date between sysdate-1 and sysdate​​

另一种是写为可配置的，那么需要dto中增加startDate和endDate这2个字段，并在查询前设置值，代码中这么写。

<if test="startDate!=null>
    <![CDATA[  and create_date>=#{startDate} ]]>
</if>
<if test="excEndTime!=null>
    <![CDATA[  and create_date<=#{endDate} ]]>
</if>

当然了,如果日期格式的，也可能需要格式化下：

这里是伪代码：
startDate >= #{startDate} 00:00:00
endDate <= #{endDate} 23:59:59

动态sql官网文档地址：
​​​https://mybatis.org/mybatis-3/zh/dynamic-sql.html​​

日期格式化的兼容处理

例如，有时会报错：
ORA-01830: date format picture ends before converting entire input string

SELECT to_date('2022-07-20 00:00:00','yyyy-mm-dd') FROM dual;
这是会报错的。

解决方案：
之前的写法：

<if test="createDate != null">
  CREATE_DATE = to_date(#{createDate,jdbcType=VARCHAR},'yyyy-mm-dd'),
</if>

兼容性写法：

<if test="createDate != null">
    <choose>
        <when test="19==createDate.length">
            CREATE_DATE = to_date(#{createDate,jdbcType=VARCHAR},'yyyy-mm-dd hh24:mi:ss'),
        </when>
      <otherwise>
           CREATE_DATE = to_date(#{createDate,jdbcType=VARCHAR},'yyyy-mm-dd'),
        </otherwise>
    </choose>
</if>


## 其他
### resultMap和resultType
结果映射的2大元素。
都是非常常用的。
#### resultMap的使用
机制：在<select>标签中根据id来查抄对应resultMap。
1、用<resultMap>标签中定义。
2、在<select>标签中根据id来查找对应resultMap。

例子：
```xml
定义：
<resultMap type="com.test.Product" id="productBaseMap">
        <id column="id" property="id" />
        <result column="product_name" property="productName" />
</resultMap>

引用：
<select id="queryProduct" resultMap="productBaseMap">
</select>

resultType的使用

机制： 直接查找对应的类
这个不需要在mapper.xml中定义，只需要引用的类型存在即可。

自定义类型：
<select id="queryProduct" resultType="com.test.Product"></select>

基本类型：
<select id="queryTotal" resultType="java.lang.Integer"></select>

注：
resultType的基本类型虽然可以简写，但是建议写全称。因为全称肯定是通用的，而且不容易出错。

resultMap和resultType 报错对比

如果resultType找不到，报错例子：
Result Maps collection does not contain value for java.lang.Integer

如果resultMap找不到，报错例子：
Cannot find class: UserBaseMap。

其他

update的时候一定要当心

先来段错误的代码：

<update id="updateByProductId">
    update T_PRODUCT
    <set>
        <if test="totalAmount != null and totalAmount!=''">
            TOTAL_AMOUNT = #{totalAmount},
        </if>
    </set>
    <where>
      <if test="productId != null and productId!=''">
          and PRODUCT_ID = #{productId}
      </if>
    </where>
</update>

发现问题在哪了吗?
update的where条件竟然用了​​​<if>​​。如果一不小心，入参对象中没有设置productId，那么相当于update整张表，想想就知道有多恐怖了。

所以update的判定条件不要用if标签，应该直接写固定了：

<update id="updateByProductId">
    update T_PRODUCT
    <set>
        <if test="totalAmount != null and totalAmount!=''">
            TOTAL_AMOUNT = #{totalAmount},
        </if>
    </set>
    where PRODUCT_ID = #{productId}
</update>

这样如果productId为空，语句报错，不会有update整个表的风险。

mybatis中入参日期，数据库字段是date的写法

<if test="startDate != null and startDate != ''">
    AND CREATE_DATE ">>= to_date(#{startDate},'yyyy-MM-dd HH24:MI:SS')
</if>
<if test="endDate != null and endDate != ''">
    AND CREATE_DATE <= to_date(concat(#{endDate},'23:59:59'),'yyyy-MM-dd HH24:MI:SS')
</if>

if的判断

string的写法

这个最常见，最简单，没什么坑：

<if test="username != null and username!=''">
    and username = #{username,jdbcType=VARCHAR}
</if>

Integer的写法

Integer是有坑的。

错误写法(因为如果传入的是0，会当做''来处理，也就是判断不会成功)：
<if test="times != null and times !=''">  
    and times=#{times,jdbcType=INTEGER}  
</if>  

那么如果要传入0，怎么办呢?  正确写法：
<if test="times != null and times !='' or times==0">  
    and times=#{times,jdbcType=INTEGER}  
</if>

Boolean类型的写法

特殊查询

有些特殊查询，可以额外的添加字段来实现。例如字段后面加上后缀，然后进行特殊处理。
NotEq
NotIn
IsNull
IsNotNull

需要手动维护，有一定工作量。 但是至少解决了特殊查询不好实现的问题。

isNull的特殊查询

java代码要用Boolean类。
​​​private Boolean codeIsNull;​​

<if test="codeIsNull != null and codeIsNull==true >
    and CODE is null
</if>

其他

sql语句中不要有分号

这是很基础的知识了，因为每个语句都是单独的，所以根本不用加分号。如果加了，会认为是语句的一部分。报错：
Cause: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character