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

一、动态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

	<!-- QueryVo pojo中进行List<Integer>封装 通过List<Integer> ids查询用户 select * from 
		user where id in (1,2,3) -->
	<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

<!-- List直接进行封装 通过List<Integer> ids查询用户 select * from user where id in (1,2,3) -->
	<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

<!-- Array数组直接进行封装,数组是Object[] objs:里面都是pojo类型的User 通过Object[] objs查询用户 
		select * from user where id in (1,2,3) -->
	<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

<!-- Array数组直接进行封装,数组是Integer[] ids:里面都是Integer类型的 通过Integer[] ids查询用户 select 
		* from user where id in (1,2,3) -->
	<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 id="xxx" parameterType="xxx"resultType="xxx">

              select语句

              <where>

                     idin

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

                            separator=",">

                            #{xxx}  

                     </foreach>

              </where>

       </select>

重点区分五个部分的值

select中的parameterType以及resultType

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

 

二、项目组成及完整代码

1、项目组成

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封装类）

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;
	}
}

【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">

<!-- namespace：命名空间，用于隔离sql -->
<!-- 还有一个很重要的作用，使用动态代理开发DAO -->
<!-- 1. namespace必须和Mapper接口类路径一致 -->
<!-- 2. id必须和Mapper接口方法名一致 -->
<!-- 3. parameterType必须和接口方法参数类型一致 -->
<!-- 4. resultType必须和接口方法返回值类型一致 -->

<!-- mapper接口类路径 -->
<mapper namespace="cn.zhku.jsj.mybatis.mapper.UserMapper">


	<!-- QueryVo pojo中进行List<Integer>封装 通过List<Integer> ids查询用户 select * from 
		user where id in (1,2,3) -->
	<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>
	
	<!-- List直接进行封装 通过List<Integer> ids查询用户 select * from user where id in (1,2,3) -->
	<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>


	<!-- Array数组直接进行封装,数组是Integer[] ids:里面都是Integer类型的 通过Integer[] ids查询用户 select 
		* from user where id in (1,2,3) -->
	<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>

	<!-- Array数组直接进行封装,数组是Object[] objs:里面都是pojo类型的User 通过Object[] objs查询用户 
		select * from user where id in (1,2,3) -->
	<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>
	<!-- 是用resource属性加载外部配置文件 -->

	<!-- 在 properties 元素体内定义的属性首先被读取。 -->
	<!--  然后会读取properties 元素中resource或 url 加载的属性，它会覆盖已读取的同名属性。 -->

	<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">
			<!--使用jdbc事务管理 -->
			<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>

			<!-- <dataSource type="POOLED"> -->
			<!-- <property name="driver" value="com.mysql.jdbc.Driver"/> -->
			<!-- <property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8"/> -->
			<!-- <property name="username" value="root"/> -->
			<!-- <property name="password" value="1234"/> -->
			<!-- </dataSource> -->

		</environment>
	</environments>
	<!-- 引入映射配置文件 -->
	<mappers>
<!-- 		<mapper resource="cn/zhku/jsj/mybatis/mapper/UserMapper.xml" /> -->
		<!-- 此种方法要求mapper接口名称和mapper映射文件名称相同，且放在同一个目录中。 -->
		<!-- <mapper class="cn.zhku.jsj.mybatis.mapper.UserMapper"/> -->


		<!-- 推荐使用以下这样方法 -->
		<!-- 此种方法要求mapper接口名称和mapper映射文件名称相同，且放在同一个目录中。 -->
		<package name="cn.zhku.jsj.mybatis.mapper" />
	</mappers>
</configuration>

【9】jar包

【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);