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>
原文地址：https://blog.csdn.net/jj666mhhh/article/details/143671966
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！