mybatis04_动态sql

一、创建工程

主要代码如下：

全代码见 ▶️https://www.cnblogs.com/purearc/p/17198417.html

⬇️创建数据库

CREATE TABLE `emp` (
  `emp_id` bigint NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

⬇️mapper映射文件

<?xml version="1.0" encoding="GBK"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.ls.mapper.EmpMapper">

    <resultMap id="EmpMap" type="com.ls.pojo.Emp">
        <id column="emp_id" property="empId"></id>
        <result column="emp_name" property="empName"></result>
        <result column="address" property="address"></result>
        <result column="create_tiem" property="createTime"></result>
        <result column="update_time" property="updateTime"></result>
    </resultMap>

    <insert id="bachInsert">
        insert into emp(emp_name,address,email,create_time,update_time)
        values
        <foreach collection="list" item="emp" separator=",">
            (#{emp.empName},#{emp.address},#{emp.email},#{emp.createTime},#{emp.updateTime})
        </foreach>
    </insert>

    <sql id="COLUMNS">
        emp_id,emp_name,address,email,create_time,update_time
    </sql>
    <select id="list" resultMap="EmpMap">
        select  <include refid="COLUMNS"></include>
        from emp
        <where>
            <if test="empId != null">
                and emp_id = #{empId}
            </if>
            <if test="empName != null and empName != ''">
                and emp_name = #{empName}
            </if>
            <if test="address != null and address != ''">
                and address = #{address}
            </if>
            <if test="email != null and email != ''">
                and email = #{email}
            </if>

            <choose>
            <when test="createTime != null">
                create_time &lt; #{createTime}
            </when>
            <otherwise>
                create_time &lt; now()
            </otherwise>
        </choose>

        </where>

    <if test="order != null">
        order by ${order}
    </if>
    </select>
</mapper>

⬇️mapper接口

package com.ls.mapper;

import com.ls.pojo.Emp;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface EmpMapper {

    /**
     * 动态插入n条数据
     * @param empList
     * @return
     */
    int bachInsert(List<Emp> empList);

    /**
     * 根据条件查询
     * @return
     */
    List<Emp> list(Map<String,Object> params);
}

⬇️测试程序

/**
 * date: 2023/3/9
 *
 * @author Arc
 */
package com.ls.test;

import com.ls.mapper.EmpMapper;
import com.ls.pojo.Emp;
import com.ls.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

public class Test1 {
    public static void main(String[] args) {
//        testBachSave();
        testList();

    }

    private static void testList() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSession();
            EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
            HashMap<String, Object> params = new HashMap<>();
//            params.put("empId",12l);
            params.put("createTime",new Date());
            params.put("order","emp_id");
//            params.put("empName","庄");
            List<Emp> emps = empMapper.list(params);
            for (Emp emp : emps) {
                System.out.println(emp);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlSession != null) {sqlSession.close();}
        }
    }

    private static void testBachSave() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSession();
            EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
            List<Emp> empList = new ArrayList<>();
            for (int i = 0; i < 5; i++) {
                empList.add(new Emp().setEmpName("庄"+i).setAddress("大学城"+i+"号").setEmail("1234"+i+"@123.com")
                        .setCreateTime(new Date()).setUpdateTime(new Date()));
            }
            int result = empMapper.bachInsert(empList);
            sqlSession.commit();
            System.out.println("result"+result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlSession != null) {sqlSession.close();}
        }
    }
}

二、动态sql

1、foreach

​ 我们在插入数据时如果量比较大自然不可能一条一条插入，mybatis的动态sql让我们对数据的操作更加灵活，下面以插入5条数据为例子：

​ 在EmpMapper中定义方法：

    /**
     * 动态插入n条数据
     * @param empList
     * @return
     */
    int bachInsert(List<Emp> empList);

​ 理想状态下我们在values后面的多条数据应该是 values () , (), ()，根据这个来看就很容易理解下面的foreach语句。

​ collection属性：当参数是数组类型时（包括类型为可变参数时），此属性取值为array，当参数是List集合类型时，此属性取值为list

​ item属性：遍历过程中的每个元素的变量名，是自定义的名称

​ separator属性：遍历过程中各元素之间的分隔符号

    <resultMap id="EmpMap" type="com.ls.pojo.Emp">
        <id column="emp_id" property="empId"></id>
        <result column="emp_name" property="empName"></result>
        <result column="address" property="address"></result>
        <result column="create_tiem" property="createTime"></result>
        <result column="update_time" property="updateTime"></result>
    </resultMap>

    <insert id="bachInsert">
        insert into emp(emp_name,address,email,create_time,update_time)
        values
        <foreach collection="list" item="emp" separator=",">
            (#{emp.empName},#{emp.address},#{emp.email},#{emp.createTime},#{emp.updateTime})
        </foreach>
    </insert>

​ 测试程序

private static void testBachSave() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSession();
            EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
            List<Emp> empList = new ArrayList<>();
            for (int i = 0; i < 5; i++) {
                empList.add(new Emp().setEmpName("庄"+i).setAddress("大学城"+i+"号").setEmail("1234"+i+"@123.com")
                        .setCreateTime(new Date()).setUpdateTime(new Date()));
            }
            int result = empMapper.bachInsert(empList);
            sqlSession.commit();
            System.out.println("result"+result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlSession != null) {sqlSession.close();}
        }
    }

​ 结果：

image-20230310144653713

PS：在这里传参数的时候我曾经把collection的值改成了我自定义的EmpList（看朋友的可以这么写），但是显示Parameter 'emplist' not found. Available parameters are [arg0, collection, list] ，希望哪位大哥告诉一下这是为什么

2、以查询为例-where、if、choose

条件查询

​ 很多时候查询的时候不一定根据某个条件进行查询，也可能会有多个限定条件去查询，这时候就需要 if 语句去判定我们传过来的是什么条件，下面我们自定义一个list函数去根据不同的条件进行查询：

⬇️接口函数

    /**
     * 根据条件查询
     * @return
     */
    List<Emp> list(Map<String,Object> params);
}

⬇️xml对应的sql段

​ 这里EmpMap已省略，可以自行脑补。

​ test 中的取值是判定的条件，加入我们只传入一个 empId，则编译之后 sql 语句就变成了 select * from emp where emp_id = ?。如果后面的条件（不是null 不为空）也满足，则会以 and 的形式继续加条件，例如 select * from emp where emp_id = ? and emp_name = ？。

​ 使用 标签而不是使用 where 语句。使用where语句时，假如满足了第一个条件empId不为空，则sql语句就变成了 select * from emp where and emp_id = ?，这明显是一条错误的 sql 语句，使用 标签后，标签关键字可以自动去除掉库表字段赋值前面的 and ，不会去掉语句后面的 and 关键字，即 标签只会去掉 标签语句中的最开始的 and 关键字。


    <select id="list" resultMap="EmpMap">
        select *
        from emp
        --         where
        <where>
            <if test="empId != null">
                and emp_id = #{empId}
            </if>
            <if test="empName != null and empName != ''">
                and emp_name = #{empName}
            </if>
            <if test="address != null and address != ''">
                and address = #{address}
            </if>
            <if test="email != null and email != ''">
                and email = #{email}
            </if>
        </where>
    </select>

⬇️测试代码段

​ 这里分别传入一个和两个条件，主要看一下编译之后的 sql 语句

private static void testList() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSession();
            EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
            HashMap<String, Object> params = new HashMap<>();
            params.put("empId",12l);
//            params.put("empName","庄");
            List<Emp> emps = empMapper.list(params);
            for (Emp emp : emps) {
                System.out.println(emp);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlSession != null) {sqlSession.close();}
        }
    }

⬇️传入一个

image-20230313215445131

⬇️两个参数

image-20230313215516876

⬇️不传参数

image-20230313215718090

sql 标签

​ 可能我们查询的时候不会 select * 但是又不想多写 sql 语句，所以可以使用 sql 标签写重复的语句，在 include 进去就可以，相当于jsp的链接。

<sql id="COLUMNS">
        emp_id,emp_name,address,email,create_time,update_time
    </sql>
    <select id="list" resultMap="EmpMap">
        select  <include refid="COLUMNS"></include>
        from emp
        <where>
            <if test="empId != null">
                and emp_id = #{empId}
            </if>
            <if test="empName != null and empName != ''">
                and emp_name = #{empName}
            </if>
            <if test="address != null and address != ''">
                and address = #{address}
            </if>
            <if test="email != null and email != ''">
                and email = #{email}
            </if>
        </where>
    </select>

choose、when和otherwise

​ 有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，里面有 when 和 otherwise 两个标签，类似于 if...else 的用法，只不过使用 choose 包起来使用。

            <choose>
                <when test="createTime != null">
                    create_time &lt; #{createTime}
                </when>
                <otherwise>
                    create_time &lt; now()
                </otherwise>
            </choose>

​ 这段 sql 的目的是根据传入的时间筛选数据，如果我们传入一个 createTime ，则会筛选小于我们传入时间戳的数据，如果不传则会获得当前系统时间并筛选所有小于当前时间的数据条。

image-20230314211051444

筛选后排序

​ 在我们 sql 语句中排序通常这么写：select from emp WHERE 条件 order by 某个键。order by 后面的值应该是一个列名的字符串，而不应是取某条数据该列上的值，所以在xml文件中应该这样写：

    <if test="order != null">
        order by ${order}
    </if>

​ 关于$和#的区别你可以理解为 JDBC 中的 statement 和 preparedStatement ，具体可见上一篇博客 。

⬇️

image-20230314214934433

posted @ 2023-03-14 21:55  Purearc  阅读(43)  评论(0)  编辑  收藏  举报