MyBatis参数处理

单个参数：mybatis不会做特殊处理，
    #{参数名}：取出参数值


多个参数：mybatis会做特殊处理。
    多个参数会被封装成一个map，
    #{}就是从map中获取指定的key的值；
       key:param1...paramN或者参数的索引也可以
       value:传入参数的值
    异常：
    org.apache.ibatis.binding.BindingException:
    Parameter 'id' not found.
    Available parameters are [arg1, arg0, param1, param2]
    操作：
        方法：public Employee getEmpByIdAndLastName(Integer id,String lastName);
        取值：#{id},#{lastName}

命名参数：明确指定封装参数时map的key;@Param("id")
    多个参数会被封装成一个map，
       key:使用@Param注解指定的值
       value:参数值
    #{指定的key}取出对应的参数值

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

Map：
如果多个参数不是业务模型中的数据，没有对应的pojo,为了方便，我们也可以传入map
    #{key}取出map中对应的值

TO:
如果多个参数不是业务模型中的数据，但是要经常使用，推荐来编写一个TO（Transfer Object）数据传输对象
Page{
   int index;
   int size;
}

参数值的获取
#{}:可以获取map中的值或者pojo对象属性的值；
${}:可以获取map中的值或者pojo对象属性的值；

select * from tbl_employee where id=${id} and last_name=#{lastName}
Preparing: select * from tbl_employee where id=2 and last_name=?
    区别：
        #{}：是以预编译的形式，将参数设置到sql语句中；PreparedStatement；防止SQL注入
        ${}：取出的值直接拼装在SQL语句中；会有安全问题；
        大多情况下，我们取参数的值都应该去使用#{}

        原生jdbc不支持占位符的地方我们就可以使用${}进行取值
        比如分表,排序：按照年份分表拆分
            select * from ${year}_salary where xxx;
            select * from tbl_employee order by ${f_name} ${order};

#{}：更丰富的用法：
    规定参数的一些规则：
    javaType、jdbcType、mode（存储过程）、numericScale、
    resultMap、typeHandler、jdbcTypeName、expression

    jdbcType：通常需要在某种特定的条件下被设置：
        在我们数据为null的时候，有些数据库可能不能识别mybatis对null的默认处理。比如Oracle（报错）；
        JdbcType OTHER：无效的类型；因为mybatis对所有的null都映射的是Jdbc OTHER

package com.lzr.dao;

import com.lzr.bean.Employee;
import org.apache.ibatis.annotations.Param;

import java.util.Map;

/**
 * @author GGBond
 * @create 2021-05-22-19:34
 */
public interface EmployeeMapper {

    //public Employee getEmpByIdAndLastName(Integer id,String lastName);

    public Employee getEmpByMap(Map map);

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

    public Employee getEmpById(Integer id);

    public void addEmp(Employee employee);

    public void updateEmp(Employee employee);

    public void deleteEmpById(Integer id);
}

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

    

    
    
         select * from tbl_employee where id = #{id} and last_name=#{lastName}
    
    
    
        select * from tbl_employee where id = #{id} and last_name=#{lastName}
    

    
       select * from tbl_employee where id = #{id}
    

    
    
    
      keyProperty="id">
        insert into tbl_employee(last_name,email,gender)
        values(#{lastName},#{email},#{gender})
    

    
    
        update tbl_employee
        set last_name=#{lastName},email=#{email},gender=#{gender}
        where id=#{id}
    

    
    
        delete from tbl_employee where id=#{id}
    

/**
     * 测试增删改
     * 1.mybatis允许增删改直接定义以下类型返回值
     *    Integer,Long,Boolean,void
     * 2.需要我们手动提交数据
     *   sqlSessionFactory.openSession();===>手动提交
     *   sqlSessionFactory.openSession(true);===>自动提交
     */
    @Test
    public void test03() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //1.获取到的SqlSession不会自动提交数据
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            //测试添加
            Employee employee = new Employee(null, "jerry", "jerry@atguigu.com", "1");
            mapper.addEmp(employee);
            System.out.println(employee.getId());
            //测试修改
//            Employee employee = new Employee(1, "jerry", "jerry@atguigu.com", "0");
//            mapper.updateEmp(employee);
            //测试删除
            //mapper.deleteEmpById(2);
            //2.手动提交数据
            openSession.commit();
        }finally {
            openSession.close();
        }
    }
    @Test
    public void test04() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //1.获取到的SqlSession不会自动提交数据
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            //Employee employee = mapper.getEmpByIdAndLastName(1, "tom");
            Map map = new HashMap();
            map.put("id",1);
            map.put("lastName","Tom");
            Employee employee = mapper.getEmpByMap(map);
            System.out.println(employee);
        }finally {
            openSession.close();
        }
    }

package com.lzr.bean;

import org.apache.ibatis.type.Alias;

/**
 * @author GGBond
 * @create 2021-05-22-18:17
 */
@Alias("emp")
public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private String gender;

    public Employee() {
        super();
    }

    public Employee(Integer id, String lastName, String email, String gender) {
        this.id = id;
        this.lastName = lastName;
        this.email = email;
        this.gender = gender;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                ", gender='" + gender + '\'' +
                '}';
    }
}