Mybatis-3.5.10

环境准备

IDEA-2022.3
Mybatis-2.5.10
JUnit-4.13.2
MySQL-8.0.30
JDK-java17
Logback-1.2.11
Lombok-1.18.24

库表准备

创建数据库

1	CREATE DATABASE `mybatis`;

创建表结构

create table tb_car
(
    id           bigint not null auto_increment comment '主键自增',
    car_num      varchar(255) comment '汽车编号',
    brand        varchar(255) comment '汽车品牌',
    guide_price  decimal(10) comment '厂家指导价',
    produce_time char(10) comment '生产日期',
    car_type     varchar(255) comment '汽车类型,包括: 燃油车、电车、氢能源',
    primary key (id)
);

添加测试数据

1 2	INSERT INTO `mybatis`.`tb_car` VALUES (1, '1001', '宝马510Li', 1000, '2020-11-11', '燃油车'); INSERT INTO `mybatis`.`tb_car` VALUES ('1001', '宝马510Li', 1000, '2020-11-11', '燃油车')

获取`SQL` 语句

起步

创建maven 项目

配置IDEA 编译环境

快捷键: `ctrl+alt+shift+s`	快捷键:`ctrl + alt + s`

添加依赖

<!-- 编译环境 -->
<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.encoding>UTF-8</maven.compiler.encoding>
    <java.version>17</java.version>
    <maven.compiler.source>17</maven.compiler.source>
    <maven.compiler.target>17</maven.compiler.target>
</properties>
<!-- 打包方式 -->
<packaging>jar</packaging>
<dependencies>
    <!-- mybatis -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.10</version>
    </dependency>
    <!-- mysql 驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.30</version>
    </dependency>
    <!-- 简化实体类 -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.24</version>
    </dependency>
    <!-- 测试 -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13.2</version>
        <scope>test</scope>
    </dependency>
</dependencies>

在resources 下创建mybatis 的核心配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://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"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!-- 配置 Mybatis 映射文件位置 -->
        <mapper resource="com/example/mapper/CarMapper.xml"/>
    </mappers>
</configuration>

在resources 下创建映射文件

<?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.example.mapper.CarMapper">
    <insert id="insertCar">
        insert into tb_car(id, car_num, brand, guide_price, produce_time, car_type)
        values (null, '1003', '丰田', 30.0, '2000-10-11', '燃油车')
    </insert>
</mapper>

实体类

package com.example.domain;

import lombok.Data;

import java.math.BigDecimal;

@Data
public class Car {
    private Long id;
    private String car_num;
    private String brand;
    // decimal(mysql) => java.math.BigDecimal(java)
    private BigDecimal guide_price;
    private String produce_time;
    private String car_type;
}

dao/mapper

package com.example.mapper;

import com.example.domain.Car;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface CarMapper {
    int insertCar(Car car);
}

目录结构

目录结构

测试

package com.example.test;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class TestFirstMybatis {
    public static void main(String[] args) throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory build = sqlSessionFactoryBuilder.build(is);
        // true: 自动提交事务
        SqlSession sqlSession = build.openSession(true);
        // 返回结果是成功后影响的行数
        int count = sqlSession.insert("insertCar");
    }
}

完整的测试

package com.example.test;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class TestFirstMybatis {
    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            SqlSessionFactory build = sqlSessionFactoryBuilder.build(is);
            sqlSession = build.openSession();
            // 业务处理
            int count = sqlSession.insert("insertCar");
            // 正常的情况下,提交事务
            sqlSession.commit();
        } catch (IOException e) {
            if (sqlSession != null) {
                // 事务回滚
                sqlSession.rollback();
            }
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
    }
}

关于 mybatis 的事务管理机制

在mybatis-config.xml 文件中，可以通过以下的配置进行mybatis 的事务管理

1	<transactionManager type="JDBC"/>

type 属性值取值包括两个，不区分大小写
1. JDBC
2. MANAGED

在mybatis 中提供了两种事务管理机制

第一种: JDBC 事务管理机制

1 2	// 底层实际上会执行: conn.setAutoCommit(false); SqlSession sqlSession = build.openSession();

mybatis 框架自己管理事务，自己采用原生的JDBC 代码去管理事务

// 开启事务
conn.setAutoCommit(false);
... 业务 ...
// 手动提交事务
conn.commit();

第二种: MANAGED 事务管理机制

1
2
3

// 底层实际上会执行: conn.commit();
SqlSession sqlSession = build.openSession();
// 如果单纯的使用 mybatis,配置为 MANAGED,那么此时，事务是没有人处理的

Mybatis-集成日志框架

settings 配置顺序的约束

`configuration` 标签下的添加顺序

在mybatis-config.xml 中添加日志配置

日志组件
- SLF4J | LOG4J | LOG4J2 | JDK_LOGGING | COMMONS_LOGGING | STDOUT_LOGGING | NO_LOGGING

mybatis 中已经对标准日志做了实现，是需要添加配置即可实现日志控制

<settings>
    <!-- 输出过于简单 -->
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

集成loggback+slfj

<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.2.11</version>
</dependency>

1
2
3

<settings>
    <setting name="logImpl" value="SLF4J"/>
</settings>

<?xml version="1.0" encoding="UTF-8"?>
<!-- 名称必须为: resources/logback.xml -->
<configuration debug="false">
    <!-- 控制台输出 -->
    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
        </encoder>
    </appender>
    <!-- 按照每天生成日志文件 -->
    <appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <!--日志文件输出的文件名-->
            <FileNamePattern>${LOG_HOME}/TestWeb.log.%d{yyyy-MM-dd}.log</FileNamePattern>
            <!--日志文件保留天数-->
            <MaxHistory>30</MaxHistory>
        </rollingPolicy>
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
        </encoder>
        <!--日志文件最大的大小-->
        <triggeringPolicy class="ch.qos.logback.core.rolling.SizeBasedTriggeringPolicy">
            <MaxFileSize>100MB</MaxFileSize>
        </triggeringPolicy>
    </appender>

    <!--mybatis log configure-->
    <logger name="com.apache.ibatis" level="TRACE"/>
    <logger name="java.sql.Connection" level="DEBUG"/>
    <logger name="java.sql.Statement" level="DEBUG"/>
    <logger name="java.sql.PreparedStatement" level="DEBUG"/>

    
    <root level="DEBUG">
        <appender-ref ref="STDOUT"/>
        <appender-ref ref="FILE"/>
    </root>

</configuration>

输出更加详细

工具类

创建工具类

package com.example.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;

public class SqlSessionUtil {
    private static SqlSessionFactory sqlSessionFactory;
    
    // 类加载时执行
    // SqlSessionUtils 工具类在进行第一次加载的时候，解析 spring-config.xml,创建 SqlSessionFactory 对象
    static {
        try {
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    
    /**
     * 工具类的构造方法一般都是私有化的
     * 工具类中所有的方法都是静态的，直接采用类名即可调用。不需要 new 对象
     * 为了防止 new 对象，构造方法私有化
     */
    private SqlSessionUtil() {
    }
    
    public static SqlSession openSession() {
        return sqlSessionFactory.openSession();
    }
}

CRUD

使用map 完成传参

@Test
public void testCarMapClass() {
    // insert into tb_car(id, car_num, brand, guide_price, produce_time, car_type) values (#{k1}, #{k2}, #{k3}, #{k4}, #{k5}, #{k6})
    Map<String, Object> map = new HashMap<>();
    map.put("k1", null);
    map.put("k2", "1301");
    map.put("k3", "1301");
    map.put("k4", 1301.31);
    map.put("k5", "2020-10-11");
    map.put("k6", "1301");
    int insertCarByMap = sqlSession.insert("insertCarMapKey", map);
    System.out.println(insertCarByMap);
    sqlSession.commit();
}
// #{k1} 中括号内容是 map 的 key，如果传递不存在的key,底层调用 对应的 get 方法后会赋值为 null,这样插入数据后该位置将会显示 null

执行日志

POJO(实体类)传参

@Data
public class Car {
    private Long id;
    private String car_num;
    private String brand;
    // decimal(mysql) => java.math.BigDecimal(java)
    private BigDecimal guide_price;
    private String produce_time;
    private String car_type;
}

1	// 注意: 占位符 #{},大括号里面写: pojo 类的属性名(实际是 pojo类的属性的getXxx 方法去掉 get,并将首字母小写后的词)

删除

需求: 根据 id 删除

<!-- int deleteById(Long id); -->
    <delete id="deleteById" parameterType="long">
    delete
    from tb_car
    where id = #{id}
</delete>

@Test
public void deleteById() {
    int deleteById = sqlSession.delete("deleteById", 2l);
    sqlSession.commit();
}

删除执行日志

多参数

<!-- int selectCarByIdAndBrand(Long id, String brand); -->
<select id="selectCarByIdAndBrand" resultType="int">
    select *
    from tb_car
    where id = #{id}
    and brand = #{brand}
</select>

多参数直接传递时，会出现如下问题	此时在`mapper` 定义时使用`@Param` 注解命名进行绑定

查询专题

返回一个实体对象
返回List
返回单值

返回Map

<!--     Map<String, Object> selectByIdRetMap(Long id);  -->
<select id="selectByIdRetMap" parameterType="long" resultType="map">
    select *
    from tb_car
    where id = #{id}
</select>

`map的key就是字段`

@Test
public void testSelectByIdRetMap() {
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    Map<String, Object> map = mapper.selectByIdRetMap(1L);
    System.out.println(map);
}

返回``List<Map<String,Object>> `

<!-- List<Map<String,Object>> selectRetListMap(String brand); -->
<select id="selectRetListMap" resultType="map">
    select *
    from tb_car
    where brand like "%"#{brand}"%"
</select>

注意: `List<Map<String,Object>>` 的返回值类型是`map` 类型

返回一个大Map

/**
     * 查询所有的 Car,返回一个大 Map 集合
     * Map 集合的 key 是每条记录的主键值
     * Map 集合的 value 是每条记录
     * @MapKey("id") id: 是表的主键
     */
@MapKey("id")
Map<Long, Map<String, Object>> selectAllReListtMap();

<!-- Map<Long, Map<String, Object>> selectAllReListtMap(); -->
<select id="selectAllReListtMap" resultType="map">
    select *
    from tb_car
</select>

@Test
public void testSelectAllReListtMap() {
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    Map<Long, Map<String, Object>> allReListtMap = mapper.selectAllReListtMap();
    System.out.println(allReListtMap);
}

结果模型

resultMap 结果映射

resultMap

<resultMap id="carResultMap" type="Car">
    
    <id property="id" column="id"/>
    <result property="brand" column="brand"/>
    
    <result property="carNum" column="car_num"/>
    ...
</resultMap>

专门定义一个结果映射,在这个结果映射当中指定数据库表的字段名和Java 类的属性名的对应关系
type 属性: 用来指定实体类的类名
id 属性: 指定resultMap 的唯一标识.这个 ID 将来要用在 select 标签中

<select id="selectAllReListtMap" resultMap="carResultMap">
    select *
    from tb_car
</select>

是否开启驼峰命名自动映射

使用前提

属性名遵循Java 的命名规范，数据库的列名遵循SQL 的命名规范

Java 命名规范: 首字母小写,后面每个单次首字母大写，遵循驼峰命名方式
SQL 命名规范: 全部小写,单词之间采用下划线分割

Eg:

实体类中的属性名	数据库的列名
`carNum`	`car_num`

启动自动转换，只需要在mybatis 的主(mybatis-config.xml)配置文件中配置如下

1
2
3

<settings>
     <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

动态SQL

IF: 多条件查询

if 标签中 test 属性是必须的
if 标签中test 属性的值是boolean 类型
如果test 是true，则if 标签中的SQL 语句就会拼接。反之，则不会拼接
test 属性中可以使用的是
- 当使用了@Param 注解，那么test 中要出现的是@Param 注解指定的参数名，Eg: @Param("brand"),那么此时应该为: test="brand!=null"
- 当没有使用@Param 注解，那么test 中要出现的是param1,param2,...,arg0,...
- 当使用了实体类，那么test 中出现的是实体类的属性名
在mybatis 的动态SQL 当中，不能使用&&，只能使用and

<!-- List<Car> selectByMultiCondition(@Param("brand") String brand, @Param("guidePrice") double guidePrice, @Param("carType") String carType); -->
<select id="selectByMultiCondition" resultType="Car">
    select * from tb_car
    <where>
        <if test="brand!=null and brand!=''">
            brand like "%"#{brand}"%"
        </if>
        <if test="guidePrice!=null and guidePrice!=''">
            and guide_price=#{guidePrice}
        </if>
        <if test="carType!=null and carType!=''">
            and car_type like "%"#{carType}"%"
        </if>
    </where>
</select>

// 测试
@Test
public void testSelectByMultiCondition() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    // 记录: 20	 1301	1301	1301	2020-10-11	1301
    List<Car> cars = mapper.selectByMultiCondition("宝马510Li", null, null);
    cars.forEach(car -> System.out.println(car));
    sqlSession.close();
}

`SQL` 拼接情况

`List<Car> cars = mapper.selectByMultiCondition("1301", null, "1301");`

全为空则查询所有

WHERE

问题

`where` 关键字出现的问题

where 标签的作用: 让where 子句更加动态智能
- 所有条件为空时，where 标签保证不会生成where 子句
- 自动去除某些条件前面多余的and/or

Trim

trim 属性
- prefix: 在trim 标签中的语句前添加内容
- suffix: 在trim 标签语句后添加内容
- prefixOverrides: 前缀覆盖掉(去掉)
- sufferOverrides: 后缀覆盖掉(去掉)

Set

主要使用在update 语句中，用来生成set 关键字，同时去掉最后多余的,

比如我们只更新提交的不为空的字段，如果提交的数据是空或者" "，那么这个字段我们将不更新

<!-- int updateCarById(Car car); -->
<update id="updateCarById">
    update tb_car
    <set>
        <if test="carNum!=null and carNum!=''">
            car_num=#{carNum}
        </if>
    </set>
    where id=#{id}
</update>

// 测试
@Test
public void testUpdateCarById() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    Car car = new Car(21L, "1401");
    mapper.updateCarById(car);
    sqlSession.close();
}

`Set`

<!-- int updateCarById(Car car); -->
<update id="updateCarById">
    update tb_car
    <set>
        <if test="carNum!=null and carNum!=''">
            car_num=#{carNum}
        </if>
        <if test="brand!=null and brand!=''">
            brand=#{brand}
        </if>
    </set>
    where id=#{id}
</update>

注意事务提交

Choose

语法

<choose>
    <when test=""></when>
    <when test=""></when>
    <otherwise></otherwise>
</choose>

// 等同于 java if 多分支
if (1 > 0) {
    // xxx
} else if (1 > 0) {
     // xxx;
} else if (1 > 0) {
   // xxx
} else {
    // xxx
}

需求: 先根据品牌查询，如果没有提供品牌，再根据指导价格查询，如果没有提供指导价格，就根据生产日期查询

实现

<select id="selectByChoose" resultType="Car">
    select * from tb_car
    <where>
        <choose>
            <when test="brand!=null and brand!=''">
                and brand=#{brand}
            </when>
            <when test="guidePrice!=null and guidePrice!=''">
                guide_price=#{guidePrice}
            </when>
            <otherwise>
                produce_time=#{produceTime}
            </otherwise>
        </choose>
    </where>
</select>

// 测试一:
List<Car> cars = mapper.selectByChoose("1301", null, null);

// 测试二
List<Car> cars = mapper.selectByChoose(null, 1302.0, null);

动态选择条件	动态选择条件

Foreach

批量删除

<!--
        int deleteByIds(@Param("ids") Long[] ids);
        foreach 标签的属性:
            collection: 指定数组或者集合
            item: 代表数组或集合中的元素
            separator: 循环之间的分隔符
     -->
<delete id="deleteByIds">
    delete from tb_car where id in
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</delete>

CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Long[] ids = {20L, 21L};
int count = mapper.deleteByIds(ids);
System.out.println(count);

`in`

批量插入

<!--
        int insertBatch(@Param("cars") List<Car> cars);
    -->
<insert id="insertBatch">
    insert into tb_car values
    <foreach collection="cars" item="car" separator=",">
        (null,#{car.carNum},#{car.brand},#{car.guidePrice},#{car.produceTime},#{car.carType})
    </foreach>
</insert>

@Test
public void testInsertBatch() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    List<Car> list = new ArrayList<>();
    Collections.addAll(list, new Car(null, "10086", "10086", 100.8, "2021-11-21", "氢能源"), new Car(null, "10087", "10087", 110.8, "2026-11-21", "氢能源"), new Car(null, "10088", "10086", 120.8, "2060-11-21", "氢能源"));
    int count = mapper.insertBatch(list);
    System.out.println(count);
    sqlSession.commit();
    sqlSession.close();
}

批量插入

Mybatis的高级映射及延迟加载

环境准备

创建表

create table tb_clazz
(
    id    int not null primary key,
    cname varchar(255)
);

insert into  tb_clazz values (1001,'高三一班');
insert into  tb_clazz values (1002,'高三二班');

create table tb_student
(
    sid   int not null primary key,
    sname varchar(255),
    cid   int
);

insert into tb_student values (1,'张三',1001);
insert into tb_student values (2,'李四',1002);
insert into tb_student values (3,'王五',1001);
insert into tb_student values (4,'赵六',1002);
insert into tb_student values (5,'钱七',1002);

逆向

创建实体类

Clazz

@Data
@AllArgsConstructor
public class Clazz {
    private Integer id;
    private String cname;
}

Student

@Data
@AllArgsConstructor
public class Student {
    private Integer sid;
    private String sname;
    // cid: 维护关系的
}

主副表的确认

多对一: 多在前，主表为多

一对多: 一在前，主表为一

关联关系

多对一

@Data
@AllArgsConstructor
public class Student {
    private Integer sid;
    private String sname;
    // cid: 维护关系的
    private Clazz clazz;
}

映射关系

第一种: 一条SQL 语句,级联映射

@Mapper
public interface StudentMapper {
    /**
     * 根据 id 获取学生信息.同时获取学生关联的班级信息
     *
     * @param id 学生的id
     * @return 学生对象, 但是学生对象中含有班级对象
     */
    Student selectById(@Param("id") Integer id);
}

<resultMap id="studentResultMap" type="Student">
    <id property="sid" column="sid"/>
    <result property="sname" column="sname"/>
    <result property="clazz.id" column="id"/>
    <result property="clazz.cname" column="cname"/>
</resultMap>

<!-- Student selectById(Integer id); -->
<select id="selectById" resultMap="studentResultMap">
    SELECT s.sid, s.sname, c.id, c.cname
    FROM tb_student s
    LEFT JOIN tb_clazz c ON s.cid = c.id
    WHERE s.sid = #{id};
</select>

结果

第二种: 一条SQL 语句,assocation

<!--
    association: 翻译为关联。一个 Student 对象关联一个 Clazz 对象
    public Class Student(){
        private Clazz clazz;
    }
        property: 提供要映射的 实体类的属性名 【clazz】
        javaType: 用来指定要映射的 java 类型 【Clazz】
    -->
<resultMap id="studentResultMap" type="Student">
    <id property="sid" column="sid"/>
    <result property="sname" column="sname"/>
    <association property="clazz" javaType="Clazz">
        <id property="id" column="id"/>
        <result property="cname" column="cname"/>
    </association>
</resultMap>

<!-- Student selectById(Integer id); -->
<select id="selectById" resultMap="studentResultMap">
    SELECT s.sid, s.sname, c.id, c.cname
    FROM tb_student s
    LEFT JOIN tb_clazz c ON s.cid = c.id
    WHERE s.sid = #{id};
</select>

第三种: 两条SQL 语句，分布查询

常用第三种
优点
- 可复用
- 支持懒加载

<!-- 主表: StudentMapper.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.example.mapper.StudentMapper">

    <resultMap id="studentResultMap" type="Student">
        <id property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <association property="clazz"
                     column="cid"
                     fetchType="lazy"
                     select="com.example.mapper.ClazzMapper.selectByIdStopTwo">
            <id property="id" column="id"/>
            <result property="cname" column="cname"/>
        </association>
    </resultMap>
    <select id="selectByIdStopOne" resultMap="studentResultMap">
        select sid, sname, cid
        from tb_student
        where sid = #{sid}
    </select>
</mapper>

<!-- 副表: ClazzMapper.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.example.mapper.ClazzMapper">
    <!--
        Clazz selectByIdStopTwo(@Param("cid") Integer cid);
    -->
    <select id="selectByIdStopTwo" resultType="Clazz">
        select *
        from tb_clazz
        where id = #{cid}
    </select>
</mapper>

// 测试分布查询
@Test
public void testStep() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    StudentMapper stuMapper = sqlSession.getMapper(StudentMapper.class);
    Student student = stuMapper.selectByIdStopOne(2);
    System.out.println(student);
    sqlSession.close();
}

延迟加载

// 测试延迟加载
@Test
public void testStep() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    StudentMapper stuMapper = sqlSession.getMapper(StudentMapper.class);
    Student student = stuMapper.selectByIdStopOne(2);
    // TODO: 只获取学生姓名,不涉及第二个表
    System.out.println(student.getSname());
    try {
        System.out.println("数据加载中:.................................");
        Thread.sleep(500);
        // 在一定时间后,进行第二步骤
        String cname = student.getClazz().getCname();
        System.out.println(cname);
    } catch (InterruptedException e) {
        throw new RuntimeException(e);
    }
    sqlSession.close();
}

测试延迟加载

全局配置延迟加载

1 2	<!-- 实际应用 --> <setting name="lazyLoadingEnabled" value="true"/>

一对多

一对多映射关系

// 一对多
// 一: Clazz
// 多: 集合或者数组
List<Student> stuLists;

package com.example.entity;

import lombok.Data;

import java.util.List;

@Data
public class Clazz {
    private Integer id;
    private String cname;
    // 一(Clazz)对多(List)的关系映射
    private List<Student> stus;
}

collection

<!-- ClazzMapper.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.example.mapper.ClazzMapper">
    <resultMap id="clazzCollectionMap" type="Clazz">
        <id property="id" column="id"/>
        <result property="cname" column="cname"/>
        <collection property="stus" ofType="Student">
            <id property="sid" column="sid"/>
            <result property="sname" column="sname"/>
        </collection>
    </resultMap>
    <!-- Class selectByCollection(@Param("cid") Integer cid); -->
    <select id="selectByCollection" resultMap="clazzCollectionMap">
        select c.id, c.cname, s.sid, s.sname
        from tb_clazz c
                 left join tb_student s
                           on c.id = s.cid
        where c.id = #{cid}
    </select>
</mapper>

@Test
public void testCollection() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    ClazzMapper mapper = sqlSession.getMapper(ClazzMapper.class);
    // 查询班级为 1001 的所有学生信息
    Clazz aClazz = mapper.selectByCollection(1001);
    System.out.println(aClazz);
}

输出

分布查询

<!-- ClazzMapper.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.example.mapper.ClazzMapper">
    <resultMap id="selectByStepMap" type="Clazz">
        <id property="id" column="id"/>
        <result property="cname" column="cname"/>
        <collection property="stus" select="com.example.mapper.StudentMapper.selectByStepTwo" column="id"/>
    </resultMap>
    <!-- 主表:  Clazz selectByStepOne(@Param("cid") Integer cid); -->
    <select id="selectByStepOne" resultMap="selectByStepMap">
        select *
        from tb_clazz
        where id = #{id}
    </select>
</mapper>

<!-- StudentMapper.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.example.mapper.StudentMapper">
    <!--     List<Student> selectByStepTwo(@Param("id") Integer cid); -->
    <select id="selectByStepTwo" resultType="student">
        select *
        from tb_student
        where cid = #{id}
    </select>
</mapper>

@Test
public void testStep() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    ClazzMapper mapper = sqlSession.getMapper(ClazzMapper.class);
    Clazz clazz = mapper.selectByStepOne(1001);
    // 触发第一步
    System.out.println(clazz.getId());
    // 触发第二步
    List<Student> stus = clazz.getStus();
    stus.forEach(stu -> System.out.println(stu));
}

分布查询触发

PageHelper

添加依赖

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.3.1</version>
</dependency>

mybatis-config.xml 中配置

1
2
3

<plugins>
    <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>

注意出现位置

测试

@Test
public void testSelectAll() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    // TODO: PageHelper 一定要在执行 DQL 语句之前开发分页功能
    int pageNum = 1;
    int pageSize = 3;
    PageHelper.startPage(pageNum, pageSize);
    List<Student> students = mapper.selectAll();
    students.forEach(stu -> System.out.println(stu));
}

执行

获取分页信息对象

@Test
public void testSelectAll() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    // TODO: PageHelper 一定要在执行 DQL 语句之前开发分页功能
    int pageNum = 1;
    int pageSize = 10;
    PageHelper.startPage(pageNum, pageSize);
    List<Student> students = mapper.selectAll();
    students.forEach(stu -> System.out.println(stu));
    // 获取分页信息对象
    PageInfo<Student> pageInfo = new PageInfo<>(students, 3);
    System.out.println(pageInfo);
}

`PageInfo<Student> pageInfo = new PageInfo<>(students, 3); => 3 Eg: 10`

建议总结

实体类字段建议使用包装类的原因

如果参数赋值为`null`，则出现`java: 不兼容的类型: 无法转换为long`