Mybatis详细教程 (万字详解)
Mybatis 3.5.14来自于B站‘天气预报’,一名宝藏up,跟着他可以培养起独立解决编程问题的能力!!!01.简介1.1 官网官方中文网: MyBatis中文网中文网参考手册1.2 概念MyBatis 是一款优秀的持久层框架,支持自定义 SQL, 存储过程, 高级映射官方解释必要前置技术Mysql [本课件以 MySQL 为例]JDBC [Java Database Connection]
Mybatis 3.5.14
来自于B站‘天气预报’,一名宝藏up,跟着他可以培养起独立解决编程问题的能力!!!
01.简介
1.1 官网
官方中文网: MyBatis中文网
- 中文网参考手册
1.2 概念
MyBatis 是一款优秀的持久层框架,支持自定义 SQL, 存储过程, 高级映射
必要前置技术
- Mysql [本课件以 MySQL 为例]
- JDBC [Java Database Connection] API
通俗解释
-
Mybatis 框架 是对原生 JDBC 技术的封装 和 增强
-
Mybatis 框架 核心行为是将 JDBC ResultSet 结果集映射到类的过程 如
-
映射关系示意
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
- 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 是一款日志门面抽象框架,本身并未实现,需使用具体的日志框架
- log4j 是一款实现的日志框架,对 SLF4J 有桥接实现
- log4j2 是log4j 2.y.z 版本, 同样对 SLF4J 有桥接实现
- logback 是 直接针对 SLF$J 实现的日志框架
官网: SLF4J Manual
- 官方参考手册
版本注意事项: https://github.com/gos-ch/slf4j
- Github
03.Logback
Logback 针对 SLF4J 的日志实现 核心坐标 [logback-core | logback-classsic]
- dependency
- 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.逻辑流程
- 读取并,解析核心配置文件 mybatis-config.xml
- 装配数据库连接以及配置事务管理
- 读取 mapper 接口及 xml 文件所在位置并完成其对应的逻辑关联
- mapper 代理对象调用 mapper 接口方法时 mybatis 利用动态代理找到对应的标签 [xpath]
- mybatis 找到对应标签 解析 SQL 表达式及动态代理完成参数的适配
- mybatis 使用 JDBC 执行 SQL 操作, 拿到 ResultSet
- 通过配置告知 mybatis 列和对象属性的对应关系,让 mybatis 完整封装
03.全局配置
了解掌握 Mybatis 全局配置的 是将 Mybatis 熟练运用和解决问题的关键
- Mybatis 全局配置都位于 mybatis-config.xml 中, 也可同理基于 Java 配置
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 整体运行行为
- 当未配置内全局 key 时, Mybatis 将使用默认的内置初始值
- 参考链接: 配置_MyBatis中文网
- 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中文网
3.4 类型处理器
Mybatis 内置类型处理器用于完成 列和 对象属性的数据类型的对应关系
-
当内置的所有处理器都不能对应 列和属性的数据类型时, Mybatis 将抛出异常
-
解决
- 调整 列 或者 属性的数据类型 使用 Mybatis 能够处理 [推荐]
- 自行实现类型处理器并注册到 Mybatis 框架中 [注册分为全局和局部]
01.内置类型处理器
参考链接: 配置_MyBatis中文网
-
内置类型处理器
- 自定义数据类型
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 标签属性说明
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>
强调
- insert update delete 没有返回指定的类型
- 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 单个参数
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);
}
<?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 指定 普通列
- constructor 用于指定构造方法创建对象
-
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 默认支持两种情况的自动映射
- 当表列名和对象属性名称一致时, mybatis 能完自动映射 [可借助此点给列别名]
- 开启全局驼峰映射时,列 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();
}
}
缓存命中率
结论
-
二级缓存默契开启,但需要使用 < cache> 标签显式对其使用 cache 可轻微控制缓存算法等
-
二级缓存是基于命名空间隔离的, 各个 namespace 不会相互影响
-
二级缓存在暴力, 没有良好的可视化管理方案
-
二级缓存有命中率的概念
-
特殊的
- 无论一级二级缓存 数据库没有数据也是一种数据 null 也会缓存
03 缓存方案
-
缓存方案应该业务层中实现,业务层会处理数据, 业务层才对业务敏感
-
缓存方案必须放在读多写少的场景,否则缓存难以享受且徒增系统负担
-
缓存方案必须保证 查询被缓存,更新可能要清除缓存 [是否一定清除看策略]
-
缓存方案可以借助 命名空间 + KEY, 思路去隔离缓存来进行管理
-
缓存方案不一定非要等到更新操作去刷新缓存, 可以周期性的刷新
-
缓存要提供良好的可视化管理方案
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 概念
- PageHelper 是针对 Mybatis 框架的分页插件,同时也能适配各种数据库
- 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
- 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
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>
更多推荐
所有评论(0)