Mybatis传入参数类型为List<Intergert>作为条件进行查询

矫情吗；* 2023-07-11 15:08 33阅读 0赞

# Mybatis传入参数类型为List作为条件进行查询 #

[![format_png][]][format_png 1]

[higher2017][format_png 1]关注

2017.02.07 10:23:16字数 130阅读 5,658

表结构：

![format_png 2][]

表名称为constant

需求：

现在想查询type为2、3的所有数据（甚至想查询type为1,2,3,4,5....,100的所有数据）并且返回的值是Map（key为id，value为constant类）

如果采用一个一个传参的方式进行查询肯定是不行的,以下是通过Mybatis提供的foreach标签并配合in进行查询

Mapper：

<?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.jm.dao.ConstantDao">
        <resultMap id="BaseResultMap" type="com.jm.model.Constant">
            <result column="id" property="id" jdbcType="BIGINT" />
            <result column="key" property="key" jdbcType="VARCHAR" />
            <result column="value" property="value" jdbcType="VARCHAR" />
            <result column="type" property="type" jdbcType="INTEGER" />
        </resultMap>
    
                    
        <select id="loadConstantByType" resultMap="BaseResultMap">
            select * from constant where type in
            
            <foreach item="item" index="index" collection="list" open="("
                separator="," close=")">#{item}</foreach>
        </select>
    
    </mapper>

DAO：

import java.util.List;
    import java.util.Map;
    
    import org.apache.ibatis.annotations.MapKey;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public interface ConstantDao {
        
        /**
         * 注释@MapKey表示表中那个字段作为Map的key
         * @return
         */
        @MapKey("id")
        Map<Long,Constant> loadConstantByType(List<Integer> type);
    }

POJO实体类：

public class Constant {
        private Long id;
        private String key;
        private String value;
        private Integer type;
        
        public String toString(){
            StringBuffer s = new StringBuffer("Constant:{");
            s.append("id:").append(this.id).append(",");
            s.append("key:").append(this.key).append(",");
            s.append("value:").append(this.value).append(",");
            s.append("type:").append(this.type).append("}");
            return s.toString();
        }
        public Constant() {
            super();
            // TODO Auto-generated constructor stub
        }
        public Long getId() {
            return id;
        }
        public void setId(Long id) {
            this.id = id;
        }
        public String getKey() {
            return key;
        }
        public void setKey(String key) {
            this.key = key == null ? null : key.trim();
        }
        public String getValue() {
            return value;
        }
        public void setValue(String value) {
            this.value = value == null ? null : value.trim();
        }
        public Integer getType() {
            return type;
        }
        public void setType(Integer type) {
            this.type = type;
        }
    }

pojo中的实体类最好重写toString方法

junitTest:

import java.util.Arrays;
    import java.util.List;
    import java.util.Map;
    
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.test.context.ContextConfiguration;
    import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
    
    @RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration(locations = { "classpath:spring.xml", "classpath:spring-mybatis.xml" })
    public class MubatisMapTest {
        @Autowired
        private ConstantDao constantDao;
        
        @Test
        public void mapTest1() {
            List<Integer> typeList = Arrays.asList(2,3);
            Map<Long,Constant> constantMap = constantDao.loadConstantByType(typeList);
            System.out.println(constantMap);
        }
    }

最终测试结果：

![format_png 3][]

Paste\_Image.png

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jZG4yLmppYW5zaHUuaW8vYXNzZXRzL2RlZmF1bHRfYXZhdGFyLzQtMzM5NzE2M2VjZGIzODU1YTBhNDEzOWMzNGE2OTU4ODUuanBn?x-oss-process=image/format,png
[format_png 1]: https://www.jianshu.com/u/056959c2e74a
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8zOTQzODQ2LWYxNTVhZDE1MGIzMmUzOWQucG5n?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8zOTQzODQ2LWUzYmQwMWU5MWM1ZTM5Y2QucG5n?x-oss-process=image/format,png