MyBatis操作--进阶

 博主主页: 码农派大星.

    数据结构专栏:Java数据结构

 数据库专栏:MySQL数据库

JavaEE专栏:JavaEE

软件测试专栏:软件测试

关注博主带你了解更多知识

1. 动态SQL

动态SQL是Mybatis的强⼤特性之⼀，能够完成不同条件下不同的sql拼接

1.1 <if>标签

比如说注册分为两种字段：必填字段和⾮必填字段,⾮必填字段该如何实现呢,这就需要使⽤动态标签了

接⼝定义:

Integer insertByCondition(UserInfo userInfo);

Mapper.xml实现:

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

    </insert>

测试:

@Test
    void insertByCondition() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("zhangwu222");
        userInfo.setPassword("zhangwu222");
        userInfo.setGender(0);
       // userInfo.setAge(16);
       //注释掉age,看它怎么测出结果
        userInfo.setPhone("123456789");
        userInfoXmlMapper.insertByCondition(userInfo);
    }

<if>标签判断age是否为null,如果为null,就会拼接后面的phone属性 

 注解⽅式(不推荐) 

使用<script></script>方式

 @Insert("<script> insert into user_info (username, password,\n" +
            "        <if test='age!=null'>age,</if>\n" +
            "        gender," +
            "        phone" +
            "        )" +
            "        values (#{username}, #{password}," +
            "        <if test='age!=null'>#{age},</if>" +
            "        #{gender}," +
            "        #{phone}" +
            "        )</script>")
    Integer insertByCondition(UserInfo userInfo);

测试: 

    @Test
    void insertByCondition() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("zhangwu222");
        userInfo.setPassword("zhangwu222");
        userInfo.setGender(0);
        // userInfo.setAge(16);
        userInfo.setPhone("123456789");
        userInfoMapper.insertByCondition(userInfo);
    }

1.2 <trim>标签

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

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

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

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

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

    </insert>

测试

@Test
    void insertByCondition() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("zhangwu222");
        userInfo.setPassword("zhangwu222");
        userInfo.setGender(0);
       // userInfo.setAge(16);
       // userInfo.setPhone("123456789");
        userInfoXmlMapper.insertByCondition(userInfo);
    }

1.3 <where>标签  

 List<UserInfo> selectByCondition(UserInfo userInfo);

 <select id="selectByCondition" resultType="com.mybatis.model.UserInfo">
        select * from user_info
       <where>
           <if test="username!=null">
               username = #{username}
           </if>
           <if test="age!=null">
               and age = #{age}
           </if>
           <if test="gender!=null">
               and gender = #{gender}
           </if>
       </where>


    </select>

 @Test
    void insertByCondition() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("zhangwu222");
        userInfo.setPassword("zhangwu222");
        userInfo.setGender(0);
       // userInfo.setAge(16);
       // userInfo.setPhone("123456789");
        userInfoXmlMapper.insertByCondition(userInfo);
    }

<where>只会在⼦元素有内容的情况下才插⼊where⼦句，⽽且会⾃动去除⼦句的开头的AND或 OR

1.4 <set>标签

根据传⼊的⽤⼾对象属性来更新⽤⼾数据，可以使⽤标签来指定动态内容

 <update id="updateByCondition2">
        update user_info
        <set>
            <if test="username!=null">
                username=#{username},
            </if>
            <if test="password!=null">
                password=#{password},
            </if>
            <if test="age!=null">
                age=#{age}
            </if>
        </set>
        <where>
            <if test="id!=null">
                id=#{id}
            </if>
        </where>
    </update>

测试 

 @Test
    void updateByCondition() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("cxk");
        userInfo.setPassword("cxk");
        userInfo.setId(3);
        userInfo.setAge(12);
        userInfoXmlMapper.updateByCondition2(userInfo);
    }

 1.5 <foreach>标签

对集合进⾏遍历时可以使⽤该标签

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

item：遍历时的每⼀个对象

open：语句块开头的字符串

close：语句块结束的字符串

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

需求:根据多个userid,删除⽤⼾数据

 Integer batchDelete(List<Integer> ids);

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

 测试:

 @Test
    void batchDelete() {
        userInfoXmlMapper.batchDelete(List.of(6,7,8));

    }

1.6<include> 标签

在xml映射⽂件中配置的SQL，有时可能会存在很多重复的⽚段，此时就会存在很多冗余的代码

对重复的代码⽚段抽取,将其通过<sql>标签封装到⼀个SQL⽚段,然后再通过 <include>标签进⾏引⽤

 <sql id="selectAll">
        select * from user_info;
 </sql>

  <select id="queryUserInfos" resultType="com.mybatis.model.UserInfo">
       <include refid="selectAll"></include>
    </select>