mybatis 使用in条件查询 博客分类： mybatis

mybatis

使用in条件查询

java mapper
<!--List<Agency> findAgencyById(Long[] ids) 对应 collection="array"-->
<!--List<Agency> findAgencyById(@Param("ids") Long[] ids) 对应 collection="ids"-->
<!--List<Agency> findAgencyById(@Param("data") List<Long> list) 对应 collection="data"-->

xml mapper

<!-- array的非空判断 -->
<if test=" ids != null and ids.lengh > 0 " >

<!-- list的非空判断 -->
<if test=" ids != null and ids.size() > 0 " >

<!-- array和list通过 -->

<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>

完整示例(array)

select
		<include refid="Base_Column_List" />
		from t_agency_site
		where 1=1
		<if test="ids.length>0">
			and agency_id in 
			<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
	</select>

-----分割线-----

<!--List:forech中的collection属性类型是List,collection的值必须是:list,item的值可以随意,Dao接口中参数名字随意 --> 
    <select id="getEmployeesListParams" 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的值必须是:list,item的值可以随意,Dao接口中参数名字随意 --> 
    <select id="getEmployeesArrayParams" 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属性是map.key,其它所有属性都是map.key,比如下面的departmentId --> 
    <select id="getEmployeesMapParams" resultType="Employees"> 
        SELECT * 
        FROM employees e 
        <where> 
            <if test="departmentId!=null and departmentId!=''"> 
                e.department_id=#{departmentId} 
            </if> 
            <if test="employeeIdsArray!=null and employeeIdsArray.length!=0"> 
                AND e.employee_id in 
                <foreach collection="employeeIdsArray" item="employeeId" 
                    index="index" open="(" close=")" separator=","> 
                    #{employeeId} 
                </foreach> 
            </if> 
        </where> 
    </select>