动态sql SQL语句中的 if choose trim set foreach

动态sql

动态SQL是mybatis的强大特征之一,解决根本不同条件拼接SQL语句的不便
可以借助功能强大的基于OGNL的表达式

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

if 常用于字符的非空判断

xml配置

<!--List<User> queryUsersBySex(String sex);-->
    <select id="queryUsersByGender" resultType="user">
        <!--select * from user where gender = #{gender} -->
        select * from user where 1 = 1
        <if test=" gender != '' and gender != null">
           and gender = #{gender}
        </if>

        <if test=" username != '' and username != null">
            and username like '%' #{username} '%'
        </if>
  </select>

接口和测试类

// 使用动态sql 标签  if
List<User> queryUsersByGender(@Param("gender") String gender,@Param("username") String username);
    
/**
 * 测试动态sql 标签  if
 */
@Test
public void testQueryUsersBySex(){
    List<User> users = userDao.queryUsersByGender("男","大叔");
    for (User user : users) {
        System.out.println(user);
    }
}

choose(when otherwise)

有时候我们不想使用所有的条件,而只是从这些条件里面选择一个使用,Mybatis提供了choose标签
xml文件配置

<!--User queryUserByCondition(String username,String address,int age);-->
    <!--使用choose标签-->
    <select id="queryUserByCondition" resultType="user">
        <!--select * from user where username like '%'#{username} '%' and address = #{address} and age &lt; #{age};-->
        select * from user where 1=1
        <choose>
            <when test="username != null and username != ''">
                and username like '%' #{username} '%'
            </when>
            <when test="address != null and address != ''">
                and address = #{address}
            </when>
            <otherwise>
                age &lt; #{age}
            </otherwise>
        </choose>
    </select>

接口和测试类代码

// 使用动态sql 标签 choose
 List<User> queryUserByCondition(@Param("username") String username, @Param("address") String address,@Param("age") int age);

/**
 * 测试动态sql 标签 choose  when情形是互斥的
 */
@Test
public void testQueryUserByCondition(){
    List<User> user = userDao.queryUserByCondition("花", "河南信阳", 20);
    System.out.println(user);
}

trim(where set)

where可以去掉第一个and
set用于动态更新语句,set标签可以用于动态更新的手段,忽略其他不更新的字段

where

where里面的元素只会在子元素返回人和内容的情况下茶会插入where语句,如果字句开头为AND或者OR,那么where元素就会把他们除去
xml配置

<!--List<User> queryUserByAgeAndDid(@Param("age") int age,@Param("u_did") int u_did);-->
<!--测试动态sql where标签-->
<select id="queryUserByAgeAndDid" resultType="user">
<!-- select * from user where age &lt; #{age} and u_did &lt; #{u_did} -->
select * from user
<where>
   <if test="age != '' and age != null">
       age &lt; #{age}
   </if>
   <if test="u_did &lt;= 2">
      and age &lt; #{age}
   </if>
</where>
</select>

接口和测试类

// 使用动态sql 标签 where
List<User> queryUserByAgeAndDid(@Param("age") int age,@Param("u_did") int u_did);

/**
* 测试动态sql 标签 where
*/
@Test
public void testQueryUserByAgeAndDid() {
   // 年龄 < 20 部门id值 > 2
   List<User> users = userDao.queryUserByAgeAndDid(20, 2);
   for (User user : users) {
       System.out.println(user);
   }
}

set

set元素会动态地在首行插入SET关键字,并且会删除额外的逗号,(这些逗号是在使用条件语句给列赋值的时候引入的)
xml文件代码

<!--void updateUserByNecessary(User user);-->
<!--测试动态标签 set -->
<update id="updateUserByNecessary" parameterType="user">
  <!--update user set username = #{username},age = #{age},address = #{address},gender = #{gender} where id = #{id}-->
    update user
    <set>
        <if test="username != '' and username != null">username = #{username},</if>
        <if test="age &gt; 0"> age = #{age},</if>
        <if test="address != '' and address != null">address = #{address},</if>
        <if test="gender != '' and gender != null">gender = #{gender}</if>
    </set>
    <where>
        <if test="id &gt; 0">
            id = #{id}
        </if>
    </where>
</update>

接口或者测试类代码

// 使用动态sql标签 set
void updateUserByNecessary(User user);

/**
 * 测试动态sql标签 set
 */
@Test
public void testUpdateUserByNecessary() {
    User user = new User("马大神2",0,"","");
    user.setId(4);
    userDao.updateUserByNecessary(user);
}

sql片段
可以吧多次出现的sql封装到一个片段里面,在使用的时候直接进行引用
sql中将重复的sql提取出来,使用时使用标签include直接引用,最终达到sql重复使用的目的
示例如下:xml文件代码

select * from user
<!--把select * from user提取到sql片段中-->
<sql id = "selectUser">
    select * from user
</sql>

<!--根据性别和姓名查询用户信息-->
<select id = "findUserByGenderAndUsername" parameterType = "user" resultType = "user">
	<!--select * from user where gender = #{gender} and username like '%' #{username} '%'-->
    <include refid = "selectUser"/>  <!--相当于 select * from user -->
    <where>
        <if test = "gender != '' and gender != null">
          gender = #{gender}
        </if>
        <if test = "username != '' and username != null">
          username = #{username}
        </if>
    </where>
</select>

foreach

foreach的元素功能非常强大,允许指定一个集合,声明可以在元素体内使用的集合项,(item)和索引(index) 变量 它允许你指定开头与结尾的字符串以及集合项对待之间的分隔符.
你可以将任何迭代对象(如List,Set等) Map对象或者数组对象作为集合参数传递给foreach
尤其是在构建IN条件语句的时候使用的, select * from user where id in(1,3,5,7);
示例代码如下:

<!--
使用动态标签 foreach
List<User> queryUserByIds(List<Integer> ids);
-->
<sql id="selectUser">
select * from user
</sql>
<!--list集合-->
<select id="queryUserByIds" parameterType="list" resultType="user">
<include refid="selectUser"/>
<where>
   <!-- id in(1,3,5,7,9) -->
   <foreach collection="list" item="id" open="id  in (" close=" )" separator=",">
       #{id}
   </foreach>
</where>
</select>
<!--数组-->
<select id="queryUserByIds" parameterType="java.util.Arrays" resultType="user">
<include refid="selectUser"/>
<where>
   <!-- id in(1,3,5,7,9) -->
   <foreach collection="array" item="id" open="id  in (" close=" )" separator=",">
       #{id}
   </foreach>
</where>
</select>

接口代码

// 使用动态标签 foreach
List<User> queryUserByIds(List<Integer> ids);
List<User> queryUserByIds(Integer[] ids);

测试类代码

/**
 * 测试动态sql标签  foreach
 *
 */
@Test
public void testQueryUserByIds() {
    ArrayList<Integer> list = new ArrayList<>();
    // (1,3,11,19)
    list.add(1);
    list.add(3);
    list.add(11);
    list.add(19);
    Integer[] ids = {1, 3, 11, 19};
    //List<User> users = userDao.queryUserByIds(list);
    List<User> users = userDao.queryUserByIds(ids);
    for (User user : users) {
        System.out.println(user);
    }
}

注意事项

1.如果传入的是数组,collection需要使用arrays
2.如果传入的是List集合 collection需要使用 list