mybatis-xml使用总结

最新推荐文章于 2024-08-27 08:39:52 发布

薇薇

最新推荐文章于 2024-08-27 08:39:52 发布

阅读量825

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/qqzhengwei/article/details/112011074

本文介绍如何在Java ORM中使用if、choose、foreach等条件语句进行SQL构建，包括多条件判断、IN列表处理、通用SQL片段和嵌套关系的数据映射。适合理解ORM查询优化和复杂查询表达。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、条件使用

1.1 if条件

SELECT
        *
FROM
  `trade_order` t
WHERE
  t.is_valid = 'Y'
<!--先看看查询条件是否为空-->
<if test="searchDto.beginDate != null and searchDto.beginDate != ''">
   AND DATE_FORMAT(t.trade_finish_time, '%Y-%m-%d') >= #{searchDto.beginDate}
</if>
<!--要获取某个值条件等于时-->
<if test="searchDto.tradeOrderType == 'CONSUME' || searchDto.tradeOrderType == 'AGENT_COLLECT'">
   AND t.trade_order_type = #{searchDto.tradeOrderType} AND t.status LIKE 'SUCCESS%'
</if>

加入在xml中使用>=或者<=时，出现与xml标签<>冲突时，可以使用

原符号 < <= > >= & ' "
替换符号 < <= > >= & ' "

大于等于
<![CDATA[ >= ]]>
小于等于
<![CDATA[ <= ]]>

例如：

create_date_time >= #{startTime} and create_date_time <= #{endTime}
create_date_time <![CDATA[ >= ]]> #{startTime} and create_date_time <![CDATA[ <= ]]> #{endTime}

1.2 choose（if else）使用

<choose>
	<when test="dto.commentState==1">
		AND ro.has_comment='N'
	</when>
	<when test="dto.commentState==2">
		AND ro.has_comment='Y' AND ro.comment_result_type='GOOD'
	</when>
	<when test="dto.commentState==3">
		AND ro.has_comment='Y' AND ro.comment_result_type='BAD'
	</when>
</choose>

1.3 foreach使用（循环）

场景1 多条件or拼接

AND (1!=1 OR
<foreach collection="dto.orderStateList" item="item" separator=" OR ">
	<choose>
		<when test='item == "ASSIGNED"'>
			ro.order_state='ASSIGNED'
		</when>
		<when test='item == "EXCEPTION"'>
		   (ro.order_state='EXCEPTION' OR roas.after_sale_state='EXCEPTION')
		</when>
		<when test='item == "CANCEL"'>
			ro.order_state='CANCEL'
		</when>
		<when test='item == "FINISH"'>
			ro.order_state='FINISH'
		</when>
		<when test='item == "CLOSED"'>
		   ro.order_state='CLOSED'
		</when>
		<when test='item == "AFTER_SALE"'>
			(ro.order_state='FINISH' AND ro.id_repair_order_after_sale IS NOT NULL AND roas.after_sale_state IN ('HANDING','EXCEPTION','ASSIGNED'))
		</when>
		<otherwise>
			(rod.order_state=#{item} AND ro.order_state='HANDING')
		</otherwise>
	</choose>
</foreach>

场景2 in内容拼接

tri.id IN
<foreach collection="insuranceIds" index="index" item="item" open="(" separator="," close=")">
	#{item}
</foreach>

转化为sql形如
tri.id IN ('1','2','3')

1.4 如果某个sql需要在几个查询语句中通用，解决办法

首先通过定义sql语句，使用<sql标签定义>定义一个id，在使用时需要

<sql id="getTradeOrderVOList">
	SELECT
		*	
	FROM
	`trade_order` t
	WHERE
	t.is_valid = 'Y'
	ORDER BY t.create_time DESC
</sql>

如何使用，建立查询语句

<select id="getTradeOrderVOForPage" resultType="包名.TradeOrderVo">
	<include refid="getTradeOrderVOList"></include>
</select>
<select id="getTradeOrderVOForList" resultType="包名.TradeOrderVo">
	<include refid="getTradeOrderVOList"></include>
</select>

1.5 在查询语句，存在list-detail关系时，需要直接将其在查询后转化为对象时

需要使用resultMap

resultMap标签属性解说
1、column表示sql查询出来的列明
2、property 表示type对应实体model的属性名称，这里定义转化关系即可
3、collection 子标签表示要将查询detail内容转化为集合
将结果集中groupId、groupName、groupDesc转化为ApplyPersonVo的applyPersonGroupVoList属性

<resultMap id="queryPage" type="com.tfysy.manage.vo.yz.ApplyPersonVo">
	<id column="id" property="id"></id>
	<result column="phone" property="phone"></result>
	<result column="userName" property="userName"></result>
	<result column="openType" property="openType"></result>
	<result column="open" property="open"></result>
	<result column="applyStartTime" property="applyStartTime"></result>
	<result column="applyEndTime" property="applyEndTime"></result>
	<result column="isLongValid" property="isLongValid"></result>
	<collection property="applyPersonGroupVoList" ofType="com.tfysy.manage.vo.yz.ApplyPersonGroupVo">
		<result column="groupId" property="id"></result>
		<result column="groupName" property="groupName"></result>
		<result column="groupDesc" property="groupDesc"></result>
	</collection>
</resultMap>

<select id="queryPage" resultMap="queryPage">
	SELECT
		r.id,
		r.phone,
		r.user_name AS userName,
		r.open_type AS openType,
		IF(r.open_type ='Y',"已启用","已停用") AS open,
		r.apply_start_time AS applyStartTime,
		r.apply_end_time AS applyEndTime,
		r.is_long_valid AS isLongValid,
		pg.group_id as groupId,
		pg.group_name as groupName,
		pg.group_desc as groupDesc
	FROM
	  yz_apply_person r
	left join yz_apply_person_group_ref pgr on pgr.id_apply_person = r.id
	left join yz_product_group pg on pg.id = pgr.id_product_group
	WHERE r.is_valid='Y'
	<if test="dto.phone!=null and dto.phone!=''">
		and r.phone like concat('%',#{dto.phone},'%')
	</if>
	<if test="dto.userName!=null and dto.userName!=''">
		and r.user_name like concat('%',#{dto.userName},'%')
	</if>
	<if test="dto.statusType!=null and dto.statusType!='' and dto.statusType=='INVALID'">
		and DATE_FORMAT(now(), '%Y-%m-%d') > DATE_FORMAT(r.apply_end_time, '%Y-%m-%d')
		and r.is_long_valid = 'N'
	</if>
	<if test="dto.statusType!=null and dto.statusType!='' and dto.statusType=='DEFAULT'">
		and (DATE_FORMAT(r.apply_end_time, '%Y-%m-%d') >= DATE_FORMAT(now(), '%Y-%m-%d') or r.is_long_valid = 'Y')
	</if>
	<if test="dto.groupIdsList!=null">
		AND pg.group_id in
		<foreach collection="dto.groupIdsList" item="groupId"  open="(" close=")" separator=",">
			#{groupId}
		</foreach>
	</if>
	ORDER BY r.create_time desc
</select>

java类
@Data
public class ApplyPersonVo extends RechargePersonVo implements Serializable {

    @ApiModelProperty(value = "生效开始时间")
    private Date applyStartTime;

    @ApiModelProperty(value = "生效结束时间")
    private Date applyEndTime;

    @ApiModelProperty(value = "是否长期有效（Y-是，N-否）")
    private String isLongValid;

    @ApiModelProperty(value = "核销人员拥有的分组")
    private List<ApplyPersonGroupVo> applyPersonGroupVoList;
}

@Data
public class ApplyPersonGroupVo implements Serializable {

    @ApiModelProperty(value = "分组db-Id")
    private String id;
    @ApiModelProperty(value = "分组名称")
    private String groupName;
    @ApiModelProperty(value = "分组描述")
    private String groupDesc;
}

仅做参考。实际情况根据项目需要，这里仅提供思路和项目组使用总结。