MyBatis 中实现SQL语句中in的操作
概括：应用myBatis实现SQL查询中IN的操作
1、数据库结构及其数据
2、mapper.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.mybatis03.mapper.PersonMapper">
<!-- foreach 操作 -->
<select id="queryPersonWithInIDs" parameterType="com.mybatis03.bean.PersonIDs" resultType="com.mybatis03.bean.Person">
select * from t_person_01
<!-- where 只会处理第一个条件 -->
<!--
1、PersonIDs 为集合类
2、personNos 为PersonIDs集合类中的属性
3、personNo 为遍历personNos 的临时变量（相当于 for循环中的i）
4、separator 作用为 select * from t_person_01 where in(1001,1002,1003) 中的分割符号逗号，既：“，”
-->
<where>
<if test="personNos !=null and personNos.size >0 ">
<foreach collection="personNos" open=" and id in(" close=")" item="personNo" separator=",">
#{personNo}
</foreach>
</if>
</where>
</select> </mapper>
3、接口类
/**
* @author ：jack.zhao
* @Describe: 操作mybatis接口
* @date ：2021-10-16 22:55
*/
public interface PersonMapper {
// 人员编号集合（根据人员id集合查询）select * from t_person_01 where id in(1001,1002,1003);
List<Person> queryPersonWithInIDs(PersonIDs personIDs);
}
4、作为查询条件集合类
package com.mybatis03.bean; import java.util.List; /**
* @author ：jack.zhao
* @Describe: 查询条件集合类
* @date ：2021-10-16 22:55
*/
public class PersonIDs { public List<Integer> getPersonNos() {
return personNos;
} public void setPersonNos(List<Integer> personNos) {
this.personNos = personNos;
} // 人员编号集合（根据人员id集合查询）select * from t_person_01 where id in(1001,1002,1003);
private List<Integer> personNos;
}
5、测试类中测试方法
/**
* @author ：jack.zhao
* @Describe: 测试类
* @date ：2021-10-16 22:55
*/
public class test03 {
// // 人员编号集合（根据人员id集合查询）select * from t_person_01 where id in(1001,1002,1003);
@Test
public void queryPersonWithInIDs() throws Exception{
Reader reader = Resources.getResourceAsReader("mybatis-03.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader); SqlSession session = sessionFactory.openSession();
PersonIDs personIDs = new PersonIDs(); List<Integer> lists = new ArrayList<>();
lists.add(1001);
lists.add(1002);
lists.add(1003);
personIDs.setPersonNos(lists); // 动态代理
PersonMapper personMapper = session.getMapper(PersonMapper.class);
List<Person> personList = personMapper.queryPersonWithInIDs(personIDs);
System.out.println("动态SQL查询结果为:"+personList);
session.close();
}
}
查询结果：
"C:\Program Files\Java\jdk1.8.0_25\bin\java" -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:C:\Users\newsoft\AppData\Roaming\JetBrains\IntelliJ IDEA 2017.3.5\lib\idea_rt.jar=1182:C:\Users\newsoft\AppData\Roaming\JetBrains\IntelliJ IDEA 2017.3.5\bin" -Dfile.encoding=UTF-8 -classpath "C:\Users\newsoft\AppData\Roaming\JetBrains\IntelliJ IDEA 2017.3.5\lib\idea_rt.jar;C:\Users\newsoft\AppData\Roaming\JetBrains\IntelliJ IDEA 2017.3.5\plugins\junit\lib\junit-rt.jar;C:\Users\newsoft\AppData\Roaming\JetBrains\IntelliJ IDEA 2017.3.5\plugins\junit\lib\junit5-rt.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\junit\platform\junit-platform-launcher\1.5.2\junit-platform-launcher-1.5.2.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\apiguardian\apiguardian-api\1.1.0\apiguardian-api-1.1.0.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\junit\platform\junit-platform-engine\1.5.2\junit-platform-engine-1.5.2.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\opentest4j\opentest4j\1.2.0\opentest4j-1.2.0.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\junit\platform\junit-platform-commons\1.5.2\junit-platform-commons-1.5.2.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\junit\jupiter\junit-jupiter-engine\5.5.2\junit-jupiter-engine-5.5.2.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\junit\jupiter\junit-jupiter-api\5.5.2\junit-jupiter-api-5.5.2.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\junit\vintage\junit-vintage-engine\5.5.2\junit-vintage-engine-5.5.2.jar;D:\download\lib\mavenTollTransfer\mic-repository\junit\junit\4.12\junit-4.12.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\rt.jar;D:\ideaworkspace\ProjectStudy\mybatis-02\target\classes;D:\download\lib\mavenTollTransfer\mic-repository\org\hamcrest\hamcrest-core\2.1\hamcrest-core-2.1.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\hamcrest\hamcrest\2.1\hamcrest-2.1.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\mybatis\mybatis\3.4.5\mybatis-3.4.5.jar;D:\download\lib\mavenTollTransfer\mic-repository\mysql\mysql-connector-java\5.1.44\mysql-connector-java-5.1.44.jar;D:\download\lib\mavenTollTransfer\mic-repository\cglib\cglib\3.3.0\cglib-3.3.0.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\ow2\asm\asm\7.1\asm-7.1.jar;D:\download\lib\mavenTollTransfer\mic-repository\net\logstash\logback\logstash-logback-encoder\5.3\logstash-logback-encoder-5.3.jar;D:\download\lib\mavenTollTransfer\mic-repository\com\fasterxml\jackson\core\jackson-databind\2.10.3\jackson-databind-2.10.3.jar;D:\download\lib\mavenTollTransfer\mic-repository\com\fasterxml\jackson\core\jackson-annotations\2.10.3\jackson-annotations-2.10.3.jar;D:\download\lib\mavenTollTransfer\mic-repository\com\fasterxml\jackson\core\jackson-core\2.10.3\jackson-core-2.10.3.jar" com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 -junit5 com.mybatis03.test.test03,queryPersonWithInIDs
动态SQL查询结果为:[Person{id=1001, name='zhangsan', age=27, sex=true, address=null}, Person{id=1002, name='chenqi', age=29, sex=true, address=null}, Person{id=1003, name='maliu', age=16, sex=true, address=null}] Process finished with exit code 0