MyBatis笔记 | 详解动态SQL

作者: 一颗白菜_ | 来源:发表于2019-12-11 13:20 被阅读0次

我们想，对于SQL映射文件中的sql语句，能够根据传入的值的不同来动态的拼接sql语句。此时就可以使用到动态SQL。

环境的准备

新建EmployeeMapperDynamicSQL接口：

package com.cerr.mybatis.dao;

import com.cerr.mybatis.Employee;

import java.util.List;

public interface EmployeeMapperDynamicSQL {

}

新建EmployeeMapperDynamicSQL.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="com.cerr.mybatis.dao.EmployeeMapperDynamicSQL">
    
</mapper>

使用if标签实现表达式判断

我们可以使用<if>标签来对表达式进行判定，然后作出对应的SQL拼接。<if>标签有一个test属性用来判断表达式用的，这里的表达式采用的是OGNL表达式，对于OGNL表达式的介绍可以参考该文章的部分内容：Struts2学习笔记 | 值栈和OGNL。

扩展

我们应该注意转义字符，例如""应该写为""。

我们在接口中新增一个方法：

    //携带了哪个字段，查询条件就带上这个字段
    public List< Employee > getEmpsByConditionIf(Employee employee);

测试方法：

package com.cerr.mybatis;
import com.cerr.mybatis.dao.*;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MyBatisTest {

    //获取SQLSessionFactory
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void testDynamicSql() throws IOException {
        SqlSessionFactory factory = getSqlSessionFactory();
        SqlSession session = factory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(1,"%e%",null,null);
            List<Employee> employees = mapper.getEmpsByConditionIf(employee);
            for(Employee e : employees){
                System.out.println(e);
            }
        }finally {
            session.close();
        }
    }
}

在SQL映射文件中的配置：

    <!-- 查询员工，要求：携带了哪个字段，查询条件就带上这个字段 -->
    <select id="getEmpsByConditionIf" resultType="com.cerr.mybatis.Employee">
        select * from tb1_employee
        where
            <if test="id!=null">
                id=#{id}
            </if>
            <if test="lastName!=null and lastName!=''">
                and last_name like #{lastName}
            </if>
            <if test="email!=null and email.trim()!=&quot;&quot;">
                and email=#{email}
            </if>
            <!-- ognl会进行字符串与数字的转换判断 -->
            <if test="gender==0 or gender==1">
                and gender=#{gender}
            </if>
    </select>

但是这个配置文件的话，有个问题，每次传入的id必须不为空，如果id为空的话，假设后面任意一个不为空（例如lastName），则sql语句为select * from tb1_employee where and last_name like ?，很明显多了个and，该sql语句是错误的，我们可以使用下面的where标签来改善这个问题。

使用<where>标签，MyBatis就会将<where>标签里面拼接的sql中多余的and和or去掉。
正确用法：

    <select id="getEmpsByConditionIf" resultType="com.cerr.mybatis.Employee">
        select * from tb1_employee
        <where>
            <if test="id!=null">
                id=#{id}
            </if>
            <if test="lastName!=null and lastName!=''">
                and last_name like #{lastName}
            </if>
            <if test="email!=null and email.trim()!=&quot;&quot;">
                and email=#{email}
            </if>
            <!-- ognl会进行字符串与数字的转换判断 -->
            <if test="gender==0 or gender==1">
                and gender=#{gender}
            </if>
        </where>
    </select>

但是其只会去掉第一个多余的and和or，如果在<if>标签中每次都把and或or写在后面，则该标签也无法正常去除多余的and和or，所以应该在<if>标签中每次都把and或or写在前面。例如下面这种情形就无法正常去除：

<!-- 查询员工，要求：携带了哪个字段，查询条件就带上这个字段 -->
    <select id="getEmpsByConditionIf" resultType="com.cerr.mybatis.Employee">
        select * from tb1_employee
        <where>
            <if test="id!=null">
                id=#{id} and
            </if>
            <if test="lastName!=null and lastName!=''">
                last_name like #{lastName} and
            </if>
            <if test="email!=null and email.trim()!=&quot;&quot;">
                email=#{email} and
            </if>
            <!-- ognl会进行字符串与数字的转换判断 -->
            <if test="gender==0 or gender==1">
                gender=#{gender}
            </if>
        </where>
    </select>

使用trim标签实现字符串截取

<trim>标签体中是整个字符串拼串后的结果，有四个属性：

prefix：前缀，给拼串后的整个字符串加一个前缀
prefixOverrides：前缀覆盖，去掉整个字符串前面多余的字符
suffix：后缀，给拼串后的整个字符串加一个后缀
suffixOverrides：后缀覆盖，去掉整个字符串后面多余的字符

配置可修改如下：

    <select id="getEmpsByConditionTrim" resultType="com.cerr.mybatis.Employee">
        select * from tb1_employee
        <trim prefix="where" suffixOverrides="and">
            <if test="id!=null">
                id=#{id} and
            </if>
            <if test="lastName!=null and lastName!=''">
                 last_name like #{lastName} and
            </if>
            <if test="email!=null and email.trim()!=&quot;&quot;">
                 email=#{email} and
            </if>
            <!-- ognl会进行字符串与数字的转换判断 -->
            <if test="gender==0 or gender==1">
                 gender=#{gender}
            </if>
        </trim>
    </select>

测试方法：

package com.cerr.mybatis;
import com.cerr.mybatis.dao.*;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MyBatisTest {

    //获取SQLSessionFactory
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void testDynamicSql() throws IOException {
        SqlSessionFactory factory = getSqlSessionFactory();
        SqlSession session = factory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(null,"%e%",null,null);
            List<Employee> employees = mapper.getEmpsByConditionTrim(employee);
            for(Employee e : employees){
                System.out.println(e);
            }
        }finally {
            session.close();
        }
    }
}

使用choose标签来实现分支选择

相当于java中的switch语句，但是是有加break的，可配合<when>和<otherwise>标签使用。

我们现在想实现以下功能，如果传入id就用id查，如果传入lastName就用lastName查，只会选一个。
接口新增方法：

public List<Employee> getEmpsByConditionChoose(Employee employee);

SQL映射文件：

    <select id="getEmpsByConditionChoose" resultType="com.cerr.mybatis.Employee">
        select * from tb1_employee
        <where>
            <!-- 如果传入`id`就用`id`查，如果传入`lastName`就用`lastName`查，只会选一个 ，如果这两个都没有传，则查女生-->
            <choose>
                <when test="id!=null">
                    id = #{id}
                </when>
                <when test="lastName!=null">
                    last_name like #{lastName}
                </when>
                <otherwise>
                    gender = 0
                </otherwise>
            </choose>
        </where>
    </select>

测试方法：

    @Test
    public void testDynamicSql() throws IOException {
        SqlSessionFactory factory = getSqlSessionFactory();
        SqlSession session = factory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(null,"%e%",null,null);
            List<Employee> employees = mapper.getEmpsByConditionChoose(employee);
            for(Employee e : employees){
                System.out.println(e);
            }
        }finally {
            session.close();
        }
    }

测试方法中传入的是lastName，则发送的sql为：

Preparing: select * from tb1_employee WHERE last_name like ?

结果：

如果改为Employee employee = new Employee(null,null,null,null);，则发送的sql为：

select * from tb1_employee WHERE gender = 0

结果如下：

使用if与set标签来实现动态的update语句

<set>标签可以替换掉我们之前update语句中的set，并且可以支持将后面多余的,去掉。

我们有一个需求，对于tb1_employee表，我们要更新其一条记录，但是我们的目标是，传入哪些参数，我们就更新哪些字段。我们如果单纯用<if>标签的话，就是下面的配置：

    <update id="updateEmp" >
        update tb1_employee set
        <if test="lastName!=null">
            last_name = #{lastName},
        </if>
        <if test="email!=null">
            email=#{email},
        </if>
        <if test="gender!=null">
            gender=#{gender}
        </if>
            where id=#{id}
    </update>

但是这样会出问题，假设我只传了一个last_name字段，则SQL语句是这样的：

update tb1_employee set last_name = ? , where id = ?

可以看到在last_name = ?后面多了一个,，这个时候我们就可以使用<set>标签来进行改进了，改进版如下：

    <update id="updateEmp" >
        update tb1_employee
        <set>
            <if test="lastName!=null">
                last_name = #{lastName},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
            <if test="gender!=null">
                gender=#{gender}
            </if>
        </set>
        where id=#{id}
    </update>

此时如果只传入last_name，也不会有多余的逗号，发送的SQL为：

  update tb1_employee set last_name = ? where id=?

同样也可以使用<trim>标签来进行修改，加上前缀的set，并且取出后缀中多余的,：

    <update id="updateEmp" >
        update tb1_employee
        <trim prefix="set" suffixOverrides=",">
            <if test="lastName!=null">
                last_name = #{lastName},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
            <if test="gender!=null">
                gender=#{gender}
            </if>
        </trim>
        where id=#{id}
    </update>

剩下的代码：
接口方法：

public void updateEmp(Employee employee);

测试方法：

package com.cerr.mybatis;
import com.cerr.mybatis.dao.*;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MyBatisTest {

    //获取SQLSessionFactory
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void testDynamicSql() throws IOException {
        SqlSessionFactory factory = getSqlSessionFactory();
        SqlSession session = factory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(1,"Admin",null,null);
            mapper.updateEmp(employee);
            session.commit();
        }finally {
            session.close();
        }
    }
}

foreach标签

<foreach>标签可以使用遍历集合，有如下几个属性：

collection：指定要遍历的集合，其中List类型的参数会特殊处理封装在Map中，Map的key就叫list。
item：将当前遍历出的元素赋值给指定的变量
separator：每个元素之间的分隔符
open：遍历出所有结果拼接一个开始的字符
close：遍历出所有结果拼接一个结束的字符
index：索引。当遍历list的时候index就是索引，item就是当前值；当遍历map的时候index表示的就是map的key，item就是map的值。

使用#{变量名}就能取出变量的值也就是当前遍历出的元素。

使用foreach标签来遍历集合

我们下面想实现一个循环查询，即我在方法中传入一个id数组，然后使用<foreach>来让我们的查询条件可以动态的改变，即我传入几个id就查那几个id对应的记录，例如如下的sql语句：

select * from tb1_employee where id in(1,4);
select * from tb1_employee where id in(1,2,3,4,5,6);

根据传入的id的集合来查询对应的记录。

首先接口的方法如下：

public List<Employee> getEmpsByConditionForeach(@Param("ids") List<Integer> ids);

在该接口中，我们使用了@Param注解来传入命名参数，以便我们SQL映射文件的collection属性来使用该集合。有关参数处理可以看这篇文章点击查看：MyBatis笔记 | 详解参数处理（多种类型的参数处理、源码分析、读取参数的两种格式的区别）。

SQL映射文件如下：

    <select id="getEmpsByConditionForeach" resultType="com.cerr.mybatis.Employee">
        select * from tb1_employee where id in
            <foreach collection="ids" item="item_id" separator="," open="(" close=")">
                #{item_id}
            </foreach>
    </select>

测试方法：

package com.cerr.mybatis;
import com.cerr.mybatis.dao.*;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MyBatisTest {

    //获取SQLSessionFactory
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void testDynamicSql1() throws IOException {
        SqlSessionFactory factory = getSqlSessionFactory();
        SqlSession session = factory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
            List<Employee> employees = mapper.getEmpsByConditionForeach(Arrays.asList(1,4));
            for(Employee e : employees){
                System.out.println(e);
            }
        }finally {
            session.close();
        }
    }

因为我们此时方法传递的List集合只有两个元素，因此发送的sql如下：

Preparing: select * from tb1_employee where id in( ? , ? )

结果：

mysql下使用foreach实现批量插入的两种方式

第一种是直接使用insert into table_name(...) values(),(),()这种语法格式，即是使用foreach遍历values后面的括号。
接口方法如下：

public void addEmps(@Param("emps") List<Employee> emps);

配置如下：

<insert id="addEmps">
        insert into tb1_employee(last_name,email,gender,d_id)
        values
        <foreach collection="emps" item="emp" separator=",">
            (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id})
        </foreach>

</insert>

第二种是每插入一条语句就发一条insert语句，即使用foreach循环遍历insert语句，中间用;分割，接口同上。
使用这种方法的话需要在数据库连接中设置一个allowMultiQueries属性，表示允许多个查询之间使用;分割，即url为：

jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true

配置如下：

<insert id="addEmps">
        <foreach collection="emps" item="emp" separator=";">
            insert into tb1_employee(last_name,email,gender,d_id)
            values
            (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id})
        </foreach>
</insert>

两个内置参数

不止是方法传递过来的参数可以被用来判断，取值。MyBatis默认还有两个内置参数：

_parameter：代表整个参数。如果传过来的参数是单个参数，那么_parameter就是这个传过来的参数；如果传过来的参数是多个参数，则参数会被封装成一个map，_parameter就是这个map。
_databaseId：如果配置了DatabaseIdProvider标签，那么_databaseId就是代表当前数据库的别名。

例如我们现在想编写一个sql映射：当数据库为mysql、Oracle时使用不同的sql，且当传入的Employee参数为空时，就不带条件查询，那么就可以使用到上面这两个参数了。
接口如下：

public List<Employee> getEmpsTestInnerParameter(Employee employee);

配置如下：

<select id="getEmpsTestInnerParameter" resultType="com.cerr.mybatis.Employee">
        <if test="_databaseId=='mysql'">
            select * from tb1_employee
            <if test="_parameter!=null">
                where last_name = #{_parameter.lastName}
            </if>
        </if>

        <if test="_databaseId=='oracle'">
            select * from employee
        </if>
</select>

当我们传入的Employee不为空时，sql语句为：

select * from tb1_employee where last_name = ?

当传入的Employee为空时，sql语句为：

select * from tb1_employee

使用bind标签来进行动态绑定

可以将OGNL表达式的值绑定到一个变量中，方便后面引用这个变量的值。

<select id="getEmpsTestInnerParameter" resultType="com.cerr.mybatis.Employee">
        <bind name="_lastName" value="'%'+lastName+'%'"/>
        select * from tb1_employee
        <if test="_parameter!=null">
                where last_name like #{_lastName}
        </if>
</select>

上述SQL映射中的<bind>标签将传入的lastName值的前后加了%并赋值给_lastName，在sql语句中我们就可以使用#{_lastName}参数来做模糊查询的条件。

使用sql标签来抽取重用的sql片段

在sql语句中，总是存在一些可以重用的sql片段。我们可以使用<sql>标签来抽取这些可重用的sql片段，然后使用<include>标签来引用它，<include>里面还可以自定义一些property，在<sql>标签内部就可以通过${变量名}来使用这些自定义属性（不能使用#{}来取这些自定义属性的值）

<sql id="insertColumn">
        last_name,email,gender,d_id
</sql>

上述代码，我们使用sql抽取了tb1_employee表的部分字段，接下来我们要插入表的时候就可以直接引用：

    <insert id="addEmps">
        <foreach collection="emps" item="emp" separator=";">
            insert into tb1_employee(
                <include refid="insertColumn"/>
            )
            values
            (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id})
        </foreach>
    </insert>

相当于：

    <insert id="addEmps">
        <foreach collection="emps" item="emp" separator=";">
            insert into tb1_employee(
                  last_name,email,gender,d_id
            )
            values
            (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id})
        </foreach>
    </insert>

许多可以在sql里面写的标签在该标签里面也可以用。

MyBatis笔记 | 详解动态SQL

环境的准备

使用if标签实现表达式判断

扩展

使用trim标签实现字符串截取

使用choose标签来实现分支选择

使用if与set标签来实现动态的update语句

foreach标签

使用foreach标签来遍历集合

mysql下使用foreach实现批量插入的两种方式

两个内置参数

使用bind标签来进行动态绑定

使用sql标签来抽取重用的sql片段

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

Java

person