8.MyBatis动态sql

MyBatis动态sql

Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能，它存在的意义是为了解决拼接SQL语句字符串时的痛点问题。

if

if标签可通过test属性的表达式进行判断，若表达式的结果为true，则标签中的内容会执行；反之标签中的内容不会执行

UserMapper.java

    /**
     * 动态sql查询用户
     * @param user
     * @return
     */
    User dynamicSQL(User user);

UserMapper.xml

    <!--User dynamicSQL(User user)-->
    <select id="dynamicSQL" resultType="User">
        select * from t_user where
        <if test="id != null and id !=''">
            id = #{id}
        </if>
        <if test="username !=null and username !=''">
            and username = #{username}
        </if>
        <if test="password !=null and password !=''">
            and password = #{password}
        </if>
    </select>

MyBatisTest.java

    @Test
    public void dynamicSQL(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(3);
        user.setUsername("admin");
        //这里没有给password赋值，所以sql语句中不会出现password
        System.out.println(userMapper.dynamicSQL(user));
    }

DEBUG 01-12 20:42:37,505 ==>  Preparing: select * from t_user where id = ? and username = ? (BaseJdbcLogger.java:137) 
DEBUG 01-12 20:42:37,543 ==> Parameters: 3(Integer), admin(String) (BaseJdbcLogger.java:137) 
DEBUG 01-12 20:42:37,577 <==      Total: 1 (BaseJdbcLogger.java:137) 
User{id=3, username='admin', password='123456', age=23, gender='男', email='1312@qq.com'}

where

where一般和if结合使用，解决单独使用if时关键字"and"的问题

若where标签中的if条件都不满足，则where标签没有任何功能，即不会添加where关键字

若where标签中的if条件满足，则where标签会自动添加where关键字，并将所有if标签中**第一个满足条件的语句中最前方多余的"and"**去掉（只会去一次，后面满足条件的语句中的"and"不会再去掉了）

注意：where标签不能去掉条件最后多余的"and"，所以"and"写在单条if语句的最后面

UserMapper.xml

    <!--User dynamicSQL(User user)-->
    <select id="dynamicSQL" resultType="User">
        select * from t_user
        <where>
            <!--第一个判断语句可以省略"and"，如果写了且满足条件也会自动去掉-->
            <if test="id != null and id !=''">
                id = #{id}
            </if>
            <if test="username !=null and username !=''">
                and username = #{username}
            </if>
            <if test="password !=null and password !=''">
                and password = #{password}
            </if>
        </where>
    </select>

MyBatisTest.java

    @Test
    public void dynamicSQL(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        //sql语句中username前面的and去掉了
        user.setUsername("admin");
        user.setPassword("123456");
        System.out.println(userMapper.dynamicSQL(user));
    }

DEBUG 01-12 21:04:18,712 ==>  Preparing: select * from t_user WHERE username = ? and password = ? (BaseJdbcLogger.java:137) 
DEBUG 01-12 21:04:18,759 ==> Parameters: admin(String), 123456(String) (BaseJdbcLogger.java:137) 
DEBUG 01-12 21:04:18,790 <==      Total: 1 (BaseJdbcLogger.java:137) 
User{id=3, username='admin', password='123456', age=23, gender='男', email='1312@qq.com'}

set

set一般和if结合使用，解决单独使用if时关键字","的问题

若set标签中的if条件都不满足，则set标签没有任何功能，即不会添加where关键字

若set标签中的if条件满足，则set标签会自动添加set关键字，并将所有if标签中**最后一个满足条件的语句中最后面多余的“,”**去掉

注意：

​ set标签不能去掉条件最后前面的",“，所以”,"写在单条if语句的最后面

​ 至少得有一个if标签为真，否则会出现sql语句错误

具体使用方式参考where，这里不再赘述

trim

trim用于去掉或添加标签中的内容（标签内sql语句去掉或者添加一次）

常用属性：

prefix：在trim标签中的内容的前面添加某些内容

prefixOverrides：在trim标签中的内容的前面去掉某些内容

suffix：在trim标签中的内容的后面添加某些内容

suffixOverrides：在trim标签中的内容的后面去掉某些内容

UserMapper.xml

    <!--User dynamicSQL(User user)-->
    <select id="dynamicSQL" resultType="User">
        select * from t_user
        <!--在标签内sql语句前面添加where，并去掉结尾"and"(结尾没有"and"则不执行)-->
        <trim prefix="where" suffixOverrides="and">
            <if test="id != null and id !=''">
                id = #{id} and
            </if>
            <if test="username !=null and username !=''">
                username = #{username} and
            </if>
            <if test="password !=null and password !=''">
                password = #{password} and
            </if>
        </trim>
    </select>

MyBatisTest.java

    @Test
    public void dynamicSQL(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        //sql语句条件了where且中username前面的and去掉了
        user.setId(3);
        user.setPassword("123456");
        System.out.println(userMapper.dynamicSQL(user));
    }

DEBUG 01-12 21:14:58,785 ==>  Preparing: select * from t_user where id = ? and password = ? (BaseJdbcLogger.java:137) 
DEBUG 01-12 21:14:58,816 ==> Parameters: 3(Integer), 123456(String) (BaseJdbcLogger.java:137) 
DEBUG 01-12 21:14:58,863 <==      Total: 1 (BaseJdbcLogger.java:137) 
User{id=3, username='admin', password='123456', age=23, gender='男', email='1312@qq.com'}

choose、when、otherwise

choose、when、otherwise相当于if…else if…else（多个条件中如果满足其中一个则执行后退出）

UserMapper.xml

    <!--User dynamicSQL(User user)-->
    <select id="dynamicSQL" resultType="User">
        select * from t_user where
        <choose>
            <when test="id != null and id !=''">
                id = #{id}
            </when>
            <when test="username !=null and username !=''">
                username = #{username}
            </when>
            <!--前面所有条件都不满足的话执行-->
            <otherwise>
                id = 3
            </otherwise>
        </choose>
    </select>

MyBatisTest.java

    @Test
    public void dynamicSQL(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(3);
        user.setPassword("123456");
        //这里有两个条件满足也只执行了前面第一个满足的条件后退出
        System.out.println(userMapper.dynamicSQL(user));
    }

DEBUG 01-12 21:28:41,280 ==>  Preparing: select * from t_user where id = ? (BaseJdbcLogger.java:137) 
DEBUG 01-12 21:28:41,327 ==> Parameters: 3(Integer) (BaseJdbcLogger.java:137) 
DEBUG 01-12 21:28:41,358 <==      Total: 1 (BaseJdbcLogger.java:137) 
User{id=3, username='admin', password='123456', age=23, gender='男', email='1312@qq.com'}

foreash

批量添加和批量删除时使用，可以循环链表

批量添加

UserMapper.java

    /**
     * 批量添加
     * @param users
     */
    void insertMoreUser(List<User> users);

UserMapper.xml

    <!--void insertMoreUser(List<User> users)-->
    <insert id="insertMoreUser">
        insert into t_user value
        <!--
            collection：循环的Java链表
            item：foreach范围内数组或集合的局部变量
            separator：每次循环之间的分隔符
        -->
        <!--链表默认的键为"list"(也可以通过@param设置)-->
        <foreach collection="list" item="user" separator=",">
            (null,#{user.username},#{user.password},#{user.age},#{user.gender},#{user.email})
        </foreach>
    </insert>

MyBatisTest.java

    @Test
    public void insertMoreUser(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User(null,"admin1","123",20,"男","158@qq.com");
        User user1 = new User(null,"admin1","123",20,"女","158@qq.com");
        //asList方法实现初始化
        List<User> list = Arrays.asList(user,user1);
        userMapper.insertMoreUser(list);
    }

DEBUG 01-12 21:49:43,757 ==>  Preparing: insert into t_user value (null,?,?,?,?,?) , (null,?,?,?,?,?) (BaseJdbcLogger.java:137) 
DEBUG 01-12 21:49:43,804 ==> Parameters: admin1(String), 123(String), 20(Integer), 男(String), 158@qq.com(String), admin1(String), 123(String), 20(Integer), 女(String), 158@qq.com(String) (BaseJdbcLogger.java:137) 
DEBUG 01-12 21:49:43,819 <==    Updates: 2 (BaseJdbcLogger.java:137) 

批量删除

UserMapper.java

    /**
     * 批量删除
     * @param userIds
     */
    void deleteMoreUser(Integer[] userIds);

UserMapper.xml

    <!--void deleteMoreUser(Integer[] userIds)-->
    <delete id="deleteMoreUser">
        delete from t_user where id in
        <!--
            open：标签内sql语句以什么开头
            close：标签内sql语句以什么结尾
        -->
        <!--数组默认为array-->
        <foreach collection="array" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
    </delete>
    <!--另外一种写法：delete from t_user where id = ? or id = ?-->
<!--    <delete id="deleteMoreUser">-->
<!--        delete from t_user where-->
<!--        <foreach collection="array" item="userId" separator="or">-->
<!--            id = #{userId}-->
<!--        </foreach>-->
<!--    </delete>-->

MyBatisTest.java

    @Test
    public void deleteMoreUser(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        Integer[] userIds = new Integer[]{23,24};
        userMapper.deleteMoreUser(userIds);
    }

DEBUG 01-12 22:13:12,970 ==>  Preparing: delete from t_user where id in ( ? , ? ) (BaseJdbcLogger.java:137) 
DEBUG 01-12 22:13:13,007 ==> Parameters: 23(Integer), 24(Integer) (BaseJdbcLogger.java:137) 
DEBUG 01-12 22:13:13,007 <==    Updates: 2 (BaseJdbcLogger.java:137) 

sql

sql片段，可以记录一段公共sql片段，在使用的地方通过include标签进行引入(refid)

    <sql id="userColumns">
        id,username,password
    </sql>
    
    <!--User dynamicSQL(User user)-->
    <select id="dynamicSQL" resultType="User">
        select <include refid="userColumns"></include> from t_user where
        <choose>
            <when test="id != null and id !=''">
                id = #{id}
            </when>
            <when test="username !=null and username !=''">
                username = #{username}
            </when>
            <!--前面所有条件都不满足的话执行-->
            <otherwise>
                id = 3
            </otherwise>
        </choose>
    </select>