MyBatis传入参数为list、数组、map写法

叁歲伎倆 2022-06-09 10:50 197阅读 0赞

### 1.foreach简单介绍： ###

foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。

foreach元素的属性主要有item，index，collection，open，separator，close。

item表示集合中每一个元素进行迭代时的别名，

index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，

open表示该语句以什么开始，

separator表示在每次进行迭代之间以什么符号作为分隔符，

close表示以什么结束，

collection属性是在使用foreach的时候最关键的也是最容易出错的，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有一下3种情况：

（1）如果传入的是单参数且参数类型是一个List的时候，collection属性值为list .

（2）如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array .

（3）如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key.

### 2.实践-实体类 ###

**\[java\]** view plain copy

print ?

1.  public class Employees \{
2.  private Integer employeeId;
3.  private String firstName;
4.  private String lastName;
5.  private String email;
6.  private String phoneNumber;
7.  private Date hireDate;
8.  private String jobId;
9.  private BigDecimal salary;
10. private BigDecimal commissionPct;
11. private Integer managerId;
12. private Short departmentId;
13. \}

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;
    }

### 3.实践-XML ###

**\[html\]** view plain copy

print ?

1.  <!–List:forech中的collection属性类型是List,collection的值必须是:list,item的值可以随意,Dao接口中参数名字随意 –>
2.  <select id=“getEmployeesListParams” resultType=“Employees”>
3.  select \*
4.  from EMPLOYEES e
5.  where e.EMPLOYEE\_ID in
6.  <foreach collection=“list” item=“employeeId” index=“index”
7.  open=“(“ close=“)” separator=“,”>
8.  \#\{employeeId\}
9.  </foreach>
10. </select>
11. 
12. <!–Array:forech中的collection属性类型是array,collection的值必须是:list,item的值可以随意,Dao接口中参数名字随意 –>
13. <select id=“getEmployeesArrayParams” resultType=“Employees”>
14. select \*
15. from EMPLOYEES e
16. where e.EMPLOYEE\_ID in
17. <foreach collection=“array” item=“employeeId” index=“index”
18. open=“(“ close=“)” separator=“,”>
19. \#\{employeeId\}
20. </foreach>
21. </select>
22. 
23. <!–Map:不单单forech中的collection属性是map.key,其它所有属性都是map.key,比如下面的departmentId –>
24. <select id=“getEmployeesMapParams” resultType=“Employees”>
25. select \*
26. from EMPLOYEES e
27. <where>
28. <if test=“departmentId!=null and departmentId!=””>
29. e.DEPARTMENT\_ID=\#\{departmentId\}
30. </if>
31. <if test=“employeeIdsArray!=null and employeeIdsArray.length!=0”>
32. AND e.EMPLOYEE\_ID in
33. <foreach collection=“employeeIdsArray” item=“employeeId”
34. index=“index” open=“(“ close=“)” separator=“,”>
35. \#\{employeeId\}
36. </foreach>
37. </if>
38. </where>
39. </select>

<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>
    
        
        <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>
    
        
        <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>

### 4.实践-Mapper ###

**\[java\]** view plain copy

print ?

1.  public interface EmployeesMapper \{
2.  
3.  List<Employees> getEmployeesListParams(List<String> employeeIds);
4.  
5.  List<Employees> getEmployeesArrayParams(String\[\] employeeIds);
6.  
7.  List<Employees> getEmployeesMapParams(Map<String,Object> params);
8.  \}

public interface EmployeesMapper { 
    
        List<Employees> getEmployeesListParams(List<String> employeeIds);
    
        List<Employees> getEmployeesArrayParams(String[] employeeIds);
    
        List<Employees> getEmployeesMapParams(Map<String,Object> params);
    }

转载来源：[http://blog.csdn.net/s592652578/article/details/52871884][http_blog.csdn.net_s592652578_article_details_52871884]

[http_blog.csdn.net_s592652578_article_details_52871884]: http://blog.csdn.net/s592652578/article/details/52871884