Mybatis/Ibatis框架--动态SQL， foreach标签与List<T>、数组参数案例

最新推荐文章于 2023-05-22 11:44:50 发布

_明月

最新推荐文章于 2023-05-22 11:44:50 发布

阅读量2.8k

点赞数 1

分类专栏： IT之旅 Java求学路 Mybatis框架 IBatis框架

本文链接：https://blog.csdn.net/dear_Alice_moon/article/details/87534656

版权

IT之旅同时被 3 个专栏收录

512 篇文章 10 订阅

订阅专栏

Java求学路

288 篇文章 0 订阅

订阅专栏

Mybatis框架

27 篇文章 0 订阅

订阅专栏

在根据刘增辉老师编写的《Mybatis从入门到精通》一书中， "4.4 foreach用法"一节中，对于foreach标签与List<T>参数、数组参数的案例，我自己扩展了一些，希望能够帮到小伙伴们。

项目目录结构如下图图一所示：

图一

1、pojo层SysUser.java文件代码：

/**
 * 用户表
 * @author moon
 *
 */
public class SysUser {
	
	/**
	 * 用户ID
	 */
	private Long id;
	
	/**
	 * 用户名
	 */
	private String userName;
	
	/**
	 * 密码
	 */
	private String userPassword;
	
	/**
	 * 邮箱
	 */
	private String userEmail;
	
	/**
	 * 简介
	 */
	private String userInfo;
	
	/**
	 * 头像
	 */
	private byte[] headImg;
	
	/**
	 * 创建时间
	 */
	@JSONField(format ="yyyy-MM-dd HH:mm:ss")
	private Date createTime;      // 共7个字段      @moon  2019/01/26  10:28                        
	

    // 此处省略 get、set函数 

}

2、mapper层SysUserMapper.java文件代码：




	/**
	 * foreach标签，List<T>参数，批量插入用户信息
	 * 
	 * @param userList
	 * @return
	 * 
	 * @author moon  2019/02/16  17:35  
	 */
	Integer insertSysUserList(List<SysUser> userList);


	/**
	 * foreach标签， foreach标签实现in集合(或数组)动态查询SysUser信息.
	 * 根据用户id集合查询
	 * 
	 * @param idArray
	 * @return
	 * 
	 * @author moon  2019/02/16  16:01      
	 */
	List<SysUser> selectSysUserByIdArray(Long[] idArray);


	/**
	 * foreach标签， foreach标签实现in集合(或数组)动态查询SysUser信息.
	 * 根据用户id集合查询
	 * 
	 * @param idList
	 * @param userNameList
	 * @return
	 * 
	 * @author moon  2019/02/16  16:05      
	 */
	List<SysUser> selectSysUserByIdListAndUsernameList(@Param(value="idLists") List<Long> idList, @Param(value = "userNameLists") List<String> userNameList);
	
	
	/**
	 * foreach标签， foreach标签实现in集合(或数组)动态查询SysUser信息.
	 * 根据用户id集合查询
	 * 
	 * @param idList
	 * @return
	 * 
	 * @author moon  2019/02/16  15:39      
	 */
	List<SysUser> selectSysUserByIdList(List<Long> idList);

3、对应的SysUserMapper.xml文件代码：



	<!-- foreach标签，List<T>参数，批量插入数据.  @moon  2019/02/16  17:40 -->
	<!-- 
		说明：目前仅Mysql数据库支持在批量插入数据过程中，可批量返回自增主键id值。
		           配置 useGeneratedKeys、keyProperty这2个参数，其目的是批量返回插入信息的自增主键. 
	 -->
	<insert id="insertSysUserList" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
		INSERT INTO sys_user(
			user_name, user_password, user_email,
			user_info, head_img, create_time
		) VALUES
		<foreach collection="list" item="user" separator=",">
			(
				#{user.userName}, #{user.userPassword}, #{user.userEmail},
				#{user.userInfo}, #{user.headImg, jdbcType=BLOB},
				#{user.createTime, jdbcType=TIMESTAMP}
			)
		</foreach>
	</insert>


	<!-- foreach标签，Array[]数组，实现IN集合，动态插入数据.  @moon  2019/02/16  16:12 -->
	<select id="selectSysUserByIdArray" resultType="com.moon.mybatis.pojo.SysUser">
		SELECT id,
			user_name userName,
			user_password userPassword,
			user_email userEmail,
			user_info userInfo,
			head_img headImg,
			create_time createTime
		FROM sys_user
		WHERE id IN
		<foreach collection="array" open="(" close=")" separator="," item="id" index="i">
			#{id}
		</foreach>
	</select>
	
	
	<!-- foreach标签，多个List<T>参数，实现IN集合，动态插入数据.  @moon  2019/02/16  16:11 -->
	<select id="selectSysUserByIdListAndUsernameList" resultType="com.moon.mybatis.pojo.SysUser">
		SELECT id,
			user_name userName,
			user_password userPassword,
			user_email userEmail,
			user_info userInfo,
			head_img headImg,
			create_time createTime
		FROM sys_user
		WHERE id IN
		<foreach collection="idLists" open="(" close=")" separator="," item="id" index="i">
			#{id}
		</foreach>
		OR user_name IN
		<foreach collection="userNameLists" open="(" close=")" separator="," item="userName" index="i">
			#{userName}
		</foreach>
	</select>
	
	
	<!-- foreach标签，单个List<T>参数，实现IN集合，动态插入数据.  @moon  2019/02/16  15:47 -->
	<select id="selectSysUserByIdList" resultType="com.moon.mybatis.pojo.SysUser">
		SELECT id,
			user_name userName,
			user_password userPassword,
			user_email userEmail,
			user_info userInfo,
			head_img headImg,
			create_time createTime
		FROM sys_user
		WHERE id IN
		<foreach collection="list" open="(" close=")" separator="," item="id" index="i">
			#{id}
		</foreach>
	</select>

在这里，需要说明的是：单参数List<T>集合，foreach标签中collection属性的默认值是list。当传递的是多参数List<T>集合时，传递的多参数List<T>集合各个参数需要使用@Param注解修饰，创建“指定名”。foreach标签中collection属性的值即为@Param注解中的指定名。单参数Array[]数组，foreach标签中collection属性的默认值是array。当传递的是多参数 Array[]数组时，处理方式与多参数List<T>集合相同。

4、test层BaseMapperTest.java文件代码：


package com.moon.mybatis.test;


import java.io.IOException;
import java.io.Reader;

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.BeforeClass;

/**
 * 基础测试类
 * 
 * @author moon  2019/02/12  16:45      
 */
public class BaseMapperTest {
	
	private static SqlSessionFactory sqlSessionFactory;
	
	@BeforeClass
	public static void init() {
		
		try {
			// Reader reader = Resources.getResourceAsReader("/src/main/resources/mybatis-config.xml");
			Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
			
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
			reader.close();
		} catch (IOException ignore) {
			ignore.printStackTrace();
		}
		
	}
	
	
	public SqlSession getSqlSession() {
		return sqlSessionFactory.openSession();
	}
	
	
}

UserMapperTest.java文件测试代码如下：




package com.moon.mybatis.test;

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

import org.apache.ibatis.session.SqlSession;
import org.junit.Assert;
import org.junit.Test;

import com.moon.mybatis.mapper.SysUserMapper;
import com.moon.mybatis.pojo.SysUser;


/**
 * 测试类，集中进行验证Mybatis的动态SQL功能。
 * 
 * @author moon  2019/02/15  10:39         
 *
 */
public class UserMapperTest extends BaseMapperTest {



		/**
	 * foreach标签，List<T>集合参数，实现批量插入用户信息.  
	 * 
	 * @author moon  2019/02/16  18:29  
	 */
	@Test
	public void testInsertSysUserByUser() {
		
		// 创建sqlSession
		SqlSession sqlSession = getSqlSession();
		
		try {
			
			SysUserMapper userMapper = sqlSession.getMapper(SysUserMapper.class);
			
			System.out.println("------------------------------------ foreach标签，单参数List<T>集合批量插入用户信息测试 ------------------------------------");
			// 创建一个List<SysUser> 对象 
			List<SysUser> userList = new ArrayList<SysUser>();
			for (int i = 0; i < 3; i++) {
				SysUser sysUser = new SysUser();
				sysUser.setUserName("test" + i);
				sysUser.setUserPassword("123456");
				sysUser.setUserEmail("test@mybatis.tk");
				userList.add(sysUser);
				
			}
			
			// 将新建的对象批量插入数据库中
			// 特别注意，这里的返回值 result 是执行SQL影响的行数
			Integer result = userMapper.insertSysUserList(userList);
			System.out.println("userList值为： " + userList.toString());
			Assert.assertEquals(new Integer(3), result);
			
			
			
		} finally {
			
			// 提交事务
			// sqlSession.commit();
			// 对事务进行回滚
			sqlSession.rollback();
			
			// 关闭sqlSession
			sqlSession.close();
		}
		
	}



	/**
	 * foreach标签，List<T>集合、 Map<K,V>集合、Array[]数组 参数，测试动态SQL查询功能.
	 * 
	 * @author moon  2019/02/16  16:35  
	 */
	@Test
	public void testSelectSysUserByParams() {
		
		// 创建sqlSession
		SqlSession sqlSession = getSqlSession();
		
		try {
			
			SysUserMapper userMapper = sqlSession.getMapper(SysUserMapper.class);
			
			
			System.out.println("--------------------------- foreach标签，单参数List<T>集合测试 ---------------------------");
			// 创建List<T> idList集合
			List<Long> idList = new ArrayList<Long>();
			idList.add(1L);
			idList.add(1001L);
			idList.add(1004L);
			List<SysUser> sysUserList = userMapper.selectSysUserByIdList(idList);
			System.out.println("sysUserList值为： " + sysUserList.toString());
			
			
			// 创建List<T> userNameList集合
			List<String> userNameList = new ArrayList<String>();
			userNameList.add("李四");
			
			System.out.println("--------------------------- foreach标签，多参数List<T>集合测试 ---------------------------");
			List<SysUser> sysUserList2 = userMapper.selectSysUserByIdListAndUsernameList(idList, userNameList);
			System.out.println("sysUserList2数量为： " + sysUserList2.size());
			System.out.println("sysUserList2值为： " + sysUserList2.toString());
			
			
			
			System.out.println("--------------------------- foreach标签，单参数Array[]数组测试 ---------------------------");
			// 创建 Array[] idArray数组
			Long[] idArray = {1L, 1001L, 1006L};
			List<SysUser> sysUserList3 = userMapper.selectSysUserByIdArray(idArray);
			System.out.println("sysUserList3值为： " + sysUserList3.toString());
			
			
			
		} finally {
			
			// 提交事务
			// sqlSession.commit();
			// 回滚事务
			sqlSession.rollback();
			
			// 关闭 sqlSession
			sqlSession.close();
		}
		
	}


}

5、testSelectSysUserByParams(...)函数运行的SQL日志如下所示，SQL日志是非常有必要仔细观看的，特别是在程序运行错误的时候。



--------------------------- foreach标签，单参数List<T>集合测试 ---------------------------
DEBUG [main] - ==>  Preparing: SELECT id, user_name userName, user_password userPassword, user_email userEmail, user_info userInfo, head_img headImg, create_time createTime FROM sys_user WHERE id IN ( ? , ? , ? ) 
       DEBUG [main] - ==> Parameters: 1(Long), 1001(Long), 1004(Long)
       TRACE [main] - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
       TRACE [main] - <==        Row: 1, admin, 123456, admin@mybatis.tk, <<BLOB>>, <<BLOB>>, 2016-04-01 17:00:58.0
       TRACE [main] - <==        Row: 1001, test, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2016-04-01 17:00:58.0
       TRACE [main] - <==        Row: 1004, test01, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2019-01-28 15:39:16.0
       DEBUG [main] - <==      Total: 3
       sysUserList值为： [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@mybatis.tk, userInfo=管理员, headImg=null, createTime=Fri Apr 01 17:00:58 CST 2016], SysUser [id=1001, userName=test, userPassword=123456, userEmail=test@mybatis.tk, userInfo=测试用户, headImg=null, createTime=Fri Apr 01 17:00:58 CST 2016], SysUser [id=1004, userName=test01, userPassword=123456, userEmail=test@mybatis.tk, userInfo=test info, headImg=[1, 2, 3, 4], createTime=Mon Jan 28 15:39:16 CST 2019]]
--------------------------- foreach标签，多参数List<T>集合测试 ---------------------------
DEBUG [main] - ==>  Preparing: SELECT id, user_name userName, user_password userPassword, user_email userEmail, user_info userInfo, head_img headImg, create_time createTime FROM sys_user WHERE id IN ( ? , ? , ? ) OR user_name IN ( ? ) 
       DEBUG [main] - ==> Parameters: 1(Long), 1001(Long), 1004(Long), 李四(String)
       TRACE [main] - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
       TRACE [main] - <==        Row: 1, admin, 123456, admin@mybatis.tk, <<BLOB>>, <<BLOB>>, 2016-04-01 17:00:58.0
       TRACE [main] - <==        Row: 1001, test, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2016-04-01 17:00:58.0
       TRACE [main] - <==        Row: 1004, test01, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2019-01-28 15:39:16.0
       TRACE [main] - <==        Row: 1006, 李四, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2019-02-15 17:40:41.0
       DEBUG [main] - <==      Total: 4
       sysUserList2数量为： 4
sysUserList2值为： [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@mybatis.tk, userInfo=管理员, headImg=null, createTime=Fri Apr 01 17:00:58 CST 2016], SysUser [id=1001, userName=test, userPassword=123456, userEmail=test@mybatis.tk, userInfo=测试用户, headImg=null, createTime=Fri Apr 01 17:00:58 CST 2016], SysUser [id=1004, userName=test01, userPassword=123456, userEmail=test@mybatis.tk, userInfo=test info, headImg=[1, 2, 3, 4], createTime=Mon Jan 28 15:39:16 CST 2019], SysUser [id=1006, userName=李四, userPassword=123456, userEmail=test@mybatis.tk, userInfo=普通用户, headImg=[1, 2, 3], createTime=Fri Feb 15 17:40:41 CST 2019]]
--------------------------- foreach标签，单参数Array[]数组测试 ---------------------------
DEBUG [main] - ==>  Preparing: SELECT id, user_name userName, user_password userPassword, user_email userEmail, user_info userInfo, head_img headImg, create_time createTime FROM sys_user WHERE id IN ( ? , ? , ? ) 
       DEBUG [main] - ==> Parameters: 1(Long), 1001(Long), 1006(Long)
       TRACE [main] - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
       TRACE [main] - <==        Row: 1, admin, 123456, admin@mybatis.tk, <<BLOB>>, <<BLOB>>, 2016-04-01 17:00:58.0
       TRACE [main] - <==        Row: 1001, test, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2016-04-01 17:00:58.0
       TRACE [main] - <==        Row: 1006, 李四, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2019-02-15 17:40:41.0
       DEBUG [main] - <==      Total: 3
       sysUserList3值为： [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@mybatis.tk, userInfo=管理员, headImg=null, createTime=Fri Apr 01 17:00:58 CST 2016], SysUser [id=1001, userName=test, userPassword=123456, userEmail=test@mybatis.tk, userInfo=测试用户, headImg=null, createTime=Fri Apr 01 17:00:58 CST 2016], SysUser [id=1006, userName=李四, userPassword=123456, userEmail=test@mybatis.tk, userInfo=普通用户, headImg=[1, 2, 3], createTime=Fri Feb 15 17:40:41 CST 2019]]

testInsertSysUserByUser(...)函数运行的SQL日志如下所示，SQL日志是非常有必要仔细观看的，特别是在程序运行错误的时候。



------------------------------------ foreach标签，单参数List<T>集合批量插入用户信息测试 ------------------------------------
DEBUG [main] - ==>  Preparing: INSERT INTO sys_user( user_name, user_password, user_email, user_info, head_img, create_time ) VALUES ( ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ? ) 
       DEBUG [main] - ==> Parameters: test0(String), 123456(String), test@mybatis.tk(String), null, null, null, test1(String), 123456(String), test@mybatis.tk(String), null, null, null, test2(String), 123456(String), test@mybatis.tk(String), null, null, null
       DEBUG [main] - <==    Updates: 3
       userList值为： [SysUser [id=1025, userName=test0, userPassword=123456, userEmail=test@mybatis.tk, userInfo=null, headImg=null, createTime=null], SysUser [id=1026, userName=test1, userPassword=123456, userEmail=test@mybatis.tk, userInfo=null, headImg=null, createTime=null], SysUser [id=1027, userName=test2, userPassword=123456, userEmail=test@mybatis.tk, userInfo=null, headImg=null, createTime=null]]

祝各位小伙伴学习顺利、愉快。 Lucky.....

_明月

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
3
评论
Mybatis/Ibatis框架--动态SQL， foreach标签与List<T>、数组参数案例

在根据刘增辉老师编写的《Mybatis从入门到精通》一书中， "4.4 foreach用法"一节中，对于foreach标签与List&lt;T&gt;参数、数组参数的案例，我自己扩展了一些，希望能够帮到小伙伴们。项目目录结构如下图图一所示： ...
复制链接

扫一扫

专栏目录