MyBatis无接口绑定的多个集合参数传递

MyBatis无接口绑定的多个集合参数传递

当我们需要向SQL语句中传递多个集合参数的时候，一般使用接口绑定来实现。

但是不通过接口绑定，能不能实现多集合参数传递呢？

方式一：使用Map集合存放参数，并通过key获取参数

DeptMapper.xml

<select id="queryDeptByDeptnoLocNoInterface" resultType="Dept" >
        select * from dept where deptno in(
        <foreach collection="q1" item="item" separator=",">
            #{item}
        </foreach>
        ) or loc in(
        <foreach collection="q2" item="item" separator=",">
            #{item}
        </foreach>)
    </select>

测试

public class DeptTest001 {
    public static void main(String[] args) {
        SqlSession session = MyBatisUtils.getSession();
        List<Integer> li01 = List.of(20,10);
        List<String> li02 = List.of("CHICAGO","BOSTON");
        Map<String,List> ma =new HashMap<>();
        ma.put("q1",li01);
        ma.put("q2",li02);
        List<Dept> liGet = session.selectList("com.class69.mappers.DeptMapper.queryDeptByDeptnoLocNoInterface",ma);
        liGet.forEach(System.out::println);
        session.close();
    }
}

方式二：使用对象存放参数，并通过属性名获取参数

DeptMapper.xml

<select id="queryDeptByDeptnoLocNoInterface" resultType="Dept" parameterType="com.class69.test.DeptTest003">
        select * from dept where deptno in(
        <foreach collection="list01" item="item" separator=",">
            #{item}
        </foreach>
        ) or loc in(
        <foreach collection="list02" item="item" separator=",">
            #{item}
        </foreach>)
    </select>

测试

//作为传参类型
public class DeptTest003 {
    public List<Integer> list01 = new ArrayList<>();
    public List<String> list02 = new ArrayList<>();

    public void setList01(List<Integer> list01) {
        this.list01 = list01;
    }

    public void setList02(List<String> list02) {
        this.list02 = list02;
    }

    public List<Integer> getList01() {
        return list01;
    }

    public List<String> getList02() {
        return list02;
    }
//测试
    public static void main(String[] args) {
        List<Integer> l01 = List.of(20,10);
        List<String> l02 = List.of("CHICAGO","BOSTON");
        DeptTest003 deptTest003 = new DeptTest003();
        deptTest003.setList01(l01);
        deptTest003.setList02(l02);

        SqlSession session = MyBatisUtils.getSession();
        List<Dept> liGet = session.selectList("com.class69.mappers.DeptMapper.queryDeptByDeptnoLocNoInterface",deptTest003);
        liGet.forEach(System.out::println);
        session.close();
    }
}