JavaEE_Mybatis_SpringMVC_Mybatis_lesson10__利用foreach标签_拼接SQL串，实现IN(X,Y,Z,...的形式)

目的在mybatis下拼接串 , 

 实现Preparing: SELECT * FROM user WHERE user.id IN ( ? , ? , ? )的形式。

 同理可以实现 SELECT * FROM user WHERE AND(id =1 or id=10 or...)的形式。

重要代码 Mapper.xml

	<!-- 通过OGNL的方式进行获取 -->
	<select id="findUserList" parameterType="cn.itcast.mybatis.po.UserQueryVo"
		resultType="cn.itcast.mybatis.po.UserCustom">
		SELECT * FROM user
		<!-- where 可以自动去掉第一个and -->
		<where>
			<include refid="query_user_where"></include>
			<if test="ids!=null">
				<!--
				 使用foreach遍历传入的ids
				 collection:指定输入对象中的集合属性
				 item:每个便利的对象
				 open:开始遍历拼接的串
				 close:结束遍历拼接的串
				 separator:遍历的两个对象中需要拼接的串
				 -->
				<foreach collection="ids" item="id" open="AND user.id IN (" close=")" separator=",">
					#{id}
				</foreach>
			</if>
		</where>
	</select>

foreach标签中的重要属性：

collection:指定输入对象中的集合属性
item:每个便利的对象
open:开始遍历拼接的串
close:结束遍历拼接的串
separator:遍历的两个对象中需要拼接的串

cn.itcast.mybatis.po.UserQueryVo类的定义

package cn.itcast.mybatis.po;

import java.util.List;

/**
 * 
 * @author szh
 * 
 */
public class UserQueryVo {
	private UserCustom userCustom;
	private List<Integer> ids;

	public List<Integer> getIds() {
		return ids;
	}

	public void setIds(List<Integer> ids) {
		this.ids = ids;
	}

	public UserCustom getUserCustom() {
		return userCustom;
	}

	public void setUserCustom(UserCustom userCustom) {
		this.userCustom = userCustom;
	}

	// 可以包装其他的查询条件，订单，商品
}

相关的测试代码片段，利用JUnit 进行测试

	@Test
	public void testFindUserList() throws Exception {
		SqlSession sqlSession = null;
		try {
			sqlSession = sqlSessionFactory.openSession();
			// 创建UserMapper对象，mybatis 自动生成mapper代理对象
			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

			UserQueryVo userQueryVo = new UserQueryVo();
			UserCustom userCustom = new UserCustom();
			// userCustom.setSex("1");
			// userCustom.setUsername("小");
			userQueryVo.setUserCustom(userCustom);
			List<Integer> ids = new ArrayList<>();
			ids.add(1);
			ids.add(22);
			ids.add(24);
			userQueryVo.setIds(ids);
			// userQueryVo
			List<UserCustom> list = userMapper.findUserList(userQueryVo);
			System.out.println(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlSession.close();
		}
	}

输出：

完整的测试单元

package cn.itcast.mybatis.mapper;

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.itcast.mybatis.po.User;
import cn.itcast.mybatis.po.UserCustom;
import cn.itcast.mybatis.po.UserQueryVo;

public class UserMapperTest {

	private SqlSessionFactory sqlSessionFactory;

	// 此方法是在执行testFindUserById之前执行
	@Before
	public void setUp() throws Exception {
		String resource = null; // mybatis全局配置文件
		InputStream inputStream = null; // 输入流
		try {
			// mybatis配置文件
			resource = "SqlMapConfig.xml";
			inputStream = Resources.getResourceAsStream(resource);

			// 创建会话工厂，传入mybatis配置文件信息
			// 创建sqlSessionFactory
			this.sqlSessionFactory = new SqlSessionFactoryBuilder()
					.build(inputStream);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Test
	public void testFindUserById() throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		User user = userMapper.findUserById(1);
		System.out.println(user);
		sqlSession.close();
	}

	@Test
	public void testFindUserByIdResultMap() throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		User user = userMapper.findUserByIdResultMap(1);
		System.out.println(user);
		sqlSession.close();
	}

	@Test
	public void testFindUserByName() throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		// 创建UserMapper对象，mybatis自动生成mapper代理对象
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		List<User> userList = userMapper.findUserByName("小");
		System.out.println(userList);
		sqlSession.close();
	}

	@Test
	public void testFindUserList() throws Exception {
		SqlSession sqlSession = null;
		try {
			sqlSession = sqlSessionFactory.openSession();
			// 创建UserMapper对象，mybatis 自动生成mapper代理对象
			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

			UserQueryVo userQueryVo = new UserQueryVo();
			UserCustom userCustom = new UserCustom();
			// userCustom.setSex("1");
			// userCustom.setUsername("小");
			userQueryVo.setUserCustom(userCustom);
			List<Integer> ids = new ArrayList<>();
			ids.add(1);
			ids.add(22);
			ids.add(24);
			userQueryVo.setIds(ids);
			// userQueryVo
			List<UserCustom> list = userMapper.findUserList(userQueryVo);
			System.out.println(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlSession.close();
		}
	}

	@Test
	public void testFindUserCount() throws Exception {
		SqlSession sqlSession = null;
		try {
			sqlSession = sqlSessionFactory.openSession();
			// 创建UserMapper对象，mybatis 自动生成mapper代理对象
			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

			UserQueryVo userQueryVo = new UserQueryVo();
			UserCustom userCustom = new UserCustom();
			userCustom.setSex("1");
			userCustom.setUsername("小");
			userQueryVo.setUserCustom(userCustom);

			Integer count = userMapper.findUserCount(userQueryVo);
			System.out.println(count);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlSession.close();
		}
	}

	@Test
	public void testInsertUser() throws Exception {
		SqlSession sqlSession = null;
		try {
			sqlSession = sqlSessionFactory.openSession();
			// 创建UserMapper对象，mybatis自动生成mapper代理对象
			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
			User user = new User();
			user.setUsername("hahak");
			userMapper.insertUser(user);

			sqlSession.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlSession.close();
		}
	}

	@Test
	public void testDeleteUser() throws Exception {
		SqlSession sqlSession = null;
		try {
			sqlSession = sqlSessionFactory.openSession();
			// 创建UserMapper对象，mybatis 自动生成mapper代理对象
			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
			userMapper.deleteUser(36);
			sqlSession.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlSession.close();
		}
	}

}