一、需求
数据库关联关系主要包括四类:一对多关系,多对一,多对多,自关联,本章主要详细讲解一对多和多对一关联关系的mybatis实现,下面我们就通过实例来具体了解关联关系的实现。
实例需求:一个国家可以有多个国防部长,一个国防部长只能属于一个国家
二、完整实例
2.1、数据库country表和minister表建立及数据初始化
DROP TABLE IF EXISTS `minister`;
CREATE TABLE `minister` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`minister_name` varchar(15) COLLATE utf8_bin NOT NULL,
`cid` int(3) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `minister` */
insert into `minister`(`id`,`minister_name`,`cid`) values
(1,'张三',1),
(2,'李四',1),
(3,'王五',1),
(4,'特朗普',2),
(5,'奥巴马',2),
(6,'安倍晋三',3),
(7,'小院镇四郎',3),
(8,'朴景惠',4),
(9,'金正恩',4);
2.2、数据库实体建立
1、country实体
private int cid;
private String name;
private List<Minister> ministerList;
public List<Minister> getMinisterList() {
return ministerList;
}
public void setMinisterList(List<Minister> ministerList) {
this.ministerList = ministerList;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
@Override
public String toString() {
return "Country{" +
"cid=" + cid +
", name='" + name + '\'' +
", ministerList=" + ministerList +
'}';
}
2、minister实体类
private int mid;
private String name;
private int cid;
private Country country;
public Country getCountry() {
return country;
}
public void setCountry(Country country) {
this.country = country;
}
public int getMid() {
return mid;
}
public void setMid(int mid) {
this.mid = mid;
}
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Minister{" +
"mid=" + mid +
", name='" + name + '\'' +
", cid=" + cid +
", country=" + country +
'}';
}
2.3、一对多关系的实现:根据指定国家的id获取它所有国防部长的信息
2.3.1、多表联查
1、Mapper接口中的方法
//一对多,多表联查值,查询特定国家的所有领导人
Country selectCountryMultiList(int cid);
2、Mapper.xml配置
<!--多表联查-->
<select id="selectCountryMultiList" resultMap="countryMultiList">
select * from country c,minister m where c.cid = #{cid} and c.cid = m.cid;
</select>
<resultMap id="countryMultiList" type="com.achuan.demo.entity.linklearning.Country">
<id property="cid" jdbcType="INTEGER" column="cid"/>
<result property="name" jdbcType="VARCHAR" column="country_name"/>
<collection property="ministerList"
ofType="com.achuan.demo.entity.linklearning.Minister">
<id property="mid" jdbcType="INTEGER" column="id"/>
<result property="name" jdbcType="VARCHAR" column="minister_name"/>
<result property="cid" jdbcType="INTEGER" column="cid"/>
</collection>
</resultMap>
如上图collection对应的就是country实体类中的List<Minister> ministerList属性,ofType中的值就是Minister实体类的绝对路径,注意必须是ofType,不能是javaType否则报错。
3、测试结果
@Test
public void test01(){
Country country = countryMapper.selectCountryMultiList(1);
System.out.println(country);
}
15:06:40,628 DEBUG - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
15:06:40,650 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:06:40,651 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:06:40,651 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:06:40,651 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:06:40,822 DEBUG - Opening JDBC Connection
15:06:41,021 DEBUG - Created connection 283717519.
15:06:41,023 DEBUG - ==> Preparing: select * from country c,minister m where c.cid = ? and c.cid = m.cid;
15:06:41,056 DEBUG - ==> Parameters: 1(Integer)
15:06:41,085 DEBUG - <== Total: 3
Country{cid=1, name='中国', ministerList=[Minister{mid=1, name='张三', cid=1, country=null}, Minister{mid=2, name='李四', cid=1, country=null}, Minister{mid=3, name='王五', cid=1, country=null}]}
2.3.2、单表多次查询实现一对多的关系
1、mapper接口中的方法
//一对多,单表查询,查询特定国家的所有领导人
Country selectCountryByCid(int cid);
List<Minister> selectMinistersByCid(int cid);
2、mapper.xml中的配置
<!--单表查询:用的比较多,对于延时加载有用-->
<select id="selectCountryByCid" resultMap="country">
select * from country where cid = #{cid};
</select>
<select id="selectMinistersByCid" resultType="com.achuan.demo.entity.linklearning.Minister">
select id as mid,minister_name as name,cid from minister where cid = #{cid};
</select>
<resultMap id="country" type="com.achuan.demo.entity.linklearning.Country">
<id property="cid" jdbcType="INTEGER" column="cid"/>
<result property="name" jdbcType="VARCHAR" column="country_name"/>
<collection property="ministerList" ofType="com.achuan.demo.entity.linklearning.Minister"
select="selectMinistersByCid" column="cid"/>
</resultMap>
3、测试结果
@Test
public void test02(){
Country country = countryMapper.selectCountryByCid(1);
System.out.println(country);
}
15:12:18,790 DEBUG - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
15:12:18,815 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:12:18,815 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:12:18,815 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:12:18,816 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:12:18,959 DEBUG - Opening JDBC Connection
15:12:19,163 DEBUG - Created connection 283717519.
15:12:19,165 DEBUG - ==> Preparing: select * from country where cid = ?;
15:12:19,197 DEBUG - ==> Parameters: 1(Integer)
15:12:19,228 DEBUG - ====> Preparing: select id as mid,minister_name as name,cid from minister where cid = ?;
15:12:19,228 DEBUG - ====> Parameters: 1(Integer)
15:12:19,231 DEBUG - <==== Total: 3
15:12:19,232 DEBUG - <== Total: 1
Country{cid=1, name='中国', ministerList=[Minister{mid=1, name='张三', cid=1, country=null}, Minister{mid=2, name='李四', cid=1, country=null}, Minister{mid=3, name='王五', cid=1, country=null}]}
4、逻辑分析
由上可知,1)测试类中首先调用的是selectCountryByCid方法,获取到目标country对象,2)然后通过collection标签中的select和column属性值关联selectMinistersByCid方法,并给selectMinistersByCid方法传递column中的参数值,3)selectMinistersByCid方法中的statement语句获取参数后执行sql查询操作获取符合要求的所有minister对象实例。
总结,这种查询方式与上面多表联查查询的结果一样,但是相比于多表联查它可以实现延迟加载,当并发量很大的情况下,采用第二种方式可以极大的缓解数据库的压力。
2.4、多对一关系:通过一个国防部长的id获取自身以及它所属国家的所有数据信息
2.4.1、多表联查
1、mapper接口
//多对一:多表联查
Minister selectMinisterMultiListByMid(int mid);
2、mapper.xml中的配置
<!--//多表联查-->
<select id="selectMinisterMultiListByMid" resultMap="ministerMultiList">
select m.id,minister_name,c.cid,c.country_name from country c ,minister m
where c.cid = m.cid and m.id = #{mid};
</select>
<resultMap id="ministerMultiList" type="com.achuan.demo.entity.linklearning.Minister">
<id property="mid" column="id" jdbcType="INTEGER"/>
<result property="name" column="minister_name" jdbcType="VARCHAR"/>
<result property="cid" column="cid" jdbcType="INTEGER"/>
<association property="country" javaType="com.achuan.demo.entity.linklearning.Country">
<id property="cid" jdbcType="INTEGER" column="cid"/>
<result property="name" jdbcType="VARCHAR" column="country_name"/>
</association>
</resultMap>
注意:association标签对应的是minister实体中的Country country变量,javaType中声明的内容是country实体类的绝对路径,必须是javaType,不能是ofType,否则报错。
3、测试结果
@Test
public void test03(){
Minister minister = ministerMapper.selectMinisterMultiListByMid(1);
System.out.println(minister);
}
15:28:12,100 DEBUG - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
15:28:12,144 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:28:12,144 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:28:12,145 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:28:12,145 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:28:12,364 DEBUG - Opening JDBC Connection
15:28:12,564 DEBUG - Created connection 283717519.
15:28:12,566 DEBUG - ==> Preparing: select m.id,minister_name,c.cid,c.country_name from country c ,minister m where c.cid = m.cid and m.id = ?;
15:28:12,605 DEBUG - ==> Parameters: 1(Integer)
15:28:12,637 DEBUG - <== Total: 1
Minister{mid=1, name='张三', cid=1, country=Country{cid=1, name='中国', ministerList=null}}
2.4.2、单表查询
1、mapper接口
//多对一:单表查询
Minister selectMinisterByMid(int mid);
Country selectCountryByCid(int cid);
2、mapper.xml中的配置
<!--多对一:单表查询-->
<select id="selectMinisterByMid" resultMap="minister">
select id,minister_name,cid from minister where id = #{mid};
</select>
<resultMap id="minister" type="com.achuan.demo.entity.linklearning.Minister">
<id property="mid" column="id" jdbcType="INTEGER"/>
<result property="name" column="minister_name" jdbcType="VARCHAR"/>
<result property="cid" column="cid" jdbcType="INTEGER"/>
<association property="country" javaType="com.achuan.demo.entity.linklearning.Country"
select="selectCountryByCid" column="cid"/>
</resultMap>
<select id="selectCountryByCid" resultType="com.achuan.demo.entity.linklearning.Country">
select cid,country_name as name from country where cid = #{cid};
</select>
3、测试结果
@Test
public void test04(){
Minister minister = ministerMapper.selectMinisterByMid(1);
System.out.println(minister);
}
15:31:12,735 DEBUG - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
15:31:12,760 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:31:12,760 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:31:12,760 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:31:12,760 DEBUG - PooledDataSource forcefully closed/removed all connections.
15:31:12,930 DEBUG - Opening JDBC Connection
15:31:13,131 DEBUG - Created connection 283717519.
15:31:13,132 DEBUG - ==> Preparing: select id,minister_name,cid from minister where id = ?;
15:31:13,164 DEBUG - ==> Parameters: 1(Integer)
15:31:13,193 DEBUG - ====> Preparing: select cid,country_name as name from country where cid = ?;
15:31:13,194 DEBUG - ====> Parameters: 1(Integer)
15:31:13,197 DEBUG - <==== Total: 1
15:31:13,198 DEBUG - <== Total: 1
Minister{mid=1, name='张三', cid=1, country=Country{cid=1, name='中国', ministerList=null}}
Process finished with exit code 0
注意:此种执行逻辑与2.3.2一样,我们可以对比一下多表联查和单表多次查询的debug日志可以看出其执行逻辑的本质区别。