业务说明

表结构

t_book为图书表，t_borrow_record为借阅记录表，t_book的主键id是t_borrow_record的外键bookId

image-20220125000623293

说明

通过图书名称查询借阅记录，因为t_borrow_record是没有图书名称字段的，所以先通过模糊查询查询t_book的图书名称获取id集合，然后通过t_borrow_record的bookId字段使用in查询即可查询到数据

XML代码

关键代码

当idList不为空时，才使用in查询

1
2
3
4
5
6
<if test="idList != null">
    and bookId in
    <foreach item="item" index="index" collection="idList" open="(" separator="," close=")">
        #{item}
    </foreach>
</if>

完整代码

idList通过map传入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<select id="list" parameterType="map" resultMap="BorrowRecordResult">
    select *
    from t_borrow_record
    <where>
        <if test="userName != null and userName != ''">
            and userName like #{userName}
        </if>
        <if test="userId != null">
            and userId = #{userId}
        </if>
        <if test="idList != null">
            and bookId in
            <foreach item="item" index="index" collection="idList" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </where>
    <if test="start != null and size != null">
        limit #{start},#{size}
    </if>
</select>

Java代码

下面代码主要是获取idList以及将idList传入map

1
2
3
4
5
6
Map<String, Object> map = new HashMap<>(16);
List<Integer> idList;
if (StringUtil.isNotEmpty(borrowRecord.getBookName())) {
    idList = bookService.selectIdByNameLike(StringUtil.formatLike(borrowRecord.getBookName()));
    map.put("idList", idList);
}