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

准备工作

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

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

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;
    }
}

View Code

使用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());
    }