本章记录传参mybatis遇到的一些问题
问题
在给mapper.xml 文件传多参数且其中一个参数为 in 参数
刚开始思考拼接当做一个参数传入 MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| <select id="selectBookInfoList" resultMap="ResultMapWithBLOBs" parameterType="Map">
select
<include refid="Base_Column_List"/>,
<include refid="Blob_Column_List"/>
from book_info
<where>
<if test="bookId != null and bookId != ''">
book_id in (#{bookId})
</if>
<if test="bookIsbn != null and bookIsbn != ''">
and book_isbn like #{bookIsbn}
</if>
<if test="bookName != null and bookName != ''">
and book_name like #{bookName}
</if>
<if test="bookAuthor != null and bookAuthor != ''">
and book_author like #{bookAuthor}
</if>
</where>
<if test="start!=null and size!=null">
limit #{start}, #{size}
</if>
</select>
|
resultMap 为自定义实体类
book_id 为 int类型 #{bookId} 为拼接 1,2,3
结果发现mysql 将sql识别为 select * from book_info where book_id in (‘1,2,3’); 查询出 book_id 为 1 的数据
查询可知
如果直接传入拼接好的where in 条件， 比如（’1’,’2’,’3’)，需要使用${bookId}传参，即绝对引用，而不能使用#
, 如果使用#传参会被mybatis当成字符串再添加一层’’引号，导致错误.
所以多参数拼接可以使用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| <select id="selectBookInfoList" resultMap="ResultMapWithBLOBs" parameterType="Map">
select
<include refid="Base_Column_List"/>,
<include refid="Blob_Column_List"/>
from book_info
<where>
<if test="bookId != null and bookId != ''">
book_id in (${bookId})
</if>
<if test="bookIsbn != null and bookIsbn != ''">
and book_isbn like #{bookIsbn}
</if>
<if test="bookName != null and bookName != ''">
and book_name like #{bookName}
</if>
<if test="bookAuthor != null and bookAuthor != ''">
and book_author like #{bookAuthor}
</if>
</where>
<if test="start!=null and size!=null">
limit #{start}, #{size}
</if>
</select>
|
拼接参数可以是：bookId = ‘1’,’2’,’3’
优点：简单方便，高效，缺点：不能防止SQL注入
评论