第九章、Mybatis多表操作
1. 级联映射
关系型数据库中,表之间的关系映射通常分为
- 一对一
- 一对多
- 多对一
- 多对多:(拆解-一对多或多对一)
mybatis处理这些关系的两种方式:
嵌套查询:通过执行另外一个SQL映射语句来返回预期的复杂类型(通常为单查询)。
嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集(通常为联表查询)。
RBAC 权限模型(Role Basic Acess Controll , 基于角色的权限访问控制模型)
1.1 一对一/多对一查询
1.1.1 对一的模型
1.1.2 对一的查询语句
联表查询
select
*
from user_role ur
inner join user u on ur.user_id = u.id
1.1.3 对一的实体
package com.xuetang9.mybatis.demo.entity;
import lombok.Data;
import java.io.Serializable;
/**
* 定义一个用来对应user表结构的实体类
*/
@Data
public class User implements Serializable {
private Integer id;
private String account;
private String password;
private String phone;
private String email;
private Boolean disabled;
private Boolean deleted;
}
package com.xuetang9.mybatis.demo.entity;
import lombok.Data;
import java.io.Serializable;
/**
* 用来对应user_role的表结构
*/
@Data
public class UserRole implements Serializable {
private Integer id;
private Integer userId;
private Integer roleId;
// 每行数据都对应一个user对象 (对一的关系)
private User user;
}
1.1.4 对一Mapper映射
写在需要关联对象的类的对应的Mapper
1.1.4.1 嵌套结果
<?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="com.xuetang9.mybatis.demo.mapper.UserRoleMapper">
<resultMap id="BaseResultMap" type="UserRole" autoMapping="true">
<id property="id" column="id"/>
<result property="userId" column="user_id"/>
<result property="roleId" column="role_id"/>
<!--嵌套结果-->
<association property="user" javaType="User" columnPrefix="u_" autoMapping="true">
<id property="id" column="id"/>
<result property="disabled" column="is_disabled"/>
<result property="deleted" column="is_deleted"/>
</association>
</resultMap>
<!--联表查询-->
<select id="selectByPrimaryKey" resultMap="BaseResultMap">
select
ur.*,
u.id as u_id,
u.account as u_account,
u.password as u_password,
u.phone as u_phone,
u.email as u_email,
u.is_disabled as u_is_disabled,
u.is_deleted as u_is_deleted
from user_role ur
inner join user u on ur.user_id = u.id
where ur.id = #{id}
</select>
</mapper>
1.1.4.2 嵌套查询
<?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="com.xuetang9.mybatis.demo.mapper.UserRoleMapper">
<resultMap id="BaseResultMap" type="UserRole" autoMapping="true">
<id property="id" column="id"/>
<result property="userId" column="user_id"/>
<result property="roleId" column="role_id"/>
<!--嵌套查询-->
<association property="user" column="user_id" select="com.xuetang9.mybatis.demo.mapper.UserMapper.selectByPrimaryKey">
</association>
</resultMap>
<select id="selectByPrimaryKey" resultMap="BaseResultMap">
select * from user_role
where id = #{id}
</select>
</mapper>
1.2 一对多查询
1.2.1 一对多的模型
1.2.2 一对多的查询语句
select
*
from user u
left outer join user_role ur on ur.user_id = u.id
1.2.3 一对多的实体
package com.xuetang9.mybatis.demo.entity;
import lombok.Data;
import java.io.Serializable;
/**
* 用来对应user_role的表结构
*/
@Data
public class UserRole implements Serializable {
private Integer id;
private Integer userId;
private Integer roleId;
// 每行数据都对应一个user对象 (对一的关系)
private User user;
}
package com.xuetang9.mybatis.demo.entity;
import lombok.Data;
import java.io.Serializable;
import java.util.List;
/**
* 定义一个用来对应user表结构的实体类
*/
@Data
public class User implements Serializable {
private Integer id;
private String account;
private String password;
private String phone;
private String email;
private Boolean disabled;
private Boolean deleted;
// 一对多的关联关系
List<UserRole> userRoles;
}
1.2.4 一对多Mapper映射
1.2.4.1 嵌套结果
<resultMap id="BaseResultMap" type="User" autoMapping="true">
<id property="id" column="id"/>
<result property="disabled" column="is_disabled"/>
<result property="deleted" column="is_deleted"/>
</resultMap>
<resultMap id="AdvanceResultMap" type="User" extends="BaseResultMap">
<!--一对多的关系(嵌套结果)-->
<collection property="userRoles" ofType="UserRole" columnPrefix="ur_">
<id property="id" column="id"/>
<result property="userId" column="user_id"/>
<result property="roleId" column="role_id"/>
</collection>
</resultMap>
<!--联表查询-->
<select id="selectByPrimaryKey" resultMap="AdvanceResultMap">
select
u.*,
ur.id as ur_id,
ur.user_id as ur_user_id,
ur.role_id as ur_role_id
from user u
left outer join user_role ur on ur.user_id = u.id
where u.id = #{id}
</select>
1.2.4.2 嵌套查询
<resultMap id="BaseResultMap" type="User" autoMapping="true">
<id property="id" column="id"/>
<result property="disabled" column="is_disabled"/>
<result property="deleted" column="is_deleted"/>
</resultMap>
<resultMap id="AdvanceResultMap" type="User" extends="BaseResultMap">
<!--一对多的关系-->
<collection property="userRoles"
column="id"
fetchType="lazy"
select="com.xuetang9.mybatis.demo.mapper.UserRoleMapper.selectByForeignKeyUserId">
</collection>
</resultMap>
<!--单表查询-->
<select id="selectByPrimaryKey" resultMap="AdvanceResultMap">
select
*
from user
where id = #{id}
</select>
2. 延迟加载
2.1 多对一的嵌套查询
使用的是嵌套查询:会出现 N + 1 的问题 调用一条语句查询多个数据时,查询出的每行数据都会关联一个对象,一个语句关联多个对象(1 +关联N)
使用懒加载(延迟加载/按需加载):fetchType=“lazy”
<resultMap id="AdvanceResultMap" type="UserRole" extends="BaseResultMap">
<!--多对一的嵌套查询-->
<association property="user"
column="user_id"
fetchType="lazy"
select="com.xuetang9.mybatis.demo.mapper.UserMapper.selectByPrimaryKey">
</association>
</resultMap>
<!--查询全部的user_role表中的信息-导致每一个UserRole对象会关联一个User对象-->
<select id="selectAll" resultMap="AdvanceResultMap">
select * from user_role
</select>
2.2 一对多的嵌套查询
可能加载大量不需要的数据:每一个查询的数据,都会关联一个包含大量数据的集合。
使用懒加载(延迟加载/按需加载):fetchType=“lazy”
<resultMap id="AdvanceResultMap" type="User" extends="BaseResultMap">
<!--一对多的关系(嵌套查询)-->
<collection property="userRoles"
column="id"
fetchType="lazy"
select="com.xuetang9.mybatis.demo.mapper.UserRoleMapper.selectByForeignKeyUserId">
</collection>
</resultMap>
3. 分页插件
使用的网址:https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md
3.1 导入分页插件的依赖坐标
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
3.2 配置分页插件
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!--使用对应数据库的分页-->
<property name="helperDialect" value="mysql"/>
<!--分页的页码为0时的,默认查询全部-->
<property name="pageSizeZero" value="true"/>
<!--分页的参数合理化,页码数字<0 默认查询第一页 页码数字>总页数 默认查询最后一页-->
<property name="reasonable" value="true"/>
</plugin>
</plugins>
3.3 在业务层使用
@Test
public void selectAll() {
// 创建了一个集合,用来保存查询的数据
Page<Resource> resources = PageHelper.startPage(2, 3);
// 被拦截代理,执行完毕后,把结果添加到创建好的集合中
resourceMapper.selectAll();
// 把结果转换为一个保存完整的分页信息的对象
PageInfo pageInfo = new PageInfo(resources);
System.out.println(resources);
for (Resource r : resources) {
System.err.println(r);
}
System.out.println(pageInfo);
}
4. 存储过程的调用
数据库优化:实现一个完整业务功能。数据库优化的目标是提高整个程序运行的效率(数据库稳定性、响应效率、安全性等方面处理)为目标。否则采取任何手段做的一些事情可能产生负优化效果。
帖子回复数据添加的时候,需要将帖子表上的回复数量加1
注意代码中对的row_count() 函数表示一条语句执行结束后的影响行数
什么是存储过程: 在数据库中对一段数据库操作的代码封装,没有返回值的封装。
drop PROCEDURE if EXISTS reply_process;
# 定义结束语义符
delimiter $$
# 创建存储过程
# 参数前使用in 表示输入参数, out表示输出参数。输出参数表示数据在过程中修改后,外部可以得到修改后的数据
create PROCEDURE reply_process(tid int, customer_id int, content text, replay_time datetime, out success TINYINT )
begin
-- 定义保存错误信息的变量 0 表示没有错误,1 表示有错误
declare temp_error int default 0;
-- 定义出现错误的时候应该如何操作
declare continue handler for sqlexception
begin
set temp_error = 1;
end;
# 开启事务
start TRANSACTION;
# 执行插入操作
insert into reply (id, topic_id, customer_id, reply_time,content, parent_reply_id)
values(default, tid, customer_id,replay_time , content, null);
# 判断插入结果
if row_count() < 1 then
set temp_error = 1;
end if;
# 修改帖子表
update `topic` set `reply_count` =
case `reply_count` when NULL then 1 ELSE `reply_count` + 1 END
where id = tid;
# 判断修改结果
if row_count() < 1 then
set temp_error = 1;
end if;
if temp_error = 0 then
begin
set success = 1;
commit;
end;
else
begin
set success = 0;
rollback;
end;
end if;
end $$
delimiter ;
set @success = 1;
CALL reply_process(15,4,'我不快乐,我会了就快乐.','2020-10-20 14:07:02',@success);
SELECT @success;
package com.xuetang9.framework.mybatismapper.mapper;
import com.xuetang9.framework.mybatismapper.entity.Reply;
import org.apache.ibatis.annotations.Insert;
public interface ReplyMapper {
@Insert("set @success = 1")
int setVar();
@Insert({"call reply_process(#{topicId},#{customerId},#{content},#{replyTime},@success)"})
int insertReply(Reply reply);
}
package com.xuetang9.framework.mybatismapper.mapper;
import com.xuetang9.framework.mybatismapper.entity.Reply;
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.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
public class ReplyMapperTest {
private SqlSession sqlSession;
private ReplyMapper replyMapper;
@BeforeEach
public void beforeDo(){
try{
InputStream configStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = factoryBuilder.build(configStream,"FORUM");
sqlSession = sqlSessionFactory.openSession();
replyMapper = sqlSession.getMapper(ReplyMapper.class);
}catch (IOException e){
e.printStackTrace();
}
}
@AfterEach
public void afterDo(){
sqlSession.commit();
sqlSession.close();
}
@Test
public void insertReply(){
Reply reply = new Reply(null,4,15,"好吧,这就是痛并快乐着",new Date());
replyMapper.setVar();
int rowCount = replyMapper.insertReply(reply);
System.out.println(rowCount);
}
}