Mybatis 入门，包含动态 SQL 的理解，便于初学者快速上手。

最新推荐文章于 2024-09-23 10:06:07 发布

一榔捶

最新推荐文章于 2024-09-23 10:06:07 发布

阅读量216

点赞数

文章标签： mybatis sql 数据库

本文链接：https://blog.csdn.net/Ban_dan/article/details/128754111

版权

@一榔捶

一、Mybatis操作数据库

1、Mybatis入门

数据库的创建：employee数据库中的tbl_employee表

CREATE TABLE tbl_employee (
	id INT(11) PRIMARY KEY AUTO_INCREMENT,
	last_name VARCHAR(255),
	gender char(1),
	email VARCHAR(255)
)

javaBean的创建：Employee类的创建

public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private String 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 + '\'' +
                '}';
    }
}

引入依赖：mybatis、mysql-connection-java、log4j（pom.xml中）

 <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

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

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

全局配置文件的创建：mybatis-config.xml（以下的xml文件均放入Resource文件夹中）

其中<mapper resource="EmployeeMapper.xml"为sql的映射文件，需要将其注册到此全局配置文件中

<?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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="374761727"/>
            </dataSource>
        </environment>
    </environments>
<!--    需要将写好的sql映射文件（EmployeeMapper.xml ）注册到全局配置文件中（mybatis-config.xml）-->
    <mappers>
        <mapper resource="EmployeeMapper.xml"/>
    </mappers>
</configuration>

sql映射文件的创建：EmployeeMapper.xml（通常以Mapper结尾，保存了每一个sql语句的映射信息）

namespace：名称空间（可为接口名，注意路径间隔符为 ‘.’ 写src/main/java之后的路径

id：唯一标识（当名称空间为接口类型时，id值为抽象方法名）

resultType：返回值类型（通常为javaBean，需要写全路径）

#（id）：从测试方法中传递回来的参数中取到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">
<mapper namespace="com.ban.dao.EmployeeMapper">
<!--
namespace：名称空间
id：唯一标识
 resultType：返回值类型
 #{id}：从传递过来的参数中取出id值
 public Employee getEmpById(Integer id);
 -->
    <select id="getEmpById" resultType="com.ban.bean.Employee">
        select id,last_name lastName,email,gender from tbl_employee where id = #{id}
    </select>
</mapper>

log4j.xml文件的创建（复制即可）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
 
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
 
 <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
   <param name="Encoding" value="UTF-8" />
   <layout class="org.apache.log4j.PatternLayout">
    <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) \n" />
   </layout>
 </appender>
 <logger name="java.sql">
   <level value="debug" />
 </logger>
 <logger name="org.apache.ibatis">
   <level value="info" />
 </logger>
 <root>
   <level value="debug" />
   <appender-ref ref="STDOUT" />
 </root>
</log4j:configuration>

EmployeeMapper接口的创建

public interface EmployeeMapper {
    public Employee getEmpById(Integer id);
}

myBatis测试类的创建

/**
 * 1、根据xml配置文件（全局配置文件）创建一个SqlSessionFactory对象,存在数据源一些运行环境信息
 * 2、sql映射文件：配置了每一个sql，以及sql的封装规则等
 * 3、将sql映射文件注册在全局配置文件中
 * 4、写代码{
 *     a：根据全局配置文件得到SqlSessionFactory
 *     b：使用sqlSession工厂，获取到sqlSession对象使用他来执行增删改查，一个sqlSession就是代表和数据库的一次对话，用完即关闭
 *     c：使用sql的唯一标识（id）来告诉mybatis执行哪个sql。sql都是保存在sql映射文件中的
 * }
 * 1、接口式编程
 * 原生：          Dao ------- DaoImpl
 * mybatis：      Dao ------- xxMapper.xml
 * 2、SqlSession代表和数据库的一次会话，用完必须关闭
 * 3、SqlSession和Connection一样都是非线程安全的，每次使用都应该获取新的对象
 * 4、mapper接口没有实现类，但是mybatis会为这个接口生成一个代理对象
 * 将接口和xml进行绑定
 *  EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
 * 5、两个重要的配置文件：
 * mybatis的全局配置文件：包涵数据库连接池信息，事务管理器信息，系统运行环境信息等
 * sql映射文件：保存了每一个sql语句的映射信息，将sql抽取出来
 */

public class mybatisTest {
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }
    @Test
    public void testMybatis1() throws IOException {
        //1、获取sqlSessionFactory对象，其中getSqlSessionFactory方法已被封装
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //2、获取sqlSession对象
        SqlSession openSession = sqlSessionFactory.openSession();
        //3、获取接口的实现类对象
        try {
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            Employee empById = mapper.getEmpById(1);
            System.out.println(empById);
        }finally {
            openSession.close();
        }
    }
}

结果为：

DEBUG 11-08 17:18:11,452 ==>  Preparing: select id,last_name lastName,email,gender from tbl_employee where id = ?  (BaseJdbcLogger.java:137) 
DEBUG 11-08 17:18:11,490 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 11-08 17:18:11,519 <==      Total: 1  (BaseJdbcLogger.java:137) 
Employee{id=1, lastName='tom', email='tom@qq.com', gender='0'}

2、mybatis配置

参数值的获取

#{}：是以预编译的形式，将参数设置到sql语句中；PreparedStatement：防止sql注入

${}：取出的值直接拼装到sql语句中，会有安全问题

大多数情况下，我们去参数的值都应该使用#{}

原声JDBC中不支持占位符的地方我们就可以使用${}进行取值，比如分表、排序、按照年份分表拆分

    <select id="getEmpByMap" resultType="com.ban.bean.Employee">
        select * from ${tableName} where id = #{id} and last_name = #{lastName}
    </select>

Employee

public interface EmployeeMapper {

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

Test

@Test
    public void testMap() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            HashMap<String, Object> map = new HashMap<>();
            map.put("id",1);
            map.put("lastName","liuliului");
            map.put("tableName","tbl_employee");
            Employee empByMap = mapper.getEmpByMap(map);
            System.out.println(empByMap);
        }finally {
            openSession.close();
        }

    }

结果为

Employee{id=1, lastName='liuliului', email='374761727@qq.com', gender='1'}

3、SQl映射器（CRUD）

（1）、select

与下方过于相似，不再赘述

（2）、insert

EmployeeMapper.xml

    <!--增加：public void addEmp(Employee employee);  其中parameterType可以省略
        useGeneratedKeys="true" 使用自增主键获取主键值策略
        keyProperty：指定对应的主键属性，也就是mybatis获取到主键值后，将这个值封装到javaBean（Employee）中的哪个属性（id）
    -->
    <insert id="addEmp" parameterType="com.ban.bean.Employee" useGeneratedKeys="true" keyProperty="id">
        insert into tbl_employee(last_name,email,gender) values(#{lastName},#{email},#{gender})
    </insert>

EmployeeMapper接口

public interface EmployeeMapper {
   public void addEmp(Employee employee);
   }

mybatis-config.xml配置文件

<mapper resource="EmployeeMapper.xml"/>

Test

public class MyBatisTest {
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    /**
     * 测试增删改：
     * 1、mybatis允许增删改查直接定义以下类型的返回值
     *              Integer、Long、Boolean、void
     * 2、需要手动提交数据
     *                  sqlSessionFactory.openSession();
     *                  sqlSessionFactory.openSession(true);
     * @throws IOException
     */
    @Test
    public void testCRUD() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //1、获取到的SqlSession不会自动提交数据
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);

            //测试添加
         	 Employee employee = new Employee(null,"rwy","374761727@qq.com","1");
           mapper.addEmp(employee);
           System.out.println(employee.getId());

            
        }finally {
            openSession.close();
        }
    }

（3）、update

EmployeeMapper.xml

<!--    更新：public void updateEmp(Employee employee);-->
   <update id="updateEmp" parameterType="com.ban.bean.Employee">
       update tbl_employee set last_name=#{lastName},email=#{email},gender=#{gender} where id=#{id}
   </update>

EmployeeMapper接口

public Long updateEmp(Employee employee);

Test文件见InsertTest中MybatisTest

（4）、delete

EmplyeeMapper.xml

<!-- 删除:public void deleteEmpById(Integer id);  -->
   <delete id="deleteEmpById">
       delete from tbl_employee where id=#{id}
   </delete>

EmployeeMapper接口

public boolean deleteEmpById(Integer id);

Test文件见InsertTest中MybatisTest

多个参数的测试（查询员工表中id与lastName）

<!--    多个参数的测试-->
    <select id="getEmpByIdAndLastName" resultType="com.ban.bean.Employee">
        select * from tbl_employee where id = #{id} and last_name = #{lastName}
    </select>

EmployeeMapper接口

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

Test方法

public class MyBatisTest {
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }
    @Test
    public void testCRUD() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //1、获取到的SqlSession不会自动提交数据
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            //测试多个参数
            Employee rwy = mapper.getEmpByIdAndLastName(3, "rwy");
            System.out.println(rwy);
            openSession.commit();
        }finally {
            openSession.close();
        }
    }

（5）、通过Map进行查找（key-value键值对）

EmployeeMapper.xml

    <select id="getEmpByMap" resultType="com.ban.bean.Employee">
        select * from ${tableName} where id = #{id} and last_name = #{lastName}
    </select>

EmployeeMapper接口

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

Test方法

  @Test
    public void testMap() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            HashMap<String, Object> map = new HashMap<>();
            map.put("id",1);
            map.put("lastName","liuliului");
            map.put("tableName","tbl_employee");
            Employee empByMap = mapper.getEmpByMap(map);
            System.out.println(empByMap);
        }finally {
            openSession.close();
        }
    }

（6）、返回集合元素（使用List进行定义）

EmployeeMapper接口

    public List<Employee> getEmpByLastNameLike(String lastName);

EmployeeMapper.xml

<!--  如果返回的是一个集合，需要写集合中元素的类型  -->
    <select id="getEmpByLastNameLike" resultType="com.ban.bean.Employee">
        select * from tbl_employee where last_name like #{lastName}
    </select>

Test

@Test
    public void testLikeList() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            List<Employee> empByLastNameLike = mapper.getEmpByLastNameLike("%u%");
            for (Employee employee:empByLastNameLike){
                System.out.println(employee);
            }
        }finally {
            openSession.close();
        }
    }

（7）、Map封装测试（一条记录，进行查找操作，通过给定的id值）

    //返回一条记录的map：key就是列名，值就是对应的值
    public Map<String,Object> getEmpByIdReturnMap(Integer id);

EmployeeMapper.xml

<!--    map封装测试-->
    <select id="getEmpByIdReturnMap" resultType="map">
        select * from tbl_employee where id = #{id}
    </select>

Test

@Test
    public void testReturnMap() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            Map<String, Object> empByIdReturnMap = mapper.getEmpByIdReturnMap(1);
            System.out.println(empByIdReturnMap);
        }finally {
            openSession.close();
        }
    }

（8）、Map封装测试（多条记录，通过Sql中的like及通配符进行多条记录查找）

    //多条记录封装一个map，Map<Integer,Employee>：键是这条记录的主键，值是记录封装哦呼的javabean
    //告诉mybatis封装这个map的时候使用那些方面作为主键的key
    @MapKey("id")
    public Map<Integer,Employee> getEmpByLastNameLikeReturnMap(String lastName);

Employee.xml

    <select id="getEmpByLastNameLikeReturnMap" resultType="com.ban.bean.Employee">
        select * from tbl_employee where last_name like #{lastName}
    </select>

Test

    @Test
    public void testMapByLastNameAndAnnotation() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            Map<Integer, Employee> empByLastNameLikeReturnMap = mapper.getEmpByLastNameLikeReturnMap("%u%");
            System.out.println(empByLastNameLikeReturnMap);
        }finally {
            openSession.close();
        }
    }
//结果：{1=Employee{id=1, lastName='liuliului', email='374761727@qq.com', gender='1'}, 5=Employee{id=5, lastName='112uu5', email='21312321321@', gender='1'}}

4、级联查询（sql）

EmployeeMapperPlus接口

    public Employee getEmpById(Integer id);

EmployeeMapperPlus.xml

<mapper namespace="com.ban.dao.EmployeeMapperPlus">
    <resultMap id="MySimpleEmp" type="com.ban.bean.Employee">
<!--        指定列的的封装规则
            id：定义主键底层会有优化
            column：指定哪一列
            property：指定对应的javaBean属性，即Employee（Bean）类中的属性
-->
        <id column="id" property="id"/>
<!--        定义普通的列的规则-->
        <result column="last_name" property="lastName"/>
<!--        其他的不指定的列会自动封装，我们写resultMap就需要把全部的映射规则都写出来-->
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
    </resultMap>

    <select id="getEmpById" resultMap="MySimpleEmp">
        select * from tbl_employee where id = #{id}
    </select>

Test

		@Test
    public void testEmployeeMapperPlue() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
            Employee empById = mapper.getEmpById(1);
            System.out.println(empById);
        }finally {
            openSession.close();
        }
    }

tips：不要忘了在mybatis-config中进行配置xml文件

5、级联查询（association）

    public Employee getEmpAndDept(Integer id);

EmployeeMapperPlus.xml

<!--    第二种方法：association可以指定联合的javaBean对象
property="dept"：指定哪个属性是联合的对象
javaType：指定这个属性对象的类型【不能省略】
必须指定全部属性，不可封装
-->
    <resultMap id="MyPlusemp1" type="com.ban.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
        <association property="dept" javaType="com.ban.bean.department" >
            <id column="d_id" property="id"/>
            <result column="dept_name" property="departmentName"/>
        </association>
    </resultMap>

<!--    public Employee getEmpAndDept(Integer id);-->
    <select id="getEmpAndDept" resultMap="MyPlusemp1">
        SELECT *
        FROM tbl_employee e JOIN tbl_dept d
        ON e.d_id=d.id WHERE e.id=1
    </select>

Test

    @Test
    public void testJoinOutKeyAndMap() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
            Employee empAndDept = mapper.getEmpAndDept(1);
            System.out.println(empAndDept);
            System.out.println(empAndDept.getDept());

        }finally {
            openSession.close();
        }
    }
//结果：Employee{id=1, lastName='liuliului', email='374761727@qq.com', gender='1'}
//			department{id=2, departmentName='测试部'}

分步查询（Step）

    public Employee getEmpByIdStep(Integer id);

DepartmentMapper接口

    public department getDeptById(Integer id);

EmployeeMapperPlus.xml

    <!--public Employee getEmpByIdStep(Integer id);-->
    <select id="getEmpByIdStep" resultMap="MyEmpByStep">
        select * from tbl_employee where id = #{id}
    </select>

<!--************************************************************-->
<!--    使用association进行分步查询 1、先按照员工id查询员工信息  2、 根据查询员工信息中的d_id值去查出部门信息 3、将查出来的部门调用到员工里-->
    <resultMap id="MyEmpByStep" type="com.ban.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>

<!--        association定义关联对象的规则
            select：表明当前属性是调用select指定的方法查出来的结果
            column：指定将哪一列的值传给这个方法
            流程：使用select指定的方法（传入 column指定的这列参数的值）查出对象，并封装给property指定的属性-->
        <association property="dept" select="com.ban.dao.DepartmentMapper.getDeptById"
                     column="d_id"/>
    </resultMap>
<!--************************************************************-->

departmentMapper.xml

<mapper namespace="com.ban.dao.DepartmentMapper">
<!--    public department getDeptById(Integer id); -->
    <select id="getDeptById" resultType="com.ban.bean.department">
        select id,dept_name departmentName from tbl_dept where id = #{id}
    </select>

Tips:其中select作为连接两个Sql语句的关键

Test

    @Test
    public void testEmpByIdStep() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
            Employee empByIdStep = mapper.getEmpByIdStep(1);
            System.out.println(empByIdStep);
            System.out.println(empByIdStep.getDept());
        }finally {
            openSession.close();
        }
    }
//结果：Employee{id=1, lastName='liuliului', email='374761727@qq.com', gender='1'}
//		department{id=2, departmentName='测试部'}

6、collection集合(Collection)

获取部门id中的所有员工

Employee

package com.ban.bean;

public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private String gender;
    private department dept;
}

department

package com.ban.bean;

import org.omg.CosNaming.NamingContextExtPackage.StringNameHelper;

import java.util.List;

public class department {
    private Integer id;
    private String departmentName;
    private List<Employee> emps
}

以上两个类均有GetAndSetter、Constrocture、ToString方法

departmentMapper接口

    public department getDeptByIdPlus(Integer id);

departmentMapper.xml

<!--public class department {
    private Integer id;
    private String departmentName;
    private List<Employee> emps; -->
<!--    采用嵌套结果集的方式，使用Collection标签定义关联的集合类型的属性封装规则-->
    <resultMap id="MyDeptByIdPlus" type="com.ban.bean.department">
        <id column="did" property="id"/>
        <result column="dept_name" property="departmentName"/>
        <collection property="emps" ofType="com.ban.bean.Employee">
            <id column="e_id" property="id"/>
            <result column="last_name" property="lastName"/>
            <result column="email" property="email"/>
            <result column="gender" property="gender"/>
        </collection>
    </resultMap>

<!--    public department getDeptByIdPlus(Integer id);-->
    <select id="getDeptByIdPlus" resultMap="MyDeptByIdPlus">
        SELECT d.id did,d.dept_name dept_name,e.id e_id,e.last_name last_name,e.email email,e.gender gender
        FROM tbl_dept d
                 INNER JOIN tbl_employee e
                            On d.id=e.d_id
        WHERE d.id=1
    </select>

Test

    @Test
    public void testEmpByIdPlus() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
            department deptByIdPlus = mapper.getDeptByIdPlus(1);
            System.out.println(deptByIdPlus);
            System.out.println(deptByIdPlus.getEmps());
        }finally {
            openSession.close();
        }
    }

分步查询（Step）

Employee和departmentBean类在上面有所体现不再赘述

EmployeeMapper接口

public List<Employee> getEmpByDeptIdStep(Integer id);

departmentMapper接口

    public department getDeptByIdPlusStep(Integer id);

EmployeeMapperPlus.xml

<!--    public List<Employee> getEmpByDeptIdStep(Integer id);-->
    <select id="getEmpByDeptIdStep" resultType="com.ban.bean.Employee">
        select * from tbl_employee where d_id = #{deptId}
    </select>

departmentMapper.xml

<!--    public department getDeptByIdPlusStep(Integer id);-->
    <select id="getDeptByIdPlusStep" resultMap="getByIdStepMap" >
        select id,dept_name from tbl_dept where id = #{id}
    </select>

<!--fetchType='lazy'表示使用延迟加载
        eager表示立即-->
    <resultMap id="getByIdStepMap" type="com.ban.bean.department">
        <id column="id" property="id"/>
        <result column="dept_name" property="departmentName"/>
        <collection property="emps" column="{deptId=id}" select="com.ban.dao.EmployeeMapperPlus.getEmpByDeptIdStep"
        fetchType="lazy"/>
    </resultMap>

Test

    @Test
    public void testEmpByIdPlusStep() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
            department deptByIdPlusStep = mapper.getDeptByIdPlusStep(1);
            System.out.println(deptByIdPlusStep);
            System.out.println(deptByIdPlusStep.getEmps());
        }finally {
            openSession.close();
        }
    }
    //结果：department{id=1, departmentName='开发部'}
//[Employee{id=3, lastName='rwy', email='374761727@qq.com', gender='1'}, Employee{id=5, lastName='112uu5', //email='21312321321@', gender='1'}]

二、动态SQL

动态SQL是Mybatis的强大特性之一，如果你使用过JDBC或其他不同条件拼接SQL语句很痛苦，例如拼接时要确保不能添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态SQL，可以彻底摆脱这种痛苦

动态SQL中主要分为以下方法

if
choose（when，otherwise）
trim（where，set）
foreach

1、if

EmployeeMapperDynamicSQL接口

public interface EmployeeMapperDynamicSQL {
    public List<Employee> getEmpsByConditionIf(Employee employee);
}

动态SQL的xml配置文件

<!--查询员工，要求：携带了哪个字段查询条件就带上这个字段的值-->
<!--    public List<Employee> getEmpsByConditionIf(Employee employee);-->
<!--    这是如果id没有值或为null时，语句拼接就容易出现where后紧跟and 导致SQL语法错误
我们使用两种方法来解决这个问题：
                1、where后紧跟1=1，以后每一个条件哪均加入and
                2、mybatis使用where标签来将所有的查询条件包括在内，mybatis就会将where标签中拼装的SQL多出来的and或or去掉
                （只会去掉第一个多出来的and或or）
-->
<!--    if：-->
    <select id="getEmpsByConditionIf" resultType="com.ban.bean.Employee">
        select * from tbl_employee
        where 1=1
        <if test="id!=null">
            and id=#{id}
        </if>
        <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
            and last_name like #{lastName}
        </if>
        <if test="email!=null and email.trim()!=&quot;&quot;">
            and email = #{email}
        </if>
        <if test="gender == 0 or gender == 1 ">
            and gender= #{gender}
        </if>
    </select>

Test测试类

public class DynamicSqlTest {
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }
    @Test
    public void testIf() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(1,"%u%",null,"1");
            List<Employee> empsByConditionIf = mapper.getEmpsByConditionIf(employee);
            System.out.println(empsByConditionIf);
        }finally {
            openSession.close();
        }
    }

2、choose、when、otherwise

public List<Employee> getEmpsByConditionChoose(Employee employee);

动态SQL的配置文件xml

<!--        public List<Employee> getEmpsByConditionChoose(Employee employee);-->
<!--    choose：如果带了id就用id查，有lastName就用lastName查，只进入其中一个-->
    <select id="getEmpsByConditionChoose" resultType="com.ban.bean.Employee">
        select * from tbl_employee
        <where>
            <choose>
                <when test="id!=null">
                    id=#{id}
                </when>
                <when test="lastName!=null">
                    lastName=#{lastName}
                </when>
                <when test="email!=null">
                    email = #{email}
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
        </where>
    </select>

3、trim、where、set

public List<Employee> getEmpsByConditionTrim(Employee employee);

动态SQL的配置文件XML

<!--  Trim：  getEmpsByConditionTrim-->
<!--     prefix=""                  前缀，trim标签体中是整个字符串拼串后的结果，给拼串后的整个字符串加一个前缀
         prefixOverrides=""         前缀覆盖，去掉整个字符串前面多余的字符
         suffix=""                  后缀，suffix给拼串后的整个字符串加一个后缀
         suffixOverrides=""         后缀覆盖，去掉整个字符串后面多余的字符-->
    <select id="getEmpsByConditionTrim" resultType="com.ban.bean.Employee">
        select * from tbl_employee
        <trim suffix="where" suffixOverrides="and">
            <if test="id!=null">
                 id=#{id} and
            </if>
            <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
                 last_name like #{lastName} and
            </if>
            <if test="email!=null and email.trim()!=&quot;&quot;">
                 email = #{email} and
            </if>
            <if test="gender == 0 or gender == 1 ">
                 gender= #{gender} and
            </if>
        </trim>
    </select>

whereAndSet

public void updateEmp(Employee employee);

动态SQL的配置文件XML

<!--    利用可选字段进行更新方法-->
<!--        public void updateEmp(Employee employee);-->
<!--    也可以使用Trim标签，set为前缀，","为后缀重复，不使用Set-->
        <update id="updateEmp">
        update tbl_employee
        <set>
            <if test="lastName!=null">
                last_Name=#{lastName},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
            <if test="gender!=null">
                gender=#{gender}
            </if>
        </set>
             where id=#{id}
        </update>

Test

    @Test
    public void testUpdateSet() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(1,"Tmo","122@qq.com",null);
            mapper.updateEmp(employee);
            openSession.commit();
        }finally {
            openSession.close();
        }
    }

4、Foreach

    public List<Employee> getEmpsByConditionForeach(@Param("ids") List<Integer> ids);

动态SQL的xml配置文件

<!--    public List<Employee> getEmpsByConditionForeach(List<Integer> ids);-->
<!--    select * from tbl_employee where id in {1,2,3}-->
<!--    collection:指定需要遍历的集合，list类型的参数会特殊处理封装在map中，map的key就叫list
        item：将当前遍历出来的元素赋值给指定的变量
        separator：每个元素之间的分隔符
        open：遍历出所有结果拼接一个开始的字符
        close：遍历出所有结果拼接一个结束的字符
        index：索引，遍历list的时候item是索引,item就是当前值；遍历map的时候index表示的就是map的key，item就是map的值
-->
    <select id="getEmpsByConditionForeach" resultType="com.ban.bean.Employee">
        select * from tbl_employee
        <foreach collection="ids" open="where id in(" close=")" separator="," item="item_id">
            #{item_id}
        </foreach>
    </select>

Test

   @Test
    public void testForeach() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            List<Employee> list = mapper.getEmpsByConditionForeach(Arrays.asList(1, 2, 3, 4));
            for (Employee emp:list){
                System.out.println(emp);
            }
        }finally {
            openSession.close();
        }
    }

批量插入，查询

    public void addEmps(@Param("emps") List<Employee> emps);

动态SQL的配置文件xml

<!--    批量插入｜查询-->
<!--     public void addEmps(@Param("emps") List<Employee> emps);-->
    <insert id="addEmps">
        insert into tbl_employee(last_name,email,gender,d_id)
        values
            <foreach collection="emps" separator="," item="emp">
                (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
            </foreach>
    </insert>

Test文件（其中根据插入的要求在bean中新增了控制器方法，此处不显式指出）

    @Test
    public void testAddForeach() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            ArrayList<Employee> emps = new ArrayList<>();
            emps.add(new Employee(null,"jerry","3@qq.com","0",new department(1)));
            emps.add(new Employee(null,"haapp","4@qq.com","1",new department(2)));
            mapper.addEmps(emps);
            openSession.commit();
        }finally {
            openSession.close();
        }
    }

三、缓存

共分为两级缓存

一级缓存（本地缓存）
二级缓存（全局缓存）

1、一级缓存

sqlSession级别的缓存，一级缓存是一直开启的

与数据库同一次会话期间的数据会存放在本地缓存中，以后如果再次需要获取相同的数据，直接从缓存中取出即可，不需要JDBC再次查询数据库

    @Test
    public void testCacheEqual1() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            //发送了一次Sql请求
            Employee empById1 = mapper.getEmpById(1);
            System.out.println(empById1);//Employee{id=1, lastName='Tmo', email='122@qq.com', gender='1'}
            Employee empById2 = mapper.getEmpById(1);
            System.out.println(empById2);//Employee{id=1, lastName='Tmo', email='122@qq.com', gender='1'}
            System.out.println(empById1==empById2);//true
        }finally {
            openSession.close();
        }
    }