Mybatis uses foreach to traverse list sets or array arrays

Time:2021-10-13

1、 Preparatory work

1. Db.properties file (remember to modify your database, user name and password)


dataSource.driver=com.mysql.jdbc.Driver
dataSource.url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf8
dataSource.username=blog
dataSource.password=blog

2. Master profile

<?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>
<!--   Import external profile -- >
	<properties resource="db.properties"></properties>
	<!--  Alias settings. When not set, the reference should use the full package name. After setting, you can use a user-defined alias, which is more concise -- >
	<typeAliases>
		<!--  There are two kinds of alias settings. One is to set one, and the other is to set a package. The default alias is the class name (case is OK, lowercase is recommended) - >
		<!--  First setting 
	 	<typeAlias type="com.mybatis_demo.domain.User" alias="user"/>-->
	 	<!--  The second setting is to set the alias of the classes under the whole package. The second one is recommended -- >
	 	<package name="com.mybatis_demo.domain"/>
	 </typeAliases>
<!--  Environment mode: development mode work mode -- >
  <environments default="development">
  <!--  Environment variable -- >
    <environment>
    <!--  Transaction management using JDBC -- >
      <transactionManager type="JDBC"/>
      <!--  Use connection pool -- >
      <dataSource type="POOLED">
        <property name="driver" value="${dataSource.driver}"/>
        <property name="url" value="${dataSource.url}"/>
        <property name="username" value="${dataSource.username}"/>
        <property name="password" value="${dataSource.password}"/>
      </dataSource>
    </environment>
  </environments>
  <!--  Import mapper mapping file -- >
  <mappers>
  <!--   1. Relative path introduction -- >
   <!--  <mapper resource="mapper/UserMapper.xml"/> -->
    <!--  2. Absolute path introduction -- >
    <!-- <mapper url="file:\\\D:\sts-bundle\workplace\mybatis_demo\src\main\resources\mapper\UserMapper.xml"/> -->
    <!--  3. To import the full package name of the corresponding mapper interface, the corresponding mapper.xml must be in the same package as the interface mapper, and the XML file name must be the same as the interface name. The namespace in the XML file must be the full package name of the corresponding interface -- >
    <!-- <mapper/> -->
    <!--  4. Package import requires the same as interface import -- >
   <!--  <mapper resource="mapper/UserMapper2.xml"/> -->
    <package name="com.mybatis_demo.mapper"/>
  </mappers>
</configuration>

3. Create user class and wrapper class uservo

User.java


package com.mybatis_demo.domain;
public class User {
	private Integer uid;
	private String uname;
	private Integer age;
	private String address;
	public Integer getUid() {
		return uid;
	}
	public void setUid(Integer uid) {
		this.uid = uid;
	}
	public String getUname() {
		return uname;
	}
	public void setUname(String uname) {
		this.uname = uname;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	@Override
	public String toString() {
		return "User [uid=" + uid + ", uname=" + uname + ", age=" + age + ", address=" + address + "]";
	}
}

UserVo.java


package com.mybatis_demo.domain;
import java.util.List;
public class UserVo extends User {
	private Integer[] ids;
	private List<Integer> idList;
	public Integer[] getIds() {
		return ids;
	}
	public void setIds(Integer[] ids) {
		this.ids = ids;
	}
	public List<Integer> getIdList() {
		return idList;
	}
	public void setIdList(List<Integer> idList) {
		this.idList = idList;
	}
	
}

2、 Traverse the mapping files and corresponding interfaces of arrays and collections

1. Mapper mapping file

<?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.mybatis_demo.mapper.UserMapper">
<!--  Traverse the list set, collection = "list". If you pass the list set directly when passing parameters, you can only fill in the list, not the parameter name -- >
<select resultType="User">
	select * from t_user where uid in
	<foreach collection="list" item="item" open="(" separator="," close=")">
		#{item}
	</foreach>
</select>
<!--  Traverse the array, collection = "array". If you pass the array directly when passing parameters, you can only fill in array, not parameter name -- >
<select resultType="User">
	select * from t_user where uid in
	<foreach collection="array" item="item" open="(" separator="," close=")">
		#{item}
	</foreach>
</select>
<!--  Traverse the array in the wrapper class, collection = "IDS". Here is no longer array, but the corresponding variable name in the wrapper class, because the parameter you pass is a wrapper class, and mybatis obtains the array in the wrapper class through the get method -- >
<select parameterType="UserVo" resultType="User">
	select * from t_user where uid in
	<foreach collection="ids" item="item" open="(" separator="," close=")">
		#{item}
	</foreach>
</select>
<!--  Traverse the list set in the wrapper class. Collection = "idlist". Here is no longer a list, but the corresponding variable name in the wrapper class. Because the parameter you pass is a wrapper class, mybatis obtains the list set in the wrapper class through the get method -- >
<select parameterType="UserVo" resultType="User">
	select * from t_user where uid in
	<foreach collection="idList" item="item" open="(" separator="," close=")">
		#{item}
	</foreach>
</select>
</mapper>

2. Mapper interface

UserMapper.interface

package com.mybatis_demo.mapper;
import java.util.List;
import java.util.Map;
import com.mybatis_demo.domain.User;
import com.mybatis_demo.domain.UserVo;
public interface UserMapper {
	//Mybatis uses mapper dynamic proxy
	
	//Four principles, one note
	//1. The method name in the interface should be consistent with the ID of the corresponding mapper.xml
	//2. The return value in the interface must be consistent with the return value type of the corresponding mapper.xml
	//3. The parameters in the interface should be consistent with the parameter type, number and parameter name of the corresponding mapper.xml
	//4. The namespace corresponding to mapper.xml needs to be modified to the full package name of the corresponding interface
	//Note: according to the return value type, mybatis will automatically select whether to call selectone or selectlist
	//Encapsulate conditions with list
	public List<User> selectByList(List<Integer> testlist);
	//Encapsulating conditions with arrays
	public List<User> selectByArray(Integer[] ids);
	//Encapsulate conditions with arrays in wrapper classes
	public List<User> selectUserVoByArray(UserVo userVo);
	//Encapsulate the condition with a list in the wrapper class
	public List<User> selectUserVoByList(UserVo userVo);	
}

3、 Test code

package com.mybatis_demo.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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.Test;
import com.mybatis_demo.domain.User;
import com.mybatis_demo.domain.UserVo;
import com.mybatis_demo.mapper.UserMapper;
public class TestMapper {
	
	//Encapsulate the condition with the list in the wrapper class, and the passed parameter is a wrapper class
	@Test
	public void test_selectUserVoByList() {
		try {
			 //Read configuration file
			InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
			//Create a sqlsessionfactorybuilder object to get the sqlsessionfactory object
			SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
			//Build a sqlsessionfactory object using the sqlsessionfactorybuilder object
			SqlSessionFactory build = builder.build(in);
			//Using sqlsessionfactory to obtain session object
			SqlSession session = build.openSession();
			//Obtain the corresponding mapper interface through the session object
			UserMapper mapper = session.getMapper(UserMapper.class);
			List<Integer> idList = new ArrayList<Integer>();
			idList.add(5);
			idList.add(3);
			idList.add(123);
			idList.add(19);
			UserVo userVo = new UserVo();
			userVo.setIdList(idList);
			List<User> users = mapper.selectUserVoByList(userVo);
			for (User user : users) {
				System.out.println(user);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	//Encapsulate the condition with array in the wrapper class, and the passed parameter is a wrapper class
	@Test
	public void test_selectUserVoByArray() {
		try {
			 //Read configuration file
			InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
			//Create a sqlsessionfactorybuilder object to get the sqlsessionfactory object
			SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
			//Build a sqlsessionfactory object using the sqlsessionfactorybuilder object
			SqlSessionFactory build = builder.build(in);
			//Using sqlsessionfactory to obtain session object
			SqlSession session = build.openSession();
			//Obtain the corresponding mapper interface through the session object
			UserMapper mapper = session.getMapper(UserMapper.class);
			Integer[] ids = new Integer[]{5,9,30};
			UserVo userVo = new UserVo();
			userVo.setIds(ids);
			List<User> users = mapper.selectUserVoByArray(userVo);
			for (User user : users) {
				System.out.println(user);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	//The condition is encapsulated with an array, and the passed parameter is an array
	@Test
	public void test_selectByArray() {
		try {
			 //Read configuration file
			InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
			//Create a sqlsessionfactorybuilder object to get the sqlsessionfactory object
			SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
			//Build a sqlsessionfactory object using the sqlsessionfactorybuilder object
			SqlSessionFactory build = builder.build(in);
			//Using sqlsessionfactory to obtain session object
			SqlSession session = build.openSession();
			//Obtain the corresponding mapper interface through the session object
			UserMapper mapper = session.getMapper(UserMapper.class);
			Integer[] ids = new Integer[]{5,9,30};
			List<User> users = mapper.selectByArray(ids);
			for (User user : users) {
				System.out.println(user);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	//The conditions are encapsulated with a list, and the passed parameters are a list collection
	@Test
	public void test_selectByList() {
		try {
			 //Read configuration file
			InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
			//Create a sqlsessionfactorybuilder object to get the sqlsessionfactory object
			SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
			//Build a sqlsessionfactory object using the sqlsessionfactorybuilder object
			SqlSessionFactory build = builder.build(in);
			//Using sqlsessionfactory to obtain session object
			SqlSession session = build.openSession();
			//Obtain the corresponding mapper interface through the session object
			UserMapper mapper = session.getMapper(UserMapper.class);
			List<Integer> list = new ArrayList<Integer>();
			list.add(5);
			list.add(3);
			list.add(123);
			list.add(19);
			List<User> users = mapper.selectByList(list);
			for (User user : users) {
				System.out.println(user);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

4、 Summary

1. If you directly pass an array when passing parameters, then use foreach to traverse collection = “array”, which is a fixed writing method, that is, the array here has nothing to do with your actual parameter name

2. If you directly pass a list set when passing parameters, then when using foreach traversal, collection = “list”, here is a fixed writing method, that is, the list here has nothing to do with your actual parameter name

3. If you directly pass a class containing array member variables when passing parameters, collection = “your variable name” is no longer a fixed writing method when using foreach traversal, that is, the name here depends on the variable name of the member variable. For example, if the member variable name is test, it is collection = “test”

4. If you directly pass a class containing list set member variables when passing parameters, as in the case of 3

The above is my personal experience. I hope I can give you a reference, and I hope you can support developpaer.

Recommended Today

The selector returned by ngrx store createselector performs one-step debugging of fetching logic

Test source code: import { Component } from ‘@angular/core’; import { createSelector } from ‘@ngrx/store’; export interface State { counter1: number; counter2: number; } export const selectCounter1 = (state: State) => state.counter1; export const selectCounter2 = (state: State) => state.counter2; export const selectTotal = createSelector( selectCounter1, selectCounter2, (counter1, counter2) => counter1 + counter2 ); // […]