动态sql简介
会根据传入的条件字段值,动态变化sql
if
mapper接口
public List<User> findUserByUsernameAndSex(User user);
映射文件
<!--写1=1是为了保证至少有一个条件,此时后面可以跟上and条件-->
<select id="findUserByUsernameAndSex" parameterType="User" resultType="User">
select * from user where 1=1
<if test="username!=null and username!=''">
and username=#{username}
</if>
<if test="sex!=null and sex!=''">
and sex=#{sex}
</if>
</select>
两个条件都给值
测试文件
@Test
public void testFindUserByUsernameAndSex(){
User user = new User();
user.setUsername("qzcsbj5");
user.setSex("1");
List<User> users = userMapper.findUserByUsernameAndSex(user);
for (User u : users) {
System.out.println(u);
}
}
结果
只有username有值
测试文件
@Test
public void testFindUserByUsernameAndSex(){
User user = new User();
user.setUsername("qzcsbj5");
// user.setSex("1");
List<User> users = userMapper.findUserByUsernameAndSex(user);
for (User u : users) {
System.out.println(u);
}
}
结果
只有sex有值
测试文件
@Test
public void testFindUserByUsernameAndSex(){
User user = new User();
// user.setUsername("qzcsbj5");
user.setSex("1");
List<User> users = userMapper.findUserByUsernameAndSex(user);
for (User u : users) {
System.out.println(u);
}
}
结果
两个条件都没值,就表示查所有
测试文件
@Test
public void testFindUserByUsernameAndSex(){
User user = new User();
// user.setUsername("qzcsbj5");
// user.setSex("1");
List<User> users = userMapper.findUserByUsernameAndSex(user);
for (User u : users) {
System.out.println(u);
}
}
结果
username值为空,sex的值不为空
测试文件
@Test
public void testFindUserByUsernameAndSex(){
User user = new User();
user.setUsername("");
user.setSex("1");
List<User> users = userMapper.findUserByUsernameAndSex(user);
for (User u : users) {
System.out.println(u);
}
}
结果
where+if结合使用(和上面if等价)
mapper接口
public List<User> findUserByUsernameAndSex2(User user);
映射文件
如果if标签中有返回值就插入一个‘where’,还可以去掉多余的and、or
<select id="findUserByUsernameAndSex2" parameterType="User" resultType="User">
select * from user
<where>
<if test="username!=null and username!=''">
and username=#{username}
</if>
<if test="sex!=null and sex!=''">
and sex=#{sex}
</if>
</where>
</select>
测试类
@Test
public void testFindUserByUsernameAndSex2(){
User user = new User();
user.setUsername("qzcsbj5");
user.setSex("1");
List<User> users = userMapper.findUserByUsernameAndSex2(user);
for (User u : users) {
System.out.println(u);
}
}
结果
和if里面测试结果一样
set+if
一个问题
mapper接口
public int updateUser(User user);
映射文件
<update id="updateUser" parameterType="User">
update user set username=#{username},password=#{password},realname=#{realname},sex=#{sex},birthday=#{birthday},phone=#{phone},utype=#{utype} where id=#{id}
</update>
测试类:只设置了部分属性的值
@Test
public void testUpdateUserById(){
User user = new User();
user.setId(1); // 更新编号为1的用户
user.setUsername("qzcsbj1-11");
user.setPassword("123456");
user.setRealname("qzcsbj1-11");
user.setPhone("13900000001");
int n = userMapper.updateUser(user);
System.out.println(n>0?"更新成功":"更新失败");
}
结果:映射文件中的sql中的每个字段都更新了,并不是测试类中设置了值的字段
sex、birthday、utype变成null了(addtime和adduser没有在映射文件的sql语句中)
优化
mapper接口
public int updateUser(User user);
映射文件
<!--set会自动移除多余的逗号-->
<update id="updateUser" parameterType="User">
update user
<set>
<if test="username!=null and username!=''">
username=#{username},
</if>
<if test="password!=null and password!=''">
password=#{password},
</if>
<if test="realname!=null and realname!=''">
realname=#{realname},
</if>
<if test="sex!=null and sex!=''">
sex=#{sex},
</if>
<if test="birthday!=null">
birthday=#{birthday},
</if>
<if test="phone!=null and phone!=''">
phone=#{phone},
</if>
<if test="utype!=null and utype!=''">
utype=#{utype},
</if>
</set>
where id=#{id}
</update>
测试类
@Test
public void testUpdateUserById(){
User user = new User();
user.setId(2); // 更新编号为1的用户
user.setUsername("qzcsbj1-22");
user.setPassword("123456");
user.setRealname("qzcsbj1-22");
user.setPhone("13900000002");
int n = userMapper.updateUser(user);
System.out.println(n>0?"更新成功":"更新失败");
}
结果:可以看到只更新了测试类中设置了值的字段
trim
可以完成set或者where的功能
用法一:查询,和上面if、if+where等价
mapper接口
public List<User> findUserByUsernameAndSex3(User user);
映射文件
<select id="findUserByUsernameAndSex3" parameterType="User" resultType="User">
select * from user
<trim prefix="where" prefixOverrides="AND|OR">
<if test="username!=null and username!=''">
and username=#{username}
</if>
<if test="sex!=null and sex!=''">
and sex=#{sex}
</if>
</trim>
</select>
测试类
@Test
public void testFindUserByUsernameAndSex3(){
User user = new User();
user.setUsername("qzcsbj5");
user.setSex("1");
List<User> users = userMapper.findUserByUsernameAndSex3(user);
for (User u : users) {
System.out.println(u);
}
}
结果
和if里面测试结果一样
用法二:更新,和上面set标签+if标签等价
mapper接口
public int updateUser2(User user);
映射文件
<update id="updateUser2" parameterType="User">
update user
<trim prefix="set" suffixOverrides=",">
<if test="username!=null and username!=''">
username=#{username},
</if>
<if test="password!=null and password!=''">
password=#{password},
</if>
<if test="realname!=null and realname!=''">
realname=#{realname},
</if>
<if test="sex!=null and sex!=''">
sex=#{sex},
</if>
<if test="birthday!=null">
birthday=#{birthday},
</if>
<if test="phone!=null and phone!=''">
phone=#{phone},
</if>
<if test="utype!=null and utype!=''">
utype=#{utype},
</if>
</trim>
where id=#{id}
</update>
测试类
@Test
public void testUpdateUserById2(){
User user = new User();
user.setId(3); // 更新编号为1的用户
user.setUsername("qzcsbj1-33");
user.setPassword("123456");
user.setRealname("qzcsbj1-33");
user.setPhone("13900000003");
int n = userMapper.updateUser2(user);
System.out.println(n>0?"更新成功":"更新失败");
}
结果
sql片段
上面if、if+where、trim,有公共的sql代码:
<if test="username!=null and username!=''">
and username=#{username}
</if>
<if test="sex!=null and sex!=''">
and sex=#{sex}
</if>
可以抽离出来,定义为一个sql片段
<!--抽离出来的sql片段-->
<sql id="queryByUsernameAndSex">
<if test="username!=null and username!=''">
and username=#{username}
</if>
<if test="sex!=null and sex!=''">
and sex=#{sex}
</if>
</sql>
引入sql片段,这样代码量更少
<select id="findUserByUsernameAndSex" parameterType="User" resultType="User">
select * from user where 1=1
<!--引入sql片段-->
<include refid="queryByUsernameAndSex"/>
</select>
原文会持续更新,原文地址:https://www.cnblogs.com/uncleyong/p/17007139.html