mybatis学习--动态sql之foreach标签

川长思鸟来 2022-06-01 06:20 219阅读 0赞

# 一、动态sql之foreach标签 #

向sql传递数组或List，mybatis使用foreach解析，如下：

## 1、通过pojo传递list ##

### （1）需求 ###

传入多个id查询用户信息，用下边两个sql实现：

SELECT \* FROM USERWHERE username LIKE '%张%' AND (id =10 OR id =89 OR id=16)

SELECT \* FROM USERWHERE username LIKE '%张%'  id IN (10,89,16)

### （2）实现 ###

【1】在pojo中定义list属性ids存储多个用户id，并添加getter/setter方法（QueryVo类）

package cn.zhku.jsj.mybatis.pojo;
    
    import java.util.List;
    
    public class QueryVo {
    	
    	private List<Integer> ids;
    	public List<Integer> getIdList() {
    		return ids;
    	}
    	public void setIdList(List<Integer> ids) {
    		this.ids = ids;
    	}
    }

【2】mapper.xml

<select id="queryUserByIdsQueryVo" parameterType="queryVo"
    		resultType="user">
    		select * from user
    		<where>
    			id in
    			<foreach collection="ids" item="item" open="(" close=")"
    				separator=",">
    				#{item}
    			</foreach>
    		</where>
    	</select>

【3】Mapper接口

//查询所有用户，通过id
    	//QueryVo
    	public List<User> queryUserByIdsQueryVo(QueryVo queryVo);

【4】测试代码：

//查询所有id的用户  QueryVo
    	@Test
    	public void testQueryUserByIds(){
    		SqlSession sqlSession = this.sqlSessionFactory.openSession();
    		//创建mapper接口的动态代理对象
    		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    		QueryVo queryVo=new QueryVo();
    		List<Integer> ids=new ArrayList<Integer>();
    		ids.add(1);
    		ids.add(10);
    		ids.add(22);
    		queryVo.setIdList(ids);
    		
    		List<User> list = userMapper.queryUserByIdsQueryVo(queryVo);
    		System.out.println("sqlLabel:--QueryVo");
    		for (User user : list) {
    			System.out.println(user);
    		}
    	}

## 2、传递单个List ##

传递List类型在编写mapper.xml没有区别，唯一不同的是只有一个List参数时它的参数名为list。

如下：

【1】Mapper.xml

<select id="queryUserByIdsList" parameterType="list" resultType="user">
    		select * from user
    		<where>
    			id in
    			<if test="list!=null">
    				<foreach collection="list" item="item" open="(" close=")"
    					separator=",">
    					#{item}
    				</foreach>
    			</if>
    		</where>
    	</select>

【2】Mapper接口

//查询所有用户，通过id
    	//list
    	public List<User> queryUserByIdsList(List<Integer> list);

【3】测试：

//查询所有id的用户--方法二 list
    	@Test
    	public void testQueryUserByIds2(){
    		SqlSession sqlSession = this.sqlSessionFactory.openSession();
    		//创建mapper接口的动态代理对象
    		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    		List<Integer> ids=new ArrayList<Integer>();
    		ids.add(1);
    		ids.add(10);
    		ids.add(22);
    		
    		List<User> list = userMapper.queryUserByIdsList(ids);
    		System.out.println("sqlLabel2:--List");
    		for (User user : list) {
    			System.out.println(user);
    		}
    	}

## 3、传递单个数组（数组中是pojo）： ##

【1】Mapper.xml

<select id="queryUserByPojoArray" parameterType="Object[]"
    		resultType="user">
    		select * from user
    		<where>
    			id in
    			<if test="array!=null">
    				<foreach collection="array" item="item" open="(" close=")"
    					separator=",">
    					#{item.id}
    				</foreach>
    			</if>
    		</where>
    	</select>

如果数组中不是简单类型则写为\#\{item\}，需要通过ognl获取对象属性值。比如此处的 \#\{item.id\}

sql只接收一个数组参数，这时sql解析参数的名称mybatis固定为array，如果数组是通过一个pojo传递到sql则参数的名称为pojo中的属性名。

index：为数组的下标。

item：为数组每个元素的名称，名称随意定义

open：循环开始

close：循环结束

separator：中间分隔输出

【2】Mapper接口：

//array --里面是user类型
    	public List<User> queryUserByPojoArray(Object[] objs);

【3】测试：

//查询所有id的用户--方法三  array --- Object[]
    		@Test
    		public void testQueryUserByIds4(){
    			SqlSession sqlSession = this.sqlSessionFactory.openSession();
    			//创建mapper接口的动态代理对象
    			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    			User user1=new User();
    			user1.setId(1);
    			User user2=new User();
    			user2.setId(10);
    			User user3=new User();
    			user3.setId(22);
    			Object[] objs=new Object[]{user1,user2,user3};
    			
    			List<User> list = userMapper.queryUserByPojoArray(objs);
    			System.out.println("sqlLabel4---PojoArray:");
    			for (User user : list) {
    				System.out.println(user);
    			}
    		}

## 4、传递单个数组（数组中是Integer类型）： ##

【1】Mapper.xml

<select id="queryUserByArray" parameterType="Integer[]"
    		resultType="user">
    		select * from user
    		<where>
    			id in
    			<if test="array!=null">
    				<foreach collection="array" item="item" open="(" close=")"
    					separator=",">
    					#{item}
    				</foreach>
    			</if>
    		</where>
    	</select>

如果数组中是简单类型则写为\#\{item\}，不用再通过ognl获取对象属性值了。

【2】Mapper接口：

//查询所有用户，通过id
    	//array --里面是Integer类型
    	public List<User> queryUserByArray(Integer[] ids);

【3】测试：

//查询所有id的用户--方法三  array --- Integer[]
    	@Test
    	public void testQueryUserByIds3(){
    		SqlSession sqlSession = this.sqlSessionFactory.openSession();
    		//创建mapper接口的动态代理对象
    		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    		Integer[] ids=new Integer[]{1,10,22};
    		List<User> list = userMapper.queryUserByArray(ids);
    		System.out.println("sqlLabel3:--Array");
    		for (User user : list) {
    			System.out.println(user);
    		}
    	}

## 5、总结 ##

select语句

<where>

idin

<foreach collection="xxx" item="xxx"open="(" close=")"

separator=",">

\#\{xxx\}

</foreach>

</where>

</select>

重点区分五个部分的值

select中的parameterType以及resultType

foreach中的collection以及item，以及每一个item的子项目里面的值

# 二、项目组成及完整代码 #

## 1、项目组成 ##

![20180122202859965][]

## 2、完整代码 ##

### 【1】User ###

package cn.zhku.jsj.mybatis_spring.pojo;
    
    import java.io.Serializable;
    import java.util.Date;
    
    public class User implements Serializable {
    	private static final long serialVersionUID = 1L;
    	private Integer id;
    	private String username;// 用户姓名
    	private String sex;// 性别
    	private Date birthday;// 生日
    	private String address;// 地址
    
    	public Integer getId() {
    		return id;
    	}
    	public void setId(Integer id) {
    		this.id = id;
    	}
    	public String getUsername() {
    		return username;
    	}
    	public void setUsername(String username) {
    		this.username = username;
    	}
    	public String getSex() {
    		return sex;
    	}
    	public void setSex(String sex) {
    		this.sex = sex;
    	}
    	public Date getBirthday() {
    		return birthday;
    	}
    	public void setBirthday(Date birthday) {
    		this.birthday = birthday;
    	}
    	public String getAddress() {
    		return address;
    	}
    	public void setAddress(String address) {
    		this.address = address;
    	}
    	@Override
    	public String toString() {
    		return "User [id=" + id + ", username=" + username + ", sex=" + sex
    				+ ", birthday=" + birthday + ", address=" + address + "]";
    	}
    
    	
    	
    
    }

### 【2】QueryVo（pojo封装类） ###

### 【3】UserMapper（Mapper接口） ###

package cn.zhku.jsj.mybatis.mapper;
    
    import java.util.List;
    
    import cn.zhku.jsj.mybatis.pojo.QueryVo;
    import cn.zhku.jsj.mybatis.pojo.User;
    
    /*
     * mapper动态代理开发的四大原则
     * 1.mapper.xml文件中的namespace与mapper接口的类路径相同
     * 2.mapper接口方法名和mapper.xml中定义的每个statement中的id相同
     * 3.mapper接口方法的输入参数类型和mapper.xml中定义的每个parameterType的类型相同
     * 4.mapper接口方法的输出 参数类型和mapper.xml中定义的每个resultType的类型相同
     */
    public interface UserMapper {
    
    	
    	//查询所有用户，通过id
    	//QueryVo
    	public List<User> queryUserByIdsQueryVo(QueryVo queryVo);
    	
    	//查询所有用户，通过id
    	//list
    	public List<User> queryUserByIdsList(List<Integer> list);
    	
    	//查询所有用户，通过id
    	//array --里面是Integer类型
    	public List<User> queryUserByArray(Integer[] ids);
    	
    	//array --里面是user类型
    	public List<User> queryUserByPojoArray(Object[] objs);
    }

### 【4】UserMapper.xml（mybatis映射配置文件） ###

<?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="cn.zhku.jsj.mybatis.mapper.UserMapper">
    
    
    	
    	<select id="queryUserByIdsQueryVo" parameterType="queryVo"
    		resultType="user">
    		select * from user
    		<where>
    			id in
    			<foreach collection="ids" item="item" open="(" close=")"
    				separator=",">
    				#{item}
    			</foreach>
    		</where>
    	</select>
    	
    	
    	<select id="queryUserByIdsList" parameterType="list" resultType="user">
    		select * from user
    		<where>
    			id in
    			<if test="list!=null">
    				<foreach collection="list" item="item" open="(" close=")"
    					separator=",">
    					#{item}
    				</foreach>
    			</if>
    		</where>
    	</select>
    
    
    	
    	<select id="queryUserByArray" parameterType="Integer[]"
    		resultType="user">
    		select * from user
    		<where>
    			id in
    			<if test="array!=null">
    				<foreach collection="array" item="item" open="(" close=")"
    					separator=",">
    					#{item}
    				</foreach>
    			</if>
    		</where>
    	</select>
    
    	
    	<select id="queryUserByPojoArray" parameterType="Object[]"
    		resultType="user">
    		select * from user
    		<where>
    			id in
    			<if test="array!=null">
    				<foreach collection="array" item="item" open="(" close=")"
    					separator=",">
    					#{item.id}
    				</foreach>
    			</if>
    		</where>
    	</select>
    
    
    </mapper>

### 【5】SqlLabelTest（测试类） ###

package cn.zhku.jsj.mybatis.test;
    
    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 org.junit.Before;
    import org.junit.Test;
    
    import cn.zhku.jsj.mybatis.mapper.UserMapper;
    import cn.zhku.jsj.mybatis.pojo.QueryVo;
    import cn.zhku.jsj.mybatis.pojo.User;
    
    public class SqlLabelTest {
    
    	private SqlSessionFactory sqlSessionFactory;
    	@Before
    	public void init() throws IOException {
    		SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    		InputStream inputStream = Resources
    				.getResourceAsStream("SqlMapConfig.xml");
    		sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
    	}
    	
    	//查询所有id的用户  QueryVo
    	@Test
    	public void testQueryUserByIds(){
    		SqlSession sqlSession = this.sqlSessionFactory.openSession();
    		//创建mapper接口的动态代理对象
    		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    		QueryVo queryVo=new QueryVo();
    		List<Integer> ids=new ArrayList<Integer>();
    		ids.add(1);
    		ids.add(10);
    		ids.add(22);
    		queryVo.setIdList(ids);
    		
    		List<User> list = userMapper.queryUserByIdsQueryVo(queryVo);
    		System.out.println("sqlLabel:--QueryVo");
    		for (User user : list) {
    			System.out.println(user);
    		}
    	}
    	
    	//查询所有id的用户--方法二 list
    	@Test
    	public void testQueryUserByIds2(){
    		SqlSession sqlSession = this.sqlSessionFactory.openSession();
    		//创建mapper接口的动态代理对象
    		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    		List<Integer> ids=new ArrayList<Integer>();
    		ids.add(1);
    		ids.add(10);
    		ids.add(22);
    		
    		List<User> list = userMapper.queryUserByIdsList(ids);
    		System.out.println("sqlLabel2:--List");
    		for (User user : list) {
    			System.out.println(user);
    		}
    	}
    	
    	//查询所有id的用户--方法三  array --- Integer[]
    	@Test
    	public void testQueryUserByIds3(){
    		SqlSession sqlSession = this.sqlSessionFactory.openSession();
    		//创建mapper接口的动态代理对象
    		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    		Integer[] ids=new Integer[]{1,10,22};
    		List<User> list = userMapper.queryUserByArray(ids);
    		System.out.println("sqlLabel3:--Array");
    		for (User user : list) {
    			System.out.println(user);
    		}
    	}
    	
    		//查询所有id的用户--方法三  array --- Object[]
    		@Test
    		public void testQueryUserByIds4(){
    			SqlSession sqlSession = this.sqlSessionFactory.openSession();
    			//创建mapper接口的动态代理对象
    			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    			User user1=new User();
    			user1.setId(1);
    			User user2=new User();
    			user2.setId(10);
    			User user3=new User();
    			user3.setId(22);
    			Object[] objs=new Object[]{user1,user2,user3};
    			
    			List<User> list = userMapper.queryUserByPojoArray(objs);
    			System.out.println("sqlLabel4---PojoArray:");
    			for (User user : list) {
    				System.out.println(user);
    			}
    		}
    	
    }

### 【6】db.properties（数据库信息配置文件） ###

jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8
    jdbc.username=root
    jdbc.password=1234

### 【7】log4j.properties（日志配置） ###

# Global logging configuration
    log4j.rootLogger=DEBUG, stdout
    # Console output...
    log4j.appender.stdout=org.apache.log4j.ConsoleAppender
    log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
    log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

### 【8】SqlMapConfig.xml（mybatis核心配置文件） ###

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration 
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
    	
    
    	
    	
    
    	<properties resource="db.properies">
    		<property name="jdbc.driver" value="com.mysql.jdbc.Driver" />
    		<property name="jdbc.url"
    			value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8" />
    	</properties>
    
    	
    	<typeAliases>
    		
    		<typeAlias type="cn.zhku.jsj.mybatis.pojo.User" alias="user" />
    
    		
    		<package name="cn.zhku.jsj.mybatis.pojo" />
    
    	</typeAliases>
    
    	<environments default="development">
    		<environment id="development">
    			
    			<transactionManager type="JDBC" />
    			
    			<dataSource type="POOLED">
    				<property name="driver" value="${jdbc.driver}" />
    				<property name="url" value="${jdbc.url}" />
    				<property name="username" value="${jdbc.username}" />
    				<property name="password" value="${jdbc.password}" />
    			</dataSource>
    
    			
    			
    			
    			
    			
    			
    
    		</environment>
    	</environments>
    	
    	<mappers>
    
    		
    		
    
    
    		
    		
    		<package name="cn.zhku.jsj.mybatis.mapper" />
    	</mappers>
    </configuration>

### 【9】jar包 ###

![20180122200655190][]

### 【10】数据库数据 ###

新建一个mybatis的数据库，然后里面建立了一个user表

CREATE DATABASE  IF NOT EXISTS \`mybatis\`;  
USE \`mybatis\`;  
  
  
DROP TABLE IF EXISTS \`user\`;  
CREATE TABLE \`user\` (  
  \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
  \`username\` varchar(32) NOT NULL COMMENT '用户名称',  
  \`birthday\` date DEFAULT NULL COMMENT '生日',  
  \`sex\` char(1) DEFAULT NULL COMMENT '性别',  
  \`address\` varchar(256) DEFAULT NULL COMMENT '地址',  
  PRIMARY KEY (\`id\`)  
) ENGINE=InnoDB AUTO\_INCREMENT=34 DEFAULT CHARSET=utf8;  
  
  
INSERT INTO \`user\` VALUES (1,'张11','2014-07-10','2','广东广州'),  
(10,'张三','2014-07-10','1','北京市'),  
(22,'陈小明','2014-07-10','1','河南郑州'),  
(24,'张三丰','2014-07-10','1','河南郑州'),  
(26,'王五','2014-07-10','1','河南郑州'),  
(27,'你好','2018-01-21',NULL,NULL),  
(33,'12342','2018-01-22','1','beijing');

INSERT INTO \`user\` VALUES ('26', '王五', null, null, null);

![20180122200711165][]

[20180122202859965]: /images/20220601/1c4c2de7b3a7400daa2a14b4a81c7641.png
[20180122200655190]: /images/20220601/431bbec7a6b547ad8d189100e1b8d1ba.png
[20180122200711165]: /images/20220601/a81b3a9198c24c20b197938d946ccae4.png