Foreach is mainly used in building in conditions, it can iterate over a collection in SQL statements. The attributes of the foreach element mainly include item, index, collection, open, separator, and close. Item represents the alias when each element in the collection is iterated. Index specifies a name to represent the position to which each iteration is reached during the iteration process. Open represents what starts with the statement, separator represents what symbols are used as separators between each iteration, and close represents what ends with. The most critical and most error-prone thing when using foreach is the collection attribute. This attribute must be specified, but in different cases, the value of the attribute is different. There are three main situations:
If the passed in is a single parameter and the parameter type is a List, the collection attribute value is list.
If the passed in is a single parameter and the parameter type is an array, the property value of the collection is array.
If there are multiple parameters passed in, we need to encapsulate them into a map. Of course, a single parameter can also be encapsulated into a map. In fact, if you pass in parameters, it will also be encapsulated into a map in MyBatis. The map's key is the parameter name, so at this time the collection attribute value is the key of the passed List or array object in the map encapsulated by itself.
Let's practice it through code:
Data table:
Using Oracle's HR.Employees table
Entity: Employees
public class Employees { private Integer employeeId; private String firstName; private String lastName; private String email; private String phoneNumber; private Date hireDate; private String jobId; private BigDecimal salary; private BigDecimal commissionPct; private Integer managerId; private Short departmentId;}Mapping files:
<!--List: The collection attribute type in forech is List, the value of collection must be: list, item value can be arbitrary, and the parameter names in Dao interface are arbitrary--> <select id="getEmployeesListParams" resultType="Employees"> select * from EMPLOYEES e where e.EMPLOYEE_ID in <foreach collection="list" item="employeeId" index="index" open="(" close=")" separator=","> #{employeeId} </foreach> </select> <!--Array: The collection attribute type in forech is array, the value of collection must be: list, item value can be arbitrary, and the parameter names in the Dao interface are arbitrary--> <select id="getEmployeesArrayParams" resultType="Employees"> select * from EMPLOYEES e where e.EMPLOYEE_ID in <foreach collection="array" item="employeeId" index="index" open="(" close=")" separator=","> #{employeeId} </foreach> </select> <!--Map: Not only the collection attribute in forech is map.key, but all other attributes are map.key, such as the following departmentId --> <select id="getEmployeesMapParams" resultType="Employees"> select * from EMPLOYEES e <where> <if test="departmentId!=null and departmentId!=''"> e.DEPARTMENT_ID=#{departmentId} </if> <if test="employeeIdsArray!=null and employeeIdsArray.length!=0"> AND e.EMPLOYEE_ID in <foreach collection="employeeIdsArray" item="employeeId" index="index" open="(" close=")" separator=","> #{employeeId} </foreach> </if> </where> </select>Mapper class:
public interface EmployeesMapper { List<Employees> getEmployeesListParams(List<String> employeeIds); List<Employees> getEmployeesArrayParams(String[] employeeIds); List<Employees> getEmployeesMapParams(Map<String,Object> params);}The above is the complete description of the writing method of the MyBatis incoming set list array map parameters introduced to you by the editor. I hope it will be helpful to everyone!