【MyBatis】(4)动态SQL

【MyBatis】(4)动态SQL

前言

该文章记录了MaBatis动态SQL常用标签。

一、动态SQL 介绍

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。

使用动态 SQL 并非一件易事,但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言,MyBatis 显著地提升了这一特性的易用性。

如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器,你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中,需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。

  1. if
  2. choose (when, otherwise)
  3. trim (where, set)
  4. foreach

动态sql就是:可以通过标签动态的拼接sql命令。

官方文档 介绍的挺不错的,我这边将给出一些动态sql的增删改查实践。

二、动态SQL 案例

该案例实现了用户的增删改查。

1.实体类

实体类:使用了lombok

@Data
@AllArgsConstructor
public class User {
    private int id;
    private String name;
    private String password;
    private int age;
    private String phone;
    private String email;
}

2.接口

UserMapper接口

public interface UserMapper {
    int insUser(User u);

    int delUserById(int id);

    int delUserByIds(String[] id);

    int updUser(User u);

    User selUserById(int id);

    List<User> selUserList(User user);
}

3.xml映射文件

xml映射文件,将动态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">
<mapper namespace="com.shengjava.mapper.UserMapper">
    <!-- 结果映射 -->
    <resultMap id="userResultMap" type="User">
        <id property="id" column="user_id"/>
        <result property="name" column="user_name"/>
        <result property="password" column="user_password"/>
        <result property="age" column="user_age"/>
        <result property="phone" column="user_phone"/>
        <result property="email" column="user_email"/>
    </resultMap>
    <!-- 插入语句 -->
    <insert id="insUser" parameterType="User">
        insert into user
        <!-- 动态拼接 -->
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">user_id,</if>
            <if test="name != null">user_name,</if>
            <if test="password != null">user_password,</if>
            <if test="age != null">user_age,</if>
            <if test="phone != null">user_phone,</if>
            <if test="email != null">user_email</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="name != null">#{name},</if>
            <if test="password != null">#{password},</if>
            <if test="age != null">#{age},</if>
            <if test="phone != null">#{phone},</if>
            <if test="email != null">#{email},</if>
        </trim>
    </insert>
    <!-- 删除语句 -->
    <delete id="delUserById" parameterType="int">
        delete
        from user
        where user_id = #{id};
    </delete>
    <!-- 删除语句 -->
    <delete id="delUserByIds" parameterType="String">
        delete
        from user
        where user_id in
        <!-- 动态拼接(遍历) -->
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <!-- 修改语句 -->
    <update id="updUser" parameterType="User">
        update user
        <trim prefix="SET" suffixOverrides=",">
            <if test="name != null">user_name = #{name},</if>
            <if test="password != null">user_password = #{password},</if>
            <if test="age != null">user_age = #{age},</if>
            <if test="phone != null">user_phone = #{phone},</if>
            <if test="email != null">user_email = #{email},</if>
        </trim>
        where user_id = #{id}
    </update>
    <!-- sql查询语句片段 -->
    <sql id="selUser">
        select user_id, user_name, user_password, user_age, user_phone, user_email
        from user
    </sql>
    <!-- 根据id查询用户信息 -->
    <select id="selUserById" parameterType="int" resultMap="userResultMap">
        <!-- 引入sql语句片段 -->
        <include refid="selUser"/>
        where user_id = #{id}
    </select>
    <!-- 查询复合条件的用户列表(如果传入空用户,则查询全部) -->
    <select id="selUserList" resultMap="userResultMap" parameterType="User">
        <include refid="selUser"/>
        <!-- where语句 -->
        <where>
            <!-- 动态拼接 -->
            <if test="id != null ">and user_id = #{id}</if>
            <if test="name != null ">and user_name = #{name}</if>
            <if test="password != null ">and user_password = #{password}</if>
            <if test="age != null ">and user_age = #{age}</if>
            <if test="phone != null ">and user_phone = #{phone}</if>
            <if test="email != null ">and user_email = #{email}</if>
        </where>
    </select>
</mapper>

测试类:
测试增删改查,不过需要注意,需要手动提交一下事务。

public class MyBatisTest {
    SqlSessionFactory sqlSessionFactory;

    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void insert() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper mapper = session.getMapper(UserMapper.class);
            int insCount = 0;
            User user = new User(101, "zhangsan", "123456", 22, "18812341234", "123456@qq.com");
            insCount += mapper.insUser(user);
            /*for (int id = 102; id <= 105; id++) {
                User u = new User(id, "zhangsan", "123456", 22, "18812341234", "123456@qq.com");
                insCount += mapper.insUser(u);
            }*/
            // 提交事务
            session.commit();
            System.out.println("成功插入条数:" + insCount);
        }
    }

    @Test
    public void deleteById() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper mapper = session.getMapper(UserMapper.class);
            int i = mapper.delUserById(101);
            // 提交事务
            session.commit();
            System.out.println("成功删除条数:" + i);
        }
    }

    @Test
    public void deleteByIds() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper mapper = session.getMapper(UserMapper.class);
            String[] ids = {"102", "103", "104", "105"};
            int i = mapper.delUserByIds(ids);
            // 提交事务
            session.commit();
            System.out.println("成功删除条数:" + i);
        }
    }
    @Test
    public void upd() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper mapper = session.getMapper(UserMapper.class);
            User user = new User(101, "updatezhangsan", "update123456", 55, "update18812341234", "update123456@qq.com");
            int i = mapper.updUser(user);
            // 提交事务
            session.commit();
            System.out.println("成功修改条数:" + i);
        }
    }
    @Test
    public void select() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper mapper = session.getMapper(UserMapper.class);
            User user = mapper.selUserById(1);
            System.out.println(user);
        }
    }
    @Test
    public void selectList() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper mapper = session.getMapper(UserMapper.class);
            // 查询全部则直接传null,如果需要查询指定值则创建User对象并赋值即可
            List<User> users = mapper.selUserList(null);
            System.out.println(users);
        }
    }
}

参考

官方文档:动态 SQL