MyBatis动态SQL的使用_【简答题】使用mybatis的动态sql完成: 当客户名称不为空,则只根据客户名称进行客-CSDN博客

本文链接：https://blog.csdn.net/qq_56444564/article/details/130070526

为什么需要使用动态sql?

在实际项目的开发中，开发人员在使用JDBC或其他持久层框架进行开发时，经常需要根据不同的条件拼接SQL语句，拼接SQL语句时还要确保不能遗漏必要的空格、标点符号等，这种编程方式给开发人员带来了非常大的不便，而MyBatis提供的SQL语句动态组装功能，恰能很好地解决这一问题。

动态SQL中的元素

<if>元素

通过一个具体的案例演示单条件判断下<if>元素的使用，案例具体实现步骤如下。

1.引入依赖

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>_20230410</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.3.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>5.2.3.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

2.数据库建表

3.根据数据库字段创建实体类Customer

package cn.hdc.pojo;

public class Customer {
    private int id;
    private String username;
    private String jobs;
    private String phone;

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", jobs='" + jobs + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getJobs() {
        return jobs;
    }

    public void setJobs(String jobs) {
        this.jobs = jobs;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

4.创建CustomerMapper接口，申明要实现的方法 findCustomerByUsernameAndJobs

package cn.hdc.mapper;

import cn.hdc.pojo.Customer;

import java.util.List;

public interface CustomerMapper {
    public List<Customer> findCustomerByUsernameAndJobs(Customer customer);
}

5.根据接口alt+insert快速创建CustomerMapper.xml(映射文件)

<?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="cn.hdc.mapper.CustomerMapper">
    <select id="findCustomerByUsernameAndJobs" resultType="cn.hdc.pojo.Customer">
        select * from t_customer where 1 = 1
        <if test="username != null and username != ''">
            and username like concat('%',#{username},'%')
        </if>
        <if test="jobs != null and jobs != ''">
            and jobs = #{jobs}
        </if>

    </select>
</mapper>

6.创建db.properties,配置数据库连接属性

driver.driver=com.mysql.cj.jdbc.Driver
driver.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false
driver.username=root
driver.password=666666

7.配置mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="db.properties"/>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver.driver}"/>
                <property name="url" value="${driver.url}"/>
                <property name="username" value="${driver.username}"/>
                <property name="password" value="${driver.password}"/>
            </dataSource>
        </environment>
    </environments>

<mappers>
    <mapper class="cn.hdc.mapper.CustomerMapper"></mapper>
</mappers>
</configuration>

8.根据CustomerMapper接口，alt+insert 快速创建测试类：

package cn.hdc.mapper;

import cn.hdc.pojo.Customer;
import cn.hdc.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.List;

import static org.junit.Assert.*;

public class CustomerMapperTest {
    private SqlSession session;

    @Before
    public void setUp() throws Exception {
        session = MybatisUtils.getSession();
    }

    @After
    public void tearDown() throws Exception {
        session.close();
    }

    @Test
    public void findCustomerByUsernameAndJobs() {
        Customer customer = new Customer();
        //customer.setUsername("jack");
        customer.setJobs("teacher");
        List<Customer> customers = session.selectList("cn.hdc.mapper.CustomerMapper.findCustomerByUsernameAndJobs", customer);
        for (Customer c : customers) {
            System.out.println(c);
        }
    }
}

运行结果：

项目结构：

<choose>、<when>、<otherwise>元素

<choose><when><otherwise>使用场景

在使用<if>元素时，只要test属性中的表达式为true，就会执行元素中的条件语句，但是在实际应用中，有时只需要从多个选项中选择一个去执行。例如下面的场景︰“当客户名称不为空，则只根据客户名称进行客户筛选;当客户名称为空，而客户职业不为空，则只根据客户职业进行客户筛选。当客户名称和客户职业都为空，则要求查询出所有电话不为空的客户信息。"针对上面情况，使用<if>元素进行处理是不合适的。MyBatis提供了<choose>、<when>、<otherwise>元素进行处理，这三个元素往往组合在一起使用，作用相当于Java语言中的if...else if...else。

代码结构不变，只是在上一个例子中CustomerMapper.xml添加如下代码：

在接口中申明方法：

添加测试方法：

运行结果：

<where>、<trim>元素

<where>元素

上述代码配置中，<where>元素会自动判断由组合条件拼装的SQL语句，只有<where>元素内的某一个或多个条件成立时，才会在拼接SQL中加入where关键字，否则将不会添加;即使where之后的内容有多余的“AND”或“OR”,<where>元素也会自动将他们去除。

<trim>元素

<trim>元素用于删除多余的关键字，它可以直接实现<where>元素的功能<trim>元素包含4个属性。

上述配置代码中，<trim>元素的作用是去除一些多余的前缀字符串，它的prefix属性代表的是语句的前缀(where )，而prefixOverrides属性代表的是需要去除的前缀字符串(SQL中的“AND”或“OR”)。

更新操作

<set>元素

在Hibernate框架中，如果想要更新某一个对象，就需要发送所有的字段给持久化对象，然而在实际应用中，大多数情况下都是更新某一个或几个字段。如果更新的每一条数据都要将其所有的属性都更新一遍，那么执行效率是非常差的。为了解决更新数据的效率问题，MyBatis提供了<set>元素。<set>元素主要用于更新操作，它可以在动态SQL语句前输出一个SET关键字，并将SQL语句中最后一个多余的逗号去除。<set>元素与<if>元素结合可以只更新需要更新的字段。

代码示例：

CustomerMapper.xml中添加以下代码：

    <update id="updateCustomerBySet" parameterType="cn.hdc.pojo.Customer">
        update t_customer
        <set>
            <if test="username!=null and username!=''">
                username=#{username},
            </if>
            <if test="jobs!=null and jobs!=''">
                jobs=#{jobs},
            </if>
            <if test="phone!=null and phone!=''">
                phone=#{phone},
            </if>
        </set>
        where id = #{id}
    </update>

接口中声明此方法：

创建测试方法：

运行结果：

第二种方法使用<trim>

 <update id="updateCustomerBySet" parameterType="cn.hdc.pojo.Customer">
            update t_customer
            <trim prefix="set" suffixOverrides=",">
                <if test="username!=null and username!=''">
                    username=#{username},
                </if>
                <if test="jobs!=null and jobs!=''">
                    jobs=#{jobs},
                </if>
                <if test="phone!=null and phone!=''">
                    phone=#{phone},
                </if>
            </trim>
            where id = #{id}
        </update>

在映射文件中使用<set>元素和<if>元素组合进行update语句动态SQL组装时，如果<set>元素内包含的内容都为空，则会出现SQL语法错误。因此，在使用<set>元素进行字段信息更新时，要确保传入的更新字段不能都为空。
除了使用<set>元素外，还可以通过<trim>元素来实现更新操作。其中，<trim>元素的prefix属性指定要添加的<trim>元素所包含内容的前缀为set ,suffixOverrides属性指定去除的<trim>元素所包含内容的后缀为逗号。

复杂查询操作

<foreach>元素的属性

<collection>属性的取值

在遍历参数时，<collection>属性的值是必须指定的。不同情况下，该属性的取值也是不一样的，主要有以下三种情况:List类型、数值类型、Map类型。

若入参为单参数且参数类型是一个List , collection属性值为list。
若入参为单参数且参数类型是一个数组，collection属性值为array。
若传入参数为多参数，就需要把参数封装为一个Map进行处理，collection属性值为Map。若传入参数为多参数，就需要把参数封装为一个Map进行处理，collection属性值为Map。

<foreach>元素迭代数组

例如，要从数据表t_customer中查询出id为1、2、3的客户信息，就可以利用数组作为参数，存储id的属性值1、2、3，并通过<foreach>元素迭代数组完成客户信息的批量查询操作。

CustomerMapper.xml

CustomerMapper接口：

测试方法：

运行结果：

<foreach>元素迭代List

CustomerMapper.xml

CustomerMapper接口：

创建测试方法：

运行结果：

<foreach>元素迭代Map

CustomerMapper.xml

CustomerMapper接口：

创建测试方法：

运行结果：

基于上述知识点实现一个：学生信息查询系统

功能：

多条件查询

当用户输入的学生姓名不为空，则只根据学生姓名进行学生信息的查询;
当用户输入的学生姓名为空，而学生专业不为空，则只根据学生专业进行学生的查询;
若学生姓名和学生专业都为空，则则要求查询出所有学号不为空的学生信息；

单条件查询

查询出所有id值小于5的学生的信息;

1.pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>_20230418</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.3.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>5.2.3.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

</project>

2.db.properites

driver.driver=com.mysql.cj.jdbc.Driver
driver.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false
driver.username=root
driver.password=666666

3.创建数据库，建表

4. 根据数据库表字段生成实体类Student

package cn.hdc.pojo;

public class Student {
    private int id;
    private String name;
    private String major;
    private String sno;

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

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getMajor() {
        return major;
    }

    public void setMajor(String major) {
        this.major = major;
    }

    public String getSno() {
        return sno;
    }

    public void setSno(String sno) {
        this.sno = sno;
    }
}

5.创建接口，声明要实现的方法StudentMapper

package cn.hdc.mapper;

import cn.hdc.pojo.Student;

import java.util.List;

public interface StudentMapper {
    public Student findStudentByNameAndMajor(Student student);

    public Student findStudentByList(List<Integer> list);
}

6.根据接口创建映射文件StudentMapper.xml

<?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="cn.hdc.mapper.StudentMapper">
    <select id="findStudentByNameAndMajor" resultType="cn.hdc.pojo.Student">
        select * from dm_student
        <where>
            <choose>
                <when test="name != null and name != ''">
                    and name like concat('%',#{name},'%')
                </when>
                <when test="major != null and major != ''">
                    and major = #{major}
                </when>
                <otherwise>
                    and sno is not null
                </otherwise>
            </choose>
        </where>
    </select>

    <select id="findStudentByList" resultType="cn.hdc.pojo.Student">
        select *
        from dm_student
        where id in
        <foreach collection="list" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>
</mapper>

7.配置到mybaits-config.xml，扫描xxxMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 环境配置 -->
    <!-- 加载类路径下的属性文件 -->
    <properties resource="db.properties"/>

    <settings>
        <!-- 全局启用或禁用延迟加载。当禁用时，所有关联对象都会即时加载。默认：true  -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 当启用时，有延迟加载属性的对象在被调用时将会完全加载任意属性 . 默认：true-->
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>


    <!-- 数据库连接相关配置 ,db.properties文件中的内容-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver.driver}"/>
                <property name="url" value="${driver.url}"/>
                <property name="username" value="${driver.username}"/>
                <property name="password" value="${driver.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="cn.hdc.mapper"/>
    </mappers>
</configuration>

8.编写测试类：

package cn.hdc.mapper;

import cn.hdc.pojo.Student;
import cn.hdc.utils.MybaitsUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;


public class StudentMapperTest {

    private SqlSession session;

    @Before
    public void setUp() throws Exception {
        session = MybaitsUtils.getSession();
    }

    @After
    public void tearDown() throws Exception {
        session.commit();
        session.close();
    }

    @Test
    public void findStudentByNameAndMajor() {
        Student student = new Student();
        student.setName("张三");
        student.setMajor("英语");
        List<Student> students = session.selectList("cn.hdc.mapper.StudentMapper.findStudentByNameAndMajor", student);
        for (Student stu : students) {
            System.out.println(stu);
        }
    }

    @Test
    public void findStudentByList() {
        List<Integer> list = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            list.add(i);
        }
        List<Student> students = session.selectList("cn.hdc.mapper.StudentMapper.findStudentByList", list);
        for (Student stu : students) {
            System.out.println(stu);
        }
    }
}

9.项目目录结构：