MyBatis 之关系映射（上）
作者：andy
- 2022-10-27 北京
本文字数：6831 字
阅读完需：约 22 分钟
一、一对一关系映射
一对一关系映射，说的通俗一点，就是一一对应关系，可以通过级联的方式，将两者相关的数据查询出来。
Country 表：
create table country( countryId varchar(50) not null, name varchar(255) not null, constraint pk_countryId primary key(countryId))engine innodb;复制代码
Capital 表：
create table capital( capitalId varchar(50) not null, countryId varchar(50) not null, name varchar(255) not null, constraint pk_capitalId primary key(capitalId))engine innodb;复制代码
Capital 类：
package org.fuys.owndb.vo;import java.io.Serializable;@SuppressWarnings("serial")public class Capital implements Serializable { private String countryId; private String capitalId; private String name;}复制代码
Country 类：
package org.fuys.owndb.vo;import java.io.Serializable;import java.util.List;@SuppressWarnings("serial")public class Country implements Serializable { private String countryId; private String name; private Capital capital;}复制代码
核心配置文件：
<typeAliases> <typeAlias type="org.fuys.owndb.vo.Country" alias="Country"/> <typeAlias type="org.fuys.owndb.vo.Capital" alias="Capital"/> </typeAliases> <mappers> <mapper resource="org/fuys/owndb/vo/mapping/Country.xml"/> <mapper resource="org/fuys/owndb/vo/mapping/Capital.xml"/> </mappers>复制代码
Country.xml 配置文件：
<mapper namespace="org.fuys.owndb.vo.mapping.Country"> <resultMap type="Country" id="CountryMap"> <id column="countryId" property="countryId"/> <result column="name" property="name"/> <association column="countryId" property="capital" javaType="Capital" select="org.fuys.owndb.vo.mapping.Capital.selectByCountryId"/> </resultMap> <select id="selectByCountryId" parameterType="java.lang.String" resultMap="CountryMap"> SELECT countryId,name FROM country WHERE countryId = #{countryId} </select> <select id="select" parameterType="Country" resultMap="CountryMap"> SELECT countryId,name FROM country <where> <if test="countryId !=null"> and countryId = #{countryId} </if> <if test="name !=null"> and name = #{name} </if> </where> </select></mapper>复制代码
Capital.xml 配置文件：
<mapper namespace="org.fuys.owndb.vo.mapping.Capital"> <resultMap type="Capital" id="CapitalMap"> <id column="capitalId" property="capitalId"/> <result column="name" property="name"/> </resultMap> <select id="selectByCountryId" parameterType="java.lang.String" resultMap="CapitalMap"> SELECT capitalId,countryId,name FROM capital WHERE countryId = #{countryId} </select></mapper>复制代码
junit 测试代码：
@Test public void testSelectOneVsOne(){ Country country = MyBatisSqlSessionFactory.getSqlSession().selectOne("org.fuys.owndb.vo.mapping.Country.selectByCountryId","CHINA"); TestCase.assertNotNull(country); logger.info(country.toString()); country = new Country(); country.setCountryId("CHINA"); List<Country> countryList = MyBatisSqlSessionFactory.getSqlSession().selectList("org.fuys.owndb.vo.mapping.Country.select",country); TestCase.assertNotNull(countryList); logger.info(countryList.toString()); }复制代码
总结得出，配置 MyBatis 一对一关系信息，级联查询时，association 级联查询对象的方法只能是单条数据，不能是多条，同时级联查询对象属性的设置也只能是单一对象属性，不能是集合。
要实现关系映射，一定要使用 resultMap，这是不二法则。
疑问：
在原课的讲解上，老李讲到了“ 1 + N ”次查询的问题，但是，我仍然不明白，怎么会造成了“ 1 + N ”次，这个暂且留着，后续再研究。
二、一对多映射
数据库设计当中，第三范式也是使用广泛的一种关系，MyBatis 也提供了该功能的支持。同时仍然需要注意，实现关系的映射，需使用 resultMap。
country 表：
create table country( countryId varchar(50) not null, name varchar(255) not null, constraint pk_countryId primary key(countryId))engine innodb;复制代码
province 表：
create table Province( provinceId varchar(50) not null, countryId varchar(50) not null, name varchar(255) not null, constraint pk_provinceId primary key(provinceId))engine innodb;复制代码
Country 类：
package org.fuys.owndb.vo;import java.io.Serializable;import java.util.List;@SuppressWarnings("serial")public class Country implements Serializable { private String countryId; private String name; private List<Province> provinces;}复制代码
Province 类：
package org.fuys.owndb.vo;import java.io.Serializable;@SuppressWarnings("serial")public class Province implements Serializable { private String provinceId; private String name; private Country country; }复制代码
配置核心文件信息：
<typeAliases> <typeAlias type="org.fuys.owndb.vo.Country" alias="Country"/> <typeAlias type="org.fuys.owndb.vo.Province" alias="Province"/> </typeAliases> <mappers> <mapper resource="org/fuys/owndb/vo/mapping/Country.xml"/> <mapper resource="org/fuys/owndb/vo/mapping/Province.xml"/> </mappers>复制代码
2.1、实现一对多的映射查询
Country.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="org.fuys.owndb.vo.mapping.Country"> <resultMap type="Country" id="CountryMap"> <id column="countryId" property="countryId"/> <result column="name" property="name"/> <collection property="provinces" column="countryId" javaType="java.util.List" ofType="Province" select="org.fuys.owndb.vo.mapping.Province.selectByCountryId"/> </resultMap> <select id="selectByCountryId" parameterType="java.lang.String" resultMap="CountryMap"> SELECT countryId,name FROM country WHERE countryId = #{countryId} </select></mapper>复制代码
Province.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="org.fuys.owndb.vo.mapping.Province"> <resultMap type="Province" id="ProvinceMap"> <id column="provinceId" property="provinceId" /> <result column="name" property="name" /> </resultMap> <select id="selectByCountryId" parameterType="String" resultMap="ProvinceMap"> SELECT provinceId,countryId,name FROM province WHERE countryId = #{countryId} </select></mapper>复制代码
2.2、实现多对一的映射查询
Country.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="org.fuys.owndb.vo.mapping.Country"> <resultMap type="Country" id="CountryMap"> <id column="countryId" property="countryId"/> <result column="name" property="name"/> <association column="countryId" property="capital" javaType="Capital" select="org.fuys.owndb.vo.mapping.Capital.selectByCountryId"/> <!-- <collection property="provinces" column="countryId" javaType="java.util.List" ofType="Province" select="org.fuys.owndb.vo.mapping.Province.selectByCountryId"/> --> </resultMap> <select id="selectByCountryId" parameterType="java.lang.String" resultMap="CountryMap"> SELECT countryId,name FROM country WHERE countryId = #{countryId} </select></mapper>复制代码
Province.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="org.fuys.owndb.vo.mapping.Province"> <resultMap type="Province" id="ProvinceMap"> <id column="provinceId" property="provinceId" /> <result column="name" property="name" /><!-- <association property="country" column="countryId" javaType="Country" resultMap="org.fuys.owndb.vo.mapping.Country.CountryMap"/> --> <association property="country" column="countryId" javaType="Country" select="org.fuys.owndb.vo.mapping.Country.selectByCountryId"/> </resultMap> <select id="selectByProvinceId" parameterType="String" resultMap="ProvinceMap"> SELECT provinceId,countryId,name FROM province WHERE provinceId = #{provinceId} </select> <select id="selectByCountryId" parameterType="String" resultMap="ProvinceMap"> SELECT provinceId,countryId,name FROM province WHERE countryId = #{countryId} </select></mapper>复制代码
执行结果：
DEBUG selectByProvinceId - ==> Preparing: SELECT provinceId,countryId,name FROM province WHERE provinceId = ? DEBUG selectByProvinceId - ==> Parameters: GUIZHOU(String)TRACE selectByProvinceId - <== Columns: provinceId, countryId, nameTRACE selectByProvinceId - <== Row: GUIZHOU, CHINA, GuiZhouDEBUG selectByCountryId - ====> Preparing: SELECT countryId,name FROM country WHERE countryId = ? DEBUG selectByCountryId - ====> Parameters: CHINA(String)TRACE selectByCountryId - <==== Columns: countryId, nameTRACE selectByCountryId - <==== Row: CHINA, chinaDEBUG selectByCountryId - ======> Preparing: SELECT capitalId,countryId,name FROM capital WHERE countryId = ? DEBUG selectByCountryId - ======> Parameters: CHINA(String)TRACE selectByCountryId - <====== Columns: capitalId, countryId, nameTRACE selectByCountryId - <====== Row: BEIJING, CHINA, beijingDEBUG selectByCountryId - <====== Total: 1DEBUG selectByCountryId - <==== Total: 1DEBUG selectByProvinceId - <== Total: 1INFO TestOneVsMore - Province [provinceId=GUIZHOU, name=GuiZhou, country=Country [countryId=CHINA, name=china, capital=Capital [countryId=CHINA, capitalId=BEIJING, name=beijing], provinces=null]]复制代码
方式二：
<?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.fuys.owndb.vo.mapping.Province"> <resultMap type="Province" id="ProvinceMap"> <id column="provinceId" property="provinceId" /> <result column="name" property="name" /> <association property="country" column="countryId" javaType="Country" resultMap="org.fuys.owndb.vo.mapping.Country.CountryMap"/><!-- <association property="country" column="countryId" javaType="Country" select="org.fuys.owndb.vo.mapping.Country.selectByCountryId"/> --> </resultMap> <select id="selectByProvinceId" parameterType="String" resultMap="ProvinceMap"> SELECT provinceId,countryId,name FROM province WHERE provinceId = #{provinceId} </select> <select id="selectByCountryId" parameterType="String" resultMap="ProvinceMap"> SELECT provinceId,countryId,name FROM province WHERE countryId = #{countryId} </select></mapper>复制代码
执行结果：
DEBUG selectByProvinceId - ==> Preparing: SELECT provinceId,countryId,name FROM province WHERE provinceId = ? DEBUG selectByProvinceId - ==> Parameters: GUIZHOU(String)TRACE selectByProvinceId - <== Columns: provinceId, countryId, nameTRACE selectByProvinceId - <== Row: GUIZHOU, CHINA, GuiZhouDEBUG selectByCountryId - ====> Preparing: SELECT capitalId,countryId,name FROM capital WHERE countryId = ? DEBUG selectByCountryId - ====> Parameters: CHINA(String)TRACE selectByCountryId - <==== Columns: capitalId, countryId, nameTRACE selectByCountryId - <==== Row: BEIJING, CHINA, beijingDEBUG selectByCountryId - <==== Total: 1DEBUG selectByProvinceId - <== Total: 1INFO TestOneVsMore - Province [provinceId=GUIZHOU, name=GuiZhou, country=Country [countryId=CHINA, name=GuiZhou, capital=Capital [countryId=CHINA, capitalId=BEIJING, name=beijing], provinces=null]]复制代码
注意：
使用级联的 resultMap 和 select，返回查询的方式不同，resultMap 进行结果映射查询，select 则是使用查询语句进行查询，相当于重新调用了声明的查询语句，故又将所有的映射关系查询了一次；
当需要进行映射查询时，不要互相使用映射查询，会出现互相之间反复调用的情况，而抛出栈溢出问题；
建议较少使用映射关系，避免互相之间的多次调用，造成内存损耗。
“1 + N”问题的阐释：
父级查询多个数据，但是因为关联映射关系。那么，只要父级查询一条 sql 语句，，通过映射，也会去查询该条数据下的关联映射的所有 sql。那么，查询多个父级数据，就会出现了“1+N”的问题。
结果显示如下：
DEBUG select - ==> Preparing: SELECT countryId,name FROM country DEBUG select - ==> Parameters: TRACE select - <== Columns: countryId, nameTRACE select - <== Row: CHINA, chinaDEBUG selectByCountryId - ====> Preparing: SELECT capitalId,countryId,name FROM capital WHERE countryId = ? DEBUG selectByCountryId - ====> Parameters: CHINA(String)TRACE selectByCountryId - <==== Columns: capitalId, countryId, nameTRACE selectByCountryId - <==== Row: BEIJING, CHINA, beijingDEBUG selectByCountryId - <==== Total: 1DEBUG selectByCountryId - ====> Preparing: SELECT provinceId,countryId,name FROM province WHERE countryId = ? DEBUG selectByCountryId - ====> Parameters: CHINA(String)TRACE selectByCountryId - <==== Columns: provinceId, countryId, nameTRACE selectByCountryId - <==== Row: BEIJING, CHINA, BeiJingTRACE selectByCountryId - <==== Row: GUIZHOU, CHINA, GuiZhouDEBUG selectByCountryId - <==== Total: 2TRACE select - <== Row: JAPAN, japanDEBUG selectByCountryId - ====> Preparing: SELECT capitalId,countryId,name FROM capital WHERE countryId = ? DEBUG selectByCountryId - ====> Parameters: JAPAN(String)TRACE selectByCountryId - <==== Columns: capitalId, countryId, nameTRACE selectByCountryId - <==== Row: TOKYO, JAPAN, tokyoDEBUG selectByCountryId - <==== Total: 1DEBUG selectByCountryId - ====> Preparing: SELECT provinceId,countryId,name FROM province WHERE countryId = ? DEBUG selectByCountryId - ====> Parameters: JAPAN(String)DEBUG selectByCountryId - <==== Total: 0DEBUG select - <== Total: 2INFO TestOneVsMore - [Country [countryId=CHINA, name=china, capital=Capital [countryId=CHINA, capitalId=BEIJING, name=beijing], provinces=[Province [provinceId=BEIJING, name=BeiJing, country=null], Province [provinceId=GUIZHOU, name=GuiZhou, country=null]]], Country [countryId=JAPAN, name=japan, capital=Capital [countryId=JAPAN, capitalId=TOKYO, name=tokyo], provinces=[]]]
复制代码
划线
评论
复制
发布于: 刚刚阅读数: 3
andy
关注
还未添加个人签名 2019-11-21 加入
还未添加个人简介
评论