mybatis XML 中<if>、<choose>、<when>、<otherwise>等标签的使用
一般使用在多条查询,虽然也可以通过注解写,我比较菜,我不会。
一般多条查询怎么解决?
1.如果是单表间的多条件查询我是直接调用mybatis plus的api ,里面可以有直接判空条件的api。
如:queryWrapper.like(StringUtils.isNotEmpty(变量参数), "表字段",变量参数);
2.如果是多表间的多条件查询,我建议用XML来做吧,当然注解也行。
<if>其实跟java的if类似,符合条件的就进去,那符合的进去,不符合的怎么办呢,
mybatis 提供了<choose>、<when>、<otherwise>这组标签组合着用,<when>就相当于if,<otherwise>就相当于else,但是<when>、<otherwise>需要放在<choose>里面才能使用。
这边我就用我最近遇到一个业务来演示吧。一个多表且不同表条件的查询 如下:
看看我的XML文件<if>、<where>、<choose>、<when>、<otherwise>等标签一条语句搞定。
建议模糊查询的参数在传参就先将参数做个处理,如: “%参数%”,网上也有人直接在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="cn.mindgd.mapper.MaterielStorageMapper"> <!--建立查询的列与对象属性的对应关系-->
<!--由于查出来的数据是多张表的,我这边就在实体类添加了一些渲染字段,下面用给这些字段取了别名-->
<resultMap id="BaseResultMap" type="cn.mindgd.domain.MaterielStorage"> <result column="materiel_storage_id" property="materielStorageId"/> <result column="material_code" property="materialCode"/> <result column="material_name" property="materialName"/> <result column="property_name" property="materialTypeName"/> <result column="stock" property="stock"/> <result column="total" property="total"/> </resultMap> <select id="findMaterielStorageByMore" resultMap="BaseResultMap"> SELECT ms.materiel_storage_id AS materiel_storage_id,ms.position,m.material_code AS material_code,m.material_name AS material_name,dp.property_name AS property_name,ms.stock AS stock,COUNT(1) OVER() AS total FROM materiel_storage ms LEFT JOIN material m ON m.material_id=ms.material_id LEFT JOIN data_dictionary_property dp ON m.material_type_id=dp.property_id LEFT JOIN warehouse w ON w.warehouse_id=ms.warehouse_id LEFT JOIN construction_team ct ON ct.team_id =ms.team_id WHERE 1=1 <choose>
<!-- 集合判空 --> <when test="(null != warehouseIds and warehouseIds.size>0) and (null != teamIds and teamIds.size>0)"> AND ( w.warehouse_id IN <foreach collection="warehouseIds" item="warehouseId" open="(" separator="," close=")">#{warehouseId} </foreach> OR ct.team_id IN <foreach collection="teamIds" item="teamId" open="(" separator="," close=")">#{teamId} </foreach> ) </when> <otherwise> <if test="null != warehouseIds and warehouseIds.size>0"> AND w.warehouse_id IN <foreach collection="warehouseIds" item="warehouseId" open="(" separator="," close=")">#{warehouseId} </foreach> </if> <if test="null != teamIds and teamIds.size>0"> AND ct.team_id IN <foreach collection="teamIds" item="teamId" open="(" separator="," close=")">#{teamId} </foreach> </if> </otherwise> </choose> <!-- 字符串判空 --> <if test="null != materialName and '' !=materialName"> AND m.material_name like #{materialName} </if> <if test="null != materialCode and '' !=materialCode"> AND m.material_code=#{materialCode} </if> <!-- Integer类型判空 如果加了 and '' !=propertyId ,0 就传不进去了,也就是不能像判断字符串一样去判断他是否是空字符串--> <if test="null != propertyId"> AND dp.property_id=#{propertyId} </if> <if test="null != teamName and '' !=teamName"> AND ct.team_name like #{teamName} </if> <if test="(null != currentPage) and (null != size)"> LIMIT #{currentPage},#{size} </if> </select> </mapper>
直接在测试类测试
@SpringBootTest public class TestMaterielStorage { @Autowired(required = false) MaterielStorageMapper materielStorageMapper; @Test public void findMaterielStorageByMore() { List<Integer> warehouseIds = new ArrayList<>(); warehouseIds.add(1); warehouseIds.add(2); List<Integer> teamIds = new ArrayList<>(); // teamIds.add(1); // teamIds.add(2); List<MaterielStorage> materielStorageByMore = // materielStorageMapper.findMaterielStorageByMore("施", "P", null, 1, warehouseIds, teamIds, 0, 10); materielStorageMapper.findMaterielStorageByMore(null, null, null, null, null, null, 0, 10); System.out.println("输出:" + materielStorageByMore); } }
效果1:
效果2:
效果3: