Db2 MyBatis where条件中多字段的in

环境

• Db2: V11.5
• MyBatis: 3.5.6

现有table T1 如下：

[db2inst1@ruili1 ~]$ db2 "select * from t1"

C1          C2          C3
----------- ----------- ----------------------------------------------------------------------------------------------------
          1         100 aaa
          2         110 bbb
          3         150 ccc
          4         100 ddd
          5         110 aaa

  5 record(s) selected.

SQL查询

如果想查询 C2 为 100 且 C3 为 aaa 的记录：

[db2inst1@ruili1 ~]$ db2 "select * from t1 where c2 = 100 and c3 = 'aaa'"

C1          C2          C3
----------- ----------- ----------------------------------------------------------------------------------------------------
          1         100 aaa

  1 record(s) selected.

也可以写成：

[db2inst1@ruili1 ~]$ db2 "select * from t1 where (c2, c3) = (100, 'aaa')"

C1          C2          C3
----------- ----------- ----------------------------------------------------------------------------------------------------
          1         100 aaa

  1 record(s) selected.

如果想查询 C2 为 100 且 C3 为 aaa ，或者 C2 为 110 且 C3 为 bbb 的记录，该怎么写SQL呢？

下面是错误的写法：

[db2inst1@ruili1 ~]$ db2 "select * from t1 where c2 in (100, 110) and c3 in ('aaa', 'bbb')"

C1          C2          C3
----------- ----------- ----------------------------------------------------------------------------------------------------
          1         100 aaa
          2         110 bbb
          5         110 aaa

  3 record(s) selected.

结果里有一条不满足条件的记录： 110, aaa 。错误的原因在于，SQL语句里把 C2 和 C3 的条件割离了，单独看 110 和 aaa 都满足一部分条件，但是合起来就不满足条件了。

正确写法如下：

[db2inst1@ruili1 ~]$ db2 "select * from t1 where (c2, c3) in (values (100, 'aaa'), (110, 'bbb'))"

C1          C2          C3
----------- ----------- ----------------------------------------------------------------------------------------------------
          1         100 aaa
          2         110 bbb

  2 record(s) selected.

MyBatis查询

MyBatis提供了 foreach 标签来做循环，因此，可以把上面SQL语句里的IN转化为MyBatis的 foreach 循环。

先定义2个POJO：

• Input1 ：传入参数

package testPck0418.pojo;

public class Input1 {
	private int p1;
	private String p2;

	public int getP1() {
		return p1;
	}

	public void setP1(int p1) {
		this.p1 = p1;
	}

	public String getP2() {
		return p2;
	}

	public void setP2(String p2) {
		this.p2 = p2;
	}
}

• Output1 ：接收查询结果

package testPck0418.pojo;

public class Output1 {
	private int c1;
	private int c2;
	private String c3;

	public int getC1() {
		return c1;
	}

	public void setC1(int c1) {
		this.c1 = c1;
	}

	public int getC2() {
		return c2;
	}

	public void setC2(int c2) {
		this.c2 = c2;
	}

	public String getC3() {
		return c3;
	}

	public void setC3(String c3) {
		this.c3 = c3;
	}
}

Mapper定义如下：

• Test0419_1Mapper.java ：

package testPck0418.dao;

import java.util.List;

import testPck0418.pojo.Input1;
import testPck0418.pojo.Output1;

public interface Test0419_1Mapper {
	List<Output1> read(List<Input1> listInput);
}

• Test0419_1Mapper.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="testPck0418.dao.Test0419_1Mapper">
	<select id = "read" parameterType="list" resultType="testPck0418.pojo.Output1">
		select c1, c2, c3 from t1
 		where (c2, c3) in (values
		<foreach item="item" index="index" collection="list" separator=",">
                   (#{item.p1}, #{item.p2})
		</foreach>
		)
	</select>
</mapper>

注意：

• 这里的collection值为 list ，这是hard-code的。
• p1 和 p2 是item的2个属性，这里可直接通过 #{item.p1} 来获取其值，相当于Java里的 item.getP1() 方法。
• 本例中循环还有另一种写法：

	<select id = "read" parameterType="list" resultType="testPck0418.pojo.Output1">
		select c1, c2, c3 from t1
		where (c2, c3) in (values
		<foreach item="item" index="index" collection="list" separator=",">
		<trim prefix="(" suffix=")" suffixOverrides=",">
			#{item.p1}, #{item.p2},
		</trim>
		</foreach>
		)
	</select>

• 另外，当传入list时，甚至可以直接通过下标来获取list的元素，比如：

	<select id = "read" parameterType="list" resultType="testPck0418.pojo.Output1">
		select c1, c2, c3 from t1
		where c2 = #{list[0].p1}
	</select>

主程序如下：

• TestMybatis419_1.java ：

package testPck0418.service;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import testPck0418.pojo.Output1;
import testPck0418.pojo.Input1;
import testPck0418.dao.Test0419_1Mapper;

public class TestMybatis419_1 {
	public static void main(String[] args) throws IOException {
		String resource = "mybatis-config.xml";
		
		InputStream inputStream = Resources.getResourceAsStream(resource);
		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		
		try {
			Test0419_1Mapper mapper = sqlSession.getMapper(Test0419_1Mapper.class);
			
			List<Input1> list1 = new ArrayList<Input1>();
			
			Input1 myObj1 = new Input1();
			myObj1.setP1(100);
			myObj1.setP2("aaa");
			list1.add(myObj1);
			
			myObj1 = new Input1();
			myObj1.setP1(110);
			myObj1.setP2("bbb");
			list1.add(myObj1);
						
			List<Output1> list = mapper.read(list1);
			list.forEach(e -> System.out.println("c1: " + e.getC1() + ", c2: " + e.getC2() + ", c3: " + e.getC3()));
		} finally {
			sqlSession.close();
		}
	}
}

运行程序，结果如下：

c1: 1, c2: 100, c3: aaa
c1: 2, c2: 110, c3: bbb

至此，问题得到解决。不过我们可以再扩展一下，假定还有其它参数，比如：

[db2inst1@ruili1 ~]$ db2 "select * from t1 where c1 = 1 and (c2, c3) in (values (100, 'aaa'), (110, 'bbb'))"

C1          C2          C3
----------- ----------- ----------------------------------------------------------------------------------------------------
          1         100 aaa

  1 record(s) selected.

也就是说，需要传2个参数：一个参数是整数 1 ，另一个参数是一个list。

一种方法是把多个参数封装到一个Map里，转化为单个参数，在mapper中通过key来获取其内容。

Mapper定义如下：

• Test0419_2Mapper.java ：

package testPck0418.dao;

import java.util.List;
import java.util.Map;

import testPck0418.pojo.Output1;

public interface Test0419_2Mapper {
	List<Output1> read(Map<String, Object> map);
}

• Test0419_2Mapper.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="testPck0418.dao.Test0419_2Mapper">
	<select id = "read" parameterType="map" resultType="testPck0418.pojo.Output1">
		select c1, c2, c3 from t1
 		where c1 = #{v1} and (c2, c3) in (values
		<foreach item="item" index="index" collection="v2" separator=",">
                   (#{item.p1}, #{item.p2})
		</foreach>
	)
	</select>
</mapper>

主程序如下：

• TestMybatis419_2.java

package testPck0418.service;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import testPck0418.pojo.Output1;
import testPck0418.pojo.Input1;
import testPck0418.dao.Test0419_2Mapper;

public class TestMybatis419_2 {
	public static void main(String[] args) throws IOException {
		String resource = "mybatis-config.xml";
		
		InputStream inputStream = Resources.getResourceAsStream(resource);
		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		
		try {
			Test0419_2Mapper mapper = sqlSession.getMapper(Test0419_2Mapper.class);
			
			List<Input1> list1 = new ArrayList<Input1>();
			
			Input1 myObj1 = new Input1();
			myObj1.setP1(100);
			myObj1.setP2("aaa");
			list1.add(myObj1);
			
			myObj1 = new Input1();
			myObj1.setP1(110);
			myObj1.setP2("bbb");
			list1.add(myObj1);
					
			Map<String, Object> map = new HashMap<String, Object>();
			
			map.put("v1", 1);
			map.put("v2", list1);
			
			List<Output1> list = mapper.read(map);
			list.forEach(e -> System.out.println("c1: " + e.getC1() + ", c2: " + e.getC2() + ", c3: " + e.getC3()));		
		} finally {
			sqlSession.close();
		}
	}
}

运行结果如下：

c1: 1, c2: 100, c3: aaa

事实上，要传递多个参数，用注解的方式会更好一些。参见我另一篇文档。