MyBatis(四)：mybatis中使用in查询时的注意事项
2024-04-03 21:17:45
准备工作
1）创建测试表
jobitem
CREATE TABLE "jobitem" (
"id" bigint(20) NOT NULL AUTO_INCREMENT COMMENT '唯一键 pk',
"appId" varchar(32) NOT NULL COMMENT 'yarn任务id(applicationId)',
"submitFilePath" varchar(256) NOT NULL COMMENT '提交脚本路径',
"state" varchar(16) DEFAULT NULL COMMENT '任务状态',
"monitorType" varchar(512) DEFAULT NULL COMMENT '监控列表',
"createUserId" varchar(32) NOT NULL COMMENT '创建者关联Id',
"createUserName" varchar(32) NOT NULL COMMENT '创建者用户名',
"createTime" datetime NOT NULL COMMENT '创建时间',
PRIMARY KEY ("id"),
UNIQUE KEY "key" ("appId")
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='yarn任务持久化存储对象';
备注：这里mysql版本是5.7
2）使用mybatis-generator-plugin生成实体类：
Jobitem.java
package com.boco.jobmonitor.model; import java.util.Date; import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id; public class Jobitem {
/**
* 唯一键 pk<br>
* 列名:id 类型:INTEGER(10) 允许空:false 缺省值:null
*/
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id; /**
* yarn任务id(applicationId)<br>
* 列名:appId 类型:VARCHAR(32) 允许空:false 缺省值:null
*/
private String appid; /**
* 提交脚本路径<br>
* 列名:submitFilePath 类型:VARCHAR(256) 允许空:false 缺省值:null
*/
private String submitfilepath; /**
* 任务状态<br>
* 列名:state 类型:VARCHAR(16) 允许空:true 缺省值:null
*/
private String state; /**
* 监控列表<br>
* 列名:monitorType 类型:VARCHAR(512) 允许空:true 缺省值:null
*/
private String monitortype; /**
* 创建者关联Id<br>
* 列名:createUserId 类型:VARCHAR(32) 允许空:false 缺省值:null
*/
private String createuserid; /**
* 创建者用户名<br>
* 列名:createUserName 类型:VARCHAR(32) 允许空:false 缺省值:null
*/
private String createusername; /**
* 创建时间<br>
* 列名:createTime 类型:TIMESTAMP(19) 允许空:false 缺省值:null
*/
private Date createtime; public Jobitem() {
} public Jobitem(String appid, String submitfilepath, String state, String monitortype, String createuserid,
String createusername, Date createtime) {
super();
this.appid = appid;
this.submitfilepath = submitfilepath;
this.state = state;
this.monitortype = monitortype;
this.createuserid = createuserid;
this.createusername = createusername;
this.createtime = createtime;
} public Jobitem(Long id, String appid, String submitfilepath, String state, String monitortype,
String createuserid, String createusername, Date createtime) {
super();
this.id = id;
this.appid = appid;
this.submitfilepath = submitfilepath;
this.state = state;
this.monitortype = monitortype;
this.createuserid = createuserid;
this.createusername = createusername;
this.createtime = createtime;
} /**
* 唯一键 pk
*
* @author boco
* @return id 唯一键 pk
*/
public Long getId() {
return id;
} /**
* 唯一键 pk
*
* @author boco
* @param id
* 唯一键 pk
*/
public void setId(Long id) {
this.id = id;
} /**
* yarn任务id(applicationId)
*
* @author boco
* @return appId yarn任务id(applicationId)
*/
public String getAppid() {
return appid;
} /**
* yarn任务id(applicationId)
*
* @author boco
* @param appid
* yarn任务id(applicationId)
*/
public void setAppid(String appid) {
this.appid = appid == null ? null : appid.trim();
} /**
* 提交脚本路径
*
* @author boco
* @return submitFilePath 提交脚本路径
*/
public String getSubmitfilepath() {
return submitfilepath;
} /**
* 提交脚本路径
*
* @author boco
* @param submitfilepath
* 提交脚本路径
*/
public void setSubmitfilepath(String submitfilepath) {
this.submitfilepath = submitfilepath == null ? null : submitfilepath.trim();
} /**
* 任务状态
*
* @author boco
* @return state 任务状态
*/
public String getState() {
return state;
} /**
* 任务状态
*
* @author boco
* @param state
* 任务状态
*/
public void setState(String state) {
this.state = state == null ? null : state.trim();
} /**
* 监控列表
*
* @author boco
* @return monitorType 监控列表
*/
public String getMonitortype() {
return monitortype;
} /**
* 监控列表
*
* @author boco
* @param monitortype
* 监控列表
*/
public void setMonitortype(String monitortype) {
this.monitortype = monitortype == null ? null : monitortype.trim();
} /**
* 创建者关联Id
*
* @author boco
* @return createUserId 创建者关联Id
*/
public String getCreateuserid() {
return createuserid;
} /**
* 创建者关联Id
*
* @author boco
* @param createuserid
* 创建者关联Id
*/
public void setCreateuserid(String createuserid) {
this.createuserid = createuserid == null ? null : createuserid.trim();
} /**
* 创建者用户名
*
* @author boco
* @return createUserName 创建者用户名
*/
public String getCreateusername() {
return createusername;
} /**
* 创建者用户名
*
* @author boco
* @param createusername
* 创建者用户名
*/
public void setCreateusername(String createusername) {
this.createusername = createusername == null ? null : createusername.trim();
} /**
* 创建时间
*
* @author boco
* @return createTime 创建时间
*/
public Date getCreatetime() {
return createtime;
} /**
* 创建时间
*
* @author boco
* @param createtime
* 创建时间
*/
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
}
使用List对sql in进行传参时
如果参数的类型是List, 则在使用时，collection属性要必须指定为 list
JobitemMapper接口类：
List<Jobitem> findByIdList(List<String> appIds);
对应JobitemMapper.xml文件配置：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.dx.jobmonitor.mapper.JobitemMapper" >
<resultMap id="BaseResultMap" type="com.dx.jobmonitor.model.Jobitem" >
<!--
WARNING - @mbggenerated
-->
<id column="id" property="id" jdbcType="BIGINT" />
<result column="appId" property="appid" jdbcType="VARCHAR" />
<result column="submitFilePath" property="submitfilepath" jdbcType="VARCHAR" />
<result column="state" property="state" jdbcType="VARCHAR" />
<result column="monitorType" property="monitortype" jdbcType="VARCHAR" />
<result column="createUserId" property="createuserid" jdbcType="VARCHAR" />
<result column="createUserName" property="createusername" jdbcType="VARCHAR" />
<result column="createTime" property="createtime" jdbcType="TIMESTAMP" />
</resultMap>
<sql id="Base_Column_List" >
<!--
WARNING - @mbggenerated
-->
id,appId,submitFilePath,state,monitorType,createUserId,createUserName,createTime
</sql>
<select id="findByIdList" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from _jobitem where appId in
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
</select>
</mapper>
测试代码：
package com.dx.jobmonitor.web; import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map; import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import com.dx.jobmonitor.App;
import com.dx.jobmonitor.mapper.JobitemMapper;
import com.dx.jobmonitor.model.Jobitem; @RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = { App.class, obitemMapper.class }, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class JobitemTest { @Autowired
private JobitemMapper sJobitemMapper; @Test
public void testFindByIdList() {
List<String> appIds = new ArrayList<String>();
appIds.add("application_1548381669007_0057");
appIds.add("application_1548381669007_0056");
appIds.add("application_1548381669007_0055"); List<Jobitem> result = sJobitemMapper.findByIdList(appIds); Assert.assertEquals(3, result.size());
}
}
使用Array对sql in进行传参时
如果参数的类型是Array,则在使用时，collection属性要必须指定为 array
JobitemMapper接口类：
List<Jobitem> findByIdArray(String[] appIds);
对应JobitemMapper.xml文件配置:
<select id="findByIdArray" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from _jobitem where appId in
<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
#{item}
</foreach>
</select>
测试代码：
@Test
public void testFindByIdArray() {
String[] appIds = new String[] { "application_1548381669007_0057", "application_1548381669007_0056",
"application_1548381669007_0055" }; List<Jobitem> result = sJobitemMapper.findByIdArray(appIds); Assert.assertEquals(3, result.size());
}
使用Map对sql in传递多参数时
当查询的参数有多个时,例如 findByIds(String name, Long[] ids)。这种情况需要特别注意，在传参数时，一定要改用Map方式, 这样在collection属性可以指定名称
JobitemMapper接口类：
List<Jobitem> findByIdMap(Map<String, Object> creatorAndappIds);
对应JobitemMapper.xml文件配置:
<select id="findByIdMap" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from _jobitem
where createUserName=#{username,jdbcType=VARCHAR}
and appId in
<foreach item="item" index="index" collection="ids" open="(" separator="," close=")">
#{item}
</foreach>
</select>
测试代码：
@Test
public void testFindByIdMap() {
Map<String, Object> creatorAndappIds = new HashMap<String, Object>();
creatorAndappIds.put("username", "admin");
String[] appIds = new String[] { "application_1548381669007_0057", "application_1548381669007_0056",
"application_1548381669007_0055" };
creatorAndappIds.put("ids", appIds); List<Jobitem> result = sJobitemMapper.findByIdMap(creatorAndappIds); Assert.assertEquals(3, result.size());
} @Test
public void testFindByIdMap2() {
Map<String, Object> creatorAndappIds = new HashMap<String, Object>();
creatorAndappIds.put("username", "admin");
List<String> appIds = new ArrayList<String>();
appIds.add("application_1548381669007_0057");
appIds.add("application_1548381669007_0056");
appIds.add("application_1548381669007_0055");
creatorAndappIds.put("ids", appIds); List<Jobitem> result = sJobitemMapper.findByIdMap(creatorAndappIds); Assert.assertEquals(3, result.size());
}
使用string...对sql in进行传参时
JobitemMapper接口类：
List<Jobitem> findByIdMutilParams(String... appIds);
对应JobitemMapper.xml文件配置:
<select id="findByIdMutilParams" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from _jobitem
where appId in
<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
#{item}
</foreach>
</select>
测试代码：
@Test
public void testFindByIdMultiParams() {
List<Jobitem> result = sJobitemMapper.findByIdMutilParams("application_1548381669007_0057",
"application_1548381669007_0056", "application_1548381669007_0055"); Assert.assertEquals(3, result.size());
}
最新文章
- 异步HTTPHandler的实现
- dragsort html拖拽排序
- java List 排序
- C语言程序设计第6堂作业
- jQuery HTML节点元素修改、追加的方法 html()、append()、prepend()、
- 在VS2010中ActiveX控件注册方法，使用regsvr32命令
- Sails 关闭自动路由 Automatic Routes 功能。
- WPF RichTextBox滚动条自动滚动实例、文本自动滚动实例
- android应用开发--------------看RadioGroup源代码，写相似单选选项卡的集成控件（如底部导航，tab等等）
- github 上传项目基本步骤
- Long Long Message （poj2774 后缀数组求最长公共子串）
- Flex读取txt文件中的内容报错
- nyoj 还是回文
- hdu1421_搬寝室
- Hibernate 基础解析（Configuration，SessionFactory，Session，Transaction，Query，Criteria）
- 编程最好用的字体consolas
- django的FBV和CBV的装饰器例子
- Android Activity传递数据使用getIntent()接收不到，揭秘Intent传递数据与Activity启动模式singleTask的关系。
- EBADF, read
- [leetcode]ZigZag Conversion @ Python