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>

 

测试:

@Testvoid 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);

测试: 

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

测试

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

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

测试 

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

 测试:

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