sql语句查询里面有list集合。xml写法

本文介绍了一个使用MyBatis实现的巡逻组变动记录查询功能,详细展示了如何通过XML映射文件定义结果映射、集合属性和SQL查询语句,以实现巡逻组人员变动的高效数据检索。

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

<?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.sunmnet.prison.supervised.dao.patrol.ChangePatrolGroupRecordMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.sunmnet.prison.supervised.entity.model.vo.patrol.PatrolGroupChangeRecordVo">
        <id column="id" property="id" />
        <result column="patrol_group_name" property="patrolGroupName" />
        <result column="patrol_shift_id" property="patrolShiftId" />
        <result column="policeman" property="policeman" />
        <result column="change_time" property="changeTime" />
        <result column="operator" property="operator"/>
        <result column="operator_time" property="operatorTime"/>
        <collection property="patrolGroupPersonnelList"  select="getPersonnels" column="id"/>
        <collection property="PatrolUpdataRecordList"  select="getPersonnelsUpdataRecord" column="id"/>
    </resultMap>
    <resultMap id="personnelMap" type="com.sunmnet.prison.supervised.entity.model.po.patrol.PatrolGroupPersonnel">
        <id column="tpg_id" property="tpgId" />
        <result column="criminal_code" property="criminalCode" />
        <result column="criminal_name" property="criminalName" />
    </resultMap>
    <resultMap id="PersonnelsUpdataRecord" type="com.sunmnet.prison.supervised.entity.model.po.patrol.PatrolUpdataRecord">
        <id column="pgp_id" property="pgpId" />
        <result column="criminal_code_after" property="criminalCodeAfter" />
        <result column="criminal_name_after" property="criminalNameAfter" />
    </resultMap>

    <select id="getPersonnels" resultMap="personnelMap">
        SELECT
        criminal_code,
        criminal_name
        FROM
        t_criminal_patrol_group_personnel WHERE tpg_id = #{id}
      <!--  WHERE
        <if test="list != null  and list.size()>0''">
            <foreach collection="list" index="index" item="patrolGroupPersonnelList" open="(" separator="," close=")">
            #{patrolGroupPersonnelList.id}

            </foreach>
        </if>-->
    </select>

    <select id="getPersonnelsUpdataRecord" resultMap="PersonnelsUpdataRecord">
        SELECT
        criminal_code_after,
        criminal_name_after
        FROM
        t_criminal_patrol_update_record WHERE pgp_id = #{id}
        ORDER BY  change_time desc
    </select>
    <!--巡更组变动记录查询-->
    <select id="queryChangeRecord" resultMap="BaseResultMap">
        SELECT
        t1.id,
        t1.patrol_group_name,
        t1.policeman,
        t1.criminal_group,
        t1.change_time,
        t3.creat_time,
        t3.patrol_site,
        t3.patrol_start_day,
        t3.patrol_end_day,
        t3.patrol_start_time,
        t3.patrol_end_time
        FROM  t_criminal_patrol_group t1
       /* LEFT JOIN t_criminal_patrol_group_personnel t2 ON t1.id=t2.tpg_id*/
        LEFT JOIN t_criminal_patrol_info t3 ON t1.patrol_shift_id = t3.id
       /* LEFT JOIN t_criminal_patrol_update_record t4 on t4.pgp_id =t1.id*/
        <where>
            <if test="patrolGroupName != null and patrolGroupName != ''">
                t1.patrol_group_name like CONCAT('%',#{patrolGroupName},'%')
            </if>
            <if test="criminalGroup != null and criminalGroup != ''">
                AND criminal_group like CONCAT('%',#{criminalGroup},'%')
            </if>
            <if test="patrolSite != null and patrolSite != ''">
                AND t3.patrol_site like CONCAT('%',#{patrolSite},'%')
            </if>
            <if test="patrolStartDay != null">
                AND t3.patrol_start_day &gt;= #{patrolStartDay}
            </if>
            <if test="patrolEndDay != null">
                AND t3.patrol_end_day &lt;= #{patrolEndDay}
            </if>
        </where>
        ORDER BY  t1.change_time desc
    </select>

</mapper>```


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值