Mybatis#

约 1299 个字 298 行代码预计阅读时间 36 分钟

JDBC的不足#

SQL语句是写在程序中的，当数据库表结构发生变化，需要修改源码。不符合开闭原则。
prepareStatement需要重复进行参数设置，代码繁琐冗余。
结果集的处理繁琐，需要手动处理。

入门案例#

创建项目，引入maven依赖：

XML

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.luguosong</groupId>
    <artifactId>mybatis-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <!--java连接MySQL-->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>8.3.0</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.15</version>
        </dependency>
        <!--注解生成getter和setter-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.34</version>
            <scope>provided</scope>
        </dependency>
        <!-- 分页插件 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>6.1.0</version>
        </dependency>

        <!--日志输出-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>
</project>

创建核心配置文件：

XML

<?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>
    <settings>
        <!--表示根据驼峰命名法自动匹配结果集-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--开启延迟加载-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>
    <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/learn_mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="12345678"/>
            </dataSource>
        </environment>
    </environments>
    <!--引入映射文件-->
    <mappers>
        <!--<mapper resource="com/luguosong/hello/mapper/EmployeesMapper.xml"/>-->
        <package name="com/luguosong"/>
    </mappers>
</configuration>

编写实体类：

Java

package com.luguosong.hello.pojo;

import lombok.Data;

import java.sql.Date;

/**
 * @author luguosong
 */
@Data
public class Employees {
    private Integer id;
    private String firstName;
    private String lastName;
    private String position;
    private Date hireDate;
    private Integer departmentId;
}

编写Mapper接口：

Java

package com.luguosong.hello.mapper;

import com.luguosong.hello.pojo.Employees;

/**
 * @author luguosong
 */
public interface EmployeesMapper {
    Employees getEmployeesById(Integer id);
}

编写Mapper映射文件：

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace属性指向mapper接口-->
<mapper namespace="com.luguosong.hello.mapper.EmployeesMapper">
    <select id="getEmployeesById" resultType="com.luguosong.hello.pojo.Employees">
        select * from employees where id = #{id}
    </select>
</mapper>

配置log4j日志配置文件：

Properties

# log4j.rootLogger=<LogLevel>, <Appender1>, <Appender2>, ...
# 设置日志记录器的根记录器的级别和多个输出目标
log4j.rootLogger=debug ,toConsole
#
#设置子logger日志记录器
log4j.logger.com.luguosong.child=debug, toConsole
#不让父输出控制器再输出一遍
log4j.additivity.com.luguosong.child=false

# 控制台输出目标
log4j.appender.toConsole=org.apache.log4j.ConsoleAppender
log4j.appender.toConsole.layout=org.apache.log4j.PatternLayout
log4j.appender.toConsole.layout.conversionPattern=%d{ISO8601} %5p %c{1}:%L - %m%n

创建测试类：

Java

package com.luguosong.hello;

import com.luguosong.hello.mapper.EmployeesMapper;
import com.luguosong.hello.pojo.Employees;
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;

/**
 * @author luguosong
 */
public class Test {
    public static void main(String[] args) throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config-hello.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(is);
        // 表示Java程序与数据库之间的会话
        SqlSession sqlSession = factory.openSession();
        // 动态创建Mapper接口对应的对象
        EmployeesMapper mapper = sqlSession.getMapper(EmployeesMapper.class);
        Employees employees = mapper.getEmployeesById(3);
        System.out.println(employees);
    }
}

Mybatis核心配置文件#

事务管理方式#

type="JDBC" JDBC事务管理，mybatis框架自己管理事务,底层使用JDBC
type="MANAGED" 由容器管理事务，例如Spring容器

XML

<configuration>
    <environments default="development">
        <environment id="development">
            <!--事务管理方式-->
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--...-->
            </dataSource>
        </environment>
    </environments>
</configuration>

数据源类型#

type="POOLED" 使用mybatis自带的连接池
type="UNPOOLED" 不使用连接池
type="JNDI" 集成第三方连接池，例如c3p0、druid等

XML

<configuration>
    <environments default="development">
        <environment id="development">
            <!--数据源类型-->
            <dataSource type="POOLED">
                <!--...-->
            </dataSource>
        </environment>
    </environments>
</configuration>

引入外部properties配置文件#

jdbc.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc-url=jdbc-url
username=root
password=123456

XML

<configuration>
    <!--引入外部配置文件-->
    <properties resource="jdbc.properties"/>

    <environments default="development">
        <environment id="development">
            <dataSource type="POOLED">
                <!--获取properties配置-->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

设置类型别名#

核心配置文件中配置类型别名：

XML

<configuration>
    <typeAliases>
        <typeAlias type="com.luguosong.pojo.Car" alias="Car"/>
    </typeAliases>
</configuration>

在Mapper映射配置文件中可以直接使用别名：

XML

<mapper namespace="com.luguosong.mapper.CarMapper">
    <!--resultType可以直接使用别名表示-->
    <select id="selectCarById" resultType="Car">
        select * from t_car where id = #{id}
    </select>
</mapper>

也可以配置package，指定包下的类都以类名作为别名：

XML

<configuration>
    <typeAliases>
        <package name="com.luguosong.pojo"/>
    </typeAliases>
</configuration>

配置映射文件#

方式一：指定每一个xml映射配置文件

XML

<configuration>
    <mappers>
        <mapper resource="com/luguosong/mapper/Mapper1.xml"/>
        <mapper resource="com/luguosong/mapper/Mapper2.xml"/>
        <mapper resource="com/luguosong/mapper/Mapper3.xml"/>
    </mappers>
</configuration>

方式二：指定映射文件目录

Warning

使用目录方式配置Mapper映射，映射文件的包名和文件名需要与Mapper接口保持一致。

XML

<configuration>
    <mappers>
        <package name="com/luguosong/mapper"/>
    </mappers>
</configuration>

参数获取#

字符串拼接#

${xxx}表示采用字符串拼接的方式生成sql语句

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace属性指向mapper接口-->
<mapper namespace="com.luguosong.get_param.string_splicing.mapper.EmployeesMapper">
    <!--Employees getEmployeesById(Integer id);-->
    <select id="getEmployeesById" resultType="com.luguosong.get_param.pojo.Employees">
        select *
        from employees
        where id = ${id}
    </select>

    <!--Employees getEmployeesByName(String firstName);-->
    <!--如果参数是字符串，需要手动加上引号-->
    <select id="getEmployeesByName" resultType="com.luguosong.get_param.pojo.Employees">
        select *
        from employees
        where first_name = '${firstName}'
    </select>
</mapper>

Warning

这种方式会引起sql注入

占位符赋值👍🏻#

#{xxx}表示采用占位符赋值的方式生成sql语句

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace属性指向mapper接口-->
<mapper namespace="com.luguosong.get_param.placeholder.mapper.EmployeesMapper">
    <!--Employees getEmployeesById(Integer id);-->
    <select id="getEmployeesById" resultType="com.luguosong.get_param.pojo.Employees">
        select *
        from employees
        where id = #{id}
    </select>

    <!--Employees getEmployeesByName(String firstName);-->
    <!--如果参数是字符串，需要手动加上引号-->
    <select id="getEmployeesByName" resultType="com.luguosong.get_param.pojo.Employees">
        select *
        from employees
        where first_name = #{firstName}
    </select>
</mapper>

单个参数#

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace属性指向mapper接口-->
<mapper namespace="com.luguosong.get_param.single_parameter.mapper.EmployeesMapper">
    <!--Employees getEmployeesById(Integer id);-->
    <select id="getEmployeesById" resultType="com.luguosong.get_param.pojo.Employees">
        select *
        from employees
        where id = #{id}
    </select>

    <!--Employees getEmployeesByName(String firstName);-->
    <!--当只有一个参数时，#{}中键名可以随便写-->
    <select id="getEmployeesByName" resultType="com.luguosong.get_param.pojo.Employees">
        select *
        from employees
        where first_name = #{aaaa}
    </select>
</mapper>

多个参数传递#

多个参数时，Mybatis会采用默认键名将参数封装到Mapper集合中。

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace属性指向mapper接口-->
<mapper namespace="com.luguosong.get_param.multiple_parameters.mapper.EmployeesMapper">
    <!--Employees getEmployeesByName1(String firstName, String lastName);-->
    <!--可以使用arg0，arg1，arg2...-->
    <select id="getEmployeesByName1" resultType="com.luguosong.get_param.pojo.Employees">
        select *
        from employees
        where first_name = #{arg0}
          and last_name = #{arg1}
    </select>

    <!--Employees getEmployeesByName2(String firstName, String lastName);-->
    <!--也可以使用param0，param1，param2...-->
    <select id="getEmployeesByName2" resultType="com.luguosong.get_param.pojo.Employees">
        select *
        from employees
        where first_name = #{param0}
          and last_name = #{param1}
    </select>
</mapper>

多个参数传递-@Param#

@Param会将参数以指定键名封装进Mapper集合中。

Java

package com.luguosong.get_param.param_annotation.mapper;

import com.luguosong.get_param.pojo.Employees;
import org.apache.ibatis.annotations.Param;

/**
 * @author luguosong
 */
public interface EmployeesMapper {

    Employees getEmployeesByName(@Param("firstName") String firstName, @Param("lastName") String lastName);
}

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace属性指向mapper接口-->
<mapper namespace="com.luguosong.get_param.param_annotation.mapper.EmployeesMapper">
    <!--Employees getEmployeesByName1(@Param("firstName") String firstName, @Param("lastName") String lastName);-->
    <!--可以使用arg0，arg1，arg2...-->
    <select id="getEmployeesByName" resultType="com.luguosong.get_param.pojo.Employees">
        select *
        from employees
        where first_name = #{firstName}
          and last_name = #{lastName}
    </select>
</mapper>

对象和Mapper集合#

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace属性指向mapper接口-->
<mapper namespace="com.luguosong.get_param.object_and_mapper.mapper.EmployeesMapper">
    <!--Employees getEmployeesByName1(Employees employees);-->
    <select id="getEmployeesByName1" resultType="com.luguosong.get_param.pojo.Employees">
        select *
        from employees
        where first_name = #{firstName}
          and last_name = #{lastName}
    </select>

    <!--Employees getEmployeesByName2(HashMap<String, String> searchMap);-->
    <select id="getEmployeesByName2" resultType="com.luguosong.get_param.pojo.Employees">
        select *
        from employees
        where first_name = #{firstName}
          and last_name = #{lastName}
    </select>


</mapper>

模糊查询#

模糊查询不能直接使用#{xxx}，需要使用${xxx}，或者使用CONCAT关键字进行字符串拼接。

XML

<mapper namespace="com.example.UserMapper">
    <!--方式一-->
    <!--List<User> selectUserByName(String name);  -->
    <select id="selectUserByName" resultType="User">
        SELECT * FROM user WHERE name LIKE '%${name}%'
    </select>

    <!--方式二-->
    <!--List<User> selectUserByName(String name);  -->
    <select id="selectUserByName" resultType="User">
        SELECT * FROM user WHERE name LIKE CONCAT('%', #{name}, '%')
    </select>

    <!--方式三👍🏻-->
    <!--List<User> selectUserByName(String name);  -->
    <select id="selectUserByName" resultType="User">
        SELECT * FROM user WHERE name LIKE "%"#{name}"%"
    </select>

    <!--错误方式❌-->
    <!--List<User> selectUserByName(String name);  -->
    <select id="selectUserByName" resultType="User">
        SELECT * FROM user WHERE name LIKE '%#{name}%'
    </select>
</mapper>

批量操作#

需要批量操作时，只能使用${xxx}进行字符串拼接，不能使用#{xxx}

XML

<mapper namespace="com.example.UserMapper">
    <!--int deleteUsers(@Param("ids") String ids)-->
    <delete>
        delete from user where id in (${ids})
    </delete>
</mapper>

设置自动递增主键#

设置useGeneratedKeys="true"表示 MyBatis 会在插入记录后自动获取数据库生成的主键值，并将这个值填充到对象的id属性 中。这样你就不需要手动查询数据库来获取新插入记录的主键。

XML

<mapper namespace="com.example.UserMapper">
    <!--void insertUser(User user);-->
    <insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
        insert into user values(null,#{name}, #{age})
    </insert>
</mapper>

结果集处理#

查询具体值#

查询具体值可以使用包装类接收

XML

<mapper namespace="xxx">
    <!--Integer getCount();-->
    <select id="getCount" resultType="Integer">
        select count(*) from t_user
    </select>
</mapper>

查询单条数据#

Mapper接口采用实体类接收

XML

<mapper namespace="xxx">
    <!--User selectUserById(Integer id);-->
    <select id="selectUserById" resultType="User">
        select * from t_user where id = #{id}
    </select>
</mapper>

也可以使用Map集合接收查询结果

XML

<mapper namespace="xxx">
    <!--Map<String, Object> selectUserById(Integer id);-->
    <select id="selectUserById" resultType="Map">
        select * from t_user where id = #{id}
    </select>
</mapper>

查询多条数据#

Mapper接口采用List集合接收

XML

<mapper namespace="xxx">
    <!--List<User> selectAllUser();-->
    <select id="selectAllUser" resultType="User">
        select * from t_user
    </select>
</mapper>

表和实体类字段名不一致#

使用别名#

查询结果使用别名,将表中的字段名改为与实体类字段名一致。

XML

<mapper namespace="xxx">
    <!--将phone_number改为与实体类一致的phoneNumber-->
    <!--User selectUserById(Integer id);-->
    <select id="selectAllUser" resultType="User">
        select id,name, phone_number phoneNumber from user where id = #{id}
    </select>
</mapper>

mapUnderscoreToCamelCase#

在核心配置文件中进行配置，让数据库字段名自动转换为实体类字段名。

比如：可以自动将phone_number转为phoneNumber

XML

<configuration>
    <settings>
        <!--表示根据驼峰命名法自动匹配结果集-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

使用ResultMap#

XML

<mapper namespace="xxx">

    <resultMap id="UserMap" type="User">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="phoneNumber" column="phone_number"/>
    </resultMap>

    <!--User selectUserById(Integer id);-->
    <select id="selectAllUser" resultMap="UserMap">
        select id,name, phone_number from user where id = #{id}
    </select>
</mapper>

resultMap处理多对一#

需求：用户和部门之间的多对一关系，查询员工以及员工所在部门。

Java

package com.luguosong.many_to_one.pojo;

import lombok.Data;

import java.sql.Date;

/**
 * 员工
 * @author luguosong
 */
@Data
public class Employees {
    private Integer id;
    private String firstName;
    private String lastName;
    private String position;
    private Date hireDate;
    //private Integer departmentId;

    // 关联部门
    private Departments departments;
}

Java

package com.luguosong.many_to_one.pojo;

import lombok.Data;

/**
 * 部门
 * @author luguosong
 */
@Data
public class Departments {
    private Integer id;
    private String departmentName;
}

association标签#

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">


<mapper namespace="com.luguosong.many_to_one.association.mapper.EmployeesMapper">

    <resultMap id="employeesResultMap" type="com.luguosong.many_to_one.pojo.Employees">
        <id property="id" column="id"/>
        <result property="firstName" column="first_name"/>
        <result property="lastName" column="last_name"/>
        <result property="position" column="position"/>
        <result property="hireDate" column="hire_date"/>

        <association property="departments" javaType="com.luguosong.many_to_one.pojo.Departments">
            <id property="id" column="dept_id"/>
            <result property="departmentName" column="department_name"/>
        </association>
    </resultMap>

    <select id="getEmployeesById" resultMap="employeesResultMap">
        SELECT e.*,d.id dept_id,d.department_name
        FROM employees e
                 LEFT JOIN departments d ON e.department_id = d.id
        WHERE e.id = #{id}
    </select>
</mapper>

分步查询#

分步查询的好处

每个查询是分开的，可以单独使用，也可以合并使用

第一步：

Java

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">


<mapper namespace="com.luguosong.many_to_one.step_by_step.mapper.EmployeesMapper">

    <resultMap id="employeesResultMap" type="com.luguosong.many_to_one.pojo.Employees">
        <id property="id" column="id"/>
        <result property="firstName" column="first_name"/>
        <result property="lastName" column="last_name"/>
        <result property="position" column="position"/>
        <result property="hireDate" column="hire_date"/>

        <association
                select="com.luguosong.many_to_one.step_by_step.mapper.DepartmentsMapper.getDepartmentsById"
                column="department_id"
                property="departments"/>
    </resultMap>

    <select id="getEmployeesById" resultMap="employeesResultMap">
        SELECT *
        FROM employees
        WHERE id = #{id}
    </select>
</mapper>

第二步：

Java

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">


<mapper namespace="com.luguosong.many_to_one.step_by_step.mapper.DepartmentsMapper">

    <select id="getDepartmentsById" resultType="com.luguosong.many_to_one.pojo.Departments">
        SELECT *
        FROM departments
        WHERE id = #{id}
    </select>
</mapper>

测试类：

Java

package com.luguosong.many_to_one.step_by_step;


import com.luguosong.many_to_one.pojo.Employees;
import com.luguosong.many_to_one.step_by_step.mapper.EmployeesMapper;
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;

/**
 * @author luguosong
 */
public class Test {
    public static void main(String[] args) throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config-hello.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(is);
        // 表示Java程序与数据库之间的会话
        SqlSession sqlSession = factory.openSession();
        // 动态创建Mapper接口对应的对象
        EmployeesMapper mapper = sqlSession.getMapper(EmployeesMapper.class);
        Employees employees = mapper.getEmployeesById(5);

        //因为开启了延迟加载，如果只是获取员工属性，只会执行第一步sql
        System.out.println(employees.getFirstName());

        //当获取全部属性（包括部门属性），会执行第二步sql
        System.out.println(employees);
    }
}

执行结果：

Bash

2024-07-21 17:58:37,388 DEBUG getEmployeesById:135 - ==>  Preparing: SELECT * FROM employees WHERE id = ?
2024-07-21 17:58:37,413 DEBUG getEmployeesById:135 - ==> Parameters: 5(Integer)
2024-07-21 17:58:37,483 DEBUG getEmployeesById:135 - <==      Total: 1
芳
2024-07-21 17:58:37,485 DEBUG getDepartmentsById:135 - ==>  Preparing: SELECT * FROM departments WHERE id = ?
2024-07-21 17:58:37,485 DEBUG getDepartmentsById:135 - ==> Parameters: 4(Integer)
2024-07-21 17:58:37,487 DEBUG getDepartmentsById:135 - <==      Total: 1
Employees(id=5, firstName=芳, lastName=陈, position=市场专员, hireDate=2016-08-13, departments=Departments(id=4, departmentName=市场部))

resultMap处理一对多#

需求：部门和用户之间的一对多关系，查询部门以及部门下的所有员工。

Java

package com.luguosong.one_to_many.pojo;

import lombok.Data;

import java.util.List;

/**
 * 部门
 * @author luguosong
 */
@Data
public class Departments {
    private Integer id;
    private String departmentName;

    private List<Employees> employees;
}

Java

package com.luguosong.one_to_many.pojo;

import lombok.Data;

import java.sql.Date;

/**
 * 员工
 * @author luguosong
 */
@Data
public class Employees {
    private Integer id;
    private String firstName;
    private String lastName;
    private String position;
    private Date hireDate;
}

collection标签#

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">


<mapper namespace="com.luguosong.one_to_many.collection.mapper.DepartmentsMapper">

    <resultMap id="departmentsResultMap" type="com.luguosong.one_to_many.pojo.Departments">
        <id property="id" column="id"/>
        <result property="departmentName" column="department_name"/>

        <collection
                ofType="com.luguosong.one_to_many.pojo.Employees"
                property="employees">
            <id property="id" column="e_id"/>
            <result property="firstName" column="first_name"/>
            <result property="lastName" column="last_name"/>
            <result property="position" column="position"/>
            <result property="hireDate" column="hire_date"/>
        </collection>
    </resultMap>

    <select id="getDepartmentById" resultMap="departmentsResultMap">
        SELECT * , employees.id AS e_id
        FROM departments
                 LEFT JOIN employees on departments.id = employees.department_id
        where departments.id = #{id}
    </select>
</mapper>

分步查询#

第一步：

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">


<mapper namespace="com.luguosong.one_to_many.step_by_step.mapper.DepartmentsMapper">

    <resultMap id="DepartmentsResult" type="com.luguosong.one_to_many.pojo.Departments">
        <id property="id" column="id"/>
        <result property="departmentName" column="department_name"/>
        <collection
                column="id"
                select="com.luguosong.one_to_many.step_by_step.mapper.EmployeesMapper.getEmployeesByDeptId"
                property="employees"/>
    </resultMap>

    <select id="getDepartmentsById" resultMap="DepartmentsResult">
        SELECT *
        FROM departments
        WHERE id = #{id}
    </select>
</mapper>

第二步：

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">


<mapper namespace="com.luguosong.one_to_many.step_by_step.mapper.EmployeesMapper">

    <select id="getEmployeesByDeptId" resultType="com.luguosong.one_to_many.pojo.Employees">
        SELECT *
        FROM employees
        WHERE department_id = #{id}
    </select>
</mapper>

延迟加载#

使用分步查询时，可以开启延迟加载，第二步查询会延迟执行：

lazyLoadingEnabled: 表示开启延迟加载
aggressiveLazyLoading: 表示是否按需加载，当等于true 时，任何方法调用都会加载该对象的所有属性。因此开启延迟加载该属性应该设置为false

XML

<configuration>
    <settings>
        <!--表示开启延迟加载，多表查询分步操作第二步会延迟执行-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>
</configuration>

如果全局设置了延迟加载，希望某个查询不延迟加载，可以通过fetchType属性来控制延迟加载，eager为不延迟加载，lazy为延迟加载。

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="xxx">
    <resultMap id="xxx" type="xxx">
        <!--...-->
        <association
                fetchType="eager"
                select="xxx"
                column="xxx"
                property="xxx"/>
    </resultMap>

    <select id="xxx" resultMap="xxx">
        ...
    </select>
</mapper>

动态sql#

if标签#

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace属性指向mapper接口-->
<mapper namespace="com.luguosong.dynamic_sql.sql_if.mapper.EmployeesMapper">
    <select id="selectEmployees" resultType="com.luguosong.dynamic_sql.pojo.Employees">
        select * from employees where 1=1
        <if test="firstName != null and firstName != ''">
            and first_name = #{firstName}
        </if>
        <if test="lastName != null and lastName != ''">
            and last_name = #{lastName}
        </if>
    </select>
</mapper>

测试类：

Java

package com.luguosong.dynamic_sql.sql_if;


import com.luguosong.dynamic_sql.pojo.Employees;
import com.luguosong.dynamic_sql.sql_if.mapper.EmployeesMapper;
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;
import java.util.List;

/**
 * @author luguosong
 */
public class Test {
    public static void main(String[] args) throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config-hello.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(is);
        // 表示Java程序与数据库之间的会话
        SqlSession sqlSession = factory.openSession();

        EmployeesMapper mapper = sqlSession.getMapper(EmployeesMapper.class);

        Employees select1 = new Employees();
        select1.setFirstName("强");
        List<Employees> employees1 = mapper.selectEmployees(select1);
        System.out.println(employees1);

        Employees select2 = new Employees();
        select2.setLastName("王");
        List<Employees> employees2 = mapper.selectEmployees(select2);
        System.out.println(employees2);

    }
}

where标签#

可以使用where标签替换where 1=1这种写法。

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace属性指向mapper接口-->
<mapper namespace="com.luguosong.dynamic_sql.sql_where.mapper.EmployeesMapper">
    <select id="selectEmployees" resultType="com.luguosong.dynamic_sql.pojo.Employees">
        select * from employees
        <where>
            <if test="firstName != null and firstName != ''">
                and first_name = #{firstName}
            </if>
            <if test="lastName != null and lastName != ''">
                and last_name = #{lastName}
            </if>
        </where>
    </select>
</mapper>

trim标签#

可以通过自定义trim标签来定制where标签的功能。

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace属性指向mapper接口-->
<mapper namespace="com.luguosong.dynamic_sql.sql_where.mapper.EmployeesMapper">
  <select id="selectEmployees" resultType="com.luguosong.dynamic_sql.pojo.Employees">
    select * from employees
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
      <if test="firstName != null and firstName != ''">
        and first_name = #{firstName}
      </if>
      <if test="lastName != null and lastName != ''">
        and last_name = #{lastName}
      </if>
    </trim>
  </select>
</mapper>

choose、when、otherwise标签#

相当于if...else if ... else的功能。

XML

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

foreach标签#

XML

<select id="selectPostIn" resultType="domain.blog.Post">
    SELECT *
    FROM POST P
    <where>
        <foreach
                item="item"
                index="index"
                collection="list"
                open="ID in ("
                separator=","
                close=")"
                nullable="true">
            #{item}
        </foreach>
    </where>
</select>

缓存#

一级缓存：默认开启,同一个SqlSession对象查询的结果会被缓存起来。可以通过SqlSession对象的clearCache()方法来清除一级缓存。 二级缓存：默认关闭,同一个SqlSessionFactory的查询结果会被缓存起来。

要启用全局的二级缓存，只需要在你的 SQL 映射文件中添加一行：

XML

<cache/>

代码生成(逆向工程)#

通过数据库表自动生成实体类和Mapper接口等相关代码

简单实现#

pom依赖中引入插件：

XML

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.luguosong</groupId>
    <artifactId>mybatis-generator</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.15</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>8.3.0</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>

            <!--代码生成插件-->
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.4.2</version>

                <!--插件依赖的环境-->
                <dependencies>
                    <!--逆向工程核心依赖-->
                    <dependency>
                        <groupId>org.mybatis.generator</groupId>
                        <artifactId>mybatis-generator-core</artifactId>
                        <version>1.4.2</version>
                    </dependency>

                    <dependency>
                        <groupId>com.mchange</groupId>
                        <artifactId>c3p0</artifactId>
                        <version>0.9.5.2</version>
                    </dependency>

                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>8.0.30</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>
</project>

代码生成插件配置：

generatorConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <context id="mysqlToTable1" targetRuntime="MyBatis3Simple">
        <!--配置数据库连接-->
        <jdbcConnection
                userId="root"
                password="12345678"
                driverClass="com.mysql.cj.jdbc.Driver"
                connectionURL="jdbc:mysql://localhost:3306/learn_mybatis">
            <!---->
            <property name="nullCatalogMeansCurrent" value="true"/>
        </jdbcConnection>

        <!--实体类生成策略-->
        <javaModelGenerator
                targetPackage="com.luguosong.pojo"
                targetProject="./src/main/java">
            <!--是否使用子包-->
            <property name="enableSubPackages" value="true"/>
            <!--去除字符串前后空格-->
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>

        <!--mapper映射文件生成策略-->
        <sqlMapGenerator
                targetPackage="com.luguosong.mapper"
                targetProject="./src/main/resources">
            <!--是否使用子包-->
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>

        <javaClientGenerator
                type="XMLMAPPER"
                targetPackage="com.luguosong.mapper"
                targetProject="./src/main/java"/>


        <!--指定相关表-->
        <table tableName="departments" domainObjectName="Departments"/>
        <table tableName="employees" domainObjectName="Employees"/>
    </context>
</generatorConfiguration>

分页插件#

添加依赖：

XML

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

Mybatis核心配置文件配置分页插件：

mybatis-config-page.xml

<?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>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>

    <!--⭐配置分页插件-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"/>
    </plugins>

    <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/learn_mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="12345678"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/luguosong/pagination_plugin/mapper/EmployeesMapper.xml"/>
    </mappers>
</configuration>

测试类：

Java

package com.luguosong.pagination_plugin;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.luguosong.hello.pojo.Employees;
import com.luguosong.pagination_plugin.mapper.EmployeesMapper;
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;
import java.util.List;

/**
 * @author luguosong
 */
public class Test {
    public static void main(String[] args) throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config-page.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(is);
        SqlSession sqlSession = factory.openSession();
        EmployeesMapper mapper = sqlSession.getMapper(EmployeesMapper.class);

        PageHelper.startPage(1, 2);
        List<Employees> list = mapper.getAll();
        System.out.println(list);
    }
}

结果：

Text Only

Page{count=true, pageNum=1, pageSize=2, startRow=0, endRow=2, total=6, pages=3, reasonable=false, pageSizeZero=false}[Employees(id=1, firstName=伟, lastName=张, position=人力资源经理, hireDate=2015-06-01, departmentId=1), Employees(id=2, firstName=娜, lastName=李, position=软件工程师, hireDate=2018-09-15, departmentId=2)]

Mybatis#

JDBC的不足#

入门案例#

Mybatis核心配置文件#

事务管理方式#

数据源类型#

引入外部properties配置文件#

设置类型别名#

配置映射文件#

参数获取#

字符串拼接#

占位符赋值👍🏻#

单个参数#

多个参数传递#

多个参数传递-@Param#

对象和Mapper集合#

模糊查询#

批量操作#

设置自动递增主键#

结果集处理#

查询具体值#

查询单条数据#

查询多条数据#

表和实体类字段名不一致#

使用别名#

mapUnderscoreToCamelCase#

使用ResultMap#

resultMap处理多对一#

association标签#

分步查询#

resultMap处理一对多#

collection标签#

分步查询#

延迟加载#

动态sql#

if标签#

where标签#

trim标签#

choose、when、otherwise标签#

foreach标签#

缓存#

代码生成(逆向工程)#

简单实现#

分页插件#

评论