java学习总结（五）MyBatis动态sql

一、动态Sql

通过MyBatis提供的各种标签方法实现动态拼接Sql。

这些标签类似于JSTL标签，可以写控制语句动态的拼接Sql。

二、if标签、where标签

select * form user where name like '%张%' and age=23 and gener='男';

select * form user where age=23 and gender='男';

select * form user where gender='男';

select * form user;

解决方案：

select * from user where name like '%张%' and gender='男'；

select * from user where and gender='男';

思路判断这个搜索条件是不是空，不是空就把and xxx拼接上，但是where后面的第一个条件是没有and。

if（name ！=空） {

and name=?

}

if（age ！=空） {

and age=?

}

if（gender！=空） {

and gender=?

}

SELECT * FROM student WHERE 1=1; -- 1=1 是true

1=1是不需要加的，这里加上1=1为的就是把where第一条件条件是不需要加and的，就把第一个条件做了特殊处理，后面的条件都可以统一为都加and。

select * from user where and name like '%张%' and age=23 and gender='男'；

select * from user where 1=1 and name like '%张%' and age=23 and gender='男'；

select * from user where 1=1 and age=23;

select * from user where 1=1 and gener='男';

select * from user where 1=1 limit ?,?

<select id="selectByCondition" parameterType="Student" resultMap="studentMap">
    SELECT <include refid="studentColumns"/>
    FROM student
    <where>
        <if test="name!=null and name!='' ">
            AND name LIKE concat('%', #{name} ,'%')
        </if>
        <if test="age!=null">
            AND age=#{age}
        </if>
        <if test="gender!=null and gender!='' ">
            AND gender=#{gender}
        </if>
    </where>

</select>

@Test
public void testselectByCondition() throws IOException {
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    Student stu = new Student();
    stu.setName("王");
    stu.setGender("女");
    List<Student> list = sqlSession.selectList("student.selectByCondition", stu);
    for (Student student : list) {
        System.out.println(student);
    }
}

三、set标签

update(User user)

update user set name=?,age=?,gender=? where id=?;

要更新的User对象原来数据: id=1，name="张三" age=23 gender="男"

update传递过来User对象: id=1, name="张三1" age=24 gender=null

gender没有设置值，用户不想修改

update user set name="张三1",age=24 where id=1;

在更新数据有些时候希望User里面只有属性值有数据的才去做更新操作，属性没有数据的时候不做任何操作还是原来的值。

update user set name=?,age=?,gender=? where id=?;

update user set name=?,age=? where id=?;

update user set age=?,gender=? where id=?;

update user set name=? where id=?;

在根据条件拼接sql时候有一个问题，如果想统一处理，最后一个“,”处理起来麻烦。

if（name ！=空） {

name=?,

}

if（age ！=空） {

age=?,

}

if（gender！=空） {

gender=?,

}

<update id="updateCondition" parameterType="Student">
    UPDATE student
    <set>
        <if test="name!=null and name!=''">
            name=#{name},
        </if>
        <if test="age!=null">
            age=#{age},
        </if>
        <if test="gender!=null and gender!=''">
            gender=#{gender},
        </if>
    </set>
    WHERE id=#{id}
</update>

@Test
public void testUpdateCondition() {
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    Student student = new Student();
    student.setId(1);
    student.setName("小李1");
    student.setAge(25);
    //student.setGender("女");
    int count = sqlSession.update("student.updateCondition", student);
    System.out.println(count);
    sqlSession.commit();
}

四、foreach标签

处理数组或者集合，MyBatis使用foreach标签解析。

做一个批量删除的操作，参数可以是集合或是是数组的形式传递过来：int[] idArray = {1,2,3,4};、List idList;

public int deleteAllByArray(int[] idArray);

public int deleteAll(List idList);

delete from student where id in(23,34,5,6);

delete form user where id=1 or id=2 or id=3 or id=4;

<!-- delete from student where id in(1,2,3,4) -->
<delete id="deleteAllByArray" >
    DELETE FROM student
    WHERE id IN
    <foreach collection="array" open="(" item="id" close=")" separator=",">
        #{id}
    </foreach>
</delete>

<!--Parameter 'array' not found. Available parameters are [list]-->
<delete id="deleteAllByList" >
    DELETE FROM student
    WHERE id IN
    <foreach collection="list" open="(" item="id" close=")" separator=",">
        #{id}
    </foreach>
</delete>

@Test
public void testDeleteAllByArray() {
   int[] array = {66};
   SqlSession sqlSession = MyBatisUtil.getSqlSession();;
   int count = sqlSession.delete("student.deleteAllByArray", array);
   System.out.println("count: " + count);
   sqlSession.commit();
   sqlSession.close();
}
@Test
public void testDeleteAllByList() {
   List<Integer> list = new ArrayList<Integer>();
   list.add(68);
   list.add(69);
   SqlSession sqlSession = MyBatisUtil.getSqlSession();
   int count = sqlSession.delete("student.deleteAllByList", list);
   System.out.println("count: " + count);
   sqlSession.commit();
   sqlSession.close();
}

五、choose、when、otherwise

if----else if----else if ----else

如果用户填写了名字，这个搜索就按照名字来搜索，你即使填写了别的条件也不去拼接，

如果没有填写名字，填写了年龄，就按照年龄来搜索，

如果名字和年龄都没有填写，但是填写了性别就按照性别来查找

name、age、gender搜索时候只能按照其中一个来搜索，但是优先级name>age>gender

List selectByCondition(Student student);

<select id="selectByCondition2" parameterType="Student" resultMap="studentMap">
   SELECT <include refid="studentColumns"/>
   FROM  student
   WHERE
      <choose>
         <when test="name!=null and name!=''">
            name LIKE concat('%', #{name}, '%')
         </when>
         <when test="age!=null"> 
            age=#{age}
         </when>
         <when test="gender!=null and gender!=''">
            gender=#{gender}
         </when>
         <otherwise>
         </otherwise>
      </choose>
</select>

@Test
public void testSelectByCondition1() {
    Student student = new Student();
    //student.setName("王");
    student.setAge(23);
    student.setGender("女");
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    List<Student> list = sqlSession.selectList("student.selectByCondition2", student);
    for (Student s : list) {
        System.out.println(s);
    }
}

六、trim

MyBatis 中的 标签是一个特殊的动态 SQL 元素，它用于在动态生成 SQL 语句时自动添加或删除前后缀，以及在多个元素之间添加分隔符。这在构建包含多个可选部分的 SQL 语句时非常有用，例如在 INSERT、UPDATE 或 WHERE 子句中。

<trim prefix="" prefixOverrides="" suffix="" suffixOverrides="" ></trim>

//insert into student(id,name,age,gender) values(1,'zhangsan',23,'男');
<insert id="addSelective" parameterType="Student">
    INSERT INTO student
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null">id,</if>
        <if test="name != null and name != ''">name,</if>
        <if test="age != null">age,</if>
        <if test="gender != null and gender != ''">gender,</if>
    </trim>
    VALUES
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null">#{id},</if>
        <if test="name != null and name != ''">#{name},</if>
        <if test="age != null">#{age},</if>
        <if test="gender != null and gender != ''">#{gender},</if>
    </trim>
</insert>

@Test
public void testAddSelective() {
    Student student = new Student();
    //student.setName("王");
    student.setAge(23);
    student.setGender("女");
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    int count = sqlSession.insert("student.addSelective", student);
    System.out.println("count: " + count);
    sqlSession.commit();
    sqlSession.close();
}

在这个例子中， 标签用于在字段列表和值列表中自动添加圆括号，并且通过 suffixOverrides="," 属性来删除每个元素后的逗号，以避免在 SQL 语句的末尾出现多余的逗号。

示例 2：更新语句中的选择性字段

<update id="updateSelective" parameterType="Student">
    UPDATE student
    SET
    <trim suffixOverrides=",">
        <if test="name != null and name != ''">
            name = #{name},
        </if>
        <if test="age != null">
            age = #{age},
        </if>
        <if test="gender != null and gender != ''">
            gender = #{gender},
        </if>
    </trim>
    WHERE id = #{id}
</update>

@Test
public void testUpdateSelective() {
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    Student student = new Student();
    student.setId(51);
    student.setName("小李1");
    student.setAge(25);
    //student.setGender("女");
    int count = sqlSession.update("student.updateSelective", student);
    System.out.println(count);
    sqlSession.commit();
}

在这个例子中， 标签用于在 SET 子句中自动删除最后一个逗号，以避免 SQL 语句错误

示例 3：动态 WHERE 子句

<select id="selectAll" parameterType="Student" resultType="Student">
    SELECT * FROM student
    <trim prefix="WHERE" prefixOverrides="AND">
        <if test="name != null and name != ''">
            AND name LIKE concat('%', #{name}, '%')
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
    </trim>
</select>

标签是 MyBatis 动态 SQL 的一个非常有用的工具，它可以帮助开发者编写更简洁、更灵活的 SQL 映射文件