测试mybatis里foreach用法-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_39162733/article/details/78664712

1.介绍

链接：http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html

foreach

动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历，通常是在构建 IN 条件语句的时候。比如：

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

foreach 元素的功能是非常强大的，它允许你指定一个集合，声明可以用在元素体内的集合项和索引变量。它也允许你指定开闭匹配的字符串以及在迭代中间放置分隔符。这个元素是很智能的，因此它不会偶然地附加多余的分隔符。

注意你可以将任何可迭代对象（如列表、集合等）和任何的字典或者数组对象传递给foreach作为集合参数。当使用可迭代对象或者数组时，index是当前迭代的次数，item的值是本次迭代获取的元素。当使用字典（或者Map.Entry对象的集合）时，index是键，item是值。

到此我们已经完成了涉及 XML 配置文件和 XML 映射文件的讨论。下一部分将详细探讨 Java API，这样才能从已创建的映射中获取最大利益。

2.用法

List，Array，Map

3.测试例子

参考：https://www.cnblogs.com/fangyu19900812/p/6046209.html

mapper：

package com.example.demo.mapper;

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

import com.example.demo.entity.UserEntity;

public interface UserMapper {
	List<UserEntity> getAll();
	
	void insert(UserEntity user);
	
	List<UserEntity> testForeachOne(List<Integer> ids);
	
	List<UserEntity> testForeachTwo(int[] ids);
	
	List<UserEntity> testForeachThree(Map<String,Object> map);
}

测试类：

package com.example.demo;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.example.demo.entity.UserEntity;
import com.example.demo.mapper.UserMapper;

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestForeach {
	
	@Autowired
	UserMapper mapper;
	
	@Test
	public void testAll() {
		List<UserEntity> list = mapper.getAll();
		for(UserEntity user:list) {
			System.out.print(user.toString());
		}
	}
	
	
	@Test
	public void testForeachOne() throws Exception{
		List ids = new ArrayList();
		ids.add(37);
		ids.add(38);
		ids.add(39);
		List<UserEntity> users = mapper.testForeachOne(ids);
		for(UserEntity user:users) {
			System.out.println(user);
		}
	}
	
	@Test
	public void testForeachTwo() throws Exception{
		int[] ids = new int[] {37,38,39};
		List<UserEntity> users = mapper.testForeachTwo(ids);
		for(UserEntity user:users) {
			System.out.println(user);
		}
	}
	
	@Test
	public void testForeachThree() throws Exception{
		List ids = new ArrayList();
		ids.add(37);
		ids.add(38);
		ids.add(39);
		Map map = new HashMap();
		map.put("ids", ids);
		List<UserEntity> users = mapper.testForeachThree(map);
		for(UserEntity user:users) {
			System.out.println(user);
		}
	}
}

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.example.demo.mapper.UserMapper">
	<resultMap id="BaseResultMap" type="com.example.demo.entity.UserEntity">
		<id column="id" property="id" jdbcType="BIGINT" />
		<result column="userName" property="userName" jdbcType="VARCHAR" />
		<result column="passWord" property="password" jdbcType="VARCHAR" />
		<result column="user_sex" property="userSex"
			javaType="com.example.demo.enums.UserSexEnum" />
		<result column="nick_name" property="nickName" jdbcType="VARCHAR" />
	</resultMap>

	<sql id="Base_Column_List"> 
		id, userName, passWord, user_sex, nick_name
	</sql>

	<select id="getAll" resultMap="BaseResultMap">
		SELECT
		<include refid="Base_Column_List" />
		FROM users
	</select>

	
	<!-- 测试foreach  list -->
	<select id="testForeachOne" parameterType="java.util.List"
		resultMap="BaseResultMap">
		select * from users where id in
		<foreach collection="list" index="index" item="item" open="("
			separator="," close=")">
			#{item}
		</foreach>
	</select>
	
	<!-- 测试foreach  array -->
	<select id="testForeachTwo" parameterType="java.util.List"
		resultMap="BaseResultMap">
		select * from users where id in
		<foreach collection="array" index="index" item="item" open="("
			separator="," close=")">
			#{item}
		</foreach>
	</select>
	
	<!-- 测试foreach  map -->
	<select id="testForeachThree" parameterType="java.util.HashMap"
		resultMap="BaseResultMap">
		select * from users where id in
		<foreach collection="ids" index="index" item="item" open="("
			separator="," close=")">
			#{item}
		</foreach>
	</select>
</mapper>

数据库：

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `userName` varchar(32) DEFAULT NULL COMMENT '用户名',
  `passWord` varchar(32) DEFAULT NULL COMMENT '密码',
  `user_sex` varchar(32) DEFAULT NULL,
  `nick_name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;

注意事项：

问题一：

Caused by: org.apache.ibatis.type.TypeException: Could not resolve type alias 'BaseResultMap'.  Cause: java.lang.ClassNotFoundException: Cannot find class: BaseResultMap
	at org.apache.ibatis.type.TypeAliasRegistry.resolveAlias(TypeAliasRegistry.java:120) ~[mybatis-3.4.0.jar:3.4.0]
	at org.apache.ibatis.builder.BaseBuilder.resolveAlias(BaseBuilder.java:149) ~[mybatis-3.4.0.jar:3.4.0]
	at org.apache.ibatis.builder.BaseBuilder.resolveClass(BaseBuilder.java:116) ~[mybatis-3.4.0.jar:3.4.0]
	... 73 common frames omitted
Caused by: java.lang.ClassNotFoundException: Cannot find class: BaseResultMap
	at org.apache.ibatis.io.ClassLoaderWrapper.classForName(ClassLoaderWrapper.java:200) ~[mybatis-3.4.0.jar:3.4.0]
	at org.apache.ibatis.io.ClassLoaderWrapper.classForName(ClassLoaderWrapper.java:89) ~[mybatis-3.4.0.jar:3.4.0]
	at org.apache.ibatis.io.Resources.classForName(Resources.java:261) ~[mybatis-3.4.0.jar:3.4.0]
	at org.apache.ibatis.type.TypeAliasRegistry.resolveAlias(TypeAliasRegistry.java:116) ~[mybatis-3.4.0.jar:3.4.0]
	... 75 common frames omitted

遇到这种错，注意mapper.xml文件中将 resultType改为 resultMap