【MyBatis11】动态SQL

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

1 <sql> 标签

<sql> 标签的作用是将重复冗余的SQL语句抽取出来,以达到简化开发的目的。

例如:

    <select id="queryUserById" resultMap="user_resultMap">
        select id,username,password,gender,regist_time
        from t_user
        where id=#{id}
    </select>
    <select id="queryUserByUsernameAndPassword" resultMap="user_resultMap">
        select  id,username,password,gender,regist_time as registerTime
        from t_user
        where username=#{username} and password=#{password}
    </select>
    <select id="queryUserByLikeUsername" resultMap="user_resultMap">
        select id,username,password,gender,regist_time as registerTime
        from t_user
        where username like concat('%',#{input})
    </select>

则可以将

 

        select id,username,password,gender,regist_time
        from t_user

这一部分的SQL给抽取出来。

 

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

<sql>标签包起来的SQL就可以通过id:user_field来引用。

 

    <select id="queryUserById" resultMap="user_resultMap">
        <include refid="user_field"/>
        where id=#{id}
    </select>
    <select id="queryUserByUsernameAndPassword" resultMap="user_resultMap">
        <include refid="user_field"/>
        where username=#{username} and password=#{password}
    </select>
    <select id="queryUserByLikeUsername" resultMap="user_resultMap">
        <include refid="user_field"/>
        where username like concat('%',#{input})
    </select>
  • <include refid="user_field"/>

    • 引用抽取出来的sql片段,refid和<sql>标签中的id一致

如此这般,便可以减少书写大量的重复代码,并且方便后续的修改。

通过log4j日志工具查看拼接的SQL语句:

==>  Preparing: select id,username,password,gender,regist_time from t_user where id=? 
==>  Preparing: select id,username,password,gender,regist_time from t_user where username like concat('%',?) 
==>  Preparing: select id,username,password,gender,regist_time from t_user where username=? and password=? 

2 <if>标签

<if> 标签的作用是进行判断,可以用来去复合多个查询条件,可以将原本需要些多个方法才能完成的查询复合成一个方法。

例如:

User queryUserByIdOrUsername(User user);

此接口的作用是根据传入的user对象,如果id属性不为空,则进行根据id查询;如果username属性不为空,则进行根据username查询。

为这个接口配置Mapper.xml:

    <select id="queryUserByIdOrUsername" resultMap="user_resultMap">
        <include refid="user_field"/>
        where
        <if test="id != null">
            id = #{id}
        </if>
        <if test="username != null">
            username = #{username}
        </if>
    </select>
  • <if test="id != null">

    • 进行条件判断

    • test中的id相当于<if>标签外的#{id},不用写#{},从方法的参数中获取id属性

  • 查询输出的结果是不固定的,这也是被称为动态SQL的原因

进行测试

        User user1 = new User();
//        user1.setUsername("Mickey");
//        user1.setId(5);
        User user = userDao.queryUserByIdOrUsername(user1);
  • 如果将// user1.setUsername("Mickey");放开,则拼接出来的SQL语句是:

 

==>  Preparing: select id,username,password,gender,regist_time from t_user where username = ? 
  • 如果将// user1.setId(5);放开,则拼接出来的SQL语句是

 

==>  Preparing: select id,username,password,gender,regist_time from t_user where id = ? 

3 <where>标签

如果将7.2 中的接口需求改成:如果id属性不为空,则进行根据id查询;如果username属性不为空,则进行根据username查询;如果id和username都不为空,则根据id or username进行查询。

此时如果直接在<if>标签中添加or:

    <select id="queryUserByIdOrUsername" resultMap="user_resultMap">
        <include refid="user_field"/>
        where
        <if test="id != null">
            id = #{id}
        </if>
        <if test="username != null">
            or username = #{username}
        </if>
    </select>

 queryUserByIdOrUsername的返回值类型修改为List<User>类型以接收多个User:

    List<User> queryUserByIdOrUsername(User user);

测试方法:

        UserDao userDao = MyBatisUtil.getMapper(UserDao.class);
        User user1 = new User();
//        user1.setUsername("Mickey");
//        user1.setId(5);
        List<User> users = userDao.queryUserByIdOrUsername(user1);
        for (User user : users) {
            System.out.println(user);
        }
  • // user1.setUsername("Mickey");// user1.setId(5);同时放开

    • 此时没有问题,拼接出来的SQL语句是:==> Preparing: select id,username,password,gender,regist_time from t_user where id = ? or username = ?

  • // user1.setId(5);放开

    • 此时没有问题,拼接出来的SQL语句是:==> Preparing: select id,username,password,gender,regist_time from t_user where id = ?

  • // user1.setUsername("Mickey");放开

    • 此时运行报错,拼接出来的SQL语句是:==> Preparing: select id,username,password,gender,regist_time from t_user where or username = ?存在语法错误

    • 很明显where or存在问题,这是由于<if>标签中的or username = #{username}导致的,此时就需要使用到<where>标签

<where>标签的作用:

  • 补充一个where关键字

  • 忽略where子句中的前后缀,如and、or

修改Mapper.xml

    <select id="queryUserByIdOrUsername" resultMap="user_resultMap">
        <include refid="user_field"/>
        <where>
            <if test="id != null">
                id = #{id}
            </if>
            <if test="username != null">
                or username = #{username}
            </if>
        </where>
    </select>

再次进行测试,发现此时已经将where子句中的or给忽略掉了

==>  Preparing: select id,username,password,gender,regist_time from t_user WHERE username = ? 

4 <set>标签

<set>标签的使用主要是针对UPDATE操作。

如果传入的对象的某个属性不为空,则更新那个属性,Mapper.xml如下:

    <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>
        where id=#{id}
    </update>

测试方法:

        User user = new User(2, "TestUpdate1111", "TestUpdate", 1, new Date());
        mapper.updateUser(user);
        sqlSession.commit();

此时是可以正常拼接出SQL的:

==>  Preparing: update t_user set username=?, password=?, gender=?, regist_time=? where id=? 

但如果将registerTime属性置为空:

        User user = new User(2, "TestUpdate1111", "TestUpdate", 1, null);
        mapper.updateUser(user);
        sqlSession.commit();

此时便会出现SQL语法错误,拼接出来的SQL是:

==>  Preparing: update t_user set username=?, password=?, gender=?, where id=? 

很明显是, where id=?出了问题,此时就需要<set>标签。

<set>标签的作用是:

  • 补充一个set关键字

  • 忽略set子句中的前后缀,如“,”

修改Mapper.xml:

    <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>

再次测试,发现此时已经能够正常拼接出SQL语句

==>  Preparing: update t_user SET username=?, password=?, gender=? where id=? 

5 <trim>标签

<trim>标签可以等价替换掉<where>标签和<set>标签。

  • <trim prefix="where" prefixOverrides="and|or"></trim>等价于<where></where>

    • 增加where前缀

    • 如果where以and 或or 开头,则进行忽略

  • <trim prefix="set" suffixOverrides=","></trim>等价于<set></set>

    • 增加set前缀

    • 如果set以“,”结尾,则进行忽略

<trim>标签的语法格式是:

<trim prefix="" suffix="" prefixOverrides="" suffixOverrides="">

</trim>
  • prefix="":补充前缀内容

  • suffix="":补充后缀内容

  • prefixOverrides="":前缀忽略

  • suffixOverrides="":后缀忽略

6 <foreach>标签

<foreach>标签主要用于批量操作(批量删除、批量更新、批量增加),可以按需求来拼接出动态SQL,语法格式:

        <foreach collection="" open="" separator="" close="" item="" index="">
            #{}
        </foreach>
  • collection="":容器类型(list、array、map),代表方法的参数是什么类型的。

  • open="":起始符

  • separator="":分隔符

  • close="":结束符

  • item="":当前项,存储遍历到的数据,可以是任意值。

    • 在标签内部通过#{xx}来访问,此处的xx是“”号中的值

  • index="":下标号

例如:

6.1 批量删除

根据id来批量删除数据库中的数据,需要拼接的SQL是delete from t_user where id in ( ? , ? , ? ......)

DAO接口:

    void deleteManyUsersById(List<Integer> ids);

Mapper.xml:

    <delete id="deleteManyUsersById" parameterType="java.util.List">
        delete from t_user where id in
        <foreach collection="list" open="(" separator="," close=")" item="ids">
            #{ids}
        </foreach>
    </delete>
  • parameterType="java.util.List":参数类型是List类型

  • <foreach collection="list" open="(" separator="," close=")" item="ids">

    • collection="list:需要拼接的SQL接收的参数是list

    • open="(":拼接出来的SQL以”(“开头

    • separator=",":用”,“分隔每一次拼接

    • close=")":拼接出来的SQL以”)“结尾

    • item="ids":遍历到的数据存储到”ids“这个临时变量中

  • #{ids}:输出每次遍历ids中存储的数据至需要拼接的SQL

测试用例:

        UserDao mapper = MyBatisUtil.getMapper(UserDao.class);
        List<Integer> list = Arrays.asList(7, 8, 9);
        mapper.deleteManyUsersById(list);

查看最终拼接出来的SQL:

==>  Preparing: delete from t_user where id in ( ? , ? , ? ) 
==> Parameters: 7(Integer), 8(Integer), 9(Integer)

6.2 批量增加

向数据库中批量插入数据,需要拼接出来的SQL是insert into t_user values (?,?,?,?,?...) , (?,?,?,?,?...) , (?,?,?,?,?...)......

DAO接口:

    void insertManyUsers(List<User> users);

Mapper.xml:

    <insert id="insertManyUsers" parameterType="java.util.List">
        insert into t_user values
        <foreach collection="list" open="" separator="," close="" item="users">
            (null,#{users.username},#{users.password},#{users.gender},#{users.registerTime})
        </foreach>
    </insert>
  • {users.registerTime}:遍历得到的数据暂存在item="users"中,users是Java的类对象,所以取值应该是.类属性

测试用例:

        UserDao mapper = MyBatisUtil.getMapper(UserDao.class);
        List<User> users = Arrays.asList(
                new User(null, "many1", "many1", 0, new Date()),
                new User(null, "many2", "many2", 1, new Date()),
                new User(null, "many3", "many3", 0, new Date())
        );
        mapper.insertManyUsers(users);
        MyBatisUtil.commit();

查看最终拼接出来的SQL:

==>  Preparing: insert into t_user values (null,?,?,?,?) , (null,?,?,?,?) , (null,?,?,?,?) 
==> Parameters: many1(String), many1(String), 0(Integer), 2022-07-31 14:32:16.699(Timestamp), many2(String), many2(String), 1(Integer), 2022-07-31 14:32:16.7(Timestamp), many3(String), many3(String), 0(Integer), 2022-07-31 14:32:16.7(Timestamp)

版权声明:
作者:jackqiang
链接:http://www.jackqiang.com/framework/mybatis/1949/dynamic_sql/
来源:JackQiang's
文章版权归作者所有,未经允许请勿转载。

THE END
分享
二维码
< <上一篇
下一篇>>
文章目录
关闭
目 录