本文主要介绍 mybatis 动态SQL，通过 if, choose, when, otherwise, trim, where, set, foreach等标签，可组合成灵活的SQL语句，从而提高 SQL 语句的准确性以及开发人员的开发效率。
以表t_dy_member_info 为例：
image.png
pojo类：
package com.sc.pojo;
import lombok.Data;
import java.util.List;
@Data
public class TdyMemberInfo {
private Long memberId;
private String memberNo;
private String mobile;
private String openId;
private String shopId;
private List<Long> ids;
public void setIds(List<Long> ids) {
this.ids = ids;
}
}
接口：
package com.sc.dao;
import com.sc.pojo.TdyMemberInfo;
import java.util.List;
public interface TdyMemberInfoMapper {
TdyMemberInfo selectMemberByIf(TdyMemberInfo tdyMemberInfo);
TdyMemberInfo selectMemberByIfWhere(TdyMemberInfo tdyMemberInfo);
int updateMemberByIfSet(TdyMemberInfo tdyMemberInfo);
TdyMemberInfo selectMemberByChoose(TdyMemberInfo tdyMemberInfo);
List<TdyMemberInfo> selectMemberByForeach1(TdyMemberInfo tdyMemberInfo);
List<TdyMemberInfo> selectMemberByForeach2(TdyMemberInfo tdyMemberInfo);
}
Mapper.xml文件：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.sc.dao.TdyMemberInfoMapper">
<resultMap id="memberInfo" type="TdyMemberInfo">
<result property="memberId" column="member_id"/>
<result property="memberNo" column="member_no"/>
<result property="mobile" column="mobile"/>
<result property="openId" column="open_id"/>
<result property="shopId" column="shop_id"/>
</resultMap>
</mapper>
1、动态SQL--- if 语句
1）TdyMemberInfoMapper 接口中定义方法：
TdyMemberInfo selectMemberByIf(TdyMemberInfo tdyMemberInfo);
2）TdyMemberInfoMapper.xml中SQL语句块：
<!--动态IF语句-->
<select id="selectMemberByIf" resultType="TdyMemberInfo" parameterType="TdyMemberInfo" resultMap="memberInfo">
select * from t_dy_member_info where
<if test="memberNo !=null"><!--这里memberNo使用的是pojo中的属性-->
member_no=#{memberNo}
</if>
<if test="mobile !=null">
and mobile = #{mobile}
</if>
</select>
对应：
select * from t_dy_member_info where member_no=#{memberNo} and mobile =#{mobile}
注意：
if标签中的test属性值，使用的是pojo中对应的属性
上述SQL中，如果 member_no为空时，则该语句就是：
select * from t_dy_member_info where and mobile = ?
因为where后面多了and所以就会报错。
3）测试类
@Test
public void selectMemberByIf(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
TdyMemberInfoMapper mapper = sqlSession.getMapper(TdyMemberInfoMapper.class);
TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
tdyMemberInfo.setMemberNo("202111231463");
tdyMemberInfo.setMobile("18727460511");
TdyMemberInfo member = mapper.selectMemberByIf(tdyMemberInfo);
System.out.println(member);
sqlSession.close();
}
2、动态SQL--- if+where 语句
1）TdyMemberInfoMapper 接口中定义方法：
TdyMemberInfo selectMemberByIfWhere(TdyMemberInfo tdyMemberInfo);
2）TdyMemberInfoMapper.xml中SQL语句块：
<!--动态IF+where 语句-->
<select id="selectMemberByIfWhere" resultMap="memberInfo" resultType="TdyMemberInfo" parameterType="TdyMemberInfo">
select * from t_dy_member_info
<where>
<if test="memberNo !=null">
member_no=#{memberNo}
</if>
<if test="mobile !=null">
and mobile = #{mobile}
</if>
</where>
</select>
上述SQL中，如果 member_no为空时，该语句正常， where 标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。
3）测试类
@Test
public void selectMemberByIfWhere(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
TdyMemberInfoMapper mapper = sqlSession.getMapper(TdyMemberInfoMapper.class);
TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
tdyMemberInfo.setMemberNo("202111231466");
tdyMemberInfo.setMobile("18727460505");
TdyMemberInfo member = mapper.selectMemberByIfWhere(tdyMemberInfo);
System.out.println(member);
sqlSession.close();
}
3、动态SQL--- if+set 语句
1）TdyMemberInfoMapper 接口中定义方法：
int updateMemberByIfSet(TdyMemberInfo tdyMemberInfo);
2）TdyMemberInfoMapper.xml中SQL语句块：
<!--动态set+if 语句-->
<update id="updateMemberByIfSet" parameterType="TdyMemberInfo" >
update t_dy_member_info m
<set>
<if test="memberNo != null and memberNo !='' ">
m.member_no = #{memberNo},
</if>
<if test="mobile !=null and mobile !='' ">
m.mobile = #{mobile}
</if>
</set>
where m.member_id = #{memberId}
</update>
如果第一个条件 memberNo 为空，set会自动将memberNo后的逗号去掉
那么 sql 语句为：
update t_dy_member_info u set u.mobile =? where member_id =?
如果第一个条件memberNo 不为空，
那么 sql 语句为：
update t_dy_member_info u set u.memberNo = ? ,u.mobile = ? where member_id =?
3）测试类
@Test
public void updateMemberByIfSet(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
TdyMemberInfoMapper mapper = sqlSession.getMapper(TdyMemberInfoMapper.class);
TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
// tdyMemberInfo.setMemberNo("2021112311112");
tdyMemberInfo.setMobile("18727460505");
tdyMemberInfo.setMemberId(new Long(5));
int code = mapper.updateMemberByIfSet(tdyMemberInfo);
System.out.println(code);
System.out.println(tdyMemberInfo);
sqlSession.close();
}
4、动态SQL---choose(when,otherwise) 语句
1）TdyMemberInfoMapper 接口中定义方法：
TdyMemberInfo selectMemberByChoose(TdyMemberInfo tdyMemberInfo);
2）TdyMemberInfoMapper.xml中SQL语句块：
<!--动态where+ choose+when/otherwise 语句-->
<select id="selectMemberByChoose" resultType="TdyMemberInfo" parameterType="TdyMemberInfo" resultMap="memberInfo">
select * from t_dy_member_info m
<where>
<choose>
<when test="memberNo != null and memberNo !='' ">
m.member_no = #{memberNo}
</when>
<when test="mobile !=null and mobile !='' ">
and m.mobile = #{mobile}
</when>
<otherwise>
and m.member_id = #{memberId}
</otherwise>
</choose>
</where>
</select>
上述SQL中有三个条件，memberNo,mobile,member_id，只能选择一个作为查询条件 当member_no为空时，mobile/member_id不为空则where会自动去掉 and。
如果 memberNo不为空，那么查询语句为：
select * from t_dy_member_info where memberNo=?
如果 memberNo为空，那么看mobile 是否为空，如果不为空，那么语句为
select * from t_dy_member_info where mobile =?;
如果 mobile 为空，那么查询语句为
select * from t_dy_member_info where member_id =?
3）测试类
@Test
public void selectMemberByChoose(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
TdyMemberInfoMapper mapper = sqlSession.getMapper(TdyMemberInfoMapper.class);
TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
// tdyMemberInfo.setMemberNo("202111231463039461702434821");
// tdyMemberInfo.setMobile("18727460505");
tdyMemberInfo.setMemberId(new Long(5));
TdyMemberInfo member = mapper.selectMemberByChoose(tdyMemberInfo);
System.out.println(member);
sqlSession.close();
}
5、动态SQL---SQL 片段
有时候某个 sql 语句用到的特别多，为了增加代码的重用性，简化代码，就可以将这些代码抽取出来，在需要用到的地方直接调用。
比如：上述SQL中经常根据member_no 和mobile来进行联合查询，那么就把这个代码抽取出来，如下：
<sql id="selectSql">
<if test="memberNo !=null">
member_no=#{memberNo}
</if>
<if test="mobile !=null">
and mobile = #{mobile}
</if>
</sql>
引用 sql 片段
<!--动态IF语句-->
<select id="selectMemberByIf" resultType="TdyMemberInfo" parameterType="TdyMemberInfo" resultMap="memberInfo">
select * from t_dy_member_info where
<include refid="selectSql"/>
</select>
<!--动态IF+where 语句-->
<select id="selectMemberByIfWhere" resultMap="memberInfo" resultType="TdyMemberInfo" parameterType="TdyMemberInfo">
select * from t_dy_member_info
<where>
<include refid="selectSql"/>
</where>
</select>
注意：在 sql 片段中最好不要包括 where
6、动态SQL---foreach 语句（方式1）
1）TdyMemberInfoMapper 接口中定义方法：
List<TdyMemberInfo> selectMemberByForeach1(TdyMemberInfo tdyMemberInfo);
2）TdyMemberInfoMapper.xml中SQL语句块：
<select id="selectMemberByForeach1" resultType="TdyMemberInfo" parameterType="TdyMemberInfo" resultMap="memberInfo">
select * from t_dy_member_info m
<where>
<foreach collection="ids" item="id" separator="or" open="and (" close=")">
member_id = #{id}
</foreach>
</where>
</select>
上述foreach语句用来改写
select * from t_dy_member_info where member_id=47 or member_id=48 or member_id=49
3）测试类
@Test
public void selectMemberByForeach1(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
TdyMemberInfoMapper mapper = sqlSession.getMapper(TdyMemberInfoMapper.class);
TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
List<Long> ids = new ArrayList<>();
ids.add(new Long(47));
ids.add(new Long(48));
ids.add(new Long(49));
tdyMemberInfo.setIds(ids);
List<TdyMemberInfo> memberList = mapper.selectMemberByForeach1(tdyMemberInfo);
for(TdyMemberInfo memberInfo:memberList){
System.out.println(memberInfo);
}
sqlSession.close();
}
7、动态SQL---foreach 语句（方式2）
1）TdyMemberInfoMapper 接口中定义方法：
List<TdyMemberInfo> selectMemberByForeach2(TdyMemberInfo tdyMemberInfo);
2）TdyMemberInfoMapper.xml中SQL语句块：
<select id="selectMemberByForeach2" resultType="TdyMemberInfo" parameterType="TdyMemberInfo" resultMap="memberInfo">
select * from t_dy_member_info m
<where>
<foreach collection="ids" item="id" separator="," open="and member_id in (" close=")">
#{id}
</foreach>
</where>
</select>
collection:指定输入对象中的集合属性
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
上述foreach语句用来改写
select * from t_dy_member_info m WHERE member_id in ( ? , ? , ? )
3）测试类
@Test
public void selectMemberByForeach2(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
TdyMemberInfoMapper mapper = sqlSession.getMapper(TdyMemberInfoMapper.class);
TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
List<Long> ids = new ArrayList<>();
ids.add(new Long(47));
ids.add(new Long(48));
ids.add(new Long(49));
tdyMemberInfo.setIds(ids);
List<TdyMemberInfo> memberList = mapper.selectMemberByForeach2(tdyMemberInfo);
for(TdyMemberInfo memberInfo:memberList){
System.out.println(memberInfo);
}
sqlSession.close();
}
网友评论