查询条件包含List的情况
在mybatis中进行搜索时,有时候参数中包含了List,比如传入参数:
1
2
3
4
5
6
7
8
9
10
11
12
|
public class FileRequest{ //文件类型 private Integer fileType; //状态 private List<Status> statusList; } public class Status{ //注册状态 private Integer registerStatus; //会议状态 private Integer meetingStatus } |
在mybatis中查询的语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
< select id= "findList" parameterType= "FileRequest" resultMap= "..." > select * from tableName where 1=1 <if test= "fileType != null " > and file_type = #{fileType} </if> <if test= "statusList != null " > and <foreach collection= "statusList" index = "index" item= "item" open "(" separator= "or" close = ")" > <if test= "item.registerStatus != null " > and register_status= #{item.registerStatus} </if> <if test= "item.meetingStatus != null " > and meeting_status= #{item.meetingStatus } </if> </foreach> </if> </ select > |
查询条件带List和其他类型字段
需求
Mybatis查询条件带List和其他类型字段(Integer,String,...).
1
2
|
select * from table where type=? and code in (?,?,?,?) |
Mapper.java文件
1
2
3
4
|
List< BaseDictionary > selectByTypeAndCodes( @Param("codes") List< Integer > codes, @Param("type") Integer type); Mapper.xml. |
注意其中<foreach collection="codes"中的collection的值要和你定义的List别名@Param(“codes”)一致,
而不是只有一个list参数时的<foreach collection="list"
1
2
3
4
5
6
7
8
9
10
11
12
13
|
< select id = "selectByTypeAndCodes" resultMap = "BaseResultMap" > select < include refid = "Base_Column_List" /> from base_dictionary where type = #{type} AND code in < foreach collection = "codes" index = "index" item = "item" open = "(" separator = "," close = ")" > #{item} </ foreach > AND show_enable=1 AND obj_status=1 ORDER BY sort </ select > |
执行结果:
BaseJdbcLogger.debug(BaseJdbcLogger.java:145)==> Preparing: select id, type, name, code, sort, show_enable, obj_remark, obj_status, obj_createdate, obj_createuser, obj_modifydate, obj_modifyuser from base_dictionary where type = ? AND code in ( ? , ? , ? ) AND show_enable=1 AND obj_status=1 ORDER BY sort
BaseJdbcLogger.debug(BaseJdbcLogger.java:145)==> Parameters: 34(Integer), 1(Integer), 2(Integer), 3(Integer)
BaseJdbcLogger.debug(BaseJdbcLogger.java:145)<== Total: 2
以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。
原文链接:https://blog.csdn.net/gaozhonghua12/article/details/90518961