MyBatis详解(3)--动态SQL

需求缘起

前面两篇文章,我们进行的SQL操作都是比较简单的,当遇到复杂sql的时候,写起来就会很麻烦且死板,稍微不注意,由于引号,空格等缺失可能都会导致错误。MyBatis 为我们提供了动态 SQL,可以让我们摆脱这种烦恼。这一节我们就来学下动态SQL。

本小节代码
mybatis-demo-article(3)

准备工作

为了方便更好的说明动态SQL的使用,对 user_info 数据表多增加几个字段,同时相应的在实体类中也增加这个字段

@Data
public class UserInfo {
    private Long id;
    private String username;
    private String password;
    private Integer high;
    private Integer age;
    private Integer sex;
}

添加数据后如下:
在这里插入图片描述

提示: 这一节要在第一节的基础上改动,这样代码改变就很少了。

1.if用法

需求:通过用户名和性别来查询用户,如果用户名为空,则只根据性别查询,

1.1 原生SQL的写法

    <select id="selectUserInfoByUsernameAndSex"
            resultType="UserInfo" parameterType="UserInfo">
        <!-- 这里和普通的sql 查询语句差不多,对于只有一个参数,后面的 #{id}表示占位符,里面不一定要写id,
                写啥都可以,但是不要空着,如果有多个参数则必须写pojo类里面的属性 -->
        select * from user_info where username=#{username} and sex=#{sex}
    </select>

转换成普通的 sql: select * from user_info where username=? and sex=?
测试方法代码

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserInfoMapperTest {
    private static SqlSessionFactory sqlSessionFactory;
    private static SqlSession sqlSession = null;

    @BeforeClass
    public static void init() {
        try {
            //将工具类读入 reader
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            //创建 SqlSessionFactory 对象,该对象包含了mybatis-config.xml相关配置信息
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            sqlSession = sqlSessionFactory.openSession();
            reader.close();
        } catch (IOException ignore) {
            ignore.printStackTrace();
        }
    }


    //根据id查询user表数据
    @Test
    public void testSelectUserInfoByUsernameAndSex() {
        try {
        /*这个字符串由 userMapper.xml 文件中 两个部分构成
            <mapper namespace="com.mybatis.demo"> 的 namespace 的值
            <select id="selectUserById" > id 值*/
            /*可以单独写 <select id="selectUserById" > id 值*/

//            String statement = "com.mybatis.demo.selectUserInfoByUsernameAndSex";
            String statement = "selectUserInfoByUsernameAndSex";
            UserInfo userInfo = new UserInfo();
            userInfo.setUsername("Jack");
            userInfo.setSex(1);
            List<UserInfo> result = sqlSession.selectList(statement, userInfo);
            log.info("result={}", result);
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }
}

此时如果用户名或者性别为空,那么结果为空,不符合我们的需求。我们可以使用 if 语句来判断

1.2 动态SQL的写法

    <select id="selectUserInfoByUsernameAndSex" resultType="UserInfo" parameterType="UserInfo">
        select * from user_info where
        <if test="username != null">
            username=#{username}
        </if>
        <if test="username != null">
            and sex=#{sex}
        </if>
    </select>

再次调用上面的测试方法,当用户名为空时,我们发现请求报错了。因为当用户名为空时,SQL语句是:
select * from user where and sex=#{sex};当性别为空时,sql是:select * from user where username=#{username}。显然前一个语句是错的,那么我们该怎么解决呢?请看 if + where 用法

2.if + where 用法

    <select id="selectUserInfoByUsernameAndSex" resultType="UserInfo" parameterType="UserInfo">
        select * from user_info
        <where>
            <if test="username != null">
                username=#{username}
            </if>

            <if test="sex != null">
                and sex=#{sex}
            </if>
        </where>
    </select>

<where>…</where> 标签会知道如果它包含的标签中有返回值的话,它就插入一个‘where’。此外,如果标签返回的内容是以AND 或OR 开头的,则它会剔除掉。

3.if + set 用法

<set> … 、 标签的用法和 <where>…</where> 相似,

 <!-- 根据 id 更新 user_info 表的数据 -->
    <update id="updateUserInfoById" parameterType="UserInfo">
        update user_info u
        <set>
            <if test="username != null and username != ''">
                u.username = #{username},
            </if>
            <if test="sex != null and sex != ''">
                u.sex = #{sex}
            </if>
        </set>
        where id=#{id}
    </update>

当username 为空时,SQL语句为:update user_info u SET u.sex = ? where id=?
当sex为空时,SQL语句为:update user_info u SET u.username = ? where id=? ,对没错,** set 标签会剔除最后的"," **

3.choose 用法

需求:当我们查询时有很多条件,但是只要满足其中一条即可,类似的需求可以使用 choose 标签

    <!--choose(when,otherwise) 标签-->
    <select id="selectUserInfoByChoose" resultType="UserInfo" parameterType="UserInfo">
        select * from user_info
        <where>
            <choose>
                <when test="id !='' and id != null">
                    id=#{id}
                </when>
                <when test="username !='' and username != null">
                    and username=#{username}
                </when>
                <otherwise>
                    and sex=#{sex}
                </otherwise>
            </choose>
        </where>
    </select>

查询总共有三个条件,但是只要有一个满足条件了,后面就不在判断了。
当id 不为空时,sql语句为 select * from user_info WHERE id=?
当id 为空,当username不为空时,sql语句为 select * from user_info WHERE username=?
如果username 也为空时,sql语句为 select * from user_info WHERE sex=?

5. trim 用法

trim标记是一个格式化的标记,可以完成set或者是where标记的功能
5.1 用 trim 改写上面第二点的 if+where 语句

 <select id="selectUserInfoByUsernameAndSex" resultType="UserInfo" parameterType="UserInfo">
        select * from user_info
        <!--<where>-->
            <!--<if test="username != null">-->
                <!--username=#{username}-->
            <!--</if>-->

            <!--<if test="sex != null">-->
                <!--and sex=#{sex}-->
            <!--</if>-->
        <!--</where>-->
         <!--prefix:标签;prefixoverride:去掉第一个and或者是or -->
        <trim prefix="where" prefixOverrides="and | or">
            <if test="username != null">
                and username=#{username}
            </if>
            <if test="sex != null">
                and sex=#{sex}
            </if>
        </trim>
    </select>

5.2 用 trim 改写上面第三点的 if+set 语句

     <update id="updateUserInfoById" parameterType="UserInfo">
        update user_info u
        <!--<set>-->
            <!--<if test="username != null and username != ''">-->
                <!--u.username = #{username},-->
            <!--</if>-->
            <!--<if test="sex != null and sex != ''">-->
                <!--u.sex = #{sex}-->
            <!--</if>-->
        <!--</set>-->
        <!--prefix:标签; suffixOverrides:去掉最后一个逗号(也可以是其他的标记)-->
        <trim prefix="set" suffixOverrides=",">
            <if test="username != null and username != ''">
                u.username = #{username},
            </if>
            <if test="sex != null and sex != ''">
                u.sex = #{sex},
            </if>
        </trim>
        where id=#{id}
    </update>

6. SQL片段

有时候可能某个 sql 语句我们用的特别多,为了增加代码的重用性,简化代码,我们需要将这些代码抽取出来,然后使用时直接调用。
比如:假如我们需要经常根据用户名和性别来进行联合查询,那么我们就把这个代码抽取出来,如:

6.1 SQL片段

    <!-- 定义 sql 片段 -->
    <sql id="selectUserByUserNameAndSexSQL">
        <if test="username != null and username != ''">
            AND username = #{username}
        </if>
        <if test="sex != null and sex != ''">
            AND sex = #{sex}
        </if>
    </sql>

6.2 引用sql片段

    <select id="selectUserInfoByUsernameAndSex" resultType="UserInfo" parameterType="UserInfo">
        select * from user_info
     
        <!--prefix:标签;prefixoverride:去掉第一个and或者是or -->
        <trim prefix="where" prefixOverrides="and | or">
            <!-- 引用 sql 片段,如果refid 指定的不在本文件中,那么需要在前面加上 namespace -->
            <include refid="selectUserByUserNameAndSexSQL"></include>
            <!-- 在这里还可以引用其他的 sql 片段 -->
        </trim>
    </select>

注意
①、最好基于 单表来定义 sql 片段,提高片段的可重用性
②、在 sql 片段中最好不要包括 where

7.foreach 用法

需求:查询id为1,2,4,5的用户,一般的sql语句为:
select * from user where id=1 or id=2 or id=3;
或者
select * from user where id in (1,2,3);

7.1 建立一个 UserForm 类,里面封装一个 List ids 的属性

@Data
public class UserInfoForm {
    private List<Integer> ids;
}

7.2 用 foreach 改写 select * from user where id=1 or id=2 or id=3;

    <select id="selectUserByListId" parameterType="com.mybatis.demo.model.UserInfoForm" resultType="UserInfo">
        select * from user_info
        <where>
            <!--
                collection:指定输入对象中的集合属性
                item:每次遍历生成的对象
                open:开始遍历时的拼接字符串
                close:结束时拼接的字符串
                separator:遍历对象之间需要拼接的字符串
                select * from user where 1=1 and (id=1 or id=2 or id=3)
              -->
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

如果 ids 为空,则sql语句为:select * from user_info
如果ids 不为空,则sqql 语句为 : select * from user_info WHERE ( id=? or id=? or id=? or id=? )

7.3 用 foreach 来改写 select * from user where id in (1,2,3)

    <select id="selectUserByListId" parameterType="com.mybatis.demo.model.UserInfoForm" resultType="UserInfo">
        select * from user_info
        <where>
            <!--
                collection:指定输入对象中的集合属性
                item:每次遍历生成的对象
                open:开始遍历时的拼接字符串
                close:结束时拼接的字符串
                separator:遍历对象之间需要拼接的字符串
                select * from user where 1=1 and id in (1,2,3)
              -->
            <foreach collection="ids" item="id" open="and id in (" close=") " separator=",">
                #{id}
            </foreach>
        </where>
    </select>

如果 ids 为空,则sql语句为:select * from user_info
如果ids 不为空,则sql 语句为 : select * from user_info WHERE id in ( ? , ? , ? , ? )

7.4 测试

    package com.mybatis.demo.mapper;

import com.mybatis.demo.model.UserInfo;
import com.mybatis.demo.model.UserInfoForm;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;

/**
 * @auther kklu
 * @date 2019/9/29 15:49
 * @describe
 */

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserInfoMapperTest {
    private static SqlSessionFactory sqlSessionFactory;
    private static SqlSession sqlSession = null;

    @BeforeClass
    public static void init() {
        try {
            //将工具类读入 reader
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            //创建 SqlSessionFactory 对象,该对象包含了mybatis-config.xml相关配置信息
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            sqlSession = sqlSessionFactory.openSession();
            reader.close();
        } catch (IOException ignore) {
            ignore.printStackTrace();
        }
    }


    //根据id查询user表数据
    @Test
    public void testSelectUserInfoByUsernameAndSex() {
        try {
        /*这个字符串由 userMapper.xml 文件中 两个部分构成
            <mapper namespace="com.mybatis.demo"> 的 namespace 的值
            <select id="selectUserById" > id 值*/
            /*可以单独写 <select id="selectUserById" > id 值*/

//            String statement = "com.mybatis.demo.selectUserInfoByUsernameAndSex";
            String statement = "selectUserInfoByUsernameAndSex";
            UserInfo userInfo = new UserInfo();
            userInfo.setUsername("Jack");
            userInfo.setSex(1);
            List<UserInfo> result = sqlSession.selectList(statement, userInfo);
            log.info("result={}", result);
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }

    @Test
    public void testSelectLikeUserName() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            List<UserInfo> listUser = sqlSession.selectList("selectLikeUserName", "%t%");
            for (UserInfo userInfo : listUser) {
                log.info("userInfo={}", userInfo);
            }
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }

    @Test
    public void testSelectUserByListId() {
        try {
            UserInfoForm uv = new UserInfoForm();
            List<Integer> ids = new ArrayList<>();
            ids.add(1);
            ids.add(2);
            ids.add(3);
            ids.add(50);
            uv.setIds(ids);
            List<UserInfo> listUser = sqlSession.selectList("selectUserByListId", uv);
            for(UserInfo info : listUser){
                log.info("info={}", info);
            }
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }



    //向 user 表中插入一条数据
    @Test
    public void testInsertUserInfo() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            UserInfo user = new UserInfo();
            user.setUsername("Bob");
            user.setPassword("123456");
            sqlSession.insert("insertUserInfo", user);
            //提交插入的数据
            sqlSession.commit();
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }

    //根据 id 更新 user 表的数据
    @Test
    public void testUpdateUserInfoById() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            //如果设置的 id不存在,那么数据库没有数据更改
            UserInfo userInfo = new UserInfo();
            userInfo.setId(4L);
            userInfo.setUsername("Text");
//            userInfo.setSex(1);
            sqlSession.update("updateUserInfoById", userInfo);
            sqlSession.commit();
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }
  //根据 id 更新 user 表的数据
    @Test
    public void testSelectUserInfoByChoose() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            //如果设置的 id不存在,那么数据库没有数据更改
            UserInfo userInfo = new UserInfo();
//            userInfo.setId(7L);
//            userInfo.setUsername("Text");
//            userInfo.setSex(1);
            List<UserInfo> listUser = sqlSession.selectList("selectUserInfoByChoose", userInfo);
            for (UserInfo info : listUser) {
                log.info("info={}", info);
            }
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }


    //根据 id 删除 user 表的数据
    @Test
    public void testDeleteUserInfoById() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            sqlSession.delete("deleteUserInfoById", 6);
            sqlSession.commit();
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }
}

总结

动态的sql其实就是根据不同的条件拼接sql的过程,通常的做法是先写原生SQL,再写动态sql,最后再做单元测试时打印sql语句,尽可能防止出错。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值