高级结果映射
MyBatis可以轻松的将一对一、一对多的查询结果映射成复杂的组合关系。
表结构
customer_login是客户表。
customer_inf是客户信息扩展表。
customer_login_log是客户登录日志表。
customer_login和customer_inf的关系是1:1.
customer_login和customer_login_log的关系是1:N。
假设有个需求是联查这3个表联查的结果映射到一个JavaBean:CustomerLoginDTO中:
public class CustomerLoginDTO {
private CustomerLogin customerLogin;
private CustomerInf customerInf;
private List<CustomerLoginLog> customerLoginLog = new ArrayList<>();
public CustomerLogin getCustomerLogin() {
return customerLogin;
}
public void setCustomerLogin(CustomerLogin customerLogin) {
this.customerLogin = customerLogin;
}
public CustomerInf getCustomerInf() {
return customerInf;
}
public void setCustomerInf(CustomerInf customerInf) {
this.customerInf = customerInf;
}
public List<CustomerLoginLog> getCustomerLoginLog() {
return customerLoginLog;
}
public void setCustomerLoginLog(List<CustomerLoginLog> customerLoginLog) {
this.customerLoginLog = customerLoginLog;
}
}
public class CustomerLogin implements Serializable {
private Integer customerId;
private String loginName;
private String password;
private Byte userStats;
private Date modifiedTime;
private static final long serialVersionUID = 1L;
getter & setter
}
public class CustomerInf implements Serializable {
private Integer customerInfId;
private Integer customerId;
private String customerName;
private Byte identityCardType;
private String identityCardNo;
private String mobilePhone;
private String customerEmail;
private String gender;
private Integer userPoint;
private Date registerTime;
private Date birthday;
private Byte customerLevel;
private BigDecimal userMoney;
private Date modifiedTime;
private static final long serialVersionUID = 1L;
getter & setter
}
public class CustomerLoginLog implements Serializable {
private Integer loginId;
private Integer customerId;
private Date loginTime;
private String loginIp;
private Byte loginType;
private static final long serialVersionUID = 1L;
getter & setter
}
3张表关联查询的sql如下:
SELECT
a.customer_id AS a_customer_id,
a.login_name AS a_login_name,
a.PASSWORD AS a_password,
a.user_stats AS a_user_stats,
a.modified_time AS a_modified_time,
b.customer_inf_id AS b_customer_inf_id,
b.customer_name AS b_customer_name,
b.identity_card_type AS b_identity_card_type,
b.identity_card_no AS b_identity_card_no,
b.mobile_phone AS b_mobile_phone,
b.customer_email AS b_customer_email,
b.gender AS b_gender,
b.user_point AS b_user_point,
b.register_time AS b_register_time,
b.birthday AS b_birthday,
b.customer_level AS b_customer_level,
b.user_money AS b_user_money,
b.modified_time AS b_modified_time,
c.login_id AS c_login_id,
c.customer_id AS c_customer_id,
c.login_time AS c_login_time,
c.login_ip AS c_login_ip,
c.login_type AS c_login_type
FROM
customer_login a
LEFT JOIN customer_inf b ON a.customer_id = b.customer_id
LEFT JOIN customer_login_log c ON a.customer_id = c.customer_id
WHERE
a.customer_id = 1
one to one
查询的结果放到:
public class CustomerLoginOneToOne implements Serializable {
private Integer customerId;
private String loginName;
private String password;
private Byte userStats;
private Date modifiedTime;
private static final long serialVersionUID = 1L;
private CustomerInf customerInf;
getter and setter
}
方法1:自动映射
<select id="testOneToOne1" parameterType="java.lang.Integer" resultType="com.sss.pojo.CustomerLoginOneToOne">
select
a.customer_id as customerId,
a.login_name as loginName,
a.password as password,
a.user_stats as userStats,
a.modified_time as modifiedTime,
b.customer_id as "customerInf.customerId",
b.customer_inf_id as "customerInf.customerInfId",
b.customer_name as "customerInf.customerName",
b.identity_card_type as "customerInf.identityCardType",
b.identity_card_no as "customerInf.identityCardNo",
b.mobile_phone as "customerInf.mobilePhone",
b.customer_email as "customerInf.customerEmail",
b.gender as "customerInf.gender",
b.user_point as "customerInf.userPoint",
b.register_time as "customerInf.registerTime",
b.birthday as "customerInf.birthday",
b.customer_level as "customerInf.customerLevel",
b.user_money as "customerInf.userMoney",
b.modified_time as "customerInf.modifiedTime"
from customer_login a
left join customer_inf b on a.customer_id = b.customer_id
where a.customer_id = #{customerId,jdbcType=INTEGER}
</select>
public interface CustomerMapper {
CustomerLoginOneToOne testOneToOne1(Integer customerId);
}
方法2:嵌套的resultMap
<resultMap id="testOneToOne2Map" type="com.sss.pojo.CustomerLoginOneToOne">
<id property="customerId" column="a.customerId"></id>
<result property="loginName" column="a.loginName"></result>
<result property="password" column="a.password"></result>
<result property="userStats" column="a.userStats"></result>
<result property="modifiedTime" column="a.modifiedTime"></result>
<result property="customerInf.customerId" column="b.customerId"></result>
<result property="customerInf.customerInfId" column="b.customerInfId"></result>
<result property="customerInf.customerName" column="b.customerName"></result>
<result property="customerInf.identityCardType" column="b.identityCardType"></result>
<result property="customerInf.identityCardNo" column="b.identityCardNo"></result>
<result property="customerInf.mobilePhone" column="b.mobilePhone"></result>
<result property="customerInf.customerEmail" column="b.customerEmail"></result>
<result property="customerInf.gender" column="b.gender"></result>
<result property="customerInf.userPoint" column="b.userPoint"></result>
<result property="customerInf.registerTime" column="b.registerTime"></result>
<result property="customerInf.birthday" column="b.birthday"></result>
<result property="customerInf.customerLevel" column="b.customerLevel"></result>
<result property="customerInf.userMoney" column="b.userMoney"></result>
<result property="customerInf.modifiedTime" column="b.modifiedTime"></result>
</resultMap>
<select id="testOneToOne2" parameterType="java.lang.Integer" resultMap="testOneToOne2Map">
select
a.customer_id as "a.customerId",
a.login_name as "a.loginName",
a.password as "a.password",
a.user_stats as "a.userStats",
a.modified_time as "a.modifiedTime",
b.customer_id as "b.customerId",
b.customer_inf_id as "b.customerInfId",
b.customer_name as "b.customerName",
b.identity_card_type as "b.identityCardType",
b.identity_card_no as "b.identityCardNo",
b.mobile_phone as "b.mobilePhone",
b.customer_email as "b.customerEmail",
b.gender as "b.gender",
b.user_point as "b.userPoint",
b.register_time as "b.registerTime",
b.birthday as "b.birthday",
b.customer_level as "b.customerLevel",
b.user_money as "b.userMoney",
b.modified_time as "b.modifiedTime"
from customer_login a
left join customer_inf b on a.customer_id = b.customer_id
where a.customer_id = #{customerId,jdbcType=INTEGER}
</select>
public interface CustomerMapper {
CustomerLoginOneToOne testOneToOne2(Integer customerId);
}
方法3 resultMap继承
<resultMap id="testOneToOne2Map3_1" type="com.sss.pojo.CustomerLoginOneToOne">
<id property="customerId" column="a.customerId"></id>
<result property="loginName" column="a.loginName"></result>
<result property="password" column="a.password"></result>
<result property="userStats" column="a.userStats"></result>
<result property="modifiedTime" column="a.modifiedTime"></result>
</resultMap>
<resultMap id="testOneToOne2Map3" type="com.sss.pojo.CustomerLoginOneToOne" extends="testOneToOne2Map3_1">
<result property="customerInf.customerId" column="b.customerId"></result>
<result property="customerInf.customerInfId" column="b.customerInfId"></result>
<result property="customerInf.customerName" column="b.customerName"></result>
<result property="customerInf.identityCardType" column="b.identityCardType"></result>
<result property="customerInf.identityCardNo" column="b.identityCardNo"></result>
<result property="customerInf.mobilePhone" column="b.mobilePhone"></result>
<result property="customerInf.customerEmail" column="b.customerEmail"></result>
<result property="customerInf.gender" column="b.gender"></result>
<result property="customerInf.userPoint" column="b.userPoint"></result>
<result property="customerInf.registerTime" column="b.registerTime"></result>
<result property="customerInf.birthday" column="b.birthday"></result>
<result property="customerInf.customerLevel" column="b.customerLevel"></result>
<result property="customerInf.userMoney" column="b.userMoney"></result>
<result property="customerInf.modifiedTime" column="b.modifiedTime"></result>
</resultMap>
<select id="testOneToOne3" parameterType="java.lang.Integer" resultMap="testOneToOne2Map3">
select
a.customer_id as "a.customerId",
a.login_name as "a.loginName",
a.password as "a.password",
a.user_stats as "a.userStats",
a.modified_time as "a.modifiedTime",
b.customer_id as "b.customerId",
b.customer_inf_id as "b.customerInfId",
b.customer_name as "b.customerName",
b.identity_card_type as "b.identityCardType",
b.identity_card_no as "b.identityCardNo",
b.mobile_phone as "b..mobilePhone",
b.customer_email as "b.customerEmail",
b.gender as "b.gender",
b.user_point as "b.userPoint",
b.register_time as "b.registerTime",
b.birthday as "b.birthday",
b.customer_level as "b.customerLevel",
b.user_money as "b.userMoney",
b.modified_time as "b.modifiedTime"
from customer_login a
left join customer_inf b on a.customer_id = b.customer_id
where a.customer_id = #{customerId,jdbcType=INTEGER}
</select>
public interface CustomerMapper {
CustomerLoginOneToOne testOneToOne3(Integer customerId);
}
方法4:association嵌套结果
<resultMap id="testOneToOne2Map4" type="com.sss.pojo.CustomerLoginOneToOne" extends="testOneToOne2Map3_1">
<association property="customerInf" columnPrefix="b." javaType="com.sss.pojo.CustomerInf">
<id property="customerId" column="customerId"></id>
<result property="customerInfId" column="customerInfId"></result>
<result property="customerName" column="customerName"></result>
<result property="identityCardType" column="identityCardType"></result>
<result property="identityCardNo" column="identityCardNo"></result>
<result property="mobilePhone" column="mobilePhone"></result>
<result property="customerEmail" column="customerEmail"></result>
<result property="gender" column="gender"></result>
<result property="userPoint" column="userPoint"></result>
<result property="registerTime" column="registerTime"></result>
<result property="birthday" column="birthday"></result>
<result property="customerLevel" column="customerLevel"></result>
<result property="userMoney" column="userMoney"></result>
<result property="modifiedTime" column="modifiedTime"></result>
</association>
</resultMap>
<select id="testOneToOne4" parameterType="java.lang.Integer" resultMap="testOneToOne2Map4">
select
a.customer_id as "a.customerId",
a.login_name as "a.loginName",
a.password as "a.password",
a.user_stats as "a.userStats",
a.modified_time as "a.modifiedTime",
b.customer_id as "b.customerId",
b.customer_inf_id as "b.customerInfId",
b.customer_name as "b.customerName",
b.identity_card_type as "b.identityCardType",
b.identity_card_no as "b.identityCardNo",
b.mobile_phone as "b.mobilePhone",
b.customer_email as "b.customerEmail",
b.gender as "b.gender",
b.user_point as "b.userPoint",
b.register_time as "b.registerTime",
b.birthday as "b.birthday",
b.customer_level as "b.customerLevel",
b.user_money as "b.userMoney",
b.modified_time as "b.modifiedTime"
from customer_login a
left join customer_inf b on a.customer_id = b.customer_id
where a.customer_id = #{customerId,jdbcType=INTEGER}
</select>
public interface CustomerMapper {
CustomerLoginOneToOne testOneToOne4(Integer customerId);
}
方法5:association嵌套查询
<resultMap id="testOneToOne2Map5" type="com.sss.pojo.CustomerLoginOneToOne" extends="testOneToOne2Map3_1">
<association property="customerInf" column="a.customerId" select="selectFromCustomerInf">
</association>
</resultMap>
<select id="selectFromCustomerInf" parameterType="java.lang.Integer" resultType="com.sss.pojo.CustomerInf">
select
b.customer_id as "customerId",
b.customer_inf_id as "customerInfId",
b.customer_name as "customerName",
b.identity_card_type as "identityCardType",
b.identity_card_no as "identityCardNo",
b.mobile_phone as "mobilePhone",
b.customer_email as "customerEmail",
b.gender as "gender",
b.user_point as "userPoint",
b.register_time as "registerTime",
b.birthday as "birthday",
b.customer_level as "customerLevel",
b.user_money as "userMoney",
b.modified_time as "modifiedTime"
from customer_inf b where b.customer_id = #{customerId}
</select>
<select id="testOneToOne5" parameterType="java.lang.Integer" resultMap="testOneToOne2Map5">
select
a.customer_id as "a.customerId",
a.login_name as "a.loginName",
a.password as "a.password",
a.user_stats as "a.userStats",
a.modified_time as "a.modifiedTime"
from customer_login a where a.customer_id = #{customerId,jdbcType=INTEGER}
</select>
public interface CustomerMapper {
CustomerLoginOneToOne testOneToOne5(Integer customerId);
}
查看控制台sql日志,参数传递正确:
15:57:16.930 [main] DEBUG o.a.i.t.jdbc.JdbcTransaction - Opening JDBC Connection
15:57:19.233 [main] DEBUG o.a.i.t.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@ec2bf82]
15:57:19.242 [main] DEBUG c.s.dao.CustomerMapper.testOneToOne5 - ==> Preparing: select a.customer_id as "a.customerId", a.login_name as "a.loginName", a.password as "a.password", a.user_stats as "a.userStats", a.modified_time as "a.modifiedTime" from customer_login a where a.customer_id = ?
15:57:19.325 [main] DEBUG c.s.dao.CustomerMapper.testOneToOne5 - ==> Parameters: 1(Integer)
15:57:19.361 [main] DEBUG c.s.d.C.selectFromCustomerInf - ====> Preparing: select b.customer_id as "customerId", b.customer_inf_id as "customerInfId", b.customer_name as "customerName", b.identity_card_type as "identityCardType", b.identity_card_no as "identityCardNo", b.mobile_phone as "mobilePhone", b.customer_email as "customerEmail", b.gender as "gender", b.user_point as "userPoint", b.register_time as "registerTime", b.birthday as "birthday", b.customer_level as "customerLevel", b.user_money as "userMoney", b.modified_time as "modifiedTime" from customer_inf b where b.customer_id = ?
15:57:19.362 [main] DEBUG c.s.d.C.selectFromCustomerInf - ====> Parameters: 1(Integer)
15:57:19.484 [main] DEBUG c.s.d.C.selectFromCustomerInf - <==== Total: 1
15:57:19.485 [main] DEBUG c.s.dao.CustomerMapper.testOneToOne5 - <== Total: 1
上例延迟加载测试
对于上例中控制台输出的2个sql,MySQL默认是没有开启延迟加载的,所以2个sql都执行了。
修改MyBatis的配置文件:
<settings>
<!-- 打开延迟加载的开关 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 将积极加载改为消极加载,即延迟加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
修改测试代码:
@Test
public void testOneToOne(){
SqlSession sqlSession = sqlSessionFactory.openSession();
System.out.println(sqlSession.getClass());
try {
CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
//CustomerLoginOneToOne result = customerMapper.testOneToOne1(1);
//CustomerLoginOneToOne result = customerMapper.testOneToOne2(1);
//CustomerLoginOneToOne result = customerMapper.testOneToOne3(1);
//CustomerLoginOneToOne result = customerMapper.testOneToOne4(1);
CustomerLoginOneToOne result = customerMapper.testOneToOne5(1);
//log.info("{}",result.getCustomerInf());
sqlSession.commit();
} finally {
sqlSession.close();
}
}
如果不放开最后一行注释,控制台指挥打印1个sql。反之放开注释,因为用到了CustomerInf对象,所以会即时去查库。
one to many
查询结果映射到:
public class CustomerLoginOneToMany implements Serializable {
private Integer customerId;
private String loginName;
private String password;
private Byte userStats;
private Date modifiedTime;
private static final long serialVersionUID = 1L;
private List<CustomerLoginLog> customerLoginLogList;
getter & setter
}
方法1:collection嵌套结果
<select id="testOneToMany1" parameterType="java.lang.Integer" resultMap="testOneToMany1Map">
select
a.customer_id as "a.customerId",
a.login_name as "a.loginName",
a.password as "a.password",
a.user_stats as "a.userStats",
a.modified_time as "a.modifiedTime",
b.login_id as "b.loginId",
b.customer_id as "b.customerId",
b.login_time as "b.loginTime",
b.login_ip as "b.loginIp",
b.login_type as "b.loginType"
from customer_login a
left join customer_login_log b on a.customer_id = b.customer_id
where a.customer_id = #{customerId,jdbcType=INTEGER}
</select>
<resultMap id="testOneToOne2Map3_1" type="com.sss.pojo.CustomerLoginOneToOne">
<id property="customerId" column="a.customerId"></id>
<result property="loginName" column="a.loginName"></result>
<result property="password" column="a.password"></result>
<result property="userStats" column="a.userStats"></result>
<result property="modifiedTime" column="a.modifiedTime"></result>
</resultMap>
<resultMap id="customerLoginLogMap" type="com.sss.pojo.CustomerLoginLog">
<id property="loginId" column="loginId"></id>
<result property="customerId" column="customerId"></result>
<result property="loginTime" column="loginTime"></result>
<result property="loginIp" column="loginIp"></result>
<result property="loginType" column="loginType"></result>
</resultMap>
<resultMap id="testOneToMany1Map" type="com.sss.pojo.CustomerLoginOneToMany" extends="testOneToOne2Map3_1">
<collection property="customerLoginLogList" columnPrefix="b." resultMap="customerLoginLogMap">
</collection>
</resultMap>
public interface CustomerMapper {
CustomerLoginOneToMany testOneToMany1(Integer customerId);
}
方法2:collection嵌套查询
<select id="testOneToMany2" parameterType="java.lang.Integer" resultMap="testOneToMany2Map">
select
a.customer_id as "a.customerId",
a.login_name as "a.loginName",
a.password as "a.password",
a.user_stats as "a.userStats",
a.modified_time as "a.modifiedTime"
from customer_login a where a.customer_id = #{customerId,jdbcType=INTEGER}
</select>
<resultMap id="testOneToOne2Map3_1" type="com.sss.pojo.CustomerLoginOneToOne">
<id property="customerId" column="a.customerId"></id>
<result property="loginName" column="a.loginName"></result>
<result property="password" column="a.password"></result>
<result property="userStats" column="a.userStats"></result>
<result property="modifiedTime" column="a.modifiedTime"></result>
</resultMap>
<resultMap id="testOneToMany2Map" type="com.sss.pojo.CustomerLoginOneToMany" extends="testOneToOne2Map3_1">
<collection property="customerLoginLogList" column="a.customerId" ofType="com.sss.pojo.CustomerLoginLog" select="selectFromCustomerLoginLog">
</collection>
</resultMap>
<select id="selectFromCustomerLoginLog" parameterType="java.lang.Integer" resultType="com.sss.pojo.CustomerLoginLog">
select b.login_id as "loginId",
b.customer_id as "customerId",
b.login_time as "loginTime",
b.login_ip as "loginIp",
b.login_type as "loginType"
from customer_login_log b where customer_id = #{customerId,jdbcType=INTEGER}
</select>
public interface CustomerMapper {
CustomerLoginOneToMany testOneToMany2(Integer customerId);
}
20:39:46.546 [main] DEBUG c.s.d.CustomerMapper.testOneToMany2 - ==> Preparing: select a.customer_id as "a.customerId", a.login_name as "a.loginName", a.password as "a.password", a.user_stats as "a.userStats", a.modified_time as "a.modifiedTime" from customer_login a where a.customer_id = ?
20:39:46.601 [main] DEBUG c.s.d.CustomerMapper.testOneToMany2 - ==> Parameters: 1(Integer)
20:39:46.623 [main] DEBUG c.s.d.C.selectFromCustomerLoginLog - ====> Preparing: select b.login_id as "loginId", b.customer_id as "customerId", b.login_time as "loginTime", b.login_ip as "loginIp", b.login_type as "loginType" from customer_login_log b where customer_id = ?
20:39:46.623 [main] DEBUG c.s.d.C.selectFromCustomerLoginLog - ====> Parameters: 1(Integer)
20:39:46.709 [main] DEBUG c.s.d.C.selectFromCustomerLoginLog - <==== Total: 2
20:39:46.709 [main] DEBUG c.s.d.CustomerMapper.testOneToMany2 - <== Total: 1
N+1问题
无论是association还是collection都支持嵌套结果和嵌套查询:
1、嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集
2、嵌套查询:通过执行另外一个 SQL 映射语句来返回预期的复杂类型
但是MyBatis官方文档不建议嵌套查询,因为会产生N+1问题。假设A表和B表关联,A表查了1次有N条记录,B表会查N次,总共N+1次。
可以用延迟加载解决。
<settings>
<!-- 打开延迟加载的开关 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 将积极加载改为消极加载,即延迟加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
fetchType=lazy
阿里java开发手册中关于ORM部分
-
【强制】 在表查询中,一律不要使用 * 作为查询的字段列表,需要哪些字段必须明确写明。说明: 1)增加查询分析器解析成本。 2)增减字段容易与 resultMap 配置不一致。 3)无用字段增加网络消耗,尤其是 text 类型的字段。
-
【强制】 POJO 类的布尔属性不能加 is,而数据库字段必须加 is_,要求在 resultMap 中进行字段与属性之间的映射。说明: 参见定义 POJO 类以及数据库字段定义规定,在中增加映射,是必须的。 在MyBatis Generator 生成的代码中,需要进行对应的修改。
-
【强制】 不要用 resultClass 当返回参数,即使所有类属性名与数据库字段一一对应,也需要定义;反过来,每一个表也必然有一个 POJO 类与之对应。说明: 配置映射关系,使字段与 DO 类解耦,方便维护。
-
【强制】 sql.xml 配置参数使用: #{}, #param# 不要使用${} 此种方式容易出现 SQL 注入。
-
【强制】 iBATIS 自带的 queryForList(String statementName,int start,int size)不推荐使用。说明: 其实现方式是在数据库取到 statementName 对应的 SQL 语句的所有记录,再通过 subList 取start,size 的子集合。
正例: Map<String, Object> map = new HashMap<>();
map.put(“start”, start);
map.put(“size”, size); -
【强制】 不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出。说明: resultClass=”Hashtable” , 会置入字段名和属性值,但是值的类型不可控。
-
【强制】 更新数据表记录时,必须同时更新记录对应的 gmt_modified 字段值为当前时间。
-
【推荐】 不要写一个大而全的数据更新接口。 传入为 POJO 类,不管是不是自己的目标更新字段,都进行 update table set c1=value1,c2=value2,c3=value3; 这是不对的。执行 SQL时, 不要更新无改动的字段,一是易出错;二是效率低;三是增加 binlog 存储。Java 开发手册
-
【参考】 @Transactional 事务不要滥用。事务会影响数据库的 QPS,另外使用事务的地方需要考虑各方面的回滚方案,包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。
10.【参考】 中的 compareValue 是与属性值对比的常量,一般是数字,表示相等时带上此条件; 表示不为空且不为 null 时执行; 表示不为 null 值时执行
其他良好设计规范
1、查询传参:
MyBatis查询不允许直接传递Map,可读性差,扩展性差。传递参数少于5个时,可以使用@Param注解传递参数。多于5个时,用JavaBean传参。和上述阿里规范第6条类似,不允许直接拿HashMap和HashTable输出查询结果。