【SSM详细教程】-08-Mybatis动态SQL语句

最新推荐文章于 2024-12-28 00:38:29 发布

听潮阁

最新推荐文章于 2024-12-28 00:38:29 发布

阅读量918

点赞数 21

分类专栏： SSM核心教程文章标签： java tomcat 数据库 java-ee spring cloud mybatis sql

本文链接：https://blog.csdn.net/yueyehuguang/article/details/143139201

版权

SSM核心教程专栏收录该内容

17 篇文章 1 订阅

订阅专栏

精品专题：

01.《C语言从不挂科到高绩点》课程详细笔记

https://blog.csdn.net/yueyehuguang/category_12753294.html?spm=1001.2014.3001.5482

02. 《SpringBoot详细教程》课程详细笔记

https://blog.csdn.net/yueyehuguang/category_12789841.html?spm=1001.2014.3001.5482

03.《SpringBoot电脑商城项目》课程详细笔记

https://blog.csdn.net/yueyehuguang/category_12752883.html?spm=1001.2014.3001.5482

04.《VUE3.0 核心教程》课程详细笔记

https://blog.csdn.net/yueyehuguang/category_12769996.html?spm=1001.2014.3001.5482

================================

|| 持续分享系列教程，关注一下不迷路 ||

|| 视频教程：小破站：墨轩大楼 ||

================================

一、动态SQL概述

动态SQL是MyBatis框架中强大特性之一，在一些组合查询页面，需要根据用户输入的查询条件生成不同的查询SQL，在这JDBC或其他相似的框架中需要在代码中拼写SQL，经常容易出错，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。而在MyBatis中可以很好解决这种问题。

使用动态SQL元素与JSTL相似，它允许我们在XML中构建不同的SQL语句，常用的元素如下：

判断元素： if 、choose
关键字元素：where、set、trim
循环元素：foreach

二、可重用代码段

在sql语句中，如果存在某段代码需要重复使用，比如：在查询中，通常会遇到频繁查询多个字段的情况，每次查询的时候都需要将这些字段写一遍非常的麻烦。有了Mybatis我们可以使用<sql>标签将需要重复使用的代码段封装起来，然后需要用的时候可以<include>标签引入需要重复使用的代码。

例如下面代码：

<!--可重用代码段-->
<sql id="columns">
    no,
    name,
    author,
    publish,
    price
</sql>

<select id="selectByPrimaryKey" resultType="book">
    select
    <!--引用可重用代码段-->
    <include refid="columns"></include>
    from books
    where id = #{id}
</select>

<select id="selectAll" resultType="book">
    select
    <include refid="columns"></include>
    from books
</select>

从上面案例可以看出，在封装重复代码段时，我们在<sql> 标签中定义了一个id属性，而在使用<include>标签时，也是使用refid属性引用这个id的属性值。

【案例】查询员工数据

在前面的案例中，已经学过怎么搭建MyBatis环境了，创建项目，引入mybatis所需的依赖、添加mybatis-config.xml配置文件，添加数据操作接口，添加实体类Emp，添加映射文件EmpMapper.xml以及在配置文件中引入映射文件这些操作可以参照上一节课程内容。

》》 emp 数据表

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int NOT NULL AUTO_INCREMENT,
  `ename` varchar(20) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL,
  `manager` int DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `salary` double DEFAULT NULL,
  `comm` double DEFAULT NULL,
  `deptno` int DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('1', '郭靖', 'clerk', '4', '2022-02-03', '7000', '2000', '2');
INSERT INTO `emp` VALUES ('2', '黄蓉', 'saleman', '4', '2023-02-01', '6000', '5000', '3');
INSERT INTO `emp` VALUES ('3', '周瑜', 'saleman', '4', '2023-02-18', '7000', '3000', '3');
INSERT INTO `emp` VALUES ('4', '孙策', 'manager', null, '2021-02-04', '10000', '20000', '1');

》》员工类Emp.java

package com.moxuan.mybatis_sql.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.sql.Date;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {

		private Integer empno;
		private String ename;
		private String job;
		private Integer manager;
		private Date hiredate;
		private Double salary;
		private Double comm;
		private Integer deptno;
}

》》封装Mybatis工具类

在前面的的案例中，每次测试的时候，获取SqlSession对象的操作比较复杂，而且代码重复，因此我们可以将这些重复复杂的操作提取一下，封装成MybatisUtil工具类。代码如下：

package com.moxuan.mybatis_sql.util;

import jdk.internal.util.xml.impl.Input;
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 java.io.IOException;
import java.io.InputStream;

public class MyBatisUtil {
    private static SqlSessionFactory factory;

    static{
        // 在静态代码块下，factory只会被创建一次
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            factory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取sqlSession对象
     * @return
     */
    public static SqlSession getSession(){
        return factory.openSession(false);// true 为自动提交事务
    }

    /**
     * 关闭sqlSession对象
     * @param session
     */
    public static void closeSqlSession(SqlSession session){
        if(session!=null){
            session.close();
        }
    }
}

》》数据操作接口（映射器）

package com.moxuan.mybatis_sql.dao;

import com.moxuan.mybatis_sql.entity.Emp;

import java.util.List;

public interface EmpDao {

    List<Emp> findAll();

    Emp findByEmpno(int empno);
}

》》在映射文件中编写sql语句

<?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">
<!-- 此处的namespace需要对应上dao包中的数据操作接口-->
<mapper namespace="com.moxuan.mybatis_sql.dao.EmpDao">

    <!--编写查询字段-->
    <sql id="emp_column">
        ename,salary,comm,deptno
    </sql>

    <select id="findAll"  resultType="emp">
        select
        <include refid="emp_column"></include>
        from emp
    </select>

    <select id="findByEmpno"  resultType="emp">
        select
        <include refid="emp_column"></include>
        from emp where empno=#{empno}
    </select>

</mapper>

》》编写测试方法

/**
* 测试MyBatis可重用代码块
*/
@Test
public void test01(){
    SqlSession session = MyBatisUtil.getSession();
    EmpDao dao = session.getMapper(EmpDao.class);
    System.out.println("==========查询所有的员工信息===========");
    List<Emp> emps_01 = dao.findAll();
    for (Emp emp :emps_01){
        System.out.println(emp);
    }

    System.out.println("==========根据empno查询员工信息===========");
    Emp emp = dao.findByEmpno(3);
    System.out.println(emp);
}

》》运行效果：

三、if 、where、trim元素用法

if元素是简单的条件判断逻辑，满足指定条件时追加if元素内的SQL，不满足条件时不追加，使用格式如下：

<select ...>
	SQL 语句1
	<if test="条件表达式">
			SQL 语句2
	</if>
</select>

if元素最常见的使用是在where子句部分，根据不同情况追加不同的SQL条件，示例代码如下：

<select id="findByDeptno" resultType="emp">
	select * from emp 
		<if test="deptno!=null">
				where deptno=#{deptno}
		</if>
</select>

这条语句提供一个带功能性的可选的文字，如果你没有传入deptno，那么所有的员工都会被返回，如果传入了一个deptno，那就会寻找指定的deptno的数据。

接下来验证一下，在数据操作接口（映射器）EmpDao 中添加方法findByDeptno和sql语句的id对应：

List<Emp> findByDeptno(Emp emp);

》》编写测试方法，代码如下：

 /**
     * 测试if元素用法
     */
@Test
public void test02(){
        SqlSession session = MyBatisUtil.getSession();
        EmpDao dao = session.getMapper(EmpDao.class);
        Emp emp = new Emp();
        System.out.println("======不给deptno的情况=====");
        List<Emp> empList = dao.findByDeptno(emp);
        for (Emp emp1:empList){
            System.out.println(emp1);
        }

        System.out.println("======给deptno的情况=====");
        // 设置emp的deptno
        emp.setDeptno(3);
        List<Emp> empList1 = dao.findByDeptno(emp);
        for (Emp emp1:empList1){
            System.out.println(emp1);
        }
}

》》运行效果如下：

从测试效果来看，当不给deptno的时候，查询语句将所有的员工数据查出来了。但是当我们给emp的deptno设置为3的时候，查询出来的仅仅只有deptno为3的员工数据。

如果我们想要可选择的使用deptno，ename或者salary查询怎么办？我们在映射文件中重新写一个SQL语句，对比一下之前的写法：

》》 sql语句

<select id="findEmp" resultType="emp">
  select * from emp
  where
  <if test="deptno!=null">
    deptno=#{deptno}
  </if>
  <if test="ename!=null">
    and ename=#{ename}
  </if>
  <if test="salary!=null">
    and salary>6000
  </if>

</select>

》》添加接口映射方法

List<Emp> findEmp(Emp emp);

》》编写测试方法

@Test
public void testFindEmp(){
    SqlSession session = MyBatisUtil.getSession();
    EmpDao dao = session.getMapper(EmpDao.class);

    System.out.println("=====根据deptno和 ename查询====");
    Emp emp = new Emp();
    emp.setDeptno(3);
    emp.setEname("黄蓉");
    List<Emp> empList = dao.findEmp(emp);
    System.out.println(empList);

    System.out.println("=====根据deptno和 ename查询====");
    Emp emp1 = new Emp();
    emp1.setDeptno(3);
    emp1.setSalary(6000.0);
    List<Emp> empList1 = dao.findEmp(emp1);
    System.out.println(empList1);

}

》》运行效果

上面案例虽然可以实现效果，但是如果我们一个条件都不给，会怎么样？

修改一下测试方法：

System.out.println("====什么条件都不给====");
Emp emp2 = new Emp();
List<Emp> empList2 = dao.findEmp(emp2);
System.out.println(empList2);

运行后会发现报SQL语句错误

会过来看看我们前面写的SQL语句

<select id="findEmp" resultType="emp">
  select * from emp
  where
  <if test="deptno!=null">
    deptno=#{deptno}
  </if>
  <if test="ename!=null">
    and ename=#{ename}
  </if>
  <if test="salary!=null">
    and salary>6000
  </if>

</select>

如果一个条件都不给，也就是条件都是空的，那么实际执行的SQL语句就会变成如下的情况：

select * from emp
where

MyBatis为了避免这个情况，提供了一个<where> 标签。将条件包含在where标签中，如果它包含的标签中有返回值的话，它就插入一个where。此外，如果标签返回的内容是以AND或者OR开头的，则它会剔除掉。人如果条件都不满足，则不会插入where。具体用法如下：

<select id="findEmp" resultType="emp">
  select * from emp
  <where>
    <if test="deptno!=null">
      deptno=#{deptno}
    </if>
    <if test="ename!=null">
      and ename=#{ename}
    </if>
    <if test="salary!=null">
      and salary>6000
    </if>
  </where>
</select>

》》测试方法：

@Test
public void testFindEmp(){
    SqlSession session = MyBatisUtil.getSession();
    EmpDao dao = session.getMapper(EmpDao.class);

    System.out.println("=====根据deptno和 ename查询====");
    Emp emp = new Emp();
    emp.setDeptno(3);
    emp.setEname("黄蓉");
    List<Emp> empList = dao.findEmp(emp);
    System.out.println(empList);

    System.out.println("=====根据deptno和 salary查询====");
    Emp emp1 = new Emp();
    emp1.setDeptno(3);
    emp1.setSalary(6000.0);
    List<Emp> empList1 = dao.findEmp(emp1);
    System.out.println(empList1);

    System.out.println("====什么条件都不给====");
    Emp emp2 = new Emp();
    List<Emp> empList2 = dao.findEmp(emp2);
    System.out.println(empList2);

}

》》运行效果：

运行之后，会发现，当什么条件都不给的时候，程序不会报SQL语句错误的异常了。这种情况下，会查询出所有的数据。

如果where元素并没有完全按照你想要的那样剔除想要剔除的内容，那也可以使用trim元素自定义一下，以下trim的作用类似于前面where的作用，代码如下：

<select id="findEmp" resultType="emp">
  select * from emp
  <trim prefix="where" prefixOverrides="and|or">
    <if test="deptno!=null">
      and deptno=#{deptno}
    </if>
    <if test="ename!=null">
      and ename=#{ename}
    </if>
    <if test="salary!=null">
      and salary>6000
    </if>
  </trim>
</select>

====说明====

prefix：需要添加的前缀。
suffix：需要添加的后缀。
suffixOverrides: 需要替换（移除）的后缀。
prefixOverrides: 需要替换（移除）的后缀。

四、choose、when、otherwise元素

有时候我们并不想应用所有的条件，而只是想从多个选项中选择一个。类似于Java中的switch语句,MyBatis提供了choose元素。

现在我们只想搜索有deptno部门号的，或者只想搜索有名字的，或者只搜索有薪水的,如果都没有，则搜索所有的。SQL语句写法如下：

<select id="findEmpChoose" resultType="emp">
        select * from emp
        <where>
            <choose>
                <when test="deptno!=null">
                    and deptno=#{deptno}
                </when>
                <when test="ename!=null">
                    and ename=#{ename}
                </when>
                <otherwise>
                    and salary=6000
                </otherwise>
            </choose>
        </where>
</select>

》》测试代码如下：

@Test
public void testFindEmpChoose(){
        SqlSession session = MyBatisUtil.getSession();
        EmpDao dao = session.getMapper(EmpDao.class);

        System.out.println("=====传递deptno和 ename去查询====");
        Emp emp = new Emp();
        emp.setDeptno(3);
        emp.setEname("黄蓉");
        List<Emp> empList = dao.findEmpChoose(emp);
        for(Emp emp_v:empList){
            System.out.println(emp_v);
        }

        System.out.println("=====ename查询====");
        Emp emp1 = new Emp();
        emp1.setEname("郭靖");
        List<Emp> empList1 = dao.findEmpChoose(emp1);
        System.out.println(empList1);

        System.out.println("====什么条件都不给====");
        Emp emp2 = new Emp();
        List<Emp> empList2 = dao.findEmpChoose(emp2);
        System.out.println(empList2);

}

》》运行效果如下：

====注意=====

choose 只能选择第一个符合条件的语句，上面案例中，虽然给了deptno和ename两个值，两个都不为null，条件都满足。但是当选择了插入deptno=#{deptno}之后，后面的条件就不会再判断了。所以会发现查询出来的结果会把所有deptno=3的数据查出来。
当什么条件都不给时，也就是choose中所有的when条件都不满足，此时就会使用otherwise中的语句。
当条件可能需要多个时，推荐使用if元素。而当需要从多个条件中挑选出某一个条件时，推荐使用choose。

五、set元素的用法

set元素用在动态update语句里，这个set元素被用做动态囊括列名来更新，而忽略其他的，比如：

   <update id="updateEmp">
        update emp
        <set>
            <if test="ename!=null"> ename=#{ename},</if>
            <if test="job!=null"> job=#{job},</if>
            <if test="manager!=null"> manager=#{manager},</if>
            <if test="salary!=null"> salary=#{salary},</if>
            <if test="comm!=null"> comm=#{comm},</if>
            <if test="deptno!=null"> deptno=#{deptno},</if>
        </set>
        where empno=#{empno}
    </update>

》》测试方法

@Test
public void testUpdateEmp(){
        SqlSession session = MyBatisUtil.getSession();
        EmpDao dao = session.getMapper(EmpDao.class);
        Emp emp = new Emp();
        emp.setEmpno(3);
        emp.setEname("心肝小宝贝儿");
        System.out.println("====修改之前====");
        List<Emp> empList = dao.findAll();
        for(Emp e:empList){
            System.out.println(e);
        }

        dao.updateEmp(emp);
    	session.commit();// 修改数据库数据之后，需要提交事务
        System.out.println("====修改之后====");
        empList = dao.findAll();
        for(Emp e:empList){
            System.out.println(e);
        }
}

》》运行效果:

上面的sql语句除了用set元素来写以外，也可以使用<trim>标签来写，写法如下：

 <update id="updateEmp">
        update emp
        <trim prefix="set" suffixOverrides=",">
            <if test="ename!=null"> ename=#{ename},</if>
            <if test="job!=null"> job=#{job},</if>
            <if test="manager!=null"> manager=#{manager},</if>
            <if test="salary!=null"> salary=#{salary},</if>
            <if test="comm!=null"> comm=#{comm},</if>
            <if test="deptno!=null"> deptno=#{deptno},</if>
        </trim>
        where empno=#{empno}
  </update>

六、foreach元素

6.1 批量查询数据

foreach元素是另外一个对于动态SQL来说非常重要的元素，主要用来迭代一个集合，通常是用于SQL语句中的in条件语句，比如：

<select id="selectByEmpnos" resultType="emp">
        select * from emp
        where empno in
        <foreach collection="list" open="("  separator="," close=")" item="empno">
            #{empno}
        </foreach>
</select>

》》数据操作接口（映射器）

List<Emp> selectByEmpnos(List<Integer> empnos);

》》测试方法

@Test
public void testForEach(){
        SqlSession session = MyBatisUtil.getSession();
        EmpDao dao = session.getMapper(EmpDao.class);
        List<Integer> empnos = new ArrayList<>();
        empnos.add(1);
        empnos.add(2);
        empnos.add(6);
        List<Emp> empList = dao.selectByEmpnos(empnos);
        for(Emp e:empList){
            System.out.println(e);
        }
}

foreach 元素功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符。

你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

6.2 批量添加数据

基于以上foreach标签的使用，可以轻松实现数据的批量插入（常见于数据的批量导入功能：考试系统题库导入，员工信息批量导入）：

》》数据操作接口（映射器）中添加方法

boolean insertMultiple(List<Emp> emps);

》》映射文件中的SQL语句如下：

<insert id="insertMultiple">
        insert into emp (ename,job,manager,hiredate,salary,comm,deptno)
        values 
        <foreach collection="list" item="e" separator="," >
            (#{e.ename},
             #{e.job},
             #{e.manager},
             #{e.hiredate},
             #{e.salary},
             #{e.comm},
             #{e.deptno})
        </foreach>
</insert>

》》在Emp中添加一个构造函数

public Emp(String ename, String job, Integer manager, Date hiredate, Double salary, Double comm, Integer deptno) {
    this.ename = ename;
    this.job = job;
    this.manager = manager;
    this.hiredate = hiredate;
    this.salary = salary;
    this.comm = comm;
    this.deptno = deptno;
}

》》编写测试方法

@Test
public void testInsertMultiple(){
    SqlSession session = MyBatisUtil.getSession();
    EmpDao dao = session.getMapper(EmpDao.class);
    List<Emp> emps = new ArrayList<>();
    emps.add(new Emp("赵云","销售",1, new Date(System.currentTimeMillis()),5000.0,5000.0,4));
    emps.add(new Emp("张飞","开发",1, new Date(System.currentTimeMillis()),8000.0,2000.0,3));
    emps.add(new Emp("关羽","运维",1, new Date(System.currentTimeMillis()),6000.0,4000.0,3));
    dao.insertMultiple(emps);
    session.commit();
}

》》运行效果

6.3 动态更新数据

foreach 也可以用来进行动态更新数据，具体操作方法如下：

》》在接口映射器中添加映射方法

void updateEmpByMap(@Param("params") Map<String ,Object> map, @Param("empno") int empno);

》》在mapper映射文件中编写SQL语句

<update id="updateEmpByMap" >
  update emp
  <set>
    <foreach collection="params" item="val" index="key" separator=",">
      ${key} = #{val}
    </foreach>
  </set>
  where empno=#{empno}
</update>

》》添加测试方法

@Test
public void testUpdateMultiple(){
    SqlSession session = MyBatisUtil.getSession();
    EmpDao dao = session.getMapper(EmpDao.class);
    Map<String ,Object> dataMap = new HashMap<>();
    dataMap.put("ename","赵四");
    dataMap.put("job","舞者");
    dao.updateEmpByMap(dataMap,7);
    session.commit();
}

七、bind用法

在进行模糊查询时，编写SQL语句如果使用“${}”进行字符串拼接，则无法防止SQL注入，如果使用concat函数进行拼接，则只能针对MySQL数据库有效，如果使用oracle数据库则此中SQL语句会出现语法错误。这样，映射文件中的SQL就要根据不同的情况提供不同形式的实现，非常麻烦。因此Mybatis提供了标签来解决此类问题。

Mybatis的标签可以通过OGNL表达式来创建一个上下文变量，其具体的语法如下所示：

<bind name="变量名"   value="变量值">

bind标签的使用：根据员工名模糊查询用户信息

》》在映射器接口中添加映射方法

List<Emp> findByEname(String ename);

》》在mapper.xml中添加SQL语句

<select id="findByEname" resultType="emp">
  select * from emp
  <where>
    <if test="ename!=null and ename!=''">
      <bind name="e_name" value="'%'+ename+'%'"></bind>
      ename like #{e_name}
    </if>
  </where>
</select>

》》编写测试方法

@Test
public void testBindSelect(){
    SqlSession session = MyBatisUtil.getSession();
    EmpDao dao = session.getMapper(EmpDao.class);
    List<Emp> empList = dao.findByEname("赵");
    for(Emp e:empList){
        System.out.println(e);
    }
}

》》运行效果