Mybatis 3.5.14

来自于B站‘天气预报’,一名宝藏up,跟着他可以培养起独立解决编程问题的能力!!

01.简介

image-20240921222309384

1.1 官网

官方中文网: MyBatis中文网

  • 中文网参考手册

image-20240921222746402

1.2 概念

MyBatis 是一款优秀的持久层框架,支持自定义 SQL, 存储过程, 高级映射

  • 官方解释

image-20240921223207882

必要前置技术
  • Mysql [本课件以 MySQL 为例]
  • JDBC [Java Database Connection] API
通俗解释
  • Mybatis 框架 是对原生 JDBC 技术的封装 和 增强

  • Mybatis 框架 核心行为是将 JDBC ResultSet 结果集映射到类的过程 如

  • 映射关系示意

image-20240921223545754

02.入门

入门示例会引导大家熟悉 MYBATIS 环境 | 配置 | DAO 接口抽象配置 | 运行流程

2.1环境

Mybatis 当前最新版为 3.5.14 [此处创建项目 maven::java mybatis]

  • mybatis
<dependencies>
    <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.14</version>
    </dependency>
</dependencies>
  • pom.xml [mybatis-3.5.14.jar\META-INF\maven\org.mybatis\mybatis\pom.xml ]
<dependency>
	<groupId>ognl</groupId>
    <artifactId>ognl</artifactId>
    <version>3.3.4</version>
    <scope>compile</scope>
    <optional>true</optional>
</dependency>

<dependency>
	<groupId>org.javassist</groupId>
    <artifactId>javassist</artifactId>
    <version>3.29.2-GA</version>
    <scope>compile</scope>
	<optional>true</optional>
</dependency>

<dependency>
<groupId>cglib</groupId>
<artifactId>cglib</artifactId>
<version>3.3.0</version>
<optional>true</optional>
</dependency>

<!--日志系列-->
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>2.0.9</version>
    <optional>true</optional>
</dependency>

<dependency>
    <groupId>ch.qos.reload4j</groupId>
    <artifactId>reload4j</artifactId>
    <version>1.2.25</version>
    <optional>true</optional>
</dependency>

<dependency>
    <groupId>org.apache.logging.Log4j</groupId>
    <artifactId>log4j-api</artifactId>
    <version>${log4j.version}</version>
    <optional>true</optional>
</dependency>

<dependency>
	<groupId>commons-logging</groupId>
    <artifactId>commons-logging</artifactId>
    <version>1.2</version>
    <optional>true</optional>
</dependency>
  • maven repository

image-20240922000431699

  • mysql
<!-- SQL: SELECT VERSION(): 查看自己的数据库版本-->

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>
  • pom.xml [src/main/java 非java文件编译打包配置]
<build>
	<resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                	<include>**/*.*</include>
            </includes>
        </resource>
        <resource>
        	<directory>src/main/resources</directory>
            <includes>
            	<include>**/*.*</include>
            </includes>
        </resource>
    </resources>
</build>    
  • 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>test</groupId>  <!-- 此处可更改 可见我博客主页写的Maven博客-->
    <artifactId>untitled</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>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.14</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
        <dependency>
            <groupId>ognl</groupId>
            <artifactId>ognl</artifactId>
            <version>3.3.4</version>
            <scope>compile</scope>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.javassist</groupId>
            <artifactId>javassist</artifactId>
            <version>3.29.2-GA</version>
            <scope>compile</scope>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>3.3.0</version>
            <optional>true</optional>
        </dependency>
		
        <!--日志 -->
        <dependency>
        	<groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>2.0.9</version>
            <optional>true</optional>
        </dependency>
        
        <dependency>
        	<groupId>ch.qos.reload4j</groupId>
            <artifactId>reload4j</artifactId>
            <version>true</version>
            <optional>true</optional>
        </dependency>
        
        <dependency>
        	<groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>${log4j.version}</version>
            <optional>true</optional>
        </dependency>
         
        <dependency>
        	<dependency>commons-logging</dependency>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
            <optioanl>true</optioanl>
        </dependency>
        
    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.*</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.*</include>
                </includes>
            </resource>
        </resources>
    </build>

</project>

2.2 数据库

  • SQL
CREATE DATABASE mybatis;

USE mybatus;

CREATE TABLE user(
	user_id 			INT PRIMARY KEY AUTO_INCREMENT,
    user_username       VARCHAR(20)
)

2.3 实体类

package org.example.mybatis.entity;

import lombok.Data;

@Data
public class User {
    private Integer id;
    private String username;
}
// 写法二
// public record User(Integer id, String username) {
//
// }

2.4 核心配置文件

  • mybatis-config.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>
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="POOLED"/>
            <dataSource type="JDBC">
                    <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                	<!-- <property name="url" value="jdbc:mysql://127.0.0.1:3306(默认时可以省略掉)/mybatis?serverTimezone=Asia/Shanghai"/>-->
                    <property name="url" value="jdbc:mysql:///mybatis?serverTimezone=Asia/Shanghai"/>
                    <property name="username" value="root"/>
                    <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

</configuration>

2.5 Mapper 抽象

Mapper 接口是 DAO (Data Access Object) 操作,对数据库表的操作抽象

  • UserMapper.java
package org.exmaple.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.exmaple.mybatis.entity.User;

@Mapper
public interface UserMapper {
    User selectById(int id);
    
    int deleteById(int id); // mysql返回值为一个状态值 可以使用 boolean 类型
}

2.6 Mapper 配置

Mapper 接口需通过配置 [Java | Xml] 方式告知 Mybatis 执行逻辑行为

  • org.example.mybatis.mapper.UserMapper.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="org.example.mybatis.mapper.UserMapper">
    
        <select id="selectById"
                parameterType="int"
                resultType="org.example.mybatis.entity.User">
            SELECT
                user_id id,
                user_username username
            FORM
                user
            WHERE
    
                user_id = #{id}
        </select>
    	
        <delete id="deleteById" parameterType="int"> <!-- 无resultType参数可选 -->
        	DELETE FROM user from user_id = #{id}
        </delete>
        
    </mapper>
    

2.7 Mapper扫描

完成 Mapper 接口及其配置后 还需告知 Mybatis Mapper 接口位置

  • src/main/resources/mybatis-config.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>
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="POOLED"/>
            <dataSource type="JDBC">
                    <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql:///Mybatis?serverTimezone=Asia/Shanghai"/>
                    <property name="username" value="root"/>
                    <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <package name="org.example.mybatis.mapper"></package>
    </mappers>
    
</configuration>

2.8 API 入口

Mybatis 提供运行的 入口 Api, 通过 Api 引导框架读取配置, 解析 Mapper 后才能正确运行

  • Application.java
package org.example.mybatis.api;

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 org.example.mybatis.mapper.UserMapper;
import org.example.mybatis.entity.User;
import java.io.IOException;
import java.io.InputStream;

public class Application {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";

        InputStream config = Resources.getResourceAsStream(resource);

        SqlSessionFactory sqlSessionFactory =
                new SqlSessionFactoryBuilder().build(config);

        SqlSession sqlSession = sqlSessionFactory.openSession(true);

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

        User user = mapper.selectById(1);

        System.out.println(user);


        int row = mapper.deleteById(1);
        System.out.println("row=" + row);

       //  sqlSession.commit(); 开启autoCommit 或者手动提交
        sqlSession.close();
    }
}

2.9 日志

Mybatis 通过使用内置日志工厂提供日志工能

  • 官网日志机制

在这里插入图片描述

01.说明
  • 内置日志工厂将会把日志工作委托给下面的实现之一

  • SLF4J [日志抽象]

  • Apache Commons Logging [日志抽象]

  • Log4j 2 [日志实现]

  • Log4j (3.5.9起废弃)

  • JDK logging [日志实现 JDK自带 较少用]

  • 机制

	1. MyBatis 内置日志工厂基于运行时自省机制选择合适的日志工具
	2. MyBatis 会使用第一个查找得到的工具 (按上文列举的顺序查找) 如果一个都未找到,日志功能就会被禁用。
  • mybatis-config.xml [指定配置]
<configuration>
	<serrings>
    	...
        <setting name="logImpl" value="LOG4J"/> <!--可参考上文,上文已经配置完毕 -->
        ...
    </serrings>
</configuration>
  • logImpl 可选的值有:
  • SLF4J, LOG4J, LOG4J2, JDK_LOGGING, COMMONS_LOGGING, STDOUT_LOGGING, NO_LOGGING
02.SLF4J

SLF4J 是一款日志门面抽象框架,本身并未实现,需使用具体的日志框架

  1. log4j 是一款实现的日志框架,对 SLF4J 有桥接实现
  2. log4j2 是log4j 2.y.z 版本, 同样对 SLF4J 有桥接实现
  3. logback 是 直接针对 SLF$J 实现的日志框架

官网: SLF4J Manual

  • 官方参考手册

image-20240922120637791

版本注意事项: https://github.com/gos-ch/slf4j

  • Github

image-20240922121133434

03.Logback

Logback 针对 SLF4J 的日志实现 核心坐标 [logback-core | logback-classsic]

  • dependency

image-20240922121805967

  • pom.xml
<!-- https://mvnrepository.com/artifact/ch.qos.logback/logback-classic -->
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.4.14</version>
    <scope>test</scope>
</dependency>
  • API
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class HelloWorld {
    public static void main(String[] args) {
        Logger logger = LoggerFactory.getLogger(HelloWorld.class);
        logger.info("Hello World");
    }
}
  • logback.xml [不配置该文件时的内置日志格式]
<?xml version="1.0" encoding="UTF-8" ?>
<configuration>
	<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender" >
    	<encoder>
        	<pattern>
            	%d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %-5level %logger{36}.%M %L - %msg%n
                <!-- %d日期 yyyy-MM-dd 年月日,HH:mm::ss.SSS时,分,毫秒,微秒 %t线程 %-5 左对齐5个字符 level级别
				%logger{36}长度为36的日志记录 .%M方法 %L行 - %msg具体信息 %n换行
				-->
            </pattern>
        </encoder>
    </appender>
</configuration>

2.10 运行逻辑流程

了解 Mybatis 大致运行流程可以更清晰在整体上,对其有良好的认识

01.动态代理

动态代理实现主要有 JDK[仅支持接口 类型] | CGLIB [支持类和接口的 类型]

  • 这里仅需知晓

    • 生成的代理对象确实属于代理的接口类型,以及代理对象调用接口方法会执行到特定机制中
  • Mybatis 利用 动态代理 主要完成 mapper 代理 和 SQL 解析处理

  • ProxyUsage.java

package org.example.mybatis.api;

import org.example.mybatis.mapper.UserMapper;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;

public class JdkProxyUsage {
    public static void main(String[] args) {

        ClassLoader loader = UserMapper.class.getClassLoader();

        Class<?>[] classes = { UserMapper.class };

        InvocationHandler handler = new InvocationHandler() {
            @Override
            public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {

                System.out.println("method.getName() = " + method.getName());
                System.out.println("args[0] = " + args[0]);

                System.out.println("...");

                return null;
            }
        };
        
        UserMapper userMapper = (UserMapper) Proxy.newProxyInstance(loader, classes, handler);

        userMapper.deleteById(1000);
    }
}

02.Javassist

Javassist是 Apache 提供的动态字节码技术, 可动态生成类及类中成员

  • Mybatis 利用 Javassist 主要完成 对象成员对应列 延迟装配功能

  • JavassistUsage.java

package org.example.mybatis.api;

import javassist.*;

public class JavassistUsage {
    public static void main(String[] args) throws Exception {

        ClassPool classPool = ClassPool.getDefault();

        CtClass ctClass = classPool.makeClass("x.y.z.People");

        CtField ctField = CtField.make("private boolean alive;", ctClass);

        ctClass.addField(ctField);

        String methodBody = "public void message() { System.out.println(\"动态生成的方法\"); }";

        CtMethod ctMethod = CtMethod.make(methodBody, ctClass);
        
        ctClass.addMethod(ctMethod);
        
        ctClass.writeFile();
    }
}

03.逻辑流程
  1. 读取并,解析核心配置文件 mybatis-config.xml
  2. 装配数据库连接以及配置事务管理
  3. 读取 mapper 接口及 xml 文件所在位置并完成其对应的逻辑关联
  4. mapper 代理对象调用 mapper 接口方法时 mybatis 利用动态代理找到对应的标签 [xpath]
  5. mybatis 找到对应标签 解析 SQL 表达式及动态代理完成参数的适配
  6. mybatis 使用 JDBC 执行 SQL 操作, 拿到 ResultSet
  7. 通过配置告知 mybatis 列和对象属性的对应关系,让 mybatis 完整封装

03.全局配置

了解掌握 Mybatis 全局配置的 是将 Mybatis 熟练运用和解决问题的关键

  • Mybatis 全局配置都位于 mybatis-config.xml 中, 也可同理基于 Java 配置

image-20240923093855766

3.1 属性

Mybatis 允许读取外部 properties 文件,并读取为上下文 通过 ${key} 获取值

  • src/main/resources/db.properties
mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql:///Mybatis?serverTimezone=Asia/Shanghai <!-- 博主的数据库名为Mybatis-->
mysql.username=root
mysql.password=123456
  • mybatis-config-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>

    <properties resource="db.properties">
        <!-- <property name="" value=""/> -->
    </properties>

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

    <mappers>
        <package name="org.example.mybatis.mapper"/>
    </mappers>
</configuration>

3.2 设置

Mybatis 预留全局 key 用于控制 Mybatis 整体运行行为

image-20240923102507889

  • mybatis-config.xml
<settings>
    <setting name="cacheEnabled" value="true"/>
    <setting name="lazyLoadingEnabled" value="true"/>
    <setting name="aggressiveLazyLoading" value="true"/>
    <setting name="multipleResultSetsEnabled" value="true"/>
    <setting name="useColumnLabel" value="true"/>
    <setting name="useGeneratedKeys" value="false"/>
    <setting name="autoMappingBehavior" value="PARTIAL"/>
    <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
    <setting name="defaultExecutorType" value="SIMPLE"/>
    <setting name="defaultStatementTimeout" value="25"/>
    <setting name="defaultFetchSize" value="100"/>
    <setting name="safeRowBoundsEnabled" value="false"/>
    <setting name="safeResultHandlerEnabled" value="true"/>
    <setting name="mapUnderscoreToCamelCase" value="false"/>
    <setting name="localCacheScope" value="SESSION"/>
    <setting name="jdbcTypeForNull" value="OTHER"/>
    <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
    <setting name="defaultScriptingLanguage" value="org.apache.ibatis.scripting.xmltags.XMLLanguageDriver"/>
    <setting name="defaultEnumTypeHandler" value="org.apache.ibatis.type.EnumTypeHandler"/>
    <setting name="callSettersOnNulls" value="false"/>
    <setting name="returnInstanceForEmptyRow" value="false"/>
    <setting name="logPrefix" value="exampleLogPrefix_"/>
    <setting name="logImpl" value="SLF4J | LOG4J | LOG4J2 | JDK_LOGGING | COMMONS_LOGGING | ..."/>
    <setting name="proxyFactory" value="CGLIB | JAVASSIST"/>
    <setting name="vfsImpl" value="org.mybatis.example.YourselfVfsImpl"/>
    <setting name="useActualParamName" value="true"/>
    <setting name="configurationFactory" value="org.mybatis.example.ConfigurationFactory"/>
</settings>

3.3 类型别名

类型别名可为 Mapper 接口返回的 [自定义类型] 定义一个短名称别名以便使用

3.3.1 自定义类型
  • mybatis-config.xml 自定义指定类型别名名称
<typeAliases>
	<typeAlias alias="Author" type="domain.blog.Author"/>
    <typeAlias alias="Bolg" type="domain.blog.Blog" />
    <typeAlias alist="Comment" type="domain.blog.Comment" />
    <typeAlias alist="Post" type="domain.blog.Post" />
    <typeAlias alias="Section" type="domain.blog.Section" />
    <typeAlias alias="Tag" type="domain.blog.Tag" />
</typeAliases>
  • mybatis-config.xml 指定包统一定义别名,规则是类名首字母小写作为名称
<typeAliases>
	<packaage name="org.example.project.entity"/>
</typeAliases>
  • Author.java (不太推荐 当注解逐渐变多的时候类的格式会逐渐混乱)
@Alias("author")
public class Author {
    ...
}
3.3.2 内置类型别名

官方网址: 配置_MyBatis中文网

image-20240923111817308

3.4 类型处理器

Mybatis 内置类型处理器用于完成 列和 对象属性的数据类型的对应关系

  • 当内置的所有处理器都不能对应 列和属性的数据类型时, Mybatis 将抛出异常

  • 解决

    • 调整 列 或者 属性的数据类型 使用 Mybatis 能够处理 [推荐]
    • 自行实现类型处理器并注册到 Mybatis 框架中 [注册分为全局和局部]
    01.内置类型处理器

    参考链接: 配置_MyBatis中文网

  • 内置类型处理器

image-20240923112454379

  • 自定义数据类型

Mybatis 内置 BaseTypeHandler 抽象用于实现自定义数据类型对应处理

  • BaseTypeHandler<泛型> 泛型表示 Java 数据类型

  • @MappedJdbcTypes (value = 数据库类型)

  • ExampleTypeHandler.java

package org.example.mybatis.type;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ExampleTypeHandler extends BaseTypeHandler<String> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, parameter);
    }

    @Override
    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return rs.getString(columnName);
    }

    @Override
    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return rs.getString(columnIndex);
    }

    @Override
    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return cs.getString(columnIndex);
    }
}
  • GenderTypeHandler.java
package org.example.mybatis.type;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.example.mybatis.entity.Gender;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@MappedJdbcTypes(JdbcType.INTEGER)
public class GenderTypeHandler extends BaseTypeHandler<Gender> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Gender gender, JdbcType jdbcType) throws SQLException {
        int sex = gender == Gender.MAN ? 0 : 1;
        ps.setInt(i, sex);
    }

    @Override
    public Gender getNullableResult(ResultSet rs, String columnName) throws SQLException {
        int sex = rs.getInt(columnName);
        return sex == 0 ? Gender.MAN : Gender.WOMAN;
    }

    @Override
    public Gender getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        int sex = rs.getInt(columnIndex);
        return sex == 0 ? Gender.MAN : Gender.WOMAN;
    }

    // 没有相关的调用方法
    @Override
    public Gender getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return null;
    }
}
  • 全局注册
<?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>

    <properties resource="db.properties">
        <!-- <property name="" value=""/> -->
    </properties>

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

    <typeAliases>
    <!-- <typeAlias alias = "User" type="org.example.mybatis.entity.User" />-->
       <package name="org.example.mybatis.entity"/>
    </typeAliases>

    <typeHandlers>
        <typeHandler handler="org.example.mybatis.type.GenderTypeHandler"/>
    </typeHandlers>

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

    <mappers>
        <package name="org.example.mybatis.mapper"/>
    </mappers>
    
</configuration>

3.5 对象工厂(仅了解)

MyBatis 创建结果对象实例是 都会使用一个内置对象工厂实例来完成实例化工作

  • 置的对象工厂需要做的仅仅是实例化目标类, 要么通过默认无参构造方法,要么是有参构造

  • 如果像覆盖对象工厂的默认行为 可以通过创建自己的对象工厂来实现

  • ExampleObjectFactory.java (不推荐改,因为没有需求需要覆盖对象工厂,而且对技术要求极高)

public class ExampleObjectFactory extends DefaultObjectFactory {
    @Override
    public <T> T create(Class<T> type) {
        return super.create(type);
    }
    
    @Override
    public <T> T create(Class<T> type, List<Class<?>> constructorArgTypes, List<Object> constructorArgs) {
        return super.create(type, constructorArgTypes, constructorArgs);
    }
    
    @Override
    public void setProperties(Properties properties) {
        super.setProperties(properties);
    }
    
    @Override
    public <T> boolean isCollection(Class<T> type) {
        return Collection.class.isAssignableFrom(type);
    }
}
  • mybatis-config.xml
<objectFactory type="org.mybatis.example.ExampleObjectFactory">
	<property name="someProperty" value="100" />
</objectFactory>               

3.6 插件

MyBatis 允许你在映射语句执行过程中的某一点进行拦截调用

  • Executor (update query flushStatements commit rollback getTransaction close isClosed)

  • ParameterHandler (getParameterObject setParameters)

  • ResultSetHandler (handleResultSets handleOutputParameters)

  • StatementHandler (perpare parameterize batch update query)

  • ExamplePlugin.java

@Intercepts (
    {
        @Signature (
        	tyep= Executor.class,
            method = "update",
            args = {MappedStatement.class, Object.class}
        )
    }
)

public class ExamplePlugin implements Interceptor {
    
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // implement pre processing if need
        Object returnObject = invocation.proceed();
        // implement post processing if need
        return returnObject;
    }
    
    private Properties properties = new Properties();
    
    @Override
    public void setProperties(Properties properties) {
        this.properties = properties;
    }
}
  • mybatis-config.xml
<plugins>
	<plugin interceptor="org.mybatis.example.ExamplePlugin">
    	<property name="someProperty" value="100"/>
    </plugin>
</plugins>

3.7 环境配置

MyBatis 可以配置成适应多种环境 如 开发,测试,生产环境 需不同的配置

01 机制
  • 记住:尽管可以配置多个环境,但每个 SqlSessionFactory 实例只能选择一种环境
  • 每个数据库对应的 SqlSessionFactory
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader, environment);

SqlSessionGactory factory = new SqlSessionFactoryBuilder().build(reader, enviroment, properties);
  • mybatis-config.xml
<environments default="development">
	<enviroment id="development">
    	<transactionManager type="JDBC">
        	<property name="..." value="..."/>
        </transactionManager>
        
        <dataSource type="POOLED">
        	<property name="driver" value="${driver}"/>
            <property name="url" value="${url}" />
			<property name="username" value="${username}"/>
            <property name="password" value="${password}" />
        </dataSource>
    </enviroment>
</environments>
02 事务管理器

Mybatis 关闭 MySQL 的事务自动提交,并推荐在关闭连接时,不再处理连接的事务自动提交

  • mybatis-config.xml
<transactionManager type="JDBC">
	<property name="skipSetAutoCommitOnClose" value="true" />
</transactionManager>

当 Mybatis + Spring 一起使用时 Mybatis 推荐使用 Spring 事务

  • 无论采用什么技术, 事务都应当放在业务层处理,持久层任何一个接口都是 单条 SQL操作

  • Demo

record UserService(UserMapper userMapper) {
    // 此处登录
    
    public void transferAccounts(int userId, int otherId, int amount) {
        userMapper.updateAmountById(userId, -amount); // 这是一条 SQL
        userMapper.updateAmountById(otherId, +amount); // 这是一条 SQWL
    }
}

3.8 数据库厂商标识

MyBatis 可根据不同数据库厂商执行 同一 mapper 接口的不同 SQL 语句

01 数据库名称
package org.example.mybatis.api;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JdbcApi {

    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql:///mybatis?serverTimezone=Asia/Shanghai";

        Connection connection = DriverManager.getConnection(url, "root", "123456");

        DatabaseMetaData metaData = connection.getMetaData();

        String productName = metaData.getDatabaseProductName();

        System.out.println("productName =" + productName);
    }

}
02 标识机制
  • mybatis-config.xml
<databaseIdProvider type="DB_VENDOR">
	<property name="SQL Server" value="sqlserver" />
    <property name="DB2" value="db2" />
    <property name="Oracle" value="oracle" />
    <property name="数据库产品名称" value="别名" />
</databaseIdProvider>
  • mapper [针对 selectById 方法可提前配置多条 SQL语句]
<select id="selectById"
        parameterType="_int"
        resultType="user"
        databaseId="oracle">
	SELECT
    	user_id id,
    	user_username username
		user_create_datetime createDatetime
    	user_gender gender
    FROM
    	user
    WHERE 
    	user_id = #{id}
</select>

<select id="selectById"
        parameterType="_int"
        resultType="user"
        databaseId="mysql">
	SELECT
    	user_id id,
    	user_username username
		user_create_datetime createDatetime
    	user_gender gender
    FROM
    	user
    WHERE 
    	user_id = #{id} AND 1 = 1 <!--为了区别上一个select语句-->
</select>

[!note]

备注: 实际开发时因尽量使用 SQL规范关键值或查询方式, 避免使用数据库特殊关键值

3.9 映射器

映射器是 Mybatis 预言指定 Mapper 接口所在位置的手段

  • 其指定逻辑方式有:mybatis-config.xml 使用 mappers 标签配置

  • 指定 全限定名 mapper xml 位置 路径目录用 / 表示

  • 指定 全限定名 本地 mapper xml 本地位置

  • 指定 全限定名 mapper 接口 位置 路径目录用 .表示

  • 指定 全限定名 mapper 接口所在包位置 路径目录用 .表示[常用]

  • mappers

<!-- 使用相对于类路径的资源引用 -->
<mappers>
	<mapper resource="org/mybatis/builder/AuthorMapper.xml"/>
    <mapper resource="org/mybatis/builder/BlogMapper.xml"?>
    <mapper resource="org/mybatis/builder/PostMapper.xml">
</mappers>
  • mappers
<!--使用完全限定资源定位符(URL)-->
<mappers>
<mapper urL="file:///var/mappers/AuthorMapper.xm"/>
<mapper url="file:///var/mappers/BlogMapper.xmL"/>
<mapper urL="file:///var/mappers/PostMapper.xmL"/>
</mappers>
  • mappers
<!--使用映射器接口实现类的完全限定类名 -->
<mappers>
<mapper class="org.mybatis.builder.AuthorMapper"/>
<mapper class="org.mybatis.builder.BlogMapper"/>
<mapper class="org.mybatis.builder.PostMapper"/>
</mappers>
  • mappers
<!-- 将包内的映射器接口全部注册为映射器 -->
<mappers>
	<package name="org.mybatis.builder"/>
</mappers>

04.映射文件

Mapper.xml 中 mapper 根元素下只允许出现以下子元素

----- SQL 映射文件只有很少的几个顶级元素 (按照应被定义的顺序列出)

  • cache - 该命名空间的缓存配置

  • cache-ref -引用其他命名空间的缓存配置

  • resultMap -描述如何从数据库结果集中加载对象,是最复杂也是最强大的元素

  • sql - 可被其他语句引用的可重用语句块

  • insert - 映射插入语句

  • update -映射更新语句

  • delete -映射删除语句

  • select -映射查询语句

4.1 select

select 标签用于 SQL 查询和逻辑映射

01 属性列表
  • select 标签元素只允许的出现的属性
<select
   id="methodName"
   parameterType="int"
   parameterMap="deprecated"
   resultType="hashmap"
   resultMap="resultMapId"
   flushCache="false"
   useCache="true"
   timeout="10"
   fetchSize="256"
   statementType="PREPARED"
   resultSetType="FORWARD_ONLY">
  • select 标签属性说明

image-20240924104447487

02 resultType

resultType 用于返回类型,基础类型,Map, 集合,对象类型

01 基础类型【单个值】
  • UserMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.example.mybatis.entity.Gender;
import org.exampl.mybatis.entity.User;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapper {
    
    User selectById(int id);
    
    User selectByUsernameAndGender(User user);
    
    User selectByGenderAndUsername(@Param("gender") Gender gender,
                                  @Param("username") String username);
    
    long count();
    
    String selectUsernameById(int id);
    
}
02 对象类型
03 集合对象类型
  • UserMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.example.mybatis.entity.Gender;
import org.example.mybatis.entity.User;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapper {
    
    User selectById(int id);
    
    User selectByUsernameAndGender(User user);
    
    User selectByGenderAndUsername(@Param("gender") Gender gender,
                                  @Param("username") String username);
    
    long count();
    
    String selectUsernameById(int id);
    
    List<User> selectList();
}
  • UserMapper.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="org.example.mybatis.mapper.UserMapper">

	<select id="selectById" parameterType="_int" resultType="User">
    	SELECT
        	user_id id,
        	user_username username,
        	user_create_datetime createDateTime,
        	user_gender gender
        FROM
        	user
        WHERE user_id = #{id}
    </select>
    
    <select id="selectByUsernameAndGender" parameterType="User" resultType="User">
    	SELECT
        	user_id id,
        	user_username username,
        	user_create_datetime createDatetime,
        	user_gender gender
        FROM
        	user
        WHERE
        	user_username = #{username} AND user_gender = #{gender}
    </select>
    
    <select id="selectByGenderAndUsername" resultTyep="User">
		SELECT 
        	user_id id,
        	user_username username,
        	user_create_datetime createDatetime,
        	user_gender gender
        FROM
        	user
        WHERE
       		user_username = #{username} AND user_gender = #{gender}
    </select>
    
    <select id="count" resultType="_long">
    	SELECT COUNT(1) FROM user
    </select>
    
    <select id="selectUsernameById" parameterType="_int" resultType="string">
    	SELECT user_username FROM user WHERE user_id = #{id}
    </select>
    
    <select id="selectList" resultType="User">
    	SELECT 
        	user_id id,
        	user_username username,
        	user_creae_datetime createDateTime,
        	user_gender gender
        FROM
        	user
    </select>
</mapper>
04 Map类型
  • UserMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.example.mybatis.entity.Gender;
import org.example.mybatis.entity.User;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapper {
    
    User selectById(int id);
    
    User selectByUsernameAndGender(User user);
    
    User selectByGenderAndUsername(@Param("gender") Gender gender,
                                  @Param("username") String username);
        							
    long count();
    
    String selectUsernameById(int id);
    
    List<User> selectList();
    
    Map<String Object> selectByIdToHashMap(int id);
    
    List<Map<String, Object>> selectListOfHashMap();
    
}

4.2 insert update delete

insert update delete 用于完成 插入 更新 删除

01 属性列表
<insert
        id="insertAuthor"
        parameterType="domain.blog.Ahthor"
        flushCache="true"
        statementType="PREPARED"
        keyProperty=""
        useGeneratedKeys=""
        timeout="20">
</insert>
<update
        id="updateAuthor"
        parameterType="domain.blog.Author"
        flushCache="true"
        statementType="PREPARED"
        timeout="20">
</update>
<delete
        id="deleteAuthor"
        parameterType="domain.blog.Author"
        flushCache="true"
        statementType="PREPARED"
        timeout="20">
</delete>
  • 属性说明

image-20240924222443061

强调
  1. insert update delete 没有返回指定的类型
  2. insert update delete 只能返回 int 或 boolean 类型
增改删实例
  • UserMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotation.Mapper;
import org.apache.ibatis.annotations.Param;
import org.example.mybatis.entity.Gender;
import org.example.mybatis.entity.User;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapper {
    
    int insert(User user);
    
    int updateUsernameById(@Param("username") String username,
                          @Param("id") int id);
    
    int deleteByLikeUsername(String username);
    
}
  • UserMapper.xml
<?xml version="1.0" encoding="TUF-8" ?>
<!DOCTYPE mapper PUBLIC
			"-//mybatis.org//DTD Mapper 3.0//EN"
			"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.mybatis.mapper.UserMapper">

	<insert id="insert" parameterType="User">
    	INSERT INTO user(
        					user_id,
        					user_username,
        					user_create_datetime,
        					user_gender)
        		VALUE (
        					null,
        					#{username},
        					#{createDatetime},
        					#{gender}
        )
    </insert>
    
    <update id="updateUsernameById">
    	UPDATE user SET user_username = #{username} WHERE user_id = #{id}
    </update>
    
    <delete id="deleteByLikeUsername" parameterType="string">
        DELETE FROM WHERE user_name LIKE CONCAT('%', #{username}, '%')
    </delete>
</mapper>
02 自增主键
  • UserMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.example.mybatis.entity.Gender;
import org.example.mybatis.entity.User;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserMapper {
    
    int insert(User user);
    
}
  • UserMapper.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="org.example.mybatis.mapper.UserMapper">
	<insert id="insert"
            patameterType="User"
            userGeneratedKeys="true"
            keyColumn="user_id"
            keyProperty="id">
        
        INSERT INTO user(
        					user_id,
        					user_username,
        					user_create_datetime,
        					user_gender)
        		VALUES (
        					null,
        					#{username},
        					#{createDatetime},
        					#{gender}
       			 )
    </insert>
    
</mapper>

4.3 参数

Mybatis 无论查询还是更新 都支持单个基本参数,对象参数,多个基本参数

  • parameterType 支持单个基本参数
  • parameterType 对象参数
  • parameterType 多个基本参数
01 单个参数
  • UserMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.example.mybatis,entity.Gender;
import org.example.mybatis.entity.User;

@Mapper
public interface UserMapper {
    
    public User selectById(int id);
    
}
  • UserMapper.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="org.example.mybatis.mapper.UserMapper">
	<select id="selectById" parameterType="_int" resultType="User">
    	SELECT
        	user_id id,
        	user_username username,
        	user_create_datetime createDatetime,
        	user_gender gender
        FROM	
        	user
        WHERE
        	user_id = #{id}
    </select>
    
</mapper>
02 对象参数
  • UserMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotaions.Param;
import org.example.mybatis.entity.Gender;
import org.example.mybatis.entity.User;

@Mapper
public interface UserMapper {
    
    public User selectById(int id);
    
    public User selectByUsernameAndGender(User user);
    
}
  • UserMapper.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="org.example.mybatis.mapper.UserMapper"
        
        <select id="selectById" parameterType="_int" resultType="User">
			SELECT
				user_id id,
				user_username username,
				user_create_datetime createDatetime,
				user_gender gender
			FROM
				user
			WHERE
				user_id = #{id}
		</select>
03 多个参数
  • UserMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.example.mybatis.entity.Gender;
import org.example.mybatis.entity.User;

@Mapper
public interface UserMapper {
    
    public User selectById(int id);
    
    public User selectByUsernameAndGender(User user);
    
    public User selectByGenderAndUsername(@Param("gender") Gender gender),
    									  @Param("username") String username);
}
  • UserMapper.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="org.example.mybatis.mapper.UserMapper">

	<select id="selectById" parameterType="_int" resultType="User">
		SELECT
			user_id id,
			user_username username
			user_create_datetime createDatetime,
			user_gender gender
		FROM
			user
		WHERE
			user_id = #{id}
 	</select>
    
    <select id="selectByUsernameAndGender" parameterType="User" resultType="User">
		SELECT
			user_id id,
			user_username username
			user_create_datetime createDatetime,
			user_gender gender
		FROM
			user
		WHERE
			user_username = #{username} AND user_gender = #{gender}
 	</select>
    
    <select id="selectByGenderAndUsername" resultType="User">
		SELECT
			user_id id,
			user_username username
			user_create_datetime createDatetime,
			user_gender gender
		FROM
			user
		WHERE
			user_username = #{username} AND user_gender = #{gender}
 	</select>
</mapper>

4.4 高级结果映射

resultMap 可完成单个类型,嵌套类型等复杂类型指定映射

01 resultMap
  • resultMap 子标签

    • constructor 用于指定构造方法创建对象
      • idArg 指定 主键列 列提高性能
      • arg 指定 普通列
  • id 无参构造创建对象 指定 主键 列

  • result 无参构造创建对象 指定 普通列

  • association 指定嵌套结果集映射

  • collection 指定被集合包裹的嵌套结果集映射

  • discriminator 指定某列值来决定选取 另外的 ResultMap 映射

    • case
  • resultMap 属性

    • id 指定当前映射的 ID 唯一标识
    • type 指定当前映射的主类型
    • autoMapping 开启或关闭默认自动映射 会覆盖 autoMappingBehavior 默认 unset
02 基础使用

实体类

  • User.java
package org.example.mybatis.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.time.LocalDateTime;

@Data
@AllArgsConstructor
@NoArgsConstructor

public class User {
    
    private Integer id;
    private String username;
    private LocalDateTime createDatetime;
    private String gender;
    
}
  • Mapper

  • UserMapper.java

package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.example.mybatis.entity.User;

import java.util.List;

@Mapper
public interface UserMapper {
    
    User selectById(int id);
    
    List<User> selectList();
}
  • UserMapper.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="org.example.mybatis.mapper.UserMapper">

    <!-- 
     <resultMap id="baseUser" type="User">
    		<constructor>
            	<idArg column="user_id" javaType="integer"/>
                <arg column="user_username" javaType="string"/>
                <arg column="user_create_datetime" javaType="java.time.LocalDateTime"/>
                <arg column="user_gender" javaType="string"/>
            </constructor>
       </resultMap>
    -->
    <resultMap id="baseUser" type="User" autoMapping="false">
    	<id column="user_id" property="id"/>
		<result column="user_username" property="username"/>
        <result column="user_create_datetime" property="createDatetime"/>
        <result column="user_gender" property="gender"/>
    </resultMap>
    
    <select id="selectById" parameterType="_int" resultMap="baseUser">
    	SELECT
        	user_id,
        	user_username,
        	user_create_datetime,
        	user_gender
        FROM
        	user
        WHERE
        	user_id = #{id}
    </select>
    
    <select id="selectList" resultMap="baseUser">
    	SELECT
        	user_id,
        	user_username,
        	user_create_datetime,
        	user_gender
        FROM
        	user
    </select>
</mapper>
03 association

association 用于完成嵌套结果集映射, 嵌套关系是 Java 的和 Mybatis 无关

  • association 允许指定指定列映射到 类中各个复杂成员

第一种 一条 SQL语句

  • Role.java
package org.example.mybatis.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Role {
    
    private Integer id;
    private String name;
    
}
  • User.java
package org.example.mybatis,entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok,NoArgsConstructor;

import java.time.LocalDateTime;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    
    private Integer id;
    private String username;
    private LocalDateTime createDatetime;
    
    private Role role;
}
  • UserMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.example.mybatis.entity.User;

import java.util.List;

@Mapper
public interface UserMapper {
    
    User selectById(int id);
    
    List<User> selectList();
}
  • UserMapper.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="org.example.mybatis.mapper.UserMapper">
    
    	<resultMap id="baseUser" type="User" autoMapping="false">
            <id column="user_id" property="id"/>
            <result column="user_username" property="username"/>
            <result column="user_create_datetime" property="createDatetime"/>
            <result column="user_gender" property="gender"/>
    	</resuLtMap>
    
    <resultMap id="baseRole" type="Role">
        <id column="role_id" property="id"/>
        <result column="role_name" property="name"/>
    </resultMap>
    
    <resultMap id="userAndRole" type="User" extends="baseUser">
    	<association property="role" resultMap="baseRole" fetchType="lazy"/>
    </resultMap>
    
    <select id="selectById" parameterType="_int" resultMap="userAndRole">
        SELECT
                U.user_id,
                U.user_username,
                U.user_create_datetime,
                U.user_gender,
                R.role_id,
                R.role_name
        FROM
       			UserU
        JOIN
        		user_role UR
        ON		U.user_id = #{id} AND U.user_id = UR.ur_user_id
        JOIN
        		Role R
        ON		UR.ur_role_id = R.role_id
    </select>
    
    <select id="selectList" resultMap="baseUser">
    	SELECT
        		user_id,
        		user_username
        		user_create_datetime,
        		user_gender
        FROM
        		user
    </select>
    
</mapper>

第二种 多条SQL 语句 [各独立 Mapper]

  • RoleMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotationsMapper;
import org.example.mybatis.entity.Role;

@Mapper
public interface RoleMapper {
	
    Role selectByUserId(int userId);
}
  • UserMapper.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="org.example.mybatis.mapper.RoleMapper">
	
    <resultMap id="baseRole" type="Role">
    	<id column="role_id" property="id"/>
        <result column="role_name" property="name"/>
    </resultMap>
	
    <select id="selectByUserId" parameterType="_int" resultMap="baseRole">
    	SELECT
        	R.role_id,
        	R.role_name
        FROM
        	Role R
        JOIN
        	user_role UR
        ON
        	R.role_id = UR.ur_role_id AND UR.ur_user_id = #{userId}
    </select>
    
</mapper>
  • UserMapper.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="org.example.mybatis.mapper.UserMapper">
	
  <resultMap id="baseUser" type="User"autoMapping="false">
        <id column="user_id" property="id"/>
        <result column="user_username" property="username"/>
        <result column="user_create_datetime" property="createDatetime"/>
   		<result column="user_gender" property="gender"/>
	</resuLtMap>
	<resultMap id="userAndRole"type="User"extends="baseUser">
    
		<association property="role"
					select="org.example.mybatis.mapper.RoleMapper.selectByUserId"
					column="user_id"
    				fetchType="lazy"/>
    </resultMap>
    
<!-- 	<resultMap id="baseRole" type="Role">
			<id column="role_id" property="id"/>
			<result column=”role_name" property="name"/>
		</resultMap>

		<resultMap id="userAndRole” type="User" extends="baseUser">
			<association property="role” resultMap="baseRole" fetchType="lazy"/>
		</resultMap>-->
    
	<select id="selectById" parameterType="_int" resultMap="userAndRole">
		SELECT
			user_id,
			user_username,
			user_create_datetime,
			user_gender
		FROM
			user
		WHERE
			user_id =#{id}
    </select>
</mapper>
04 collection
  • DepartmentMapper.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="org.example.mybatis.mapper.RoleMapper">
	
  <resultMap id="baseDepartment" type="Department">
    	<id column="department_id" property="id"/>
      	<result column="department_name" property="name"/>
  </resultMap>
    
    <resultMap id="baseEmployee" type="Employee">
    	<id column="employee_id" property="id"/>
		<result column="employee_name" property="name"?/>
        <result column="employee_phone" property="phone"/>
    </resultMap>
    
    <resultMap id="deparmentAndEmployee" type="Department" extends="baseDepartment">
    	<colleciont property="employees"
                    javaType="list"
                    ofType="Employee"
                    resultMap="baseEmployee"
                    fetchType="lazy"/>
    </resultMap>
    
    <select id="selectById" parameterType="_int" resultMap="departmentAndEmployee">
    	SELECT
        		D.department_id,
        		D.department_name,
        
        		E.employee_id,
        		E.employee_name,
        		E.employee_phone
        
        FROM
        		department D
        JOIN 	employee E
        ON
        		D.department_id = #{id} AND D.department_id = E.employee_department_id
    </select>
</mapper>

自行 SQL 逻辑关联

<?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="org.example.mybatis.mapper.RoleMapper">
    <resultMap id="baseEmployee" type="Employee">
        <id column="employee_id" property="id"/>
        <result column="employee_name" property="name"/>
        <result column="employee_phone" property="phone"/>
    </resultMap>
    
    <select id="selectByDepartmentId" parameterType="_int" resultMap="">
    		SELECT 
        		employee_id,
        		employee_name,
        		employee_phone,
        	FROM
        		employee
        	WHERE
        		employee_department_id = #{departmentId}
    </select>
</mapper>
05 discriminator

期望根据某个列的值 [这个指一般固定 比如 男 女] 再来决定指定那个 resultMap 映射

  • SQL
CREATE TABLE people (
	people_id INT PRIMARY KEY AUTO_INCREMENT,
    people_id VARCHAR(10),
    people_phone VARBINARY(12),
    people_gender VARCHAR(10)
)

CREATE TABLE male_health_form (
	mhf_id INT PRIMARY KEY AUTO_INCREMENT,
    mhf_height INT,
    mhf_weight INT,
    mhf_pulmonary INT,
    mhf_strong INT,
    mhf_people_id INT
)

CREATE TABLE female_health_foem (
	fhf_id       INT PRIMARY KEY AUTO_INCREMENT,
    fhf_height	 INT,
	fhf_weight   INT,
    fhf_bust	 INT,
    fhf_waist    INT,
    fhs_people_id   INT
)

实体类

  • People.java
package org.example.mybatis.entity;

import lombok.Data;

@public class People {
    
    private Integer id;
    private String name;
    private String phone;
    private String gender;
    
    private HealthForm healthForm;
}
  • HealthForm.java
package org.example.mybatis.entity;

import lombok.Data;

@Data 
public class HealthForm {
    private Integer id;
    private Integer height;
    private Integer weight;
}
  • MaleHealthForm.java
package org.example.mybatis.entity;

import lom.bok.Data;

@Data
public class MaleHealthForm extends HealthForm {
    
    private Integer pulmonary;
    private Integer strong;
    
}
  • FemaleHealthForm.java
package org.example.mybatis.entity;

import lombok.Data;

@Data
public class FemaleHealthForm extends HealthForm {
    
    private Integer bust;
    private Integer waist;
    
}
  • PeopleMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.example.mybatis,entity.People;

@Mapper
public interface PeopleMapper {
    
    People selectById(int id);
    
}
  • PeopleMapper.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="org.example.mybatis.mapper.PeopleMapper">
    
    <resultMap id="basePeople" tyle="People">
        <id column="people_id" property="id"/>
        <result column="people_name" property="name"/>
        <result column="people_phone" property="phone"/>
		<result column="people_gender" property="gender"/>
	</resultMap>
    
    <resultMap id="maleForm" type="MaleHealthForm">
        <id column="mhf_id" property="id"/>
        <result column="mhf_height" property="height"/>
        <result column="mhf_weight" property="weight"/>
        <result column="mhf_pulmonary" property="puLmonary"/>
        <result column="mhf_strong" property="strong"/>
    </resultMap>
   
    <resultMap id="femaleForm" type="FemaleHealthForm">
        <id column="fhf_id" property="id"/>
        <result column="fhf_height" property="height"/>
        <result column="fhf_weight" property="weight"/>
        <result column="fhf_bust" property="bust"/>
        <result column="fhf_waist" property="waist"/>
	</resultMap>
    
    <resultMap id="peopleAndHealthForm" type="People" extends="basePeople">
        <association property="healthForm">
            <discriminator javaType="string" column="people_gender">
                <case value="MALE" resultMap="maleForm"/>
                <case value="FEMALE" resuLtMap="femaLeForm"/>
            </discriminator>
        </association>
    </resultMap>
    
    <select id="selectById" parameterType="_int" resultMap="peopleAndHealthForm">
        
        SELECT
                P.people_id,
                P.people_name,
                P.people_phone,
                P.people_gender,
        
                MHF.mhf_id,
                MHF.mhf_height,
                MHF.mhf_weight,
                MHF.mhf_pulmonary,
                MHF.mhf_strong,
        
                FHF.fhf_id,
                FHF.fhf_height,
                FHF.fhf_weight,
                FHF.fhf_bust,
                FHF.fhf_waist
        FROM 	people P
        LEFT JOIN
       			male_health_form MHF
        ON		P.people_id = MHF.mhf_people_id
        LEFT JOIN
        		female_health_form FHF
        ON		P.people_id = FHF.fhf_people_id
        WHERE	P.people_id= #{id}
    </select>
 </mapper>
06 反向关联

Mybatis 的关联关系是单项的,如果有双向关联则是相互独立的

  • 若有双向关联时,要注意 A 关联 B 关联 A [此时这个 A 不能再关联 B了] 否则死循环

  • 解决方式为 灵活选择 ResultMap

  • 避免双向关联, 设计时考虑从主到次的关联方式

  • Employee.java

package org.example.mybatis.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
    
    private Integer id;
    private String name;
    private String phone;
    
    private Department department;
}
  • EmployeeMapper.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="org.example.mybatis.mapper.EmployeeMapper">
    <resuLtMap id="baseEmployee" type="Employee">
        <id column="employee_id" property="id"/>
        <result column="employee_name" property="name"/>
        <result column="empLoyee_phone" property="phone"/>
    </resultMap>
    
    <resuLtMap id="empLoyeeAndDepartment"type="Employee"eextends="baseEmpLoyee">
   		<association property="department"
   					 column="employee_department_id"
    				select="org.example.mybatis.mapper.DepartmentMapper._selectById"
    				fetchType="lazy"/>
    </resultMap>

    <select id="selectByDeartmentId"
            parameterType="_int"
            resuLtMap="empLoyeeAndDepartment">
		SELECT
                employee_id,
                employee_name,
                employee_phone,
                employee_department_id
		FROM
				employee
		WHERE
				employee_department_id = #{departmentId}
	</select>
</mapper>
  • DepartmentMapper.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="org.example.mybatis.mapper.EmployeeMapper">
    
   		<resultMap id="baseDepartment" type="Department">
            <id column="department_id" property="id"/>
            <result column="department_name" property="name"/>
        </resultMap>
    
        <resultMap id="departmentAndEmployee" type="Department" extends="baseDepartment">
        	<collection property="employees"
        				javaType="list"
        				ofType="EmpLoyee"
        				column="department_id"
        				select="org.example.mybatis.mapper.EmployeeMapper.selectByDepartmentId"
        				fetchType="lazy"/>
        </resultMap>
    
    <select id="selectById" parameterType="_int" resuLtMap="departmentAndEmployee">
            SELECT
                    department_id,
                    department_name
            FROM
                     department
            WHERE
                  department_id =#(id}
    </select>
    
    <select id="_selectById" parameterType="_int" resultMap="baseDepartment">
    SELECT
            department_id,
            department_name
    FROM
            department
    WHERE
            department_id =#{id}
    </select>
    
</mapper>

4.5 自动映射

Mybatis 默认支持两种情况的自动映射

  1. 当表列名和对象属性名称一致时, mybatis 能完自动映射 [可借助此点给列别名]
  2. 开启全局驼峰映射时,列 user_id,属性 userId, mybatis 能完自动映射

4.6 缓存

Mybatis 有一级缓存默认开启,二级缓存需 cache 标签配置

01 一级缓存

Mybatis 的一级缓存是针对 SqlSession

  • 证据 [多次执行相同查询 不会多次发送 SQL 语句]
package org.example.mybatis.api;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatisl.session.SqlSessionFactoryBuilder;
import org.example.mybatis.mapper.UserMapper;

import java.io.IOException;
import java.io.Reader;

public class Application {
    String resources = "mybatis-config.xml";
    
    Reader config = Resources.getResourceAsReader(resources);
    
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
    
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    
    userMapper.selectById(1);
    userMapper.selectById(1);
    userMapper.selectById(1);
    
    sqlSession.close();.
}
  • 只要查询就会被缓存到 SqlSession, 只要查询就会刷新 SqlSession 缓存, 只要更新就会刷新当前对应 namespace SqlSession 缓存。

  • Mybatis 一级缓存是基于命名空间隔离的, 各个 namespace 不会互相影响

  • 一级缓存难以享受且暴力,没有良好的可视化管理方案

02 二级缓存

二级缓存基于 SqlSessionFactory 先获取一级缓存 若无 再获取二级缓存

证据
package org.example.mybatis.api;

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 org.example.mybatis.mapper.UserMapper;

import java.io.IOException;
import java.io.Reader;

public class Application {
    public static void main(String[] args) throws IOEception {
        String resources = "mybaits-config.xml";
        
        Reader config = Resources.getResourceAsReader(resorces);
        
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().builder(config);
        
        SqlSession sqlSessionl = sqlSessionFactory.openSession();
        
        UserMapper userMapper1 = sqlSession1.getMapper(UserMapper.class);
        
        userMapper1.selectById(1);
        
        sqlSession1.commit();
        
        SqlSession sqlSession2 = sqlSessionFactory.openSession();
        
        UserMapper userMapper2 = sqlSession2.getMapper(UserMapper.class);
        
        userMapper2.selectById(1);
        
        sqlSession2.commit();
    }
}
缓存命中率

image-20240927131212183

结论
  • 二级缓存默契开启,但需要使用 < cache> 标签显式对其使用 cache 可轻微控制缓存算法等

  • 二级缓存是基于命名空间隔离的, 各个 namespace 不会相互影响

  • 二级缓存在暴力, 没有良好的可视化管理方案

  • 二级缓存有命中率的概念

  • 特殊的

    • 无论一级二级缓存 数据库没有数据也是一种数据 null 也会缓存
03 缓存方案
  1. 缓存方案应该业务层中实现,业务层会处理数据, 业务层才对业务敏感

  2. 缓存方案必须放在读多写少的场景,否则缓存难以享受且徒增系统负担

  3. 缓存方案必须保证 查询被缓存,更新可能要清除缓存 [是否一定清除看策略]

  4. 缓存方案可以借助 命名空间 + KEY, 思路去隔离缓存来进行管理

  5. 缓存方案不一定非要等到更新操作去刷新缓存, 可以周期性的刷新

  6. 缓存要提供良好的可视化管理方案

4.7 sql

sql 标签用于组装重复的 SQL 语句,不推荐使用 会使得 Mapper 难以维护

  • UserMapper.java
pack org.example.mybaits.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.example.mybatis.entity.User;

import java.util.List;

@Mapper
public interface UserMapper {
    
    User selectById(int id);
    
    List<User> selectList();
}
  • UserMapper.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="org.example.mybatis.mapper.UserMapper">
	
    <sql id="baseSelectUser">
    	SELECT
        	user_id id,
        	user_username username,
        	user_create_datetime createDateTime,
        	user_gender gender
        FROM
        	user
    </sql>
	
    	<select id="selectById" parameterType="_int" resultType="User">
    			<include refid="baseSelectUser"/>
            	WHERE
					user_id = #{id}
        </select>
    	
    	<select id="selectList" resultType="User">
    		<include refid="baseSelectUser"/>
    	</select>
    
</mapper>

05.动态SQL

Mybatis 提供一些特殊标签 用于运行时拼接 SQL语句

  • if | choose | when | otherwise | where |set | bind | foreach
  • 以上标签都是 select | update | delete 的子元素

5.1 where if

  • Mapper.xml
<select id="selectByCondition" parameterType="User" resultMap="baseUser">
	SELECT
			user_id,
    		user_username,
    		user_create_datetime,
    		user_gender
    FROM
    		user
    
    <where>
    	<if test="username != null">
        	user_username = #{username}
        </if>
        <if test="gender != null">
        	AND user_gender = #{gender}                          
        </if>
    </where>
</select>

5.2 choose, when, otherwise

  • Mapper.xml
<select id="selectByOnlyOneCondition" parameterType="User" resultMap="baseUesr">
	SELECT
    		user_id,
    		user_username,
    		user_create_datetime,
    		user_gender
    FROM
    		user
    
    <where>
    	<choose>
        	<when test="username != nulll">
				user_username = #{username}
            </when> 
            <when test="gender != null">
            	user_gender = #{gender}
            </when>
            <otherwise>
            	 1 = 1
            </otherwise>
        </choose>
    </where>
</select>

5.3 set

  • 一个条件都不满足时, SQL 会是错误状态:拿条件列去做一个更新

  • Mapper.xml

<update id="update" parameterType="User">
    UPDATE user
		<set>
    		<if test="id != null">
            	user_id = #{id},
            </if>
            <if test="username != null">
            	user_username = #{username},
            </if>
       </set>
    WHERE user_id = #{id}
</update>

5.4 foreach

  • **foreach 用于遍历集合或数组 比如 in 查询 | 批量插入 **

  • UserMapper.java

package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.example.mybatis.entity.User;

import java.util.List;

@Mapper
public interface UserMapper {
    User selectById(int id);
    
    int updateGenderById(@Param("gender") String gender,
                         @Param("id") int id);
    
    User selectByCondition(User user);
    
    User selectByOnlyOneCondition(User user);
    
    int update(User user);
    
    List<User> selectByIdList(List<Integer> ids);
    
    int insertBatch(List<User> userList);
}
  • Mapper.xml
<select id="selectByIdList" parameterType="list" resultMap="baseUser">
	SELECT
    	user_id,
		user_username,
    	user_create_datetime,
    	user_gender
    FROM
    	user
    WHERE
    	user_id IN
    <foreach collection="list" item="itemId" separator=", " open="(" close=")">
    	#{itemId}
    </foreach>
</select>


<insert id="insertBatch" parameterType="list">
	INSERT INTO user(user_id, user_name, user_create_datetime, user_gender) VALUES
    	<foreach collection="list" item="user" separator=", ">
      		(null, #{user.username}, #{user.createDatetime}, #{user.gender})
    	</foreach>	
</insert>

5.5 script

  • Java
@Update({"<script>",
        "update Author",
        "	<set>",
        "		<if rest='username != null'>username=#{username}, </if>",
        "		<if test='password != null>'password=#{password}, </if>",
        "		<if test='email != null'>email=#{email}, </if>",
        "		<if test='bio != null'>bio=#{bio}</if>,
        "   </set>",
        "where id=#{id}",
        "</script>"})

	void updateAuthorValues(Author author);

5.6 bind

  • 元素允许你在 OGNL 表达式以外创建一个变量,并将其绑定到当前的上下文
<select id="selectBlogsLike" resultType="Blog">
	<bind name="pattern" value="'%' + _parameter.getTitle() + '%'"/>
    	SELECT * FROM BLOG
    	WHERE title LIKE #{pattern}
</select>

5.7 ${}

  • ${} 为上下文表达式获取变量不转义, #{} 是 SQL 占位解析符放入变量会转义满足 SQL

  • #{} 是 SQL 占位符 是 Mybatis 自行实现的

  • 请参考类: org.apache.ibatis.builder.SqlSourceBuilder

  • UserMapper.java

package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.example.mybatis.entity.User;

import java.util.List;

@Mapper 
public interface UserMapper {
    
	String selectOneColumnOneValue(@Param("column") String column,
                                  @Param("id") int id);
}
  • UserMapper.xml
<select id="selectOneColumnOneValue" resultType="string">
	SELECT
    		${column}
    FORM
    		user
    WHERE
    		user_id = #{id}
</select>

06.API

6.1 核心配置

  • Application.java
package org.example.mybatis.api;

import org.apache.ibatis.datasource.pooled.PooledDataSource;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.example.mybatis.entity.User;
import org.example.mybatis.mapper.UserMapper;

public class Application {
    public static void main(String[] args) {
        
        JdbcTransactionFactory factory = new JdbcTransactionFactory();
        
        pooledDataSource dataSource = new PooledDataSource();
        
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql:///mybatis?serverTimezone=Asia/Shanghai";
        String username="root";
        String password="root";
        
        dataSource.setDriver(driver);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        
        Environment environment = new Environment("dev", factory, dataSource);
        
        Configuration configuration = new Configuration(environment);
        
        configuration.addMappers("org.example.mybatis.mapper");
        configuration.getTypeAliasRegistry().registerAliases("org.example.mybatis.entity");
        // configuration.addInterceptor();
        configuration.setCacheEnabled(true);
        
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
        
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        
        // User user = mapper.selectById(3);
        // System.out.println(user);
        
        mapper.selectList().forEach(System.out::println);
        
        sqlSession.commit();
    }
    
}

6.2 映射注解

Mybatis 提供 @Select | @Update @Result 用于完成对应 xml 的映射行为

  • User.java
package org.example.mybatis.entity;

import lombok.Data;

import java.time.LocalDateTime;

@Data
public class User {
    
    private Integer id;
    private String username;
    private LocalDateTime createDatetime;
    private Gender gender;
    
}

public enum Gender {
    
    MAN("男"), WOMAN("女")private final String gender;
    
    private Gender(String gender) {
        this.gender = gender;
    }
    
    public String getGender() {
        return gender;
    }
}
  • UserMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotaions.Select;
import org.example.mybatis.entity.User;

import java.util.List;

@Mapper
public interface UserMapper {
    
    @Select (
    	"
        	SELECT
        			user_id,
        			user_username,
        			user_create_datetime,
        			user_gender
        	FROM
        			user
        	WHERE 
        			user_id = #{id}
        "
    )
    
    @Results(id = "baseUser", value= {
        @Result(id = "true", column = "user_id", property = "id"),
        @Result(id = "user_username", property = "username"),
        @Result(column = "user_create_datetime", property = "createDatetime"),
        @Result(column = "user_gender", property = "gender")
    })
    User selectById(int id);
    
    @Select (
    	"
        	SELECT
        		user_id,
        		user_username,
        		user_create_datetime,
        	FROM
        		user
        "
    )
    
    @ResultMap("bassUser")
    List<User> selectList();
}

07.SQL构建器

Mybatis 提供 Java API 方式构建 SQL 语句

  • Demo
package org.example.mybatis.api;

import org.apache.ibatis.jdbc.SQL;

public class SqlBuilderUsage {
    public static void main(String[] args) {
        String SQL  = new SQL() {
            SELECT("user_id", "user_username");
            FROM("USER");
            WHERE("user_id = #{id}");
        }.toString();
        
        System.out.prinltn(SQL);
    }
}

08.分页插件

8.1 分页逻辑

当分页数据为全表数据或条件查询部分数据时, 可采用不同分页策略

01 全表
  • 当对全表数据进行分页时,可直接借助 ID 进行 SQL 逻辑分页
  • 页码: page = 1
  • SQL
page = 1

SELECT 
	*
FROM
	TABLE_NAME
WHERE
	id >= ((page - 1) * size) && id <= (page * size)
02 条件查询
  • 页码: page = 1 起始位置 = (page - 1) * size
  • SQL
SELECT 
	*
FROM
	TABLE_NAME
WHERE
	查询条件 LIMIT startIndex, count
分页数据
  • 必须返回 总数据量, 总页码, 当前页码数据
  • 总数据量 = SELECT COUNT(*) FROM TABLE [WHERE 条件]
  • 总页码数 = 总数据量 / 每页数据量 [如果除不尽需要 + 1]

8.2 PageHelper

https://pagehelper.github.io/

01 概念
  1. PageHelper 是针对 Mybatis 框架的分页插件,同时也能适配各种数据库
  2. PageHelper 是通过 Mybatis 拦截器 拦截 SQL 并改变 SQL 来实现分页
  • 官网
02 环境
  • pom.xml
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pageheler -->
<dependency>
	<groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>6.0.0</version>
</dependency>
  • dependency

image-20240927173114313

  • mysql
<!-- SQL: SELECT VERSION(): -->
<dependency>
	<groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>
  • pom.xml [src/main/java 非java文件编译打包配置]
<build>
	<resources>
    	<resource>
        	<directory>src/main/java</directory>
            <includes>
            	<include>**/*.*</include>
            </includes>
        </resource>
        <resource>
        	<directory>src/mian/resources</directory>
            <includes>
            	<include>**/*.*</include>
            </includes>
        </resource>
    </resources>
</build>
03 应用
  • GoodsMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.example.mybatis.entity.Goods;

import java.util.List;

@Mapper
public interface GoodsMapper {
    
    List<Goods> selectByTitleLikeKeyword(String keyword);
    
}
  • s
<?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="org.example.mybatis.mapper.GoodsMapper">
    
    <resultMap id="baseGoods" type="Goods">
        <id column="goods_id" property="id"/>
        <result column="goods_url" property="url"/>
        <result column="goods_title" property="title"/>
        <result column="goods_type" property="type"/>
        <result column="goods_price"property="price"/>
    </resultMap>
    
	<select id="selectByTitleLikeKeyword"
            parameterType="string"
            resultMap="baseGoods">
        	
        SELECT
            goods_id,
            goods_url,
            goods_title,
            goods_type,
            goods_price
        FROM
            tb_goods
        WHERE
            goods_title LIKE CONCAT('%',#{keyword}, '%'}
	</select>
</mapper>
  • Api
GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);

Page<Goods> page = PageHelper.startPage(5, 20)
mapper.selectByTitleLikeKeyWord("手机");
    
// System.out.println("page.getPages() = " + page.getPages());
// System.out.println("page.getTotal() =" + page.getTotal());
// page.getResult().forEach(System.out::println);
  • mybatis-config.xml
<plugins>
	<plugin interceptor="com.github.pagehelper.PageInterceptor"/>
</plugins>
  • Console

image-20240927180233319

09.权限模型实战

基于角色的控制权限 RBAC (Role-Based Access Control)

9.1 表

CREATE TABLE user (
	user_id			INT PRIMARY KEY AUTO_INCREMENT,
    user_username	VARCHAR(20)
    user_phone 		VARCHAR(20)
    ...
)
CREATE TABLE role (
	role_id INT PRIMARY KEY AUTO_INCREMENT,
    role_name 	VARCHAR(20),
)
CREATE TABLE permission (
	permission_id INT PRIMARY KEY AUTO_INCREMENT,
    permission_name VARCHAR(20),
)
CREATE TABLE user_role (
	ur_id INT PRIMARY KEY AUTO_INCREMENT,
    ur_user_id INT
    ur_role_id INT
)
CREATE TABLE role_permission (
	rp_id INT PRIMARY KEY AUTO_INCREMENT,
    rp_role_id 	INT,
    rp_permission_id	INT
)

9.2 实战

实体类

  • User.java
package org.example.mybatis.entity;

import lombok.Data;

@Data
public class User {
    
    private Integer id;
    private String username;
    private String phone;
    
    private Role role;
}
  • Roel.java
package org.example.mybatis.entity;

import lombok.Data;

import java.util.List;

@Data
public class Role {
    private Integer id;
    private String name;
    
    private List<Permission> permissions;
}
  • Permission.java
package org.example.mybatis.entity;

import lombok.Data;

@Data
public class Permission {
    
    private Integer id;
    private String name;
    
}

Mapper

  • UserMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.example.mybatis.entity.User;

@Mapper
public interface UserMapper {
    
    User selectById(int id);
    
}
  • UserMapper.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="org.example.mybatis.mapper.UserMapper">
	
    <resultMap id="baseUser" type="User">
        <id column="user_id" property="id"/>
    	<result column="user_username" property="username"/>
        <result column="user_phone" property="phone"/>
    </resultMap>
	
    <resultMap id="userAndRole" type="User" extends="baseUser">
    	<association property="role"
                     column="user_id"
                     select="org.example.mybatis.mapper.RoleMapper.selectByUserId"
                     fetchType="lazy"/>
    </resultMap>
    
    <select id="selectById" parameterType="_int" resultMap="userAndRole">
    	SELECT
        	user_id,
        	user_username,
        	user_phone
        FROM
        	user
        WHERE
        	user_id = #{id}
    </select>
    
</mapper>
  • RoleMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.example.mybatis.entity.Role;

@Mapper
public interface RoleMapper {
    
    Role selectByUserId(int userId);
    
}
  • RoleMapper.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="org.example.mybatis.mapper.UserMapper">
	
    <resultMap id="baseRole" type="Role">
        <id column="role_id" property="id"/>
    	<result column="role_name" property="name"/>
    </resultMap>
	
    <resultMap id="roleAndPermission" type="Role" extends="baseRole">
    	<association property="permissions"
                     javaType="list"
                     ofType="Permission"
                     column="role_id"
                     select="org.example.mybatis.mapper.PermissionMapper.selectListByRoleId"
                     fetchType="lazy"/>
    </resultMap>
    
    <select id="selectByUserId" parameterType="_int" resultMap="roleAndPermission">
    	SELECT
        	R.role_id,
        	R.role_name
        FROM
        	ROLE R
        JOIN 	user_role UR
		ON		R.role_id = UR.ur_role_id AND UR.ur_user_id = #{userId}
    </select>
    
</mapper>
  • PermissionMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.example.mybatis.entity.Permission;

import java.util.List;

@Mapper
public interface PermissionMapper {
    
    List<Permission> selectListByRoleId(int roleId);
    
}
  • PermissionMapper.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="org.example.mybatis.mapper.UserMapper">
	
    <resultMap id="basePermission" type="Permission">
        <id column="permission_id" property="id"/>
    	<result column="permission_name" property="name"/>
    </resultMap>
    
    <select id="selectListByRoleId" parameterType="_int" resultMap="basePermission">
    	SELECT
        	P.permission_id,
        	P.permission_name
        FROM
        	permission P
        JOIN 	role_permission RP
		ON		RP.rp_permission_id = P.permission_id AND RP.rp_role_id = #{roId}
    </select>
    
</mapper>
  • RoleMapper.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="org.example.mybatis.mapper.UserMapper">
	
    <resultMap id="baseRole" type="Role">
        <id column="role_id" property="id"/>
    	<result column="role_name" property="name"/>
    </resultMap>
	
    <resultMap id="roleAndPermission" type="Role" extends="baseRole">
    	<association property="permissions"
                     javaType="list"
                     ofType="Permission"
                     column="role_id"
                     select="org.example.mybatis.mapper.PermissionMapper.selectListByRoleId"
                     fetchType="lazy"/>
    </resultMap>
    
    <select id="selectByUserId" parameterType="_int" resultMap="roleAndPermission">
    	SELECT
        	R.role_id,
        	R.role_name
        FROM
        	ROLE R
        JOIN 	user_role UR
		ON		R.role_id = UR.ur_role_id AND UR.ur_user_id = #{userId}
    </select>
    
</mapper>
  • PermissionMapper.java
package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.example.mybatis.entity.Permission;

import java.util.List;

@Mapper
public interface PermissionMapper {
    
    List<Permission> selectListByRoleId(int roleId);
    
}
  • PermissionMapper.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="org.example.mybatis.mapper.UserMapper">
	
    <resultMap id="basePermission" type="Permission">
        <id column="permission_id" property="id"/>
    	<result column="permission_name" property="name"/>
    </resultMap>
    
    <select id="selectListByRoleId" parameterType="_int" resultMap="basePermission">
    	SELECT
        	P.permission_id,
        	P.permission_name
        FROM
        	permission P
        JOIN 	role_permission RP
		ON		RP.rp_permission_id = P.permission_id AND RP.rp_role_id = #{roId}
    </select>
    
</mapper>
Logo

助力广东及东莞地区开发者,代码托管、在线学习与竞赛、技术交流与分享、资源共享、职业发展,成为松山湖开发者首选的工作与学习平台

更多推荐