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

288 篇文章 0 订阅
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
    评论
要将List<UserInfo>转换成Map<Long,List<UserInfo>>,可以使用foreach标签来实现。首先,在mapper层的SysUserMapper.java文件中,可以定义一个方法来实现这个转换操作。方法的参数是List<UserInfo> userList,表示要转换的列表。在方法内部,可以使用Java的foreach循环遍历列表,将每个UserInfo对象的id作为键,将对象本身添加到对应的值列表中。最后,将键值对添加到一个Map<Long,List<UserInfo>>对象中,并将其返回。 下面是一种可能的实现方式: ```java public Map<Long, List<UserInfo>> convertListToMap(List<UserInfo> userList) { Map<Long, List<UserInfo>> resultMap = new HashMap<>(); for (UserInfo userInfo : userList) { Long id = userInfo.getId(); if (resultMap.containsKey(id)) { resultMap.get(id).add(userInfo); } else { List<UserInfo> list = new ArrayList<>(); list.add(userInfo); resultMap.put(id, list); } } return resultMap; } ``` 这样,当调用该方法并传入List<UserInfo>对象时,将返回一个Map<Long,List<UserInfo>>对象,其中每个键是UserInfo对象的id,对应的值是具有相同id的UserInfo对象列表。 请注意,以上只是一种可能的实现方式,具体的实现方式可能因具体需求而有所不同。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *2* *3* [Mybatis/Ibatis框架--动态SQLforeach标签与List<T>、数组参数案例](https://blog.csdn.net/dear_Alice_moon/article/details/87534656)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 100%"] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值