MyBatis参数传入集合之foreach动态sql
版权声明：本文为博主原创文章，未经博主允许不得转载。
- 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list .
- 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array .
- 如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key.
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;
}
映射文件:
<!--List:forech中的collection属性类型是List,collection的值必须是:list,item的值可以随意,Dao接口中参数名字随意 -->
<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:forech中的collection属性类型是array,collection的值必须是:list,item的值可以随意,Dao接口中参数名字随意 -->
<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:不单单forech中的collection属性是map.key,其它所有属性都是map.key,比如下面的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类:
public interface EmployeesMapper {
List<Employees> getEmployeesListParams(List<String> employeeIds);
List<Employees> getEmployeesArrayParams(String[] employeeIds);
List<Employees> getEmployeesMapParams(Map<String,Object> params);
}
public void testGetEmployeesListParams() {
List<String> employeeIds = Arrays.asList("100", "101", "200");
List<Employees> result = employeesMapper
.getEmployeesListParams(employeeIds);
assertEquals(3, result.size());
}
@Test
public void testGetEmployeesArrayParams() {
String[] employeeIds = new String[] { "100", "200" };
List<Employees> result = employeesMapper
.getEmployeesArrayParams(employeeIds);
assertEquals(2, result.size());
}
@Test
public void testGetEmployeesMapParams() {
String departmentId = "60";
List<String> employeeIdsList = Arrays.asList("103", "104", "105");
String[] employeeIdsArray = new String[] { "103", "104" };
// 传入多个参数
Map<String, Object> params = new HashMap<String, Object>();
params.put("departmentId", departmentId);
params.put("employeeIdsList", employeeIdsList);
params.put("employeeIdsArray", employeeIdsArray);
List<Employees> result = employeesMapper.getEmployeesMapParams(params);
assertEquals(3, result.size());
}
package com.ydssy.dao.product;
import java.util.List;
import java.util.Map;
import com.ydssy.model.product.ProductStore;
public interface ProductStoreMapper {
//接口 传入一个集合
List<ProductStore> parameterlist(String[] s);
}
映射文件
<?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.ydssy.dao.product.ProductStoreMapper" >
<!--Array:forech中的collection属性类型是array,collection的值必须是:list,item的值可以随意,Dao接口中参数名字随意 -->
<select id="parameterlist" parameterType="com.ydssy.model.product.ProductStore" resultType="com.ydssy.model.product.ProductStore">
select ps.* from es_product_store ps where ps.goodsid in<foreach collection="array" item="employeeId" index="index" open="(" close=")" separator=",">
#{employeeId}
</foreach>
</select>
</mapper>
测试类
package test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import com.ydssy.dao.product.ProductStoreMapper;
import com.ydssy.model.product.ProductStore;
public class TestProductStoreMapper {
private SqlSession session;
private ProductStoreMapper dao;
@Before// @Before在··之前
public void init() {
SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
// SqlSessionFactory通过SqlSessionFactoryBuilder的ssfb方法里的类加载器的getResourceAsStream方法获取配置文件信息
//getResourceAsStream会创建一个流 通过这个流去获取配置文件，这个文件会返回一个importString这个流
SqlSessionFactory ssf = ssfb.build(TestProductStoreMapper.class.getClassLoader().getResourceAsStream("com/ydssy/goods/goods.xml"));
// 获得SqlSession对象
// (SqlSession是执行sql的一个容器 通过session访问数据库)。
session = ssf.openSession();
//用session对象去获取接口 ，getMapper方法会返回符合Tapper映射器（ 即EmployeeDAO 接口） 要求的对象
dao = session.getMapper(ProductStoreMapper.class);
}
@Test
public void test3() {
String[] s = new String[] { "516","518" };
List<ProductStore> result = dao.parameterlist(s);
System.out.println(result);
session.commit();
session.close();
}
}
配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>
<environments default="environment">
<environment id="environment">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql:///symall_emoji?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=true" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<mappers><!-- 配置文件连接 映射器文件 -->
<mapper resource="com/ydssy/mapping/goods/GoodsCatMapper.xml" />
<mapper resource="com/ydssy/mapping/product/ProductStoreMapper.xml" />
</mappers>
</configuration>
包
