MyBatis传入参数为List对象

淡淡的烟草味﹌ 2023-10-03 11:37 21阅读 0赞

# 批量查询 #

## 1、一场景：需要根据多个参数批量查询 ##

二、解决方案  
mapper层接口

List<DocReportRequisitionRelationVo> selectDoctorAndStatusList(@Param("reportSearchRequestList") List<ReportSearchRequest> reportSearchRequestList);

xml

<select id="selectDoctorAndStatusList" resultMap="BaseResultMapVo">
           SELECT
                ds.REPORT_PROCESS_STATUS as  REPORT_PROCESS_STATUS,
                ds.STATUS as STATUS,
                ds.ST_REPORT_SOID as ST_REPORT_SOID,
                ds.UPDATE_USER as UPDATE_USER,
            	ds.FIRST_WRITE_DOCTOR AS FIRST_WRITE_DOCTOR,
            	ds.FIRST_TRIAL_DOCTOR AS FIRST_TRIAL_DOCTOR,
            	ds.SECOND_TRIAL_DOCTOR AS SECOND_TRIAL_DOCTOR,
            	dr.REQUEST_SOID,
            	dr.REGISTRATION_FORM_SOID,
            	dr.OPERATOR_SOID
            FROM
            	DOC_REPORT_REQUISITION_RELATION(NOLOCK) dr
            	LEFT JOIN DOC_STRUCT_REPORT ds ON dr.ST_REPORT_SOID= ds.ST_REPORT_SOID
            WHERE
            <foreach collection="reportSearchRequestList" index="index" item="reportSearchRequest" open="(" close=")" separator="or">
                ( dr.REQUEST_SOID=#{reportSearchRequest.requestSoid,jdbcType=NUMERIC}
                AND dr.REGISTRATION_FORM_SOID=#{reportSearchRequest.registrationFormSoid,jdbcType=NUMERIC}
                AND dr.OPERATOR_SOID=#{reportSearchRequest.operatorSoid,jdbcType=NUMERIC}
                )
            </foreach>
      </select>

执行的sql

SELECT
    	ds.REPORT_PROCESS_STATUS AS REPORT_PROCESS_STATUS,
    	ds.STATUS AS STATUS,
    	ds.ST_REPORT_SOID AS ST_REPORT_SOID,
    	ds.UPDATE_USER AS UPDATE_USER,
    	ds.FIRST_WRITE_DOCTOR AS FIRST_WRITE_DOCTOR,
    	ds.FIRST_TRIAL_DOCTOR AS FIRST_TRIAL_DOCTOR,
    	ds.SECOND_TRIAL_DOCTOR AS SECOND_TRIAL_DOCTOR,
    [添加链接描述](https://www.cnblogs.com/coderzhw/p/11094300.html)	dr.REQUEST_SOID,
    	dr.REGISTRATION_FORM_SOID,
    	dr.OPERATOR_SOID 
    FROM
    	DOC_REPORT_REQUISITION_RELATION ( NOLOCK ) dr
    	LEFT JOIN DOC_STRUCT_REPORT ds ON dr.ST_REPORT_SOID= ds.ST_REPORT_SOID 
    WHERE
    	(
    	   ( dr.REQUEST_SOID= 43383589231919104 AND dr.REGISTRATION_FORM_SOID= 12345648674894 AND dr.OPERATOR_SOID= 789456456 ) 
    	OR ( dr.REQUEST_SOID= 43368423266189312 AND dr.REGISTRATION_FORM_SOID= 948764566489 AND dr.OPERATOR_SOID= 487489456 ) 
    	);

2、单条件批量统计数量  
mapper

Integer queryInstance(@Param("allChreidSoids")List<Long> allChreidSoids);

xml

<select id="queryInstance" resultType="java.lang.Integer">
            SELECT COUNT(*) FROM KNW_COMPOSITE_INSTANCE WHERE COMMON_WORDS_TREE_SOID IN
            <foreach collection="allChreidSoids" item="chreidSoid" separator="," open="(" close=")">
                #{
        chreidSoid}
            </foreach>
        </select>

# 二 批量更新 #

## 1、删除操作 ##

mapper

void deleteCommonWordsTree(@Param("knwCommonWordsTrees")List<KnwCommonWordsTree> knwCommonWordsTrees);

xml

<delete id="deleteCommonWordsTree">
            UPDATE KNW_COMMON_WORDS_TREE
            SET IS_DELETE=1
            WHERE COMMON_WORDS_TREE_SOID IN
            <foreach collection="knwCommonWordsTrees" item="knwCommonWordsTree" separator="," open="(" close=")">
                #{
        knwCommonWordsTree.commonWordsTreeSoid}
            </foreach>
        </delete>

## 2、更新操作 ##

mapper

void updateSeriaNoForList(@Param("knwCommonWordsTrees") List<KnwCommonWordsTree> knwCommonWordsTrees);

xml

<update id="updateSeriaNoForList" parameterType="java.util.List">
            <foreach collection="knwCommonWordsTrees" item="knwCommonWordsTree" open="begin" close="end;" separator=";">
                update KNW_COMMON_WORDS_TREE
                set
                SERIAL_NO=#{
        knwCommonWordsTree.serialNo,jdbcType=NUMERIC}
                where COMMON_WORDS_TREE_SOID=${
        knwCommonWordsTree.commonWordsTreeSoid}
            </foreach>
        </update>

参考资料  
[MyBatis传入参数为List对象][MyBatis_List]

[MyBatis_List]: https://www.cnblogs.com/coderzhw/p/11094300.html