10、mybatis参数处理

1、单个参数：mybatis不做任何处理

#{参数名}：取出参数

2、多个参数：mybatis会做特殊处理

多个参数会被封装成一个map
    key:param1...paramN，或者参数的索引也可以
    value:传入的参数
#{}就是从map中获取指定的key的值；

3、命名参数：明确指定封装参数时map的key：@Param(“id”)

多个参数会被封装成一个map
    key：使用@Param注解指定的值
    value：参数值 
#{指定的key}取出对应的参数值

3.1 EmployeeMapper接口

package com.mi.dao;

import com.mi.pojo.Employee;
import org.apache.ibatis.annotations.Param;

public interface EmployeeMapper {

    public Employee getEmployeeById(Integer id);

    public Integer addEmp(Employee employee);

    public Integer deleteEmp(Integer id);

    public Integer updateEmp(Employee employee);

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

}

3.2 EmployeeMapper.xml

<?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.mi.dao.EmployeeMapper">
    <!--id 为接口的方法名-->
    <select id="getEmployeeById" resultType="com.mi.pojo.Employee">
        select * from employee where id = #{id}
    </select>

    <insert id="addEmp" parameterType="com.mi.pojo.Employee"
            useGeneratedKeys="true" keyProperty="id">
        insert into employee (last_name,gender) values (#{lastName},#{gender})
    </insert>
    
    <delete id="deleteEmp">
        delete from employee where id = #{id}
    </delete>

    <update id="updateEmp" >
        update employee set last_name = #{lastName},gender = #{gender} where id = #{id}
    </update>

    <select id="getEmployeeByParam" resultType="com.mi.pojo.Employee">
        select * from employee where id = #{id} and last_name = #{lastName}
    </select>
</mapper>

3.3 Test

@Test
    public void testGetParam() throws IOException {
        //1、获取SqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //2、获取Sqlsesion对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            //3、获取接口的实现类对象
            //会为接口自动创建一个代理对象，代理对象去执行增删改查方法
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
            Employee employee = mapper.getEmployeeByParam(3,"jerry");
            System.out.println(employee);
        }finally {
            sqlSession.close();
        }
    }

4、POJO，

如果多个参数正好是我们业务逻辑的数据模型，我们就可以直接传入pojo，
#｛属性名｝：取出传入的pojo的属性值

5、Map

如果多个参数不是业务模型中的数据，没有对应的pojo，为了方便，我们也可以传入map

#｛key｝：取出map中对应的值

5.1 EmployeeMapper

package com.mi.dao;

import com.mi.pojo.Employee;
import org.apache.ibatis.annotations.Param;

import java.util.Map;

public interface EmployeeMapper {

    public Employee getEmployeeById(Integer id);

    public Integer addEmp(Employee employee);

    public Integer deleteEmp(Integer id);

    public Integer updateEmp(Employee employee);

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

    public Employee getEmployeeByMap(Map map);

}

5.2 EmployeeMapper.xml

<?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.mi.dao.EmployeeMapper">
    <!--id 为接口的方法名-->
    <select id="getEmployeeById" resultType="com.mi.pojo.Employee">
        select * from employee where id = #{id}
    </select>

    <insert id="addEmp" parameterType="com.mi.pojo.Employee"
            useGeneratedKeys="true" keyProperty="id">
        insert into employee (last_name,gender) values (#{lastName},#{gender})
    </insert>
    
    <delete id="deleteEmp">
        delete from employee where id = #{id}
    </delete>

    <update id="updateEmp" >
        update employee set last_name = #{lastName},gender = #{gender} where id = #{id}
    </update>

    <select id="getEmployeeByParam" resultType="com.mi.pojo.Employee">
        select * from employee where id = #{id} and last_name = #{lastName}
    </select>

    <select id="getEmployeeByMap" resultType="com.mi.pojo.Employee">
        select * from employee where id = #{id} and last_name = #{lastName}
    </select>
</mapper>

5.3 Test

@Test
    public void testGetByMap() throws IOException {
        //1、获取SqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //2、获取Sqlsesion对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            //3、获取接口的实现类对象
            //会为接口自动创建一个代理对象，代理对象去执行增删改查方法
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

            Map<String,Object> map = new HashMap<String, Object>();
            map.put("id",3);
            map.put("lastName","jerry");
            Employee employee = mapper.getEmployeeByMap(map);
            System.out.println(employee);
        }finally {
            sqlSession.close();
        }
    }

6、TO

如果多个参数不是业务模型中的数据，但是经常使用，推荐来编写一个TO（Transfer Object）

page{
int index;
int size;
}

7、jdbcType通常需要在某种特定的条件下被设置

在我们数据为null的时候，有些数据库可能不能识别mybatis对null的默认处理，比如Oracle（报错）
jdbcType OTHER 无效的类型：因为mybatis对所有的null都映射的是原生的Jdbc的 OTHER 类型，oracle不能正确处理。

由于全局配置中：jdbcTypeForNull=OTHER；oracle不支持，两种办法
1、#{email,jdbcType=OTHER};
2、jdbcTypeForNULL = NULL