毕设学习第七天SSM框架之Mybatis
持久化和持久层
持久化 是指数据的保存和恢复，是一个技术概念，主要关注如何持久化数据并保证数据的长期存储。
持久层 是应用程序的一层，负责实现数据的持久化操作，通过使用不同的框架和技术（如 ORM）实现对数据的管理。
内存中的数据是无法持久保存的，因此我们需要把数据进行持久化用于长时间储存，在Java中最基础的持久化操作就是JDBC了，通过和数据库建立连接来实现对数据的持久化。
MyBatis
MyBatis 是一个 持久化框架，它使得 Java 应用程序能够通过简单的 SQL 操作与关系型数据库进行交互。与其他 ORM 框架不同，MyBatis 不会自动生成 SQL 语句，而是允许开发者直接编写 SQL 语句，给开发者更多的控制权。
MyBatis 核心组件
SqlSessionFactory：负责创建 SqlSession，管理数据库会话。
SqlSession：表示与数据库的交互，提供执行 SQL 操作的方法。
Mapper：是 Java 接口，定义了与数据库交互的方法。通过 Mapper 接口，MyBatis 可以映射 SQL 查询，并将结果自动填充到 Java 对象中。
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
</dependency>常见的增删改查
package org.example.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private int id;
private String name;
private int age;
private String sex;
private String address;
}package org.example.dao;
import org.example.pojo.User;
import org.springframework.stereotype.Repository;
import java.util.List;
public interface UserDao {
public int add(User user);
public int delete(int id);
public int update(User user);
public List<User> query(String condition);
}<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&useSSL=false&characterEncoding=utf8"/>
<property name="username" value="billsaifu"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration><?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.dao.UserDao">
<insert id="add" parameterType="org.example.pojo.User" useGeneratedKeys="true" keyProperty="id">
INSERT INTO User (name, age, sex, address)
VALUES (#{name}, #{age}, #{sex}, #{address})
</insert>
<delete id="delete" parameterType="int">
DELETE FROM User WHERE id = #{id}
</delete>
<update id="update" parameterType="org.example.pojo.User">
UPDATE User
SET name = #{name}, age = #{age}, sex = #{sex}, address = #{address}
WHERE id = #{id}
</update>
<select id="query" resultType="org.example.pojo.User">
SELECT id, name, age, sex, address
FROM User
WHERE name LIKE #{condition}
</select>
</mapper>package mybatis;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.example.dao.UserDao;
import org.example.pojo.User;
import org.junit.Test;
import java.io.IOException;
import java.util.List;
public class MybatisTest {
@Test
public void test1() throws IOException {
// 加载配置创建一个数据库会话,并且提交事务，默认为false不会改变数据库
String resource = "mybatis-config.xml";
SqlSession sqlSession = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream(resource)).openSession(true);
UserDao userDao = sqlSession.getMapper(UserDao.class);
userDao.add(new User(1, "王叶", 30, "男", "北京"));
userDao.add(new User(2, "李华", 25, "女", "上海"));
userDao.add(new User(3, "张强", 28, "男", "广州"));
userDao.add(new User(4, "刘娜", 35, "女", "深圳"));
userDao.add(new User(5, "陈雷", 22, "男", "成都"));
userDao.add(new User(6, "杨梅", 32, "女", "武汉"));
userDao.add(new User(7, "赵伟", 27, "男", "重庆"));
userDao.add(new User(8, "吴婷婷", 29, "女", "南京"));
userDao.add(new User(9, "孙浩", 24, "男", "天津"));
userDao.add(new User(10, "周静", 33, "女", "杭州"));
List<User> userList = userDao.query("%%");
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
} 
如果参数较多的话可以使用map来进行化简，还有一个细节就是_int表示int类型，而int则表示为包装类Integer类型，即常用类型前面加上_则为对象的类型
package org.example.dao;
import org.example.pojo.User;
import java.util.List;
import java.util.Map;
public interface UserDao {
public int add(User user);
public int delete(int id);
public int update(User user);
public List<User> query(Map<String, Object> map);
}<select id="query" resultType="org.example.pojo.User" parameterType="map">
SELECT id, name, age, sex, address
FROM User
WHERE name LIKE #{name} or address LIKE #{address}
</select>HashMap<String,Object> map = new HashMap<>();
map.put("address","%州%");
List<User> userList = userDao.query(map);这样可以不用定义特定的JavaBean，可以直接通过key-value进行传参
mybatis作为持久层框架，一般由xml配置文件，将dao层的实现类由类变为mapper.xml文件，映射到对应的接口实现CRUD
使用包扫描进行映射，要求xml文件和接口在同一个目录下且为相同的名字
<mappers>
<package name="org.example.dao"/>
</mappers>该配置是让idea不会忽略src目录下的xml文件
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
</build>
结果集映射（ResultMap）
resultMap是核心的结果映射机制，用于将数据库多表关联、一对多关系、对象对象等，对于复杂的表表关联查询由resultMap实现。
ResultMap子元素
constructor: 用于在实例化类时，将结果注入到构造方法中。idArg: ID 参数；标记出作为 ID 的结果可以提高整体性能。arg: 将被注入到构造方法的一个普通结果。
id: 一个 ID 结果；标记出作为 ID 的结果可以帮助提高整体性能。result: 注入到字段或 JavaBean 属性的普通结果。association: 一个复杂类型的关联；许多结果将包装成这种类型。嵌套结果映射 - 关联可以是resultMap元素，或是对其它结果映射的引用。collection: 一个复杂类型的集合。嵌套结果映射 - 集合可以是resultMap元素，或是对其它结果映射的引用。discriminator: 使用结果值来决定使用哪个resultMap。case: 基于某些值的结果映射。嵌套结果映射 -case也是一个结果映射，因此具有相同的结构和元素；或者引用其它的结果映射。
package org.example.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private int userId;
private String userName;
private int userAge;
private String userSex;
private String userAddress;
}<resultMap id="user" type="org.example.pojo.User">
<!-- column为数据库表字段名，property为实体类变量名 -->
<result column="id" property="userId"/>
<result column="name" property="userName"/>
<result column="age" property="userAge"/>
<result column="sex" property="userSex"/>
<result column="address" property="userAddress"/>
</resultMap>
<select id="query" parameterType="map" resultMap="user">
SELECT id, name, age, sex, address
FROM User
WHERE name LIKE #{name} or address LIKE #{address}
</select>这就通过resultmap简单的解决二者字段不一致的问题，但是该映射主要还是用于多表查询
日志工厂
Mybatis 通过使用内置的日志工厂提供日志功能。内置日志工厂将会把日志工作委托给下面的实现之一：
SLF4、Apache Commons Logging、Log4j 2、Log4j、JDK logging
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>log4j.properties:log4j的配置文件
# priority :debug<info<warn<error
#you cannot specify every priority with different file for log4j
log4j.rootLogger=debug,stdout,info,debug,warn,error
#log4j.appender.*.File用于指定日志存储的位置
#console
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern= [%d{yyyy-MM-dd HH:mm:ss a}]:%p %l%m%n
#info log
log4j.logger.info=info
log4j.appender.info=org.apache.log4j.DailyRollingFileAppender
log4j.appender.info.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.info.File=./src/main/log/info.log
log4j.appender.info.Append=true
log4j.appender.info.Threshold=INFO
log4j.appender.info.layout=org.apache.log4j.PatternLayout
log4j.appender.info.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n
#debug log
log4j.logger.debug=debug
log4j.appender.debug=org.apache.log4j.DailyRollingFileAppender
log4j.appender.debug.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.debug.File=./src/main/log/debug.log
log4j.appender.debug.Append=true
log4j.appender.debug.Threshold=DEBUG
log4j.appender.debug.layout=org.apache.log4j.PatternLayout
log4j.appender.debug.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n
#warn log
log4j.logger.warn=warn
log4j.appender.warn=org.apache.log4j.DailyRollingFileAppender
log4j.appender.warn.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.warn.File=./src/main/log/warn.log
log4j.appender.warn.Append=true
log4j.appender.warn.Threshold=WARN
log4j.appender.warn.layout=org.apache.log4j.PatternLayout
log4j.appender.warn.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n
#error
log4j.logger.error=error
log4j.appender.error = org.apache.log4j.DailyRollingFileAppender
log4j.appender.error.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.error.File = ./src/main/log/error.log
log4j.appender.error.Append = true
log4j.appender.error.Threshold = ERROR
log4j.appender.error.layout = org.apache.log4j.PatternLayout
log4j.appender.error.layout.ConversionPattern = %d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n注意下面的配置要一模一样不然会报错
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>package mybatis;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;
import org.example.dao.UserMapper;
import org.example.pojo.User;
import org.junit.Test;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
public class MybatisTest {
public Logger log = Logger.getLogger(MybatisTest.class);
@Test
public void test1() throws IOException {
// 加载配置创建一个数据库会话,并且提交事务，默认为false不会改变数据库
String resource = "mybatis-config.xml";
SqlSession sqlSession = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream(resource)).openSession(true);
log.info("开启会话");
UserMapper userDao = sqlSession.getMapper(UserMapper.class);
HashMap<String,Object> map = new HashMap<>();
log.info("执行查询");
map.put("address","%州%");
List<User> userList = userDao.query(map);
for (User user : userList) {
System.out.println(user);
}
log.info("关闭会话");
sqlSession.close();
}
}
对于一些简单的语句可以直接使用注解实现
@Select("select * from user where id = #{userId}")
public User queryById(@Param("userId") int id);mybatis解决复杂查询问题
这里我创建了四张表，其中三张主表，一张关联表，用户表，角色表和账号表，用户和账号是一对一关系，而用户和角色是多对多关系,下面是四张表的建表语句
create table account
(
id int auto_increment
primary key,
name varchar(255) not null,
password varchar(255) not null
);
create table role
(
id int auto_increment
primary key,
name varchar(255) not null,
description text null
);
create table user
(
id int auto_increment
primary key,
name varchar(100) not null,
age int not null,
sex varchar(10) null,
address varchar(255) null,
account_id int null,
constraint user___fk
foreign key (account_id) references account (id)
)
create table user_role
(
id int auto_increment
primary key,
u_id int not null,
r_id int not null,
constraint user_role_role_id_fk
foreign key (r_id) references role (id),
constraint user_role_user_id_fk
foreign key (u_id) references user (id)
);这里我当时写的的时候有一个小错误，查询是时候一直查询不到，后来网上查找才知道，原来列名的映射不能直接用表名.列名的形式，而是要给列名也取一个别名
<select id="queryOne" resultMap="user_account">
SELECT U.id, U.name, U.age, U.sex, U.address, A.id, A.name, A.password
FROM user AS U
INNER JOIN account AS A
ON U.account_id = A.id
WHERE U.id = #{id}
</select>
<resultMap id="user_account" type="org.example.pojo.User">
<result column="U.id" property="id"/>
<result column="U.name" property="name"/>
<result column="U.age" property="age"/>
<result column="U.sex" property="sex"/>
<result column="U.address" property="address"/>
<association property="account" javaType="org.example.pojo.Account">
<result column="A.id" property="id"/>
<result column="A.name" property="name"/>
<result column="A.password" property="password"/>
</association>
</resultMap>这是修改之后的代码
<select id="queryOne" resultMap="user_account">
SELECT
U.id AS user_id,
U.name AS user_name,
U.age,
U.sex,
U.address,
A.id AS account_id,
A.name AS account_name,
A.password AS account_password
FROM user AS U
INNER JOIN account AS A
ON U.account_id = A.id
WHERE U.id = #{id}
</select>
<resultMap id="user_account" type="org.example.pojo.User">
<!-- User 字段映射 -->
<result column="user_id" property="id"/>
<result column="user_name" property="name"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/> <!-- 修正此处 -->
<!-- Account 关联对象映射 -->
<association property="account" javaType="org.example.pojo.Account">
<result column="account_id" property="id"/>
<result column="account_name" property="name"/>
<result column="account_password" property="password"/>
</association>
</resultMap>接下来是我的所有示例代码，mybatis实现了一对多和多对多的表之间的关联
package org.example.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private int id;
private String name;
private int age;
private String sex;
private String address;
private List<Role> roles;
private Account account;
public void userPrint() {
System.out.println("----------------------------------------\n");
System.out.println("用户ID: " + id);
System.out.println("用户名: " + name);
System.out.println("年龄: " + age);
System.out.println("性别: " + sex);
System.out.println("地址: " + address);
if (roles != null && !roles.isEmpty()) {
System.out.println("角色:");
for (Role role : roles) {
System.out.println(" - " + role.getName() + ": " + role.getDescription());
}
} else {
System.out.println("没有分配角色");
}
if (account != null) {
System.out.println("账户用户名: " + account.getName());
} else {
System.out.println("没有关联账户");
}
System.out.println("----------------------------------------\n");
}
}package org.example.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Role {
private int id;
private String name;
private String description;
}package org.example.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Account {
private int id;
private String name;
private String password;
}<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.dao.UserMapper">
<insert id="add" parameterType="org.example.pojo.User" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
INSERT INTO
user (`name`, `age`, `sex`, `address`)
VALUES
(#{name}, #{age}, #{sex}, #{address})
</insert>
<delete id="delete" parameterType="_int">
DELETE FROM user WHERE id = #{id}
</delete>
<update id="update" parameterType="org.example.pojo.User">
UPDATE user
SET name = #{name}, age = #{age}, sex = #{sex}, address = #{address}
WHERE id = #{id}
</update>
<!-- 子查询嵌套方式 -->
<select id="query" parameterType="map" resultMap="user">
SELECT *
FROM user
WHERE name LIKE #{name} or address LIKE #{address} or sex = #{sex}
</select>
<select id="getAccount" resultType="org.example.pojo.Account">
SELECT * FROM account WHERE id = #{account_id}
</select>
<select id="getRoles" resultType="org.example.pojo.Role">
SELECT * FROM role R INNER JOIN user_role UR ON R.id = UR.r_id
WHERE UR.u_id = #{id}
</select>
<resultMap id="user" type="org.example.pojo.User">
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<association column="account_id" property="account" javaType="org.example.pojo.Account" select="getAccount"/>
<collection column="id" property="roles" ofType="org.example.pojo.Role" select="getRoles"/>
</resultMap>
<!-- 联表查询查询方式 -->
<select id="queryOne" resultMap="user_account">
SELECT
U.id AS user_id,
U.name AS user_name,
U.age,
U.sex,
U.address,
A.id AS account_id,
A.name AS account_name,
A.password AS account_password,
R.id AS role_id,
R.name AS role_name,
R.description AS role_description
FROM user AS U
INNER JOIN account AS A
ON U.account_id = A.id
INNER JOIN user_role AS UR
ON U.id = UR.u_id
INNER JOIN role AS R
ON UR.r_id = R.id
WHERE U.id = #{id}
</select>
<resultMap id="user_account" type="org.example.pojo.User">
<!-- User 字段映射 -->
<result column="user_id" property="id"/>
<result column="user_name" property="name"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/> <!-- 修正此处 -->
<!-- Account 关联对象映射 -->
<association property="account" javaType="org.example.pojo.Account">
<result column="account_id" property="id"/>
<result column="account_name" property="name"/>
<result column="account_password" property="password"/>
</association>
<collection property="roles" ofType="org.example.pojo.Role">
<result column="role_id" property="id"/>
<result column="role_name" property="name"/>
<result column="role_description" property="description"/>
</collection>
</resultMap>
</mapper>package mybatis;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;
import org.example.dao.UserMapper;
import org.example.pojo.User;
import org.junit.Test;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MybatisTest {
public Logger log = Logger.getLogger(MybatisTest.class);
@Test
public void test1() throws IOException {
String resource = "mybatis-config.xml";
SqlSession sqlSession = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream(resource)).openSession(true);
log.info("开启会话");
UserMapper userDao = sqlSession.getMapper(UserMapper.class);
Map<String,Object> map = new HashMap<>();
map.put("sex","男");
List<User> users = userDao.query(map);
for (User user : users) {
user.userPrint();
}
userDao.queryOne(1).userPrint();
log.info("关闭会话");
sqlSession.close();
}
}
动态SQL语句
MyBatis 的动态 SQL 功能允许在运行时根据不同条件动态生成 SQL 语句，避免了手动拼接 SQL 的繁琐和潜在错误。 通过使用 <if>、<choose>、<trim>、<foreach> 等标签，开发者可以在 XML 映射文件中灵活地构建复杂的查询条件。 这使得 MyBatis 在处理复杂查询时更加灵活和高效，减少了手动拼接 SQL 的工作量和出错的可能性。
常用的动态 SQL 标签：
<if>：用于根据条件判断是否包含某个 SQL 片段。<choose>、<when>、<otherwise>：类似于 Java 中的switch语句，用于在多个条件中选择一个执行。<trim>：用于动态地添加前缀、后缀或移除多余的分隔符。<foreach>：用于遍历集合，常用于构建IN条件语句。<bind>：用于在动态 SQL 中创建变量。<where>：用于动态地构建WHERE子句，自动处理前缀的连接符。<set>：用于动态地构建SET子句，自动处理前缀的连接符。<sql>：用于定义可重用的 SQL 片段。
package org.example.dao;
import org.apache.ibatis.annotations.Param;
import org.example.pojo.User;
import java.util.List;
import java.util.Map;
public interface UserMapper {
public int add(@Param("userList")List<User> userList);
public int delete(int id);
public int update(User user);
public List<User> query(Map<String, Object> map);
public User queryOne(int id);
}<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.dao.UserMapper">
<insert id="add" parameterType="org.example.pojo.User" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
INSERT INTO
user (`name`, `age`, `sex`, `address`)
VALUES
<foreach collection="userList" item="user" index="idx" separator=",">
(#{user.name}, #{user.age}, #{user.sex}, #{user.address})
</foreach>
</insert>
<delete id="delete" parameterType="_int">
DELETE FROM user WHERE id = #{id}
</delete>
<update id="update" parameterType="org.example.pojo.User">
UPDATE user
<set>
<if test="name != null">
name = #{name}
</if>
<if test="age != null">
age = #{age}
</if>
<if test="sex != null">
sex = #{sex}
</if>
<if test="address != null">
address = #{address}
</if>
</set>
WHERE id = #{id}
</update>
<sql id="age_sex">
<if test="age != null">
AND age = #{age}
</if>
<if test="sex != null">
AND sex = #{sex}
</if>
</sql>
<select id="query" parameterType="map" resultMap="user">
SELECT *
FROM user
<where>
<choose>
<when test="name != null">
name LIKE #{name}
</when>
<when test="address != null">
AND address LIKE #{address}
</when>
<otherwise>
<include refid="age_sex"/>
</otherwise>
</choose>
</where>
</select>
<select id="getAccount" resultType="org.example.pojo.Account">
SELECT * FROM account WHERE id = #{account_id}
</select>
<select id="getRoles" resultType="org.example.pojo.Role">
SELECT * FROM role R INNER JOIN user_role UR ON R.id = UR.r_id
WHERE UR.u_id = #{id}
</select>
<resultMap id="user" type="org.example.pojo.User">
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<association column="account_id" property="account" javaType="org.example.pojo.Account" select="getAccount"/>
<collection column="id" property="roles" ofType="org.example.pojo.Role" select="getRoles"/>
</resultMap>
<select id="queryOne" resultMap="user_account">
SELECT
U.id AS user_id,
U.name AS user_name,
U.age,
U.sex,
U.address,
A.id AS account_id,
A.name AS account_name,
A.password AS account_password,
R.id AS role_id,
R.name AS role_name,
R.description AS role_description
FROM user AS U
INNER JOIN account AS A
ON U.account_id = A.id
INNER JOIN user_role AS UR
ON U.id = UR.u_id
INNER JOIN role AS R
ON UR.r_id = R.id
WHERE U.id = #{id}
</select>
<resultMap id="user_account" type="org.example.pojo.User">
<!-- User 字段映射 -->
<result column="user_id" property="id"/>
<result column="user_name" property="name"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/> <!-- 修正此处 -->
<!-- Account 关联对象映射 -->
<association property="account" javaType="org.example.pojo.Account">
<result column="account_id" property="id"/>
<result column="account_name" property="name"/>
<result column="account_password" property="password"/>
</association>
<collection property="roles" ofType="org.example.pojo.Role">
<result column="role_id" property="id"/>
<result column="role_name" property="name"/>
<result column="role_description" property="description"/>
</collection>
</resultMap>
</mapper>package mybatis;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;
import org.example.dao.UserMapper;
import org.example.pojo.User;
import org.junit.Test;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MybatisTest {
public Logger log = Logger.getLogger(MybatisTest.class);
@Test
public void test1() throws IOException {
String resource = "mybatis-config.xml";
SqlSession sqlSession = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream(resource)).openSession(true);
log.info("开启会话");
UserMapper userDao = sqlSession.getMapper(UserMapper.class);
List<User> userList = new ArrayList<>();
userList.add(new User(1, "Alice", 30, "女", "123 Main St",null,null));
userList.add(new User(2, "Bob", 25, "男", "456 Elm St",null,null));
userList.add(new User(3, "Charlie", 35, "女", "789 Oak St",null,null));
userDao.add(userList);
Map<String,Object> map = new HashMap<>();
map.put("name","%十%");
map.put("address","%南%");
List<User> users = userDao.query(map);
for (User user : users) {
user.userPrint();
}
userDao.queryOne(1).userPrint();
log.info("关闭会话");
sqlSession.close();
}
}MyBatis 缓存机制详解
MyBatis 的缓存机制通过减少数据库查询次数提升性能，分为 一级缓存 和 二级缓存，核心区别在于作用域和生命周期。
| 特性 | 一级缓存 | 二级缓存 |
|---|---|---|
| 作用域 | SqlSession 内 | Mapper 跨 SqlSession |
| 默认状态 | 开启 | 关闭（需配置） |
| 数据隔离性 | 高（会话隔离） | 低（共享数据） |
| 适用场景 | 短时重复查询 | 全局高频读、低频写 |
| 淘汰策略 | 无（全量缓存） | 可配置（LRU、FIFO 等） |
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="logImpl" value="LOG4J"/>
<setting name="cacheEnable" value="true"/>
</settings>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&useSSL=false&characterEncoding=utf8"/>
<property name="username" value="billsaifu"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="org.example.dao"/>
</mappers>
</configuration><?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.dao.UserMapper">
<!--
eviction="FIFO" 缓存回收策略，FIFO 表示先进先出
flushInterval="60000" 刷新间隔时间，单位为毫秒，这里设置为 60 秒
size="512" 缓存的最大条目数，超过此数量时会根据回收策略清除旧的缓存项
readOnly="true" 设置缓存为只读，表示缓存中的数据不会被修改
-->
<cache
eviction="FIFO"
flushInterval="60000"
size="512"
readOnly="true"
/>
<insert id="add" parameterType="org.example.pojo.User" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
INSERT INTO
user (`name`, `age`, `sex`, `address`)
VALUES
<foreach collection="userList" item="user" index="idx" separator=",">
(#{user.name}, #{user.age}, #{user.sex}, #{user.address})
</foreach>
</insert>
<delete id="delete" parameterType="_int">
DELETE FROM user WHERE id = #{id}
</delete>
<update id="update" parameterType="org.example.pojo.User">
UPDATE user
<set>
<if test="name != null">
name = #{name}
</if>
<if test="age != null">
age = #{age}
</if>
<if test="sex != null">
sex = #{sex}
</if>
<if test="address != null">
address = #{address}
</if>
</set>
WHERE id = #{id}
</update>
<sql id="age_sex">
<if test="age != null">
AND age = #{age}
</if>
<if test="sex != null">
AND sex = #{sex}
</if>
</sql>
<select id="query" parameterType="map" resultMap="user">
SELECT *
FROM user
<where>
<choose>
<when test="name != null">
name LIKE #{name}
</when>
<when test="address != null">
AND address LIKE #{address}
</when>
<otherwise>
<include refid="age_sex"/>
</otherwise>
</choose>
</where>
</select>
<select id="getAccount" resultType="org.example.pojo.Account">
SELECT * FROM account WHERE id = #{account_id}
</select>
<select id="getRoles" resultType="org.example.pojo.Role">
SELECT * FROM role R INNER JOIN user_role UR ON R.id = UR.r_id
WHERE UR.u_id = #{id}
</select>
<resultMap id="user" type="org.example.pojo.User">
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<association column="account_id" property="account" javaType="org.example.pojo.Account" select="getAccount"/>
<collection column="id" property="roles" ofType="org.example.pojo.Role" select="getRoles"/>
</resultMap>
<select id="queryOne" resultMap="user_account">
SELECT
U.id AS user_id,
U.name AS user_name,
U.age,
U.sex,
U.address,
A.id AS account_id,
A.name AS account_name,
A.password AS account_password,
R.id AS role_id,
R.name AS role_name,
R.description AS role_description
FROM user AS U
INNER JOIN account AS A
ON U.account_id = A.id
INNER JOIN user_role AS UR
ON U.id = UR.u_id
INNER JOIN role AS R
ON UR.r_id = R.id
WHERE U.id = #{id}
</select>
<resultMap id="user_account" type="org.example.pojo.User">
<!-- User 字段映射 -->
<result column="user_id" property="id"/>
<result column="user_name" property="name"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/> <!-- 修正此处 -->
<!-- Account 关联对象映射 -->
<association property="account" javaType="org.example.pojo.Account">
<result column="account_id" property="id"/>
<result column="account_name" property="name"/>
<result column="account_password" property="password"/>
</association>
<collection property="roles" ofType="org.example.pojo.Role">
<result column="role_id" property="id"/>
<result column="role_name" property="name"/>
<result column="role_description" property="description"/>
</collection>
</resultMap>
</mapper>别名
<typeAliases>
<package name="org.example.dao"/>
<package name="org.example.pojo"/>
</typeAliases><typeAliases> 配置项是 MyBatis 中的一部分，用于为 Java 类定义别名，以简化 SQL 映射文件中的类全名引用，该配置会让mybatis自动扫描该包下的类自动为该类创建别名，例如如果有User.class这样一个类，则mybatis会自动创建别名user（首字母小写）,这样在Mapper里面需要调用User的时候不需要用全名只需要用别名就好了
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.dao.UserMapper">
<insert id="add" parameterType="user" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
INSERT INTO
user (`name`, `age`, `sex`, `address`)
VALUES
<foreach collection="userList" item="user" index="idx" separator=",">
(#{user.name}, #{user.age}, #{user.sex}, #{user.address})
</foreach>
</insert>
<delete id="delete" parameterType="_int">
DELETE FROM user WHERE id = #{id}
</delete>
<update id="update" parameterType="user">
UPDATE user
<set>
<if test="name != null">
name = #{name}
</if>
<if test="age != null">
age = #{age}
</if>
<if test="sex != null">
sex = #{sex}
</if>
<if test="address != null">
address = #{address}
</if>
</set>
WHERE id = #{id}
</update>
<sql id="age_sex">
<if test="age != null">
AND age = #{age}
</if>
<if test="sex != null">
AND sex = #{sex}
</if>
</sql>
<select id="query" parameterType="map" resultMap="user">
SELECT *
FROM user
<where>
<choose>
<when test="name != null and name != ''">
name LIKE CONCAT('%', #{name}, '%')
</when>
<when test="address != null and address != ''">
AND address LIKE CONCAT('%', #{address}, '%')
</when>
<otherwise>
<include refid="age_sex"/>
</otherwise>
</choose>
</where>
</select>
<select id="getAccount" resultType="account">
SELECT * FROM account WHERE id = #{account_id}
</select>
<select id="getRoles" resultType="role">
SELECT * FROM role R INNER JOIN user_role UR ON R.id = UR.r_id
WHERE UR.u_id = #{id}
</select>
<resultMap id="user" type="user">
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<association column="account_id" property="account" javaType="account" select="getAccount"/>
<collection column="id" property="roles" ofType="role" select="getRoles"/>
</resultMap>
<select id="queryOne" resultMap="user_account">
SELECT
U.id AS user_id,
U.name AS user_name,
U.age,
U.sex,
U.address,
A.id AS account_id,
A.name AS account_name,
A.password AS account_password,
R.id AS role_id,
R.name AS role_name,
R.description AS role_description
FROM user AS U
INNER JOIN account AS A
ON U.account_id = A.id
INNER JOIN user_role AS UR
ON U.id = UR.u_id
INNER JOIN role AS R
ON UR.r_id = R.id
WHERE U.id = #{id}
</select>
<resultMap id="user_account" type="user">
<!-- User 字段映射 -->
<result column="user_id" property="id"/>
<result column="user_name" property="name"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/> <!-- 修正此处 -->
<!-- Account 关联对象映射 -->
<association property="account" javaType="account">
<result column="account_id" property="id"/>
<result column="account_name" property="name"/>
<result column="account_password" property="password"/>
</association>
<collection property="roles" ofType="role">
<result column="role_id" property="id"/>
<result column="role_name" property="name"/>
<result column="role_description" property="description"/>
</collection>
</resultMap>
</mapper>
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 一文读懂知识蒸馏
· 终于写完轮子一部分：tcp代理 了，记录一下