Mybatis学习笔记-06 动态SQL

最新推荐文章于 2023-02-22 20:19:45 发布

WALL-SQ

最新推荐文章于 2023-02-22 20:19:45 发布

阅读量180

点赞数

分类专栏： Java开发-Mybatis

本文链接：https://blog.csdn.net/qq_39304630/article/details/112261267

版权

Java开发-Mybatis 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

Mybatis的映射文件中支持在基础SQL上添加一些逻辑操作，动态拼接成完整的SQL后再执行，以复用SQL、简化编程。

1. 抽取重复部分直接引用 <sql> <include>

1）建立表

use study_mybatis;
create table t_user(
    id int primary key auto_increment,
    username varchar(50),
    password varchar(20),
    gender varchar(1),
    regist_time date
)default charset = utf8;

insert into t_user values(1,'zzt','123','f','1999-4-13'),(2,'zjm','456','m','1998-5-20')

2）建立对应实体

package com.zt.Bean;

import java.util.Date;

public class User {
    private Integer id;
    private String username;
    private String password;
    private Boolean gender;
    private Date registerTime;

    public User() {
    }

    public User(Integer id, String username, String password, Boolean gender, Date registerTime) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.gender = gender;
        this.registerTime = registerTime;
    }

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Boolean getGender() {
        return gender;
    }

    public void setGender(Boolean gender) {
        this.gender = gender;
    }

    public Date getRegisterTime() {
        return registerTime;
    }

    public void setRegisterTime(Date registerTime) {
        this.registerTime = registerTime;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", gender=" + gender +
                ", registerTime=" + registerTime +
                '}';
    }
}

3）在DAO中注册方法

package com.zt.DAO;

import com.zt.Bean.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserDAO {

    List<User> queryUsers(@Param("id") Integer id);
    User queryUserById(@Param("id") Integer id);
    Integer deleteUser(@Param("id") Integer id);
    Integer updateUser(User user);
    Integer insertUser(User user);

}

4）编写Mapper.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.zt.DAO.UserDAO">

    <select id="queryUsers" resultType="User">
        select id,username,password,gender,regist_time as registerTime
        from t_user
    </select>

    <select id="queryUserById" resultType="User">
        select id,username,password,gender,regist_time as registerTime
        from t_user
        where id = #{id}
    </select>

    <delete id="deleteUser" parameterType="int">
        delete from t_user
        where id = #{id}
    </delete>

    <update id="updateUser" parameterType="User">
        update t_user
        set username = #{username},password = #{password},gender = #{gender},regist_time = #{registerTime}
        where id = #{id}
    </update>

    <insert id="insertUser" parameterType="User">
        <!--主键回填-->
        <selectKey order="AFTER" resultType="int" keyProperty="id">
            select last_insert_id()
        </selectKey>
        insert into t_user values(#{id},#{username},#{password},#{gender},#{registerTime})
    </insert>

</mapper>

此处我们可以发现，查询全部记录和根据id查询具有相同的SQL片段：select id,username,password,gender,regist_time as registerTime from t_user，Mybatis提供了sql和include标签用于共性片段的重复利用。

    <sql id="user_field">
        select id,username,password,gender,regist_time as registerTime
        from t_user
    </sql>

    <select id="queryUsers" resultType="User">
        <include refid="user_field"></include>
    </select>

    <select id="queryUserById" resultType="User">
        <include refid="user_field"></include>
        where id = #{id}
    </select>

5）编写测试方法

    public static void main(String[] args) {
        UserDAO mapper = MybatisUtil.getMapper(UserDAO.class);
        List<User> users = mapper.queryUsers();
        for (User user : users) {
            System.out.println(user);
        }
    }

6）为了避免每次都需要重写一次main方法或者加注释的麻烦我们从本文开始将使用junit进行测试

在pom.xml中加入包依赖：

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

编写测试方法，增加@Test注解

    @Test
    public void test1(){
        UserDAO mapper = MybatisUtil.getMapper(UserDAO.class);
        List<User> users = mapper.queryUsers();
        for (User user : users) {
            System.out.println(user);
        }
    }

2. 多种查询方式合并优化 <if> <where>

现在假设我们增加一个按照用户名查询的需求，首先在DAO中增加方法定义：

    User queryUserByUsername(@Param("username") String username);

在Mapper.xml中编写对应SQL：

    <select id="queryUserByUsername" resultType="User">
        <include refid="user_field"></include>
        where username = #{username}
    </select>

编写测试：

    @Test
    public void test2(){
        UserDAO mapper = MybatisUtil.getMapper(UserDAO.class);
        User user = mapper.queryUserByUsername("zzt");
        System.out.println(user);
    }

但是如果我们每增加一种查询方式，就要多定义一个方法，如此这般，是很麻烦的，事实上，动态SQL允许我们将这些方式合并，由于不同的方式都是根据类定义中的某一个或某几个属性来的，因此我们可以直接用一个User接收参数。在动态SQL中，我们只需要让那些属性值非空的拿出来做判断条件即可。

我们先看一下单属性有值的情况，以合并按用户名和按id查询为例：

1）在DAO中增加定义

List<User> queryUser(User user);

2）编写Mapper.xml中对应方法

    <select id="queryUser" resultType="User">
        <include refid="user_field"></include>
        where
        <if test = "id != null">
            id = #{id}
        </if>
        <if test = "username != null">
            username = #{username}
        </if>
    </select>

[注]：<if>标签类似于if语句，test为条件判断的字符串，当条件成立时，if标签内的语句会拼接上前面的部分。值得注意的是，以test = "id != null"为例，字符串内的id指的是参数user内的id。

3）编写测试方法

    @Test
    public void test3(){
        UserDAO mapper = MybatisUtil.getMapper(UserDAO.class);

        User userone = new User();
        userone.setId(1);
        List<User> users = mapper.queryUser(userone);
        for (User user : users) {
            System.out.println(user);
        }

        User usertwo = new User();
        usertwo.setUsername("zjm");
        List<User> users1 = mapper.queryUser(usertwo);
        for (User user : users1) {
            System.out.println(user);
        }
    }

然而上述方法是有缺陷的，假设两个if标签均成立的话，那么语句最后的部分就会变为where id = #{id} username = #{username}，显然拼接后是一个错误的SQL，也许有人会说，将第二个语句的标签变为 and username = #{username}，或者变为or username = #{username}。但是我们设想，如果第一个标签不成立，那么拼接后的结果是 where or username = #{username}，这显然也是错误的！！！

Mybatis提供了where标签，它有如下作用：

1. 提供一个where

2. 如果拼接后的结果在where后紧接and 或者 or，那么它会帮助我们去除。

现在我们用以id或者username的方式查询为例：

1）在DAO中定义方法

List<User> queryUser2(User user);

2）在Mapper.xml编写SQL

    <select id="queryUser2" resultType="User">
        <include refid="user_field"></include>
        <where>
            <if test = "id != null">
                id = #{id}
            </if>
            <if test = "username != null">
                or username = #{username}
            </if>
        </where>
    </select>

[注]：其实第一个if我们也可以加or，因为where会帮我们去除。

3）编写测试方法由于所有的方法测试执行第一步都是读取主配置文件因此我们可以抽取共性，使用@Before表示在方法的测试前执行。

    UserDAO mapper;

    @Before
    public void start(){
        mapper = MybatisUtil.getMapper(UserDAO.class);
    }

    @Test
    public void test4(){
        User user = new User();
        user.setId(1);
        user.setUsername("zjm");
        List<User> users = mapper.queryUser2(user);
        for (User user1 : users) {
            System.out.println(user1);
        }
    }

3.更新 <set>

我们前面的更新语句存在一次需要对所有属性更新的问题，但在实际应用中，我们很有可能仅仅针对某些属性更新。显然我们需要根据传入参数user的属性是否为空决定更新哪些属性。当然主键指明了更新哪条记录，一般我们不会更新。

我们来看更新的语法，update table_name set xx=xx,xx=xx where xx=xx。

看起来我们可以这样写SQL：

    <update id="updateUser" parameterType="User">
        update t_user
        set
            <if test="username != null">
                username = #{username},
            </if>
            <if test="password != null">
                password = #{password},
            </if>
            <if test="gender != null">
                gender = #{gender},
            </if>
            <if test="regist_time != null">
                regist_time = #{registerTime}
            </if>
        where id = #{id}
    </update>

现在我们想象如果我们没有给regist_time 属性赋值，那么拼接出来的SQL语句会变成update table_name set xx=xx,xx=xx, where xx=xx。在set结尾和where中间多出了",",这是有SQL语法错误的。

那么根据上面的经验，Mybatis应该也提供了类似的<set>标签，它的作用如下：

1. 提供一个set

2. 如果拼接后的结果在set的尾巴上有一个","，那么它会帮助我们去除

修改Mapper.xml中的SQL：

    <update id="updateUser" parameterType="User">
        update t_user
        <set>
            <if test="username != null">
                username = #{username},
            </if>
            <if test="password != null">
                password = #{password},
            </if>
            <if test="gender != null">
                gender = #{gender},
            </if>
            <if test="registerTime != null">
                regist_time = #{registerTime}
            </if>
        </set>
        where id = #{id}
    </update>

编写测试：

    @Test
    public void test_update(){
        User user = new User(2,"zjm","520",null,null);
        Integer integer = mapper.updateUser(user);
        System.out.println(integer);
        MybatisUtil.commit();
    }

[注意]：千万要记得增删改之后都需要进行提交，否则不会持久化。

4. 通用型标签 <trim> Mybatis提供了<trim>标签让我们自行定制更为复杂的功能。

<trim>标签各属性详解：

prefix：在标签所包裹的语句块的开头添加什么

suffix：在标签所包裹的语句块的结尾添加什么

prefixOverrides：整个语句块以什么开头就去掉什么

suffixOverrides：整个语句块以什么结束就去掉什么

于是<where>标签可以用如下方式替代：

    <select id="queryUser2" resultType="User">
        <include refid="user_field"></include>
        <!--<where>
            <if test = "id != null">
                id = #{id}
            </if>
            <if test = "username != null">
                or username = #{username}
            </if>
        </where>-->
        <trim prefix="where" prefixOverrides="and|or">
            <if test = "id != null">
                id = #{id}
            </if>
            <if test = "username != null">
                or username = #{username}
            </if>
        </trim>
    </select>

<set>标签可以用如下方式替代：

    <update id="updateUser" parameterType="User">
        update t_user
        <!--<set>
            <if test="username != null">
                username = #{username},
            </if>
            <if test="password != null">
                password = #{password},
            </if>
            <if test="gender != null">
                gender = #{gender},
            </if>
            <if test="registerTime != null">
                regist_time = #{registerTime}
            </if>
        </set>-->
        <trim prefix="set" suffixOverrides=",">
            <if test="username != null">
                username = #{username},
            </if>
            <if test="password != null">
                password = #{password},
            </if>
            <if test="gender != null">
                gender = #{gender},
            </if>
            <if test="registerTime != null">
                regist_time = #{registerTime}
            </if>
        </trim>
        where id = #{id}
    </update>

5.批量操作 <foreach>

A.批量删除为例

0）我们先插入一些数据

insert into t_user(id) values(3),(4),(5),(6)

1）在DAO中定义方法

Integer deleteUsers(List<Integer> ids);

2）在Mapper.xml定义对应SQL

我们先看看批量删除的SQL语句：delete from table_name where id in (x,x,x,x,x)；也就是说，我们需要通过<foreach>生成(x,x,x,x,x)部分。

    <delete id="deleteUsers" parameterType="java.util.List">
        delete from t_user where id in
        <foreach collection="list" open="(" close=")" item="it" separator=",">
            #{it}
        </foreach>
    </delete>

collection：指明需要遍历的变量的类型

open：语句块开始需要添加什么

close：语句块结束需要添加什么

item：遍历出来的每一项叫什么

separator：每一项之间用什么分隔开

[注]：由于本方法对应的参数不是基本数据类型及其包装类，也不是string，而是复合类型List，因此我们需要显式指明。

3）编写测试方法

    @Test
    public void delete_list(){
        List<Integer> ids = Arrays.asList(3, 4, 5);
        mapper.deleteUsers(ids);
        MybatisUtil.commit();
    }

B.批量增加

1）在DAO增加方法定义

Integer insertUsers(List<User> users);

2）在Mapper.xml定义对应SQL

先看一下批量增加的SQL语句：insert into t_user values(null,x,x,x,x,x),(null,x,x,x,x,x)

<insert id="insertUsers" parameterType="java.util.List">
        insert into t_user values
        <foreach collection="list" item="Puser" separator=",">
            (null,#{Puser.username},#{Puser.password},#{Puser.gender},#{Puser.registerTime})
        </foreach>
    </insert>

3）编写测试

    @Test
    public void insert_list(){
        List<User> users = Arrays.asList(new User(null, "张三", "111", "m", new Date()),
                new User(null, "李四", "222", "f", new Date()));
        mapper.insertUsers(users);
        MybatisUtil.commit();
    }