MyBatis(四)：mybatis中使用in查询时的注意事项

准备工作

1）创建测试表
jobitem

1CREATE TABLE "jobitem" (
2  "id" bigint(20) NOT NULL AUTO_INCREMENT COMMENT '唯一键 pk',
3  "appId" varchar(32) NOT NULL COMMENT 'yarn任务id(applicationId)',
4  "submitFilePath" varchar(256) NOT NULL COMMENT '提交脚本路径',
5  "state" varchar(16) DEFAULT NULL COMMENT '任务状态',
6  "monitorType" varchar(512) DEFAULT NULL COMMENT '监控列表',
7  "createUserId" varchar(32) NOT NULL COMMENT '创建者关联Id',
8  "createUserName" varchar(32) NOT NULL COMMENT '创建者用户名',
9  "createTime" datetime NOT NULL COMMENT '创建时间',
10  PRIMARY KEY ("id"),
11  UNIQUE KEY "key" ("appId")
12) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='yarn任务持久化存储对象';

备注：这里mysql版本是5.7

2）使用mybatis-generator-plugin生成实体类：

Jobitem.java

1package com.boco.jobmonitor.model;
2
3import java.util.Date;
4
5import javax.persistence.GeneratedValue;
6import javax.persistence.GenerationType;
7import javax.persistence.Id;
8
9public class Jobitem {
10    /**
11     * 唯一键 pk<br>
12     * 列名:id 类型:INTEGER(10) 允许空:false 缺省值:null
13     */
14    @Id
15    @GeneratedValue(strategy = GenerationType.IDENTITY)
16    private Long id;
17
18    /**
19     * yarn任务id(applicationId)<br>
20     * 列名:appId 类型:VARCHAR(32) 允许空:false 缺省值:null
21     */
22    private String appid;
23
24    /**
25     * 提交脚本路径<br>
26     * 列名:submitFilePath 类型:VARCHAR(256) 允许空:false 缺省值:null
27     */
28    private String submitfilepath;
29
30    /**
31     * 任务状态<br>
32     * 列名:state 类型:VARCHAR(16) 允许空:true 缺省值:null
33     */
34    private String state;
35
36    /**
37     * 监控列表<br>
38     * 列名:monitorType 类型:VARCHAR(512) 允许空:true 缺省值:null
39     */
40    private String monitortype;
41
42    /**
43     * 创建者关联Id<br>
44     * 列名:createUserId 类型:VARCHAR(32) 允许空:false 缺省值:null
45     */
46    private String createuserid;
47
48    /**
49     * 创建者用户名<br>
50     * 列名:createUserName 类型:VARCHAR(32) 允许空:false 缺省值:null
51     */
52    private String createusername;
53
54    /**
55     * 创建时间<br>
56     * 列名:createTime 类型:TIMESTAMP(19) 允许空:false 缺省值:null
57     */
58    private Date createtime;
59
60    public Jobitem() {
61    }
62
63    public Jobitem(String appid, String submitfilepath, String state, String monitortype, String createuserid,
64            String createusername, Date createtime) {
65        super();
66        this.appid = appid;
67        this.submitfilepath = submitfilepath;
68        this.state = state;
69        this.monitortype = monitortype;
70        this.createuserid = createuserid;
71        this.createusername = createusername;
72        this.createtime = createtime;
73    }
74
75    public Jobitem(Long id, String appid, String submitfilepath, String state, String monitortype,
76            String createuserid, String createusername, Date createtime) {
77        super();
78        this.id = id;
79        this.appid = appid;
80        this.submitfilepath = submitfilepath;
81        this.state = state;
82        this.monitortype = monitortype;
83        this.createuserid = createuserid;
84        this.createusername = createusername;
85        this.createtime = createtime;
86    }
87
88    /**
89     * 唯一键 pk
90     * 
91     * @author boco
92     * @return id 唯一键 pk
93     */
94    public Long getId() {
95        return id;
96    }
97
98    /**
99     * 唯一键 pk
100     * 
101     * @author boco
102     * @param id
103     *            唯一键 pk
104     */
105    public void setId(Long id) {
106        this.id = id;
107    }
108
109    /**
110     * yarn任务id(applicationId)
111     * 
112     * @author boco
113     * @return appId yarn任务id(applicationId)
114     */
115    public String getAppid() {
116        return appid;
117    }
118
119    /**
120     * yarn任务id(applicationId)
121     * 
122     * @author boco
123     * @param appid
124     *            yarn任务id(applicationId)
125     */
126    public void setAppid(String appid) {
127        this.appid = appid == null ? null : appid.trim();
128    }
129
130    /**
131     * 提交脚本路径
132     * 
133     * @author boco
134     * @return submitFilePath 提交脚本路径
135     */
136    public String getSubmitfilepath() {
137        return submitfilepath;
138    }
139
140    /**
141     * 提交脚本路径
142     * 
143     * @author boco
144     * @param submitfilepath
145     *            提交脚本路径
146     */
147    public void setSubmitfilepath(String submitfilepath) {
148        this.submitfilepath = submitfilepath == null ? null : submitfilepath.trim();
149    }
150
151    /**
152     * 任务状态
153     * 
154     * @author boco
155     * @return state 任务状态
156     */
157    public String getState() {
158        return state;
159    }
160
161    /**
162     * 任务状态
163     * 
164     * @author boco
165     * @param state
166     *            任务状态
167     */
168    public void setState(String state) {
169        this.state = state == null ? null : state.trim();
170    }
171
172    /**
173     * 监控列表
174     * 
175     * @author boco
176     * @return monitorType 监控列表
177     */
178    public String getMonitortype() {
179        return monitortype;
180    }
181
182    /**
183     * 监控列表
184     * 
185     * @author boco
186     * @param monitortype
187     *            监控列表
188     */
189    public void setMonitortype(String monitortype) {
190        this.monitortype = monitortype == null ? null : monitortype.trim();
191    }
192
193    /**
194     * 创建者关联Id
195     * 
196     * @author boco
197     * @return createUserId 创建者关联Id
198     */
199    public String getCreateuserid() {
200        return createuserid;
201    }
202
203    /**
204     * 创建者关联Id
205     * 
206     * @author boco
207     * @param createuserid
208     *            创建者关联Id
209     */
210    public void setCreateuserid(String createuserid) {
211        this.createuserid = createuserid == null ? null : createuserid.trim();
212    }
213
214    /**
215     * 创建者用户名
216     * 
217     * @author boco
218     * @return createUserName 创建者用户名
219     */
220    public String getCreateusername() {
221        return createusername;
222    }
223
224    /**
225     * 创建者用户名
226     * 
227     * @author boco
228     * @param createusername
229     *            创建者用户名
230     */
231    public void setCreateusername(String createusername) {
232        this.createusername = createusername == null ? null : createusername.trim();
233    }
234
235    /**
236     * 创建时间
237     * 
238     * @author boco
239     * @return createTime 创建时间
240     */
241    public Date getCreatetime() {
242        return createtime;
243    }
244
245    /**
246     * 创建时间
247     * 
248     * @author boco
249     * @param createtime
250     *            创建时间
251     */
252    public void setCreatetime(Date createtime) {
253        this.createtime = createtime;
254    }
255}

View Code

使用List对sql in进行传参时

如果参数的类型是List, 则在使用时，collection属性要必须指定为 list
JobitemMapper接口类：

List<Jobitem> findByIdList(List<String> appIds);

对应JobitemMapper.xml文件配置：

1<?xml version="1.0" encoding="UTF-8" ?>
2<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
3<mapper namespace="com.dx.jobmonitor.mapper.JobitemMapper" >
4  <resultMap id="BaseResultMap" type="com.dx.jobmonitor.model.Jobitem" >  
5  <!--
6      WARNING - @mbggenerated
7    -->
8    <id column="id" property="id" jdbcType="BIGINT" />
9    <result column="appId" property="appid" jdbcType="VARCHAR" />
10    <result column="submitFilePath" property="submitfilepath" jdbcType="VARCHAR" />
11    <result column="state" property="state" jdbcType="VARCHAR" />
12    <result column="monitorType" property="monitortype" jdbcType="VARCHAR" />
13    <result column="createUserId" property="createuserid" jdbcType="VARCHAR" />
14    <result column="createUserName" property="createusername" jdbcType="VARCHAR" />
15    <result column="createTime" property="createtime" jdbcType="TIMESTAMP" />
16  </resultMap>  
17  <sql id="Base_Column_List" >
18    <!--
19      WARNING - @mbggenerated
20    -->
21    id,appId,submitFilePath,state,monitorType,createUserId,createUserName,createTime
22  </sql>
23  <select id="findByIdList" resultMap="BaseResultMap">
24        select
25        <include refid="Base_Column_List" />
26        from _jobitem where appId in
27        <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
28             #{item}
29        </foreach>
30  </select> 
31</mapper>

测试代码：

1package com.dx.jobmonitor.web;
2
3import java.util.ArrayList;
4import java.util.HashMap;
5import java.util.List;
6import java.util.Map;
7
8import org.junit.Assert;
9import org.junit.Test;
10import org.junit.runner.RunWith;
11import org.springframework.beans.factory.annotation.Autowired;
12import org.springframework.boot.test.context.SpringBootTest;
13import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
14
15import com.dx.jobmonitor.App;
16import com.dx.jobmonitor.mapper.JobitemMapper;
17import com.dx.jobmonitor.model.Jobitem;
18
19@RunWith(SpringJUnit4ClassRunner.class)
20@SpringBootTest(classes = { App.class, obitemMapper.class }, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
21public class JobitemTest {
22
23    @Autowired
24    private JobitemMapper sJobitemMapper;
25
26    @Test
27    public void testFindByIdList() {
28        List<String> appIds = new ArrayList<String>();
29        appIds.add("application_1548381669007_0057");
30        appIds.add("application_1548381669007_0056");
31        appIds.add("application_1548381669007_0055");
32
33        List<Jobitem> result = sJobitemMapper.findByIdList(appIds);
34
35        Assert.assertEquals(3, result.size());
36    }
37}

使用Array对sql in进行传参时

如果参数的类型是Array,则在使用时，collection属性要必须指定为 array
JobitemMapper接口类：

List<Jobitem> findByIdArray(String[] appIds);

对应JobitemMapper.xml文件配置:

1<select id="findByIdArray" resultMap="BaseResultMap">
2        select
3        <include refid="Base_Column_List" />
4        from _jobitem where appId in
5        <foreach item="item" index="index" collection="array" open="(" separator="," close=")">
6             #{item}
7        </foreach>
8  </select>

测试代码：

1    @Test
2    public void testFindByIdArray() {
3        String[] appIds = new String[] { "application_1548381669007_0057", "application_1548381669007_0056",
4                "application_1548381669007_0055" };
5
6        List<Jobitem> result = sJobitemMapper.findByIdArray(appIds);
7
8        Assert.assertEquals(3, result.size());
9    }

使用Map对sql in传递多参数时

当查询的参数有多个时,例如 findByIds(String name, Long[] ids)。这种情况需要特别注意，在传参数时，一定要改用Map方式, 这样在collection属性可以指定名称
JobitemMapper接口类：

List<Jobitem> findByIdMap(Map<String, Object> creatorAndappIds);

对应JobitemMapper.xml文件配置:

1<select id="findByIdMap" resultMap="BaseResultMap">
2        select
3        <include refid="Base_Column_List" />
4        from _jobitem 
5        where createUserName=#{username,jdbcType=VARCHAR}
6        and appId in
7        <foreach item="item" index="index" collection="ids" open="(" separator="," close=")">
8             #{item}
9        </foreach>
10  </select>

测试代码：

1    @Test
2    public void testFindByIdMap() {
3        Map<String, Object> creatorAndappIds = new HashMap<String, Object>();
4        creatorAndappIds.put("username", "admin");
5        String[] appIds = new String[] { "application_1548381669007_0057", "application_1548381669007_0056",
6                "application_1548381669007_0055" };
7        creatorAndappIds.put("ids", appIds);
8
9        List<Jobitem> result = sJobitemMapper.findByIdMap(creatorAndappIds);
10
11        Assert.assertEquals(3, result.size());
12    }
13
14    @Test
15    public void testFindByIdMap2() {
16        Map<String, Object> creatorAndappIds = new HashMap<String, Object>();
17        creatorAndappIds.put("username", "admin");
18        List<String> appIds = new ArrayList<String>();
19        appIds.add("application_1548381669007_0057");
20        appIds.add("application_1548381669007_0056");
21        appIds.add("application_1548381669007_0055");
22        creatorAndappIds.put("ids", appIds);
23
24        List<Jobitem> result = sJobitemMapper.findByIdMap(creatorAndappIds);
25
26        Assert.assertEquals(3, result.size());
27    }

使用string...对sql in进行传参时

JobitemMapper接口类：

List<Jobitem> findByIdMutilParams(String... appIds);

对应JobitemMapper.xml文件配置:

1<select id="findByIdMutilParams" resultMap="BaseResultMap">
2        select
3        <include refid="Base_Column_List" />
4        from _jobitem 
5        where appId in
6        <foreach item="item" index="index" collection="array" open="(" separator="," close=")">
7             #{item}
8        </foreach>
9  </select>

测试代码：

1    @Test
2    public void testFindByIdMultiParams() {
3        List<Jobitem> result = sJobitemMapper.findByIdMutilParams("application_1548381669007_0057",
4                "application_1548381669007_0056", "application_1548381669007_0055");
5
6        Assert.assertEquals(3, result.size());
7    }