MyBatis动态SQL的使用

文章列表

MyBatis动态SQL的使用

为什么需要使用动态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;@Overridepublic 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;@Beforepublic void setUp() throws Exception {session = MybatisUtils.getSession();}@Afterpublic void tearDown() throws Exception {session.close();}@Testpublic 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