[大数据]MyBatis动态sql

这篇文章介绍了MyBatis-动态sql

动态 SQL 是 MyBatis 的强大特性之一。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

一.标签

if标签：条件判断
choose、when、otherwise标签：选择
trim、where、set标签
foreach标签： 遍历集合
sql、include标签：定义、使用代码片段

二.动态sql语句

准备

Student类,数据库中与之对应

package com.hem.pojo;

public class Student {
    private Integer id;
    private String name;
    private String email;
    private Integer age;

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", age=" + age +
                '}';
    }

    public Student(String name, String email, Integer age) {
        this.name = name;
        this.email = email;
        this.age = age;
    }

    public Student(Integer id, String name, String email, Integer age) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.age = age;
    }

    public Student() {
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

1.查询

1.1多条件动态查询

使用< where > + < if >
if进行条件判断,where 可以去掉多余的and

示例
StudentMapper接口：

package com.hem.mapper;

import com.hem.pojo.Student;

import java.util.List;

public interface StudentMapper {

    List<Student> selectDynamic(Student student);
}

StudentMapper.xml文件:

<select id="selectDynamic" resultType="student">
        select id,name,email,age
        from student
        <where>
            <if test=" name != null and name != '' ">
                name like concat('%',#{name},'%')
            </if>
            <if test=" email != null and email != '' ">
                and email like concat('%',#{email},'%')
            </if>
            <if test=" age != null ">
                and age = #{age}
            </if>
        </where>;
    </select>

测试类：

    @Test
    public void testinsertDynamic() throws IOException {
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        SqlSession sqlSession = factory.openSession();

//        获取StudentMapper接口的代理对象
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student student = new Student("小","1",18);
        List<Student> list = studentMapper.selectDynamic(student);
        list.forEach(student1 -> System.out.println(student1));
        sqlSession.close();
    }

测试结果：

1.2单条件-多条件动态查询（从多条件中选一个进行查询)

使用< where >,< choose >,< when >
choose:相当于switch
when:相当于case

示例
StudentMapper接口:

public interface StudentMapper {
    List<Student> selectByCondition(Student student);
}

StudentMapper.xml:

<select id="selectByCondition" resultType="student">
        select id,name,email,age
        from student
        <where>
            <choose>  <!-- 相当于switch -->
                <when test="age != null"> <!-- 相当于case -->
                    age = #{age}
                </when>
                <when test="name != null and name!='' ">
                    and name like concat('%',#{name},'%')
                </when>
                <when test="email != null and email!='' ">
                    and email like concat('%',#{email},'%')
                </when>
            </choose>;
        </where>
    </select>

2.修改

使用< set> + < if >
if进行条件判断,set可以去掉多余的逗号’ , ’

示例
StudentMapper接口：

package com.hem.mapper;

import com.hem.pojo.Student;

import java.util.List;

public interface StudentMapper {
    int updateDynamic(Student student);
}

StudentMapper.xml文件:

    <update id="updateDynamic" parameterType="student">
        update student
        <set>
            <if test=" name != null and name != '' ">
                name = #{name},
            </if>
            <if test=" email != null and email != '' ">
                email = #{email},
            </if>
            <if test=" age != null ">
                age = #{age},
            </if>
        </set>
        where id = #{id};
    </update>

3.定义使用代码片段

< sql >：定义代码片段
< include >：使用代码片段

<!--    定义代码片段-->
    <sql id="allColumns">
        id,name,email,age
    </sql>
    
<!--    使用代码片段-->
   <include refid="allColumns"/>

例子

    <sql id="allColumns">
        id,name,email,age
    </sql>
    
<select id="selectAll" resultType="student">
        select <include refid="allColumns"/>
        from student;
    </select>

202210162215日