文章目录
1 一对一查询
1.1 场景介绍
用户和该用户领的工资为1对1的关系,用户表如下:
工资表如下:
需求:设计一个接口,需要在查询用户时,可以同时查出用户的工资信息,返回数据格式如下:
{
id: 1,
userName: "yoyo",
realName: "李雷",
sex: 1,
mobile: "13300000000",
email: "yoyo@qq.com",
note: "yoyo的备注",
positionId: 1,
salary: {
id: 1,
userName: "yoyo",
baseSalary: 2000,
postSalary: 8000,
mealAllowance: 200
}
}
对应VO类如下:
package com.nrsc.mybatis.vo;
import com.nrsc.mybatis.domian.TSalary;
import com.nrsc.mybatis.domian.TUser;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
@ToString
@Setter
@Getter
public class UserAndSalaryVO extends TUser {
//用户的工资信息
private TSalary salary;
}
1.2 非懒加载的情况
非懒加载的情况 , 我代码的栗子是根据用户的id查询该用户的用户信息和工资信息。
这里只贴一下mapper.xml
<?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.nrsc.mybatis.mapper.TUserMapper">
<resultMap id="BaseResultMap" type="com.nrsc.mybatis.domian.TUser">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="user_name" jdbcType="VARCHAR" property="userName"/>
<result column="real_name" jdbcType="VARCHAR" property="realName"/>
<result column="sex" jdbcType="TINYINT" property="sex"/>
<result column="mobile" jdbcType="VARCHAR" property="mobile"/>
<result column="email" jdbcType="VARCHAR" property="email"/>
<result column="note" jdbcType="VARCHAR" property="note"/>
<result column="position_id" jdbcType="INTEGER" property="positionId"/>
</resultMap>
<!--++++++++++++++++++++++++++++++++++++++1对1非懒加载++++++++++++++++++++++++++++++++++++++++-->
<sql id="User_Salary_SA">
ts.id as SA_id , ts.user_name as SA_user_name, ts.base_salary as SA_base_salary,
ts.post_salary as SA_post_salary, ts.meal_allowance as SA_meal_allowance
</sql>
<!--可以通过extends属性来继承BaseResultMap的属性,简化配置-->
<resultMap id="UserAndSalaryMap" extends="BaseResultMap"
type="com.nrsc.mybatis.vo.UserAndSalaryVO">
<!--注意:
1. 下面的property其实是UserAndSalaryVO中TSalary属性的变量名,必填
2. columnPrefix表示对下面的column对应的值加一个前缀
-->
<association property="salary" columnPrefix="SA_"
javaType="com.nrsc.mybatis.domian.TSalary">
<id column="id" property="id"/>
<result column="user_name" property="userName"/>
<result column="base_salary" property="baseSalary"/>
<result column="post_salary" property="postSalary"/>
<result column="meal_allowance" property="mealAllowance"/>
</association>
</resultMap>
<!--非懒加载的方式-->
<select id="selectUserAndSalaryByPrimaryKey" resultMap="UserAndSalaryMap">
select tu.* ,
<include refid="User_Salary_SA"/>
from t_user tu left join t_salary ts
on tu.user_name = ts.user_name
where tu.id = #{id,jdbcType=BIGINT}
</select>
<!--++++++++++++++++++++++++++++++++++++++1对1非懒加载++++++++++++++++++++++++++++++++++++++++-->
</mapper>
这种方式其实就是运行了下面一条sql — 假设传入的值是1 :
SELECT
tu.*,
ts.id AS SA_id,
ts.user_name AS SA_user_name,
ts.base_salary AS SA_base_salary,
ts.post_salary AS SA_post_salary,
ts.meal_allowance AS SA_meal_allowance
FROM
t_user tu
LEFT JOIN t_salary ts ON tu.user_name = ts.user_name
WHERE
tu.id = 1
查到的结果如下:
Mybatis会按照我们指定的resultMap,将上面的查询结果封装成我们自定义的实体类 — UserAndSalaryVO。
1.3 懒加载的情况
1.3.1 可以开启全局懒加载,1对1,1对多都适用
开启全局懒加载,在springboot项目里只需要在yml配置文件里将lazy-loading-enabled的值配置为true就可以了,但是也可以不进行配置,因为可以在mapper.xml中的特定标签里通过fetchType=“lazy” 来指定某个查询使用懒加载。
需要注意的是,如果想开启懒加载aggressive-lazy-loading这个值不能设置为true。
mybatis:
configuration:
#该值默认就是false,所以可以不配,如果配置成了true,懒加载就会失效
aggressive-lazy-loading: false
#该属性默认为false,如果配置了true,相当于开启了全局懒加载
#当然我们也可以不管该属性,而mapper.xml中的特定标签里通过fetchType="lazy" 来指定某个查询使用懒加载
#lazy-loading-enabled: true
1.3.2 “No serializer found for class 。。。”错误解决方法
解决方式为在yml里进行如下配置:
spring:
# 懒加载情况下报“No serializer found for class org.apache.ibatis.executor.loader.javassist.JavassistProxyFactory$Enha。。”
# 解决方法
jackson:
serialization:
FAIL_ON_EMPTY_BEANS: false
1.3.3 mapper.xml中的配置
懒加载的情况下,我代码的栗子是查询所有用户的用户信息+工资信息。
- TUserMapper.xml
<?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.nrsc.mybatis.mapper.TUserMapper">
<resultMap id="BaseResultMap" type="com.nrsc.mybatis.domian.TUser">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="user_name" jdbcType="VARCHAR" property="userName"/>
<result column="real_name" jdbcType="VARCHAR" property="realName"/>
<result column="sex" jdbcType="TINYINT" property="sex"/>
<result column="mobile" jdbcType="VARCHAR" property="mobile"/>
<result column="email" jdbcType="VARCHAR" property="email"/>
<result column="note" jdbcType="VARCHAR" property="note"/>
<result column="position_id" jdbcType="INTEGER" property="positionId"/>
</resultMap>
<!--++++++++++++++++++++++++++++++++++++++1对1懒加载++++++++++++++++++++++++++++++++++++++++-->
<resultMap id="UserAndSalaryMapLazy" extends="BaseResultMap"
type="com.nrsc.mybatis.vo.UserAndSalaryVO">
<!--注意:
1.下面的property其实是UserAndSalaryVO中TSalary属性的变量名,必填
2.column指的是select对应的sql语句需要的参数,
需要格外注意的是这个值是本sql语句的列名,这里由于两个表的列名都为user_name,可能看不出来,
假如t_user表里叫name,而t_salary里叫user_name,这里应该填name
-->
<association property="salary"
javaType="com.nrsc.mybatis.domian.TSalary"
column="user_name"
fetchType="lazy"
select="com.nrsc.mybatis.mapper.TSalaryMapper.selectByUserName">
</association>
</resultMap>
<select id="getAllUserInfo" resultMap="UserAndSalaryMapLazy">
select * from t_user
</select>
<!--++++++++++++++++++++++++++++++++++++++1对1懒加载++++++++++++++++++++++++++++++++++++++++-->
</mapper>
这里需要注意的是,上面的association标签里有一个select属性,该属性指向的是TSalaryMapper.xml中的selectByUserName,这里在看一下TSalaryMapper.xml:
<?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.nrsc.mybatis.mapper.TSalaryMapper">
<resultMap id="BaseResultMap" type="com.nrsc.mybatis.domian.TSalary">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="user_name" jdbcType="VARCHAR" property="userName"/>
<result column="base_salary" jdbcType="DECIMAL" property="baseSalary"/>
<result column="post_salary" jdbcType="DECIMAL" property="postSalary"/>
<result column="meal_allowance" jdbcType="DECIMAL" property="mealAllowance"/>
</resultMap>
<sql id="Base_Column_List">
id, user_name, base_salary, post_salary, meal_allowance
</sql>
<select id="selectByUserName" resultMap="BaseResultMap" parameterType="java.lang.String">
select
<include refid="Base_Column_List"/>
from t_salary where user_name = #{userName,jdbcType=VARCHAR}
</select>
</mapper>
1.3.3 测试 — 这是Mybatis懒加载的全部吗???
- controller层代码如下:
@GetMapping(value = "/getAllUserInfo", name = "查询所有的用户信息+工资信息")
public List<UserAndSalaryVO> getAllUserInfo() {
return userService.getAllUserInfo();
}
- service层代码如下:
@Override
public List<UserAndSalaryVO> getAllUserInfo() {
//从mybatis-study数据库里获取用户信息
List<UserAndSalaryVO> VOs = userMapper.getAllUserInfo();
return VOs;
}
在controller和service层分别打断点,测试结果如下:
(1)在service层打断点可以看到走完userMapper.getAllUserInfo()方法只发起了一次对于t_user表的查询
(2)要获取第一个用户的工资数据时,才发起对该用户工资数据的查询
(3)返回到controller层可以发现还是只有(1)(2)中的两次查询
(4)放开controller层的断点后,立即又发起了三次查询
但这是Mybatis懒加载的全部吗??? —》 欢迎评论。。。
2 一对多查询
2.1 场景介绍
用户和其工作历史情况是一对多的关系,用户表和1中一样,用户的工作历史情况表如下:
需求:设计一个接口,需要在查询用户时,可以同时查出用户的工作历史情况信息,返回数据格式如下:
{
id: 2,
userName: "nrsc",
realName: "韩梅梅",
sex: 2,
mobile: "13300000001",
email: "nrsc@qq.com",
note: "nrsc的备注",
positionId: 2,
jobHistoryList: [
{
id: 2,
userId: 2,
companyName: "百度",
years: 4,
title: "项目经理"
},
{
id: 3,
userId: 2,
companyName: "腾讯",
years: 1,
title: "程序员"
}
]
}
对应VO类如下:
package com.nrsc.mybatis.vo;
import com.nrsc.mybatis.domian.TJobHistory;
import com.nrsc.mybatis.domian.TUser;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import java.util.List;
@Getter
@Setter
@ToString
public class UserAndJobHistoryVO extends TUser {
private List<TJobHistory> jobHistoryList;
}
2.2 非懒加载的情况
这里只给出mapper.xml
<?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.nrsc.mybatis.mapper.TUserMapper">
<resultMap id="BaseResultMap" type="com.nrsc.mybatis.domian.TUser">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="user_name" jdbcType="VARCHAR" property="userName"/>
<result column="real_name" jdbcType="VARCHAR" property="realName"/>
<result column="sex" jdbcType="TINYINT" property="sex"/>
<result column="mobile" jdbcType="VARCHAR" property="mobile"/>
<result column="email" jdbcType="VARCHAR" property="email"/>
<result column="note" jdbcType="VARCHAR" property="note"/>
<result column="position_id" jdbcType="INTEGER" property="positionId"/>
</resultMap>
<!--++++++++++++++++++++++++++++++++++++++1对多非懒加载 ++++++++++++++++++++++++++++++++++++++++-->
<resultMap id="UserAndJobHistoryMap" type="com.nrsc.mybatis.vo.UserAndJobHistoryVO"
extends="BaseResultMap">
<!--
注意: 1 下面的property其实是UserAndJobHistoryVO中TJobHistory属性的变量名,必填
-->
<collection columnPrefix="TH_" property="jobHistoryList"
ofType="com.nrsc.mybatis.domian.TJobHistory">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="user_id" jdbcType="BIGINT" property="userId"/>
<result column="company_name" jdbcType="VARCHAR" property="companyName"/>
<result column="years" jdbcType="INTEGER" property="years"/>
<result column="title" jdbcType="VARCHAR" property="title"/>
</collection>
</resultMap>
<sql id="JobHistorySQL_TH">
tjh.id TH_id, tjh.user_id TH_user_id , tjh.company_name TH_company_name,
tjh.years TH_years, tjh.title TH_title
</sql>
<select id="getUserAndJobsInfo" resultMap="UserAndJobHistoryMap">
select tu.*,
<include refid="JobHistorySQL_TH"/>
from t_user tu , t_job_history tjh
where tu.id = tjh.user_id
and tjh.user_id = #{userId,jdbcType=BIGINT}
</select>
<!--++++++++++++++++++++++++++++++++++++++1对多非懒加载 ++++++++++++++++++++++++++++++++++++++++-->
</mapper>
这里要注意的是
(1)1对1时,用的是association 标签,且指向TSalary类属性时用的是javaType
(2)1对多时,用的是collection 标签,且指向TJobHistory类属性时用的是ofType
SELECT
tu.*, tjh.id TH_id,
tjh.user_id TH_user_id,
tjh.company_name TH_company_name,
tjh.years TH_years,
tjh.title TH_title
FROM
t_user tu,
t_job_history tjh
WHERE
tu.id = tjh.user_id
AND tjh.user_id = 2
查到的结果如下:
Mybatis会按照我们指定的resultMap,将上面的查询结果封装成我们自定义的实体类 — UserAndJobHistoryVO。
2.3 懒加载的情况
- TUserMapper.xml
<?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.nrsc.mybatis.mapper.TUserMapper">
<resultMap id="BaseResultMap" type="com.nrsc.mybatis.domian.TUser">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="user_name" jdbcType="VARCHAR" property="userName"/>
<result column="real_name" jdbcType="VARCHAR" property="realName"/>
<result column="sex" jdbcType="TINYINT" property="sex"/>
<result column="mobile" jdbcType="VARCHAR" property="mobile"/>
<result column="email" jdbcType="VARCHAR" property="email"/>
<result column="note" jdbcType="VARCHAR" property="note"/>
<result column="position_id" jdbcType="INTEGER" property="positionId"/>
</resultMap>
<!--++++++++++++++++++++++++++++++++++++++1对多懒加载 ++++++++++++++++++++++++++++++++++++++++-->
<resultMap id="UserAndJobHistoryLazyMap" type="com.nrsc.mybatis.vo.UserAndJobHistoryVO">
<!--
注意:
column指的是select对应的sql语句需要的参数,需要格外注意的是这个值是本sql语句的列名
-->
<collection property="jobHistoryList" column="id"
select="com.nrsc.mybatis.mapper.TJobHistoryMapper.selectByUserId"
fetchType="lazy">
</collection>
</resultMap>
<select id="getUserAndJobsInfoLazy" resultMap="UserAndJobHistoryLazyMap">
select * from t_user where id = #{userId,jdbcType=VARCHAR}
</select>
<!--++++++++++++++++++++++++++++++++++++++1对多懒加载 ++++++++++++++++++++++++++++++++++++++++-->
</mapper>
这里需要注意的是 —> 上面的collection 标签里有一个select属性,该属性指向的是TJobHistoryMapper.xml.xml中的selectByUserId,这里在看一下TJobHistoryMapper.xml.xml:
<?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.nrsc.mybatis.mapper.TJobHistoryMapper">
<resultMap id="BaseResultMap" type="com.nrsc.mybatis.domian.TJobHistory">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="user_id" jdbcType="BIGINT" property="userId"/>
<result column="company_name" jdbcType="VARCHAR" property="companyName"/>
<result column="years" jdbcType="INTEGER" property="years"/>
<result column="title" jdbcType="VARCHAR" property="title"/>
</resultMap>
<sql id="Base_Column_List">
id, user_id, company_name, years, title
</sql>
<select id="selectByUserId" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from t_job_history
where user_id = #{userId,jdbcType=BIGINT}
</select>
</mapper>
不贴具体的测试结果了,有兴趣的可以clone下来代码自己测试一下。
源码地址:https://github.com/nieandsun/mybatis-study