Mybatis进阶——动态SQL（1）

最新推荐文章于 2024-07-30 20:50:32 发布

tao滔不绝

最新推荐文章于 2024-07-30 20:50:32 发布

阅读量1.7k

点赞数 47

本文链接：https://blog.csdn.net/cool_tao6/article/details/139178024

版权

动态SQL 是Mybatis的强大特性之一，能够完成不同条件下的不同SQL拼接，可以参考官方文档：动态 SQL_MyBatis中文网

准备工作：

SQL语句：

-- 创建数据库
DROP DATABASE IF EXISTS mybatis_test;
 
CREATE DATABASE mybatis_test DEFAULT CHARACTER SET utf8mb4;
 
-- 使用数据数据
USE mybatis_test;
 
-- 创建表[用户表]
DROP TABLE IF EXISTS userinfo;
CREATE TABLE `userinfo` (
        `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
        `username` VARCHAR ( 127 ) NOT NULL,
        `password` VARCHAR ( 127 ) NOT NULL,
        `age` TINYINT ( 4 ) NOT NULL,
        `gender` TINYINT ( 4 ) DEFAULT '0' COMMENT '1-男 2-女 0-默认',
        `phone` VARCHAR ( 15 ) DEFAULT NULL,
        `delete_flag` TINYINT ( 4 ) DEFAULT 0 COMMENT '0-正常, 1-删除',
        `create_time` DATETIME DEFAULT now(),
        `update_time` DATETIME DEFAULT now(),
        PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; 
 
-- 添加用户信息
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'admin', 'admin', 18, 1, '18612340001' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'zhangsan', 'zhangsan', 18, 1, '18612340002' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'lisi', 'lisi', 18, 1, '18612340003' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'wangwu', 'wangwu', 18, 1, '18612340004' );

yml配置内容：

# 数据库配置
spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/mybatis_test?characterEncoding=utf8&useSSL=false
    username: root
    password: 1234
    driver-class-name: com.mysql.cj.jdbc.Driver

mybatis:
  # 配置 mybatis xml 的文件路径，在 resources/mapper 创建所有表的 xml 文件
  mapper-locations: classpath:mybatis/**Mapper.xml
  configuration: # 配置打印 MyBatis日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true #配置驼峰自动转换

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.example.mybatisdemo4.model.UserInfo">

</mapper>

model代码如下：

@Data
public class UserInfo {
    private Integer id;
    private String username;
    private String password;
    private Integer age;
    private Integer gender;
    private String phone;
    private Integer deleteFlag;
    private Date createTime;
    private Date updateTime;
}

一、<if> 标签

在注册用户的时候，可能会有这样一个问题，如下图所示：

注册分为两种字段：必填字段和非必填字段，那如果在添加用户的时候有不确定的字段传入，程序应该如何实现呢？

这个时候就需要使用 动态标签 来判断了，比如添加的时候性别 gender 为非必填字段，具体实现如下：

userInfoMapper接口代码：

@Mapper
public interface UserInfoMapper {
    Integer insertByCondtion(UserInfo userInfo);
}

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.example.mybatisdemo4.mapper.UserInfoMapper">

    <insert id="insertByCondtion">
        insert into userinfo (username, password, age
        <if test="gender != null">
            , gender
        </if>) 
        values (#{username},#{password}, #{age}
        <if test="gender != null">
            , #{gender}
        </if>)
    </insert>

</mapper>

测试类代码：

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void insertByCondtion() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("zhaoliu");
        userInfo.setPassword("zhaoliu");
        userInfo.setAge(15);
//        userInfo.setGender(1);
        userInfoMapper.insertByCondtion(userInfo);
    }
}

表中gender属性默认是0，如图：

当我们不指定gender值时，直接执行上面代码，结果如下：

当指定gender的值时，代码如下：

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void insertByCondtion() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("zhaoliu");
        userInfo.setPassword("zhaoliu");
        userInfo.setAge(15);
        userInfo.setGender(1);
        userInfoMapper.insertByCondtion(userInfo);
    }
}

执行测试类代码，结果如下：

此时就是动态SQL语句了，想给他传值就变成所传的值，不想给他传值会有默认值0。也就变成复杂SQL了，之前博客写的那些都是简单标签，如果没有<if> 标签，单凭借之前的那些简单SQL，是不能做到上面这样的功能的。

上面的SQL语句也可以写成注解的形式，但是不推荐，其代码如下：

@Mapper
public interface UserInfoMapper {
    @Insert("<script>" +
            "insert into userinfo (username, password, age" +
            "<if test='gender != null'>, gender</if>)" +
            "values (#{username},#{password}, #{age}" +
            "<if test='gender != null'>, #{gender}</if>)"+
            "</script>")
    Integer insertByCondtion2(UserInfo userInfo);
}

（可以看出，使用注解的方式，不好写，也不好观察，可读性非常差）

测试代码如下：

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void insertByCondtion2() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("zhaoliu222");
        userInfo.setPassword("zhaoliu222");
        userInfo.setAge(25);
        userInfo.setGender(2);
        userInfoMapper.insertByCondtion(userInfo);
    }
}

运行测试代码，结果如下：

二、<trim> 标签

之前的插入用户功能，只是一个gender字段可能是选填项，如果有多个字段，一般考虑使用结合标签，对多个字段都采取动态生成的方式。

标签中有如下属性：

prefix：表示整个语句块，以 prefix 的值作为前缀。

suffix：表示整个语句块，以 suffix 的值作为后缀。

prefixOverrides：表示整个语句块要去除掉的前缀。

suffixOverrides：表示整个语句块要去除掉的后缀。

把username设置默认为admin，代码如下：

-- 创建数据库
DROP DATABASE IF EXISTS mybatis_test;
 
CREATE DATABASE mybatis_test DEFAULT CHARACTER SET utf8mb4;
 
-- 使用数据数据
USE mybatis_test;
 
-- 创建表[用户表]
DROP TABLE IF EXISTS userinfo;
CREATE TABLE `userinfo` (
        `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
        `username` VARCHAR ( 127 ) NOT NULL DEFAULT 'admin' COMMENT '默认admin',
        `password` VARCHAR ( 127 ) NOT NULL,
        `age` TINYINT ( 4 ) NOT NULL,
        `gender` TINYINT ( 4 ) DEFAULT '0' COMMENT '1-男 2-女 0-默认',
        `phone` VARCHAR ( 15 ) DEFAULT NULL,
        `delete_flag` TINYINT ( 4 ) DEFAULT 0 COMMENT '0-正常, 1-删除',
        `create_time` DATETIME DEFAULT now(),
        `update_time` DATETIME DEFAULT now(),
        PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; 
 
-- 添加用户信息
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'admin', 'admin', 18, 1, '18612340001' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'zhangsan', 'zhangsan', 18, 1, '18612340002' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'lisi', 'lisi', 18, 1, '18612340003' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'wangwu', 'wangwu', 18, 1, '18612340004' );

现在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.example.mybatisdemo4.mapper.UserInfoMapper">

    <insert id="insertByCondtion">
        insert into userinfo (
            <if test="username != null">username</if>
            <if test="password != null">, password</if>
            <if test="age != null">, age</if>
            <if test="gender != null">, gender</if>)
        values (
            <if test="username != null">#{username}</if>
            <if test="password != null">,#{password}</if>
            <if test="age != null">, #{age}</if>
            <if test="gender != null">, #{gender}</if>)
    </insert>

</mapper>

把setUsername的方法注释掉，测试类代码如下：

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void insertByCondtion() {
        UserInfo userInfo = new UserInfo();
//        userInfo.setUsername("zhaoliu");
        userInfo.setPassword("zhaoliu");
        userInfo.setAge(15);
        userInfo.setGender(1);
        userInfoMapper.insertByCondtion(userInfo);
    }
}

运行测试类代码，结果如下：

这时候使用 <trim>标签，代码如下：

<?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.example.mybatisdemo4.mapper.UserInfoMapper">

    <insert id="insertByCondtion">
        insert into userinfo
            <trim prefix="(" suffix=")" prefixOverrides=",">
                <if test="username != null">username</if>
                <if test="password != null">, password</if>
                <if test="age != null">, age</if>
                <if test="gender != null">, gender</if>
            </trim>
            values
            <trim prefix="(" suffix=")" prefixOverrides=",">
                <if test="username != null">#{username}</if>
                <if test="password != null">,#{password}</if>
                <if test="age != null">, #{age}</if>
                <if test="gender != null">, #{gender}</if>
            </trim>
    </insert>

</mapper>

这时候再执行测试类代码：

class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void insertByCondtion() {
        UserInfo userInfo = new UserInfo();
//        userInfo.setUsername("zhaoliu");
        userInfo.setPassword("zhaoliu");
        userInfo.setAge(15);
        userInfo.setGender(1);
        userInfoMapper.insertByCondtion(userInfo);
    }
}

结果如下：

成功插入数据了。

还有一种使用注解的方式，代码会很乱，很复杂，不建议使用，其代码如下：

@Insert("<script>" +
"INSERT INTO userinfo " +
"<trim prefix='(' suffix=')' prefixOverrides=','>" +
"<if test='username!=null'>,username</if>" +
"<if test='password!=null'>,password</if>" +
"<if test='age!=null'>，age</if>" +
"<if test='gender!=null'>，gender</if>" +
"</trim>" +
"VALUES " +
"<trim prefix='(' suffix=')' prefixOverrides=','>" +
"<if test='username!=null'>,#{username}</if>" +
"<if test='password!=null'>,#{password}</if>" +
"<if test='age!=null'>,#{age}</if>" +
"<if test='gender!=null'>,#{gender}</if>" +
"</trim>"+
"</script>")
Integer insertByCondtion(UserInfo userInfo);

上面SQL解析：

1、基于 prefix 配置，开始部分加上 (

2、基于 suffix 配置，结束部分加上 )

3、多个组织的语句都以 " , " 开头，拼接好的字符串还会以 " , " 开头，会基于 suffixOverrides 配置去掉开头的一个 " , " （prefixOverrides则相反，是结尾）

4、注意 < if test="username != null"> 中的 username 是传入对象的属性

三、<where> 标签

如图：通过下面的筛选条件，系统会根据我们的筛选条件，动态组装 where 条件。

数据库表的信息如下：

UserInfoMapper接口代码：

@Mapper
public interface UserInfoMapper {
    List<UserInfo> queryUserByCondition(UserInfo userInfo);
}

下面不使用<where>和<trim>标签，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.example.mybatisdemo4.mapper.UserInfoMapper">

    <select id="queryUserByCondition" resultType="com.example.mybatisdemo4.model.UserInfo">
        select * from userinfo where
        <if test="age != null">age = #{age}</if>
        <if test="gender != null">and gender = #{gender}</if>
        <if test="deleteFlag != null">and delete_flag = #{deleteFlag}</if>
    </select>

</mapper>

测试类代码：

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void queryUserByCondition() {
        UserInfo userInfo = new UserInfo();
//        userInfo.setAge(15);
        userInfo.setGender(1);
        userInfo.setDeleteFlag(0);
        System.out.println(userInfoMapper.queryUserByCondition(userInfo));
    }
}

如果把设置age给注释掉，是可以成功运行的，但注释掉后，就会报错，报错信息如下：

SQL错误。

现在给XML内容加上<trim>标签，内容如下：

<?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.example.mybatisdemo4.mapper.UserInfoMapper">
    <select id="queryUserByCondition" resultType="com.example.mybatisdemo4.model.UserInfo">

        select * from userinfo
        <trim prefix="where" prefixOverrides="and">
            <if test="age != null">age = #{age}</if>
            <if test="gender != null">and gender = #{gender}</if>
            <if test="deleteFlag != null">and delete_flag = #{deleteFlag}</if>
        </trim>
    </select>

</mapper>

再次运行测试代码，不会报错了，运行结果如下：

上面这样写有点不专业，Mybatis提供了<where>标签，所以可以不使用<trim>标签，使用<where>标签的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.example.mybatisdemo4.mapper.UserInfoMapper">

    <select id="queryUserByCondition" resultType="com.example.mybatisdemo4.model.UserInfo">
        select * from userinfo
        <where>
            <if test="age != null">and age = #{age}</if>
            <if test="gender != null">and gender = #{gender}</if>
            <if test="deleteFlag != null">and delete_flag = #{deleteFlag}</if>
        </where>
    </select>

</mapper>

可以看到，<where>标签代替了<trim>标签的prefix和prefixOverrides，运行刚才的测试类代码，运行结果如下：

注意：<where> 只会在子元素有内容的情况下才插入where子句，而且会自动去除子句开头（或结尾）的and或or。上面标签虽然可以使用<trim prefix="where" prefixOverrides="and">替换，但是此种情况下，当子元素都没有内容时，where 关键字也会保留。

四、<set> 标签

SQL语句：

update userinfo set username = ?, age = ?, delete_flag = ? where id = ?

UserInfoMapper接口代码：

@Mapper
public interface UserInfoMapper {
    Integer updateUserByCondition(UserInfo userInfo);
}

xml内容如下：（使用<trim>标签）

<?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.example.mybatisdemo4.mapper.UserInfoMapper">

    <update id="updateUserByCondition">
        update userinfo
        <trim prefix="set" prefixOverrides=",">
            <if test="username != null">username = #{username}</if>
            <if test="age != null">,age = #{age}</if>
            <if test="deleteFlag != null">,delete_flag = #{deleteFlag}</if>
        </trim>
        where id = #{id}
    </update>

</mapper>

测试类代码：

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void updateUserByCondition() {
        UserInfo userInfo = new UserInfo();
        userInfo.setId(6);
        userInfo.setUsername("wangba");
        userInfo.setAge(8);
        userInfo.setDeleteFlag(0);
        userInfoMapper.updateUserByCondition(userInfo)
    }
}

SQL表如下：

运行测试类，结果如下：

上面使用<trim>标签不太专业，针对update(改)可以使用<set>标签

改变xml内容：（使用<set>标签）

<?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.example.mybatisdemo4.mapper.UserInfoMapper">

    <update id="updateUserByCondition">
        update userinfo
        <set>
            <if test="username != null">username = #{username}</if>
            <if test="age != null">,age = #{age}</if>
            <if test="deleteFlag != null">,delete_flag = #{deleteFlag}</if>
        </set>
        where id = #{id}
    </update>

</mapper>

（其中where关键字可以使用<where>标签，但是因为where后的id是肯定必须要有的，可以直接写上）

测试类代码如下：

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void updateUserByCondition() {
        UserInfo userInfo = new UserInfo();
        userInfo.setId(5);
//        userInfo.setUsername("wangba");
        userInfo.setAge(8);
        userInfo.setDeleteFlag(1);
        userInfoMapper.updateUserByCondition(userInfo);
    }
}

运行测试类代码，结果如下：

可以看到，<set>标签代替了<trim prefix="set" prefixOverrides=",">，说明动态SQL语句中插入set关键字，并且会删掉额外的逗号。（用于update语句中）

五、<foreach> 标签

对集合进行遍历时可以使用该标签。标签有如下属性：

1、collection：绑定方法参数中的集合，如 List、Set、Map或数组对象。

2、item：遍历时的每一个对象。

3、open：语句块开头的字符串。

4、close：语句块结束的字符串。

5、separator：每次遍历之间间隔的字符串。

原本SQL语句：

delete from userinfo where id in (1, 2, 3, 4)

数据库表信息如下：

UserInfoMapper接口代码如下：

@Mapper
public interface UserInfoMapper {
    Integer batchDelete(List<Integer> ids);
}

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.example.mybatisdemo4.mapper.UserInfoMapper">

    <delete id="batchDelete">
        delete from userinfo 
        where id in
        <foreach collection="ids" open="(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </delete>

</mapper>

测试代码如下：

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void batchDelete() {
        List<Integer> ids = Arrays.asList(new Integer[]{1, 2, 3, 4});
        userInfoMapper.batchDelete(ids);
    }
}

运行测试类代码，结果如下：数据只剩下两个了

不仅可以批量删除，也可以批量插入。写法相似，类比上面代码。

六、<include> 标签

在xml映射文件中配置的SQL，有时候可能会存在很多重复的片段，此时就会存在很多冗余的代码。现在的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.example.mybatisdemo4.mapper.UserInfoMapper">

    <select id="queryAllUser" resultType="com.example.mybatisdemo4.model.UserInfo">
        select id, username, age, gender, phone, delete_falg, create_time, update_time  from userinfo
    </select>

    <select id="queryById" resultType="com.example.mybatisdemo4.model.UserInfo">
        select id, username, age, gender, phone, delete_falg, create_time, update_time from userinfo where id=#{id}
    </select>
    
</mapper>

有很多冗余的代码，如图：

我们可以对重复的片段进行抽取，将其通过<sql>标签封装到一个SQL片段，然后再通过<include>标签进行引用。

<sql>：定义可重用的SQL片段。

<include>：通过属性refid，指定包含的SQL片段。

现在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.example.mybatisdemo4.mapper.UserInfoMapper">

    <sql id="allColumn">
        id, username, age, gender, phone, delete_falg, create_time, update_time
    </sql>

    <select id="queryAllUser" resultType="com.example.mybatisdemo4.model.UserInfo">
        select <include refid="allColumn"></include>  from userinfo
    </select>

    <select id="queryById" resultType="com.example.mybatisdemo4.model.UserInfo">
        select <include refid="allColumn"></include> from userinfo where id=#{id}
    </select>
    
</mapper>

通过<include>标签，去除掉了重复出现的代码。

tao滔不绝

关注

47
点赞
踩
56

收藏

觉得还不错? 一键收藏
打赏
32
评论
Mybatis进阶——动态SQL（1）

目录一、 <if> 标签二、<trim> 标签三、<where> 标签四、<set> 标签五、<foreach> 标签六、<include> 标签动态SQL 是Mybatis的强大特性之一，能够完成不同条件下的不同SQL拼接，可以参考官方文档：动态 SQL_MyBatis中文网准备工作： SQL语句： yml配置内容： xml文件内容如下： model代码如下：一、<if> 标签在注册用户的时
复制链接

扫一扫