MyBatis XML中 SELECT IN 的写法

一、常见错误

错误代码（一般写法，# 防止SQL注入）

// Controller代码
String itemIdList = "4,5,6";
List<TtRoLabourPO> list = this.getBaseService().findByItemIdList(itemIdList);

<select id="queryByItemIdList" parameterType="java.lang.String" resultMap="roLabourMap">
    SELECT * FROM TT_RO_LABOUR
    WHERE ITEM_ID IN (#{itemIdList})
</select>

这样查出来的结果等价为

SELECT * FROM TT_RO_LABOUR WHERE ITEM_ID IN ("4,5,6")

查出结果只有 ITEM_ID=4 一条记录。

二、纠错改正

正确代码

// Controller代码
String itemIdList = "4,5,6";
Map<String, Object> itemMap = new HashMap<String, Object>();
itemMap.put("itemIdList", itemIdList.split(","));
List<TtRoLabourPO> list = this.getBaseService().findByItemIdList(itemMap);
log.debug("--------------list: {}", list);
log.debug("--------------list.size(): {}", list.size());

// Service代码
public List<TtRoLabourPO> findByItemIdList(Map<String, Object> itemMap) {
    List<TtRoLabourPO> list = this.getMapper().queryByItemIdList(itemMap);
    return list;
}

// mapper xml代码
<select id="queryByItemIdList" parameterType="java.lang.String" resultMap="roLabourMap">
    SELECT * FROM TT_RO_LABOUR
    WHERE ITEM_ID IN 
    <foreach item="item" index="index" collection="itemIdList" open="(" separator="," close=")">
        #{item}
    </foreach>      
</select>

此时达到 SELECT...IN... 的查询目的

SELECT * FROM TT_RO_LABOUR WHERE ITEM_ID IN ( 4 , 5 , 6 ) 

截图展示

控制台输出.png