mybatis整合oracle 实现一对多查询 备注？

时间：2023-12-25 11:11:13

<resultMap type="com.asiainfo.channel.model.weeklyNewspaper.WorkReportInfo" id="WorkReportInfoMap">

	<result property="reportId" column="report_id" />

	<result property="reportType" column="report_type" />

	<result property="completeContent" column="complete_content" />

	<result property="needComplete" column="need_complete" />

	<result property="needHelp" column="need_help" />

	<result property="beginDt" column="begin_dt"  javaType="date" jdbcType="DATE"/>

	<result property="endDt" column="end_dt"  javaType="date" jdbcType="DATE"/>

	<result property="createStaff" column="create_staff" />

	<result property="createDt" column="createDt"  javaType="date" jdbcType="DATE"/>

	<collection property="reportFileInfoList"    ofType="com.asiainfo.channel.model.weeklyNewspaper.ReportFileInfo"

			select="getReportFileInfoList" column="report_id">

	</collection>

	<collection property="workReportSendListInfoList"    ofType="com.asiainfo.channel.model.weeklyNewspaper.WorkReportSendListInfo"

			select="getWorkReportSendListInfoList" column="report_id">

	</collection> 

</resultMap>

<!-- 查询工作报告详情   映射到内部集合 -->

<select id="queryworkReportDetail" parameterType="map" resultMap="WorkReportInfoMap">

	select report_id ,

	report_type  ,

	complete_content,

	need_complete ,

	need_help ,

	begin_dt ,

	end_dt ,

	create_staff ,

	create_dt

	from work_report t

	where 1=1

	<include refid="queryWorkReportListCondition"/>

</select>

<!-- 一对多映射使用的子查询  -->

 <select id="getReportFileInfoList" parameterType="int" resultType="com.asiainfo.channel.model.weeklyNewspaper.ReportFileInfo">

    select t.report_file_id,

    t.report_id,

    t.file_uuid,

    t.file_name,

    t.FILE_DIR,

    t.FILE_TYPE,

    t.FILE_POST_FIX

	from report_file t

    where t.report_id = #{reportId}

</select> 

<!-- 一对多映射使用的子查询  -->

 <select id="getWorkReportSendListInfoList" parameterType="int" resultType="com.asiainfo.channel.model.weeklyNewspaper.WorkReportSendListInfo">

    select send_id,

    report_id,

    receive_name,

    receive_code,

    receive_staff_id,

    receive_mail,

    receive_num

	from work_report_send_list t

    where t.report_id = #{reportId}

</select>

<sql id="queryWorkReportListCondition">

            <!-- 类型 -->

	<if test="workReportCycle != null and  workReportCycle != '' ">

		and t.REPORT_TYPE = #{workReportCycle}

	</if>

            <!-- 地区 -->

	<if test="commonRegionId != null  and  commonRegionId != '' ">

		and t.COMMON_REGION_ID IN (

		select COMMON_REGION_ID from common_region

		CONNECT BY PRIOR COMMON_REGION_ID=UP_REGION_ID

		START WITH COMMON_REGION_ID=#{commonRegionId}

		)

	</if>

            <!-- 开始时间 -->

	<if test="beginDt != null  and  beginDt != '' ">

		and  t.BEGIN_DT &gt;    to_date(#{beginDt},'yyyy-mm-dd hh24:mi:ss')

	</if>

            <!-- 截止时间 -->

	<if test="endDt != null  and  endDt != '' ">

		and t.BEGIN_DT &lt;    to_date(#{endDt},'yyyy-mm-dd hh24:mi:ss')

	</if>

	<!-- 人名 模糊查询 staffName -->

	<if test="staffName != null  and  staffName != '' ">

		and t.create_staff like concat(concat('%',#{staffName}),'%')

	</if>

	<!-- 主键ID -->

	<if test="reportId != null  and  reportId != '' ">

		and t.report_id = #{reportId}

	</if>

</sql>

相关文章