mybatis中的文件中foreach的使用

时间：2025-03-20 11:38:08

一、类方法中设置了入参的@Param后，SQL中即使用其定义的字段，如下：

1、collection属性类型是List

List<ChannelAndAppRelationEntity> queryCoreNameAndAppNoByBorId(@Param("borIds") List borIds);


<select id="queryCoreNameAndAppNoByBorId" parameterType=""
   resultType="">
select a.coreName,a.appNo, a.coreId, c.cust_src_name as secondPlatName
from
(
  SELECT b.company_name coreName, r.app_no appNo, r.owned_channel_id coreId
  FROM `t_ins_app` r, t_com_company_base b
  WHERE r.owned_channel_id = b.id
  <if test="null!=borIds">
   and r.bor_id in
   <foreach collection="borIds" index="index" item="id" open="(" separator="," close=")">
    #{id, jdbcType=VARCHAR}
   </foreach>
  </if>
) a left join t_core_company_src_config c
on a.coreId = c.core_id
</select>

2、collection属性类型是Array

List<Map<String, Object>> queryOperatingByBorIds(@Param("borIds") Object[] borIds);


<select id="queryOperatingByBorIds" parameterType="Map" resultType="Map">
select `car_insurance_aging_proportion` as carInsuranceAgingProportion,
  `channel_code` as channelCode, `com_id` as borId
from `t_into_operating`
where 1=1
  <if test="null!=borIds">
   and com_id in
   <foreach collection="borIds" index="index" item="borId" open="(" separator="," close=")">
    #{borId}
   </foreach>
  </if>
order by id desc
</select>

二、类方法中设置了入参的@Param，如下：


    <!--List:forech中的collection属性类型是List,collection的值必须是:list,item的值可以随意,Dao接口中参数名字随意 -->  
    <select id="getIteamsList" resultType="Employees">  
        select *  
        from EMPLOYEES e  
        where e.EMPLOYEE_ID in  
        <foreach collection="list" item="employeeId" index="index"  
            open="(" close=")" separator=",">  
            #{employeeId}  
        </foreach>  
    </select>  
  
    <!--Array:forech中的collection属性类型是array,collection的值必须是:array,item的值可以随意,Dao接口中参数名字随意 -->  
    <select id="getItemsArray" resultType="Employees">  
        select *  
        from EMPLOYEES e  
        where e.EMPLOYEE_ID in  
        <foreach collection="array" item="employeeId" index="index"  
            open="(" close=")" separator=",">  
            #{employeeId}  
        </foreach>  
    </select>  
  
    <!--Map:forech中的collection属性是-->  
    <select id="getItemsMap" resultType="Employees">  
        select *  
        from EMPLOYEES e  
        <where>  
            <if test="departmentId!=null and departmentId!=''">  
                e.DEPARTMENT_ID=#{departmentId}  
            </if>  
            <if test="employeeIdsArray!=null and !=0">  
                AND e.EMPLOYEE_ID in  
                <foreach collection="employeeIdsArray" item="employeeId"  
                    index="index" open="(" close=")" separator=",">  
                    #{employeeId}  
                </foreach>  
            </if>  
        </where>  
    </select>

在使用Object[]或List作为入参时，有可能SQL执行报错，如下：
Type handler was null on parameter mapping for property ‘__frch_item_0’. It was either not specified andor could not be found for the javaType jdbcType combination specifi

原因：

  1. 与中的类型不一致
<foreach item="item" index="index" collection="branchListN" open="(" separator="," close=")">
    #{item.code,jdbcType=VARCHAR}
</foreach>
  1. xml中有注释，删除即可

相关文章