Spring boot 和 mybatis 学习笔记3--动态sql

今天抽时间将mybatis 的动态sql 学习了一下，参照http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html的文档，动态sql包括

if
choose (when, otherwise)
trim (where, set)
foreach

下面通过例子来一一介绍

一、动态sql 配置

<?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="com.thq.mybatis.dao.UserDynamicDao">
<select id="selectList" resultType="com.thq.mybatis.model.User">
select * from tb_user where true
<if test="id != null">
and id=#{id}
</if>
 <choose>
  <when test="name != null">
 and name like #{name}
  </when>
  <otherwise>
            AND true
             </otherwise>
 </choose>
</select>
<select id="selectListWhere" resultType="com.thq.mybatis.model.User">
select * from tb_user
<where>
<if test="id!= null">
and name=#{id}
</if>
<if test="name != null">
and name like #{name}
</if>

</where>
<!--  
             prefix：前缀覆盖并增加其内容
                    suffix：后缀覆盖并增加其内容
                    prefixOverrides：前缀判断的条件 和where是等价的
                    suffixOverrides：后缀判断的条件
<trim prefix="where" prefixOverrides="AND|OR" >
<if test="id!= null">
AND name=#{id}
</if>
<if test="name != null">
AND name LIKE #{name}
</if>
</trim>
-->
</select>
<update id="updateSet">
UPDATE tb_user 
<set>
<if test="name != null ">name=#{name}</if>
</set>
WHERE id=#{id}
</update>
<!-- collection : array -->
<select id="selectListByIdsArray" resultType="com.thq.mybatis.model.User">
SELECT * FROM tb_user WHERE id in 
<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
#{item}
</foreach>
</select>
<!-- collection : list -->
<select id="selectListByIdsList" resultType="com.thq.mybatis.model.User">
SELECT * FROM tb_user WHERE id in 
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
</select>
<!-- collection: Dao 调用传入的参数为Map -->
<select id="selectListByIdsMap" resultType="com.thq.mybatis.model.User">
SELECT * FROM tb_user WHERE id in 
<foreach item="item" index="index" collection="ids" open="(" separator="," close=")">
#{item}
</foreach>
</select>
</mapper>

二、dao调用接口（mapper接口）

package com.thq.mybatis.dao;

import java.util.List;
import java.util.Map;

import com.thq.mybatis.model.User;

public interface UserDynamicDao {

public List<User> selectList(User user);

public List<User> selectListWhere(User user);

public int updateSet(User user);

public List<User> selectListByIdsArray(Integer[] ids);

public List<User> selectListByIdsList(List<Integer>  ids);

public List<User> selectListByIdsMap(Map<String,Object>  ids);
}

经测试，各接口都输出指定的数据

完毕

更多相关文章

随机推荐