4. MyBatis映射文件-增删改查

一、准备工作

1. 新建项目

1. 新建maven项目 mybatis-03-mapper
   
2. 引入相关依赖

	<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>

3. 创建sqlMapConfig.xml全局配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="jdbc.properties"></properties>

	<!--设置-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--显示的指定我们需要更改的配置，防止更新带来的问题-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>

	<!--批量起别名-->
    <typeAliases>
        <package name="com.znb.bean"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!--批量注册-->
        <package name="com.znb.mapper"/>
    </mappers>
</configuration>

4. jdbc.propreties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT
username=root
password=2550

2. 数据库中表的结构

3. 实体类的创建

import lombok.Data;

@Data
public class Employee {
    private Integer id;
    private String lastName;
    private String gender;
    private String email;
    private Department dept;

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

二、增删改查

1. 在接口中声明方法

	public void addEmp(Employee employee);

    public void updateEmp(Employee employee);

    public void deleteEmpById(Integer id);

2. 在sql映射文件中添加

	<insert id="addEmp" parameterType="employee" useGeneratedKeys="true" keyProperty="id">
        insert into tbl_employee values (#{id},#{lastName},#{gender},#{email})
    </insert>

    <update id="updateEmp" parameterType="employee">
        update tbl_employee set last_Name = #{lastName},gender = #{gender},email = #{email} where id = #{id}
    </update>

    <delete id="deleteEmpById" parameterType="int">
        delete from tbl_employee where id = #{id}
    </delete>

2. 测试添加的方法

	@Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        // 获取实例对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //创建需要添加的对象
        Employee employee = new Employee();
        employee.setLastName("fffd");
        employee.setGender("1");
        employee.setEmail("123@qq.com");

        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
            // 添加
            mapper.addEmp(employee);
            sqlSession.commit();
        }finally {
            sqlSession.close();
        }
    }

三、参数处理

1. 单个参数

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

2. 多个参数

多个参数：mybatis会做特殊处理
    多个参数会被封装成一个Map
         key: param1...paramn
         value:传入的参数的值
    #{} 就是从map中获取指定key的值

    异常：
    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}

1. 根据id和lastname查询

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

2. 映射文件

 	<!--getEmpByIdAndLastName-->
    <select id="getEmpByIdAndLastName" resultType="employee">
        select * from tbl_employee where id = #{id} and last_Name = #{lastName}
    </select>

3. 命名参数

命名参数：明确指定封装参数时map的key
        public Employee getEmpByIdAndLastName(@Param("id") Integer id,@Param("lastName") String lastName);
        多个参数会被封装成一个map;
            key:使用@Param注解指定的值
            value:参数值
        #{指定的key}取出对应的参数值
POJO
如果多个参数正好是业务逻辑层的数据模型，直接传入pojo
        #{属性名} 取出传入pojo的属性值

MAP
如果多个参数不是业务逻辑层的数据模型，没有对应的pojo  可以直接传入Map
        #{属性名} 取出传入map的属性值
TO
如果多个参数经常被调用，可以编写一个TO作为数据传输对象

如果是Collection(List Set)类型或者是数组，也会被特殊处理被封装为一个map
    key:如果是Collection(collection),如果是List (list)
        如果是数组：（array）
    public Employee getEmpById(List<Integer> ids);
        #{list[0]}

1. 根据id和lastname 封装到map中查询

public Employee getEmpByMap(Map<String,Object> map);

2. 映射文件

	<!--getEmpByMap-->
    <select id="getEmpByMap" resultType="employee">
        select * from ${tableName} where id = ${id} and last_Name = #{lastName}
    </select>