Mybatis详细教程 （万字详解)

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">SELECTuser_id id,user_username usernameFORMuserWHEREuser_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() {@Overridepublic 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 整体运行行为

  • 当未配置内全局 key 时, Mybatis 将使用默认的内置初始值
  • 参考链接: 配置_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> {@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {ps.setString(i, parameter);}@Overridepublic String getNullableResult(ResultSet rs, String columnName) throws SQLException {return rs.getString(columnName);}@Overridepublic String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {return rs.getString(columnIndex);}@Overridepublic 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> {@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, Gender gender, JdbcType jdbcType) throws SQLException {int sex = gender == Gender.MAN ? 0 : 1;ps.setInt(i, sex);}@Overridepublic Gender getNullableResult(ResultSet rs, String columnName) throws SQLException {int sex = rs.getInt(columnName);return sex == 0 ? Gender.MAN : Gender.WOMAN;}@Overridepublic Gender getNullableResult(ResultSet rs, int columnIndex) throws SQLException {int sex = rs.getInt(columnIndex);return sex == 0 ? Gender.MAN : Gender.WOMAN;}// 没有相关的调用方法@Overridepublic 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 {@Overridepublic <T> T create(Class<T> type) {return super.create(type);}@Overridepublic <T> T create(Class<T> type, List<Class<?>> constructorArgTypes, List<Object> constructorArgs) {return super.create(type, constructorArgTypes, constructorArgs);}@Overridepublic void setProperties(Properties properties) {super.setProperties(properties);}@Overridepublic <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 {@Overridepublic Object intercept(Invocation invocation) throws Throwable {// implement pre processing if needObject returnObject = invocation.proceed();// implement post processing if needreturn returnObject;}private Properties properties = new Properties();@Overridepublic 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); // 这是一条 SQLuserMapper.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">SELECTuser_id id,user_username usernameuser_create_datetime createDatetimeuser_gender genderFROMuserWHERE user_id = #{id}
</select><select id="selectById"parameterType="_int"resultType="user"databaseId="mysql">SELECTuser_id id,user_username usernameuser_create_datetime createDatetimeuser_gender genderFROMuserWHERE 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 标签元素只允许的出现的属性
<selectid="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">SELECTuser_id id,user_username username,user_create_datetime createDateTime,user_gender genderFROMuserWHERE user_id = #{id}</select><select id="selectByUsernameAndGender" parameterType="User" resultType="User">SELECTuser_id id,user_username username,user_create_datetime createDatetime,user_gender genderFROMuserWHEREuser_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 genderFROMuserWHEREuser_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 genderFROMuser</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 属性列表
<insertid="insertAuthor"parameterType="domain.blog.Ahthor"flushCache="true"statementType="PREPARED"keyProperty=""useGeneratedKeys=""timeout="20">
</insert>
<updateid="updateAuthor"parameterType="domain.blog.Author"flushCache="true"statementType="PREPARED"timeout="20">
</update>
<deleteid="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">SELECTuser_id id,user_username username,user_create_datetime createDatetime,user_gender genderFROM	userWHEREuser_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">SELECTuser_id id,user_username username,user_create_datetime createDatetime,user_gender genderFROMuserWHEREuser_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">SELECTuser_id id,user_username usernameuser_create_datetime createDatetime,user_gender genderFROMuserWHEREuser_id = #{id}</select><select id="selectByUsernameAndGender" parameterType="User" resultType="User">SELECTuser_id id,user_username usernameuser_create_datetime createDatetime,user_gender genderFROMuserWHEREuser_username = #{username} AND user_gender = #{gender}</select><select id="selectByGenderAndUsername" resultType="User">SELECTuser_id id,user_username usernameuser_create_datetime createDatetime,user_gender genderFROMuserWHEREuser_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
@NoArgsConstructorpublic 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">SELECTuser_id,user_username,user_create_datetime,user_genderFROMuserWHEREuser_id = #{id}</select><select id="selectList" resultMap="baseUser">SELECTuser_id,user_username,user_create_datetime,user_genderFROMuser</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">SELECTU.user_id,U.user_username,U.user_create_datetime,U.user_gender,R.role_id,R.role_nameFROMUserUJOINuser_role URON		U.user_id = #{id} AND U.user_id = UR.ur_user_idJOINRole RON		UR.ur_role_id = R.role_id</select><select id="selectList" resultMap="baseUser">SELECTuser_id,user_usernameuser_create_datetime,user_genderFROMuser</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">SELECTR.role_id,R.role_nameFROMRole RJOINuser_role URONR.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">SELECTuser_id,user_username,user_create_datetime,user_genderFROMuserWHEREuser_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">SELECTD.department_id,D.department_name,E.employee_id,E.employee_name,E.employee_phoneFROMdepartment DJOIN 	employee EOND.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,FROMemployeeWHEREemployee_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">SELECTP.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_waistFROM 	people PLEFT JOINmale_health_form MHFON		P.people_id = MHF.mhf_people_idLEFT JOINfemale_health_form FHFON		P.people_id = FHF.fhf_people_idWHERE	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">SELECTemployee_id,employee_name,employee_phone,employee_department_idFROMemployeeWHEREemployee_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">SELECTdepartment_id,department_nameFROMdepartmentWHEREdepartment_id =#(id}</select><select id="_selectById" parameterType="_int" resultMap="baseDepartment">SELECTdepartment_id,department_nameFROMdepartmentWHEREdepartment_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">SELECTuser_id id,user_username username,user_create_datetime createDateTime,user_gender genderFROMuser</sql><select id="selectById" parameterType="_int" resultType="User"><include refid="baseSelectUser"/>WHEREuser_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">SELECTuser_id,user_username,user_create_datetime,user_genderFROMuser<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">SELECTuser_id,user_username,user_create_datetime,user_genderFROMuser<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">SELECTuser_id,user_username,user_create_datetime,user_genderFROMuserWHEREuser_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 BLOGWHERE 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}FORMuserWHEREuser_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 ("SELECTuser_id,user_username,user_create_datetime,user_genderFROMuserWHERE 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 ("SELECTuser_id,user_username,user_create_datetime,FROMuser")@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 = 1SELECT *
FROMTABLE_NAME
WHEREid >= ((page - 1) * size) && id <= (page * size)
02 条件查询
  • 页码: page = 1 起始位置 = (page - 1) * size
  • SQL
SELECT *
FROMTABLE_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">SELECTgoods_id,goods_url,goods_title,goods_type,goods_priceFROMtb_goodsWHEREgoods_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 INTur_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">SELECTuser_id,user_username,user_phoneFROMuserWHEREuser_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">SELECTR.role_id,R.role_nameFROMROLE RJOIN 	user_role URON		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">SELECTP.permission_id,P.permission_nameFROMpermission PJOIN 	role_permission RPON		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">SELECTR.role_id,R.role_nameFROMROLE RJOIN 	user_role URON		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">SELECTP.permission_id,P.permission_nameFROMpermission PJOIN 	role_permission RPON		RP.rp_permission_id = P.permission_id AND RP.rp_role_id = #{roId}</select></mapper>

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.xdnf.cn/news/1548214.html

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！

相关文章

【含文档】基于Springboot+Vue的高校竞赛管理系统（含源码+数据库+lw）

【含文档】基于Springboot+Vue的高校竞赛管理系统（含源码+数据库+lw）

1.开发环境 开发系统:Windows10/11 架构模式:MVC/前后端分离 JDK版本: Java JDK1.8 开发工具:IDEA 数据库版本: mysql5.7或8.0 数据库可视化工具: navicat 服务器: SpringBoot自带 apache tomcat 主要技术: Java,Springboot,mybatis,mysql,vue 2.视频演示地址 系统定义了三个…
阅读更多...
机器学习学习笔记-20240927

机器学习学习笔记-20240927

文章目录 一些简单的指令数据操作广播机制 标量&#xff0c;向量&#xff0c;矩阵的相互求导1. 标量对标量的求导2. 标量对向量的求导3. 向量对标量的求导4. 向量对向量的求导5. 矩阵对标量的求导6. 矩阵对向量的求导 链式求导法则YYDS求出损失函数偏导为0时的最优解w*1. 损失函…
阅读更多...
昇思MindSpore进阶教程-格式转换

昇思MindSpore进阶教程-格式转换

大家好&#xff0c;我是刘明&#xff0c;明志科技创始人&#xff0c;华为昇思MindSpore布道师。 技术上主攻前端开发、鸿蒙开发和AI算法研究。 努力为大家带来持续的技术分享&#xff0c;如果你也喜欢我的文章&#xff0c;就点个关注吧 MindSpore中可以把用于训练网络模型的数据…
阅读更多...
打造未来社交：区块链社交DAO的颠覆性开发之路

打造未来社交：区块链社交DAO的颠覆性开发之路

随着区块链技术的不断发展&#xff0c;去中心化自治组织&#xff08;DAO&#xff09;逐渐成为一种创新的社交模式。结合区块链的透明性和不可篡改性&#xff0c;社交DAO为用户提供了一种全新的参与和治理方式&#xff0c;重塑了社交网络的构建与互动方式。本文将探讨区块链社交…
阅读更多...
【鸿蒙】HarmonyOS NEXT应用开发快速入门教程之布局篇（上）

【鸿蒙】HarmonyOS NEXT应用开发快速入门教程之布局篇（上）

系列文章目录 【鸿蒙】HarmonyOS NEXT开发快速入门教程之ArkTS语法装饰器&#xff08;上&#xff09; 【鸿蒙】HarmonyOS NEXT开发快速入门教程之ArkTS语法装饰器&#xff08;下&#xff09; 【鸿蒙】HarmonyOS NEXT应用开发快速入门教程之布局篇&#xff08;上&#xff09; 文…
阅读更多...
Python画笔案例-066 绘制橙子

Python画笔案例-066 绘制橙子

1、绘制橙子 通过 python 的turtle 库绘制 橙子，如下图： 2、实现代码 绘制 橙子，以下为实现代码： """橙子.py注意亮度为0.5的时候最鲜艳本程序需要coloradd模块支持,安装方法:pip install coloradd程序运行需要很长时间,请耐心等待。可以把窗口最小化,然后…
阅读更多...
教师工作量在线管理服务

教师工作量在线管理服务

4系统概要设计 4.1概述 本系统采用B/S结构(Browser/Server,浏览器/服务器结构)和基于Web服务两种模式&#xff0c;是一个适用于Internet环境下的模型结构。只要用户能连上Internet,便可以在任何时间、任何地点使用。系统工作原理图如图4-1所示&#xff1a; 图4-1系统工作原理…
阅读更多...
springAOP和spring事务

springAOP和spring事务

AOP 1.简介 Aop面向切面编程&#xff1a;在开发中我们不能直接对已经设计好的代码进行修改&#xff08;开放-封闭原则&#xff0c;对扩展开放&#xff0c;对修改封闭&#xff09;&#xff0c;解耦 AOP的底层实现为动态代理 * Target&#xff08;目标对象&#xff09;&#…
阅读更多...
专业网站建设必备

专业网站建设必备

专业网站建设不仅仅是简单的页面搭建&#xff0c;更是一项综合性的工程&#xff0c;需要结合行业特性、用户体验和技术创新&#xff0c;打造一个符合企业需求、独具特色的线上空间。 第一印象至关重要 一个企业网站就如同公司的数字名片&#xff0c;第一印象往往决定了用户是否…
阅读更多...
衡石分析平台系统管理手册-功能配置之全局 JS 设置

衡石分析平台系统管理手册-功能配置之全局 JS 设置

全局 JS 设置​ 衡石系统提供了全局 JS 设置功能&#xff0c;用户可以通过自定义 JS 代码实现系统的个性化需求&#xff0c;如使用第三方统计工具对系统平台的 PV 、UV 进行监测。 使用场景​ 场景1&#xff1a;增加百度统计​ 下图示例中使用 js 代码引用了百度网站统计功…
阅读更多...
MQTT.fx 1.7.1使用说明篇（OneNET-MQTT-API调试）

MQTT.fx 1.7.1使用说明篇（OneNET-MQTT-API调试）

&#xff08;代码完美实现&#xff09;stm32 新版 onenet mqtt物联网(保姆级教程) &#xff08;代码完美实现&#xff09;stm32 新版 onenet mqtt物联网(保姆级教程)https://blog.csdn.net/Wang2869902214/article/details/142501323 MQTT.fx 1.7.1使用教程 下载地址 MQ…
阅读更多...
深圳龙链科技：全球区块链开发先锋，领航Web3生态未来

深圳龙链科技：全球区块链开发先锋，领航Web3生态未来

【深圳龙链科技】是全球领先的Web3区块链技术开发公司&#xff0c;专注于为全球客户提供创新高效的区块链解决方案。 深圳龙链科技由币安资深股东携手香港领先的Web3创新枢纽Cyberport联袂打造&#xff0c;立足于香港这一国际金融中心&#xff0c;放眼全球&#xff0c;汇聚了华…
阅读更多...
部分监督多器官医学图像分割中的标记与未标记分布对齐|文献速递--基于多模态-半监督深度学习的病理学诊断与病灶分割

部分监督多器官医学图像分割中的标记与未标记分布对齐|文献速递--基于多模态-半监督深度学习的病理学诊断与病灶分割

Title 题目 Labeled-to-unlabeled distribution alignment for partially-supervised multi-organ medical image segmentation 部分监督多器官医学图像分割中的标记与未标记分布对齐 01 文献速递介绍 多器官医学图像分割&#xff08;Mo-MedISeg&#xff09;是医学图像分析…
阅读更多...
『功能项目』下载Mongodb【81】

『功能项目』下载Mongodb【81】

下载网址&#xff1a;Download MongoDB Community Server | MongoDB 点击安装即可 选择Custom 此时安装已经完成 桌面会创建图标 检查是否配置好MongoDB 输入cmd命令行 Windows键 R 打开命令行 输入cmd 复制安装路径 复制data路径 如果输出一大串代码即配置mongdb成功
阅读更多...
LeetCode 面试经典150题 172.阶乘后的零

LeetCode 面试经典150题 172.阶乘后的零

题目&#xff1a;给定一个整数 n &#xff0c;返回 n! 结果中尾随零的数量。 提示 n! n * (n - 1) * (n - 2) * ... * 3 * 2 * 1 思路&#xff1a; 代码&#xff1a; class Solution {public int trailingZeroes(int n) {return n 0 ? 0 : n / 5 trailingZeroes(n / 5);}…
阅读更多...
央企办医布局智慧医康养，环球医疗（2666.HK）让养老有“医”靠

央企办医布局智慧医康养，环球医疗（2666.HK）让养老有“医”靠

投资传奇查理芒格说&#xff1a;“我给自己设定的目标是追求平常人没有的常识。”只有挖掘出市场潜移默化的趋势&#xff0c;才能抓住投资机遇。 当下&#xff0c;资本市场一个被低估的产业趋势是&#xff0c;医疗健康行业大并购时代悄然开启&#xff0c;头部上市公司对并购产…
阅读更多...
【Python快速学习笔记01】下载解释器/环境变量配置/PyCharm下载/第一个代码

【Python快速学习笔记01】下载解释器/环境变量配置/PyCharm下载/第一个代码

目录 1.下载python解释器 2.第一个python程序 3.配置解释器环境变量 4.下载开发工具 PyCharm 4.通过PyCharm编写第一个python程序 1.下载python解释器 官网下载&#xff0c;但是下载太慢了&#xff0c;所以直接百度搜了下载了个 Welcome to Python.org 1.官网下载 2.直…
阅读更多...
香港科技大学数据建模MSc(DDM)硕士学位项目25/26招生宣讲会-西安专场

香港科技大学数据建模MSc(DDM)硕士学位项目25/26招生宣讲会-西安专场

香港科技大学数据建模MSc(DDM)硕士学位项目25/26招生宣讲会-西安专场 &#x1f559;时间&#xff1a;2024 年10 月12日&#xff08;周六&#xff09; 16:00 &#x1f3e0;地点&#xff1a; 西安交大南洋大酒店(交通大学青龙寺店) 行政会议室 &#x1f9d1;‍&#x1f393;嘉宾…
阅读更多...
VS code 使用 Jupyter Notebook 时显示 line number

VS code 使用 Jupyter Notebook 时显示 line number

VS code 使用 Jupyter Notebook 时显示 line number 引言正文引言 有些时候，我们在 VS code 中必须要使用 Jupyter Notebook，但是默认情况下，Jupyter Notebook 是不显示 Line number 的，这对于调试工作的定位是不友好的，这里我们将介绍如何让 Jupyter Notebook 显示 Line…
阅读更多...
反问面试官：如何实现集群内选主

反问面试官：如何实现集群内选主

面试官经常喜欢问什么zookeeper选主原理、什么CAP理论、什么数据一致性。经常都被问烦了&#xff0c;我就想问问面试官&#xff0c;你自己还会实现一个简单的集群内选主呢&#xff1f;估计大部分面试官自己也写不出来。 本篇使用 Java 和 Netty 实现简单的集群选主过程的示例。…
阅读更多...
最新文章

Copyright @ 2022~2023