mybatis之参数传递的方式 | mybatis

1. 单个参数(基本类/包装类+String)

这种情况MyBatis可直接使用这个参数，不需要经过任何处理。

一个参数情况下#{}中内容随便写

public Employee getEmployeeById(Integer id );

<select id="selectEmployee" resultType="com.atguigu.mybatis.beans.Employee">
		<!-- select *  from tbl_employee where id = #{id} -->
		select id, last_name lastName, email, gender from tbl_employee where id = #{iaaad}
</select>

2. 多个参数

任意多个参数，都会被MyBatis重新包装成一个Map传入。Map的key是param1，param2，或者0，1…，不适合多参数，用的较少

取值: #{0 1 2 …N / param1 param2 ….. paramN}

public Employee  getEmployeeByIdAndLastName(Integer id, String lastName);

<select id="getEmployeeByIdAndLastName" resultType="com.atguigu.mybatis.beans.Employee">
	select id, last_name, email,gender 
    from tbl_employee 
    where id = #{param1} 
    and last_name = #{param2}
</select>

3. 命名参数

当有多个参数时，使用@Param为参数起一个名字，MyBatis就会将这些参数封装进map中，key就是我们自己指定的名字

取值: #{自己指定的名字 / param1 param2 … paramN}

public Employee getEmployeeByIdAndLastName(@Param("id")Integer id, @Param("lastName")String lastName);

<select id="getEmployeeByIdAndLastName" resultType="com.atguigu.mybatis.beans.Employee">
        select id, last_name, email,gender from tbl_employee
        where id = #{id/param1}  and last_name = #{lastName/param2}
</select>

4. POJO（常用）

当这些参数属于业务POJO时，我们直接传递POJO

取值: #{POJO的属性名}

5. Map

我们也可以自己封装多个参数为map，直接传递

取值: #{使用封装Map时自己指定的key}

	//Mapper接口
	public Employee getEmployeeByMap(Map<String,Object> map);

   //调用
	Map<String,Object > map = new HashMap<>();
    map.put("id", 1024);
    map.put("name", "莉香");
    Employee employee = mapper.getEmployeeByMap(map);

<select id="getEmployeeByMap" resultType="com.atguigu.mybatis.beans.Employee">
		select id, last_name, email,gender from ${tableName} where id = #{id}  and last_name = 			#{name}
</select>

6. Collection/Array

方式1

会被MyBatis封装成一个map传入，Collection对应的key是collection，Array对应的key是array。如果确定是List集合，key还可以是list。

Long selectCustomerCountList( List customerIdList);

<select id="selectCustomerCountList" parameterType="java.util.List" resultType="java.lang.Long">
    select count(1) from np_customer_info where id in
    <foreach item="item" collection="list" separator="," open="(" close=")" index="">
    	#{item, jdbcType=INTEGER}   
    </foreach>
</select>  

注意：此时collection强制指定为list且不可改变

方式2

利用注解@Param指定入参List的名称

Long selectCustomerCountList(@Param("customerIdList") List customerIdList);
 

<select id="selectCustomerCountList" parameterType="java.util.List" resultType="java.lang.Long">
    select count(1) from np_customer_info where id in
    <foreach item="item" collection="customerIdList" separator="," open="(" close=")" index="">  
    	#{item, jdbcType=INTEGER}   
    </foreach>
</select>

方式3

//在Service业务处理层次上面将参数进行包装
public Long selectCustomerCountMap(List customerIdList) {  
    Map maps = new HashMap();
    maps.put("customerIds", customerIdList);
    return customerMapper.selectCustomerCountMap(maps);
}
   
//DAO层：
Long selectCustomerCountMap(Map maps);

<select id="selectCustomerCountMap" parameterType="java.util.Map" resultType="java.lang.Long">
    select count(1) from np_customer_info where id in
    <foreach item="item" collection="customerIds" separator="," open="(" close=")" index="">
    	#{item, jdbcType=INTEGER}   
    </foreach>
</select>

方式4

把List 放入一个Bean对象中, XML不用#{item} 改为 #{tagIds[${index}]}

public class AlarmConditionDTO {
    private List<String> orgIds;  
    private List<String> tagIds;   
    private String alertType;
}

List<Map<String,String>> selectDeviceCountByCondition(AlarmConditionDTO alarmConditionDTO);

<select id="selectDeviceCountByCondition" resultType="java.util.Map">
    SELECT * from md_tag_target_relation_device 
    where 1=1
    <if test="tagIds != null and tagIds.size()>0">
        and tag_id IN
        <foreach collection="orgIds" index="index" open="(" close=")" separator="," item="item">
            #{tagIds[${index}],jdbcType=VARCHAR}
        </foreach>
    </if>
    <if test="orgIds != null and orgIds.size()>0">
        and d.region_code IN
        <foreach collection="orgIds" index="index" open="(" close=")" separator="," item="item">
            #{orgIds[${index}],jdbcType=VARCHAR}
        </foreach>
    </if>
</select>

7. json

这也是推荐的一种传参方式，controller层收到JSON型数据后，直接传递给mapper层进行查询操作，简单 方便

//controller
@ApiOperation(value = "多个参数查询_通过JSON传递多个参数")
@PostMapping("findByJSONObject")
public ResultMsg findByJSONObject(@RequestBody JSONObject params)
{
    List result= employeeMapper.findByJSONObject(params);
    return ResultMsg.getMsg(result);
}

//mapper
List <Employee> findByJSONObject(JSONObject params);

<select id="findByJSONObject" resultMap="BaseResultMap" parameterType="com.alibaba.fastjson.JSONObject">
  select
  *
  from employee where gender = #{gender} and age = #{age}
</select>