MyBatis学习3
11 关联关系查询
11.1 关联查询分类
当查询内容涉及到具有关联关系的多个表时，就需要使用关联查询。根据表与表间的关联关系的不同，关联查询分为四种：
（1）一对一关联查询
（2）一对多关联查询
（3）多对一关联查询
（4）多对多关联查询
由于日常工作中最常见的关联关系是一对多、多对一与多对多，而一对一关联查询了，其解决方案与多对一解决方案是相同的。
11.2 一对多关联查询
这里的一对多关联查询是指，在查询一方对象的时候，同时将其所关联的多方对象也都查询出来。下面以国家Country与部长Minister间的一对多关系演示。
1、测试数据
2、定义实体类
public class Country {
private Integer cid;
private String cname;
private Set<Minister> ministers;
//省略toString,getter,setter
}
public class Minister {
private Integer mid;
private String name;
public Minister() {}
//省略toString,getter,setter
}
注意：
在定义实体时，若定义的是双向关联，即双方的属性中均有对方对象作为域属性出现，那么它们在定义各自的
toString()方法时需要注意，只让某一方可以输出另一方即可，不要让双方的toString()方法均可输出对方。
这样会形成递归调用，程序出错。
3、定义Dao接口
public interface ISomeDao {
Country selectCountryById(Integer cid);
}
4、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.chengzi.dao.ISomeDao">
<resultMap id="countryMapper" type="com.chengzi.beans.Country">
<id column="cid" property="cid"/>
<result column="cname" property="cname"/>
<collection property="ministers" ofType="com.chengzi.beans.Minister">
<id column="mid" property="mid"/>
<result column="mname" property="mname"/>
</collection>
</resultMap>
<select id="selectCountryById" resultMap="countryMapper">
SELECT
cid,cname,mid,mname
FROM
country,minister
WHERE
cid = #{###} and cid = countryId
</select>
</mapper>
注意，此时即使字段名与属性名相同，在< resultMap />中也要写出它们的映射关系。因为框架是依据这人< resultMap/>封装对象的。另外，在映射文件中使用< collection/>标签体现出两个实体对象间的关联关系。其两个属性的意义为：
（1）property：指定关联属性，即Country类中的集合属性
（2）ofType：集合属性的泛型类型
5、测试类
public class MyTest3 {
private ISomeDao iSomeDao;
private SqlSession sqlSession;
@Before
public void before() throws IOException {
sqlSession = MybatisUtils.getSqlSession();
iSomeDao = sqlSession.getMapper(ISomeDao.class);
//sqlSession.commit();
}
@After
public void after() {
sqlSession.close();
}
@Test
public void testSelectCountryById(){
Country country = iSomeDao.selectCountryById(1);
System.out.println(country);
}
}
6、结果
log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
Country{cid=1, cname='USA', ministers=[Minister{mid=1, name='aaa'}, Minister{mid=2, name='bbb'}]}
Process finished with exit code 0
多表单独查询方式 :
多表连接查询方式是将多张表进行连接，连为一张表后进行查询。其查询的本质是一张表。而多表单独查询方式是多张表各自查询各自的相关内容，需要多张表的联合数据了，则将主表的查询结果联合其它表的查询结果，封装为一个对象。
当然，这多个查询是可以跨越多个映射文件的，即是可以跨越多个namespace的。在使用其它namespace的查询时，添加上其所在的namespace即可。
xml
<select id="selectMinisterByCountry" resultType="com.chengzi.beans.Minister">
select mid,mname from minister where countryId = #{ooo}
</select>
<resultMap id="countryMapper2" type="com.chengzi.beans.Country">
<id column="cid" property="cid"/>
<result column="cname" property="cname"/>
<!--关联属性的映射关系-->
<collection property="ministers" ofType="com.chengzi.beans.Minister"
select="selectMinisterByCountry"
column="cid"/>
</resultMap>
<select id="selectCountryById2" resultMap="countryMapper2">
SELECT cid,cname FROM country WHERE cid = #{###}
</select>
关联属性<collection/>的数据来自于另一个查询<selectMinisterByCountry/>。
而该查询<selectMinisterByCountry/>的动态参数countryId=#{ooo}的值来自于查询
<selectCountryById/>的查询结果字段cid。
11.3 多对一关联查询
这里的多对一关联查询是指，在查询多方对象的时候，同时将其所关联的一方对象也查询出来。由于在查询多方对象时也是一个一个查询，所以多对一关联查询，其实就是一对一关联查询。
即一对一关联查询的实现方式与多对一的实现方式是相同的。下面以部长Minister与国家Country间的多对一关系进行演示。
1、实体类
public class Country {
private Integer cid;
private String cname;
//private Set<Minister> ministers;
//省略toString,getter,setter
}
public class Minister {
private Integer mid;
private String name;
private Country country;
public Minister() {}
//省略toString,getter,setter
}
2、xml
<resultMap id="ministerMapper" type="com.chengzi.beans.Minister">
<id column="mid" property="mid"/>
<result column="mname" property="mname"/>
<association property="country" javaType="com.chengzi.beans.Country">
<id column="cid" property="cid"/>
<result column="cname" property="cname"/>
</association>
</resultMap>
<select id="selectMinisterById" resultMap="ministerMapper">
select
cid,cname,mid,mname
from
country,minister
where
mid = #{###} and cid = countryId
</select>
3、定义接口
Minister selectMinisterById(int mid);
4、定义测试类
@Test
public void testSelectMinisterById(){
Minister minister = iSomeDao.selectMinisterById(1);
System.out.println(minister);
}
注意:
在映射文件中使用<association/>标签体现出两个实体对象间的关联关系。
1、property：指定关联属性，即Minister类中的country属性
2、javaType：关联属性的类型
5、结果
[log4j-demo] 2019-09-15 12:03:14,413 [DEBUG] com.chengzi.dao.ISomeDao.selectMinisterById:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ==> Preparing: SELECT cid,cname,mid,mname FROM country,minister WHERE mid = ? and cid = countryId
[log4j-demo] 2019-09-15 12:03:14,441 [DEBUG] com.chengzi.dao.ISomeDao.selectMinisterById:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ==> Parameters: 1(Integer)
[log4j-demo] 2019-09-15 12:03:14,465 [DEBUG] com.chengzi.dao.ISomeDao.selectMinisterById:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <== Total: 1
Minister{mid=1, mname='aaa', country=Country{cid=1, cname='USA'}}
Process finished with exit code 0
多表单独查询方式：
<select id="selectCountryById3" resultType="com.chengzi.beans.Country">
select * from country where cid = #{ooo}
</select>
<resultMap id="ministerMapper2" type="com.chengzi.beans.Minister">
<id column="mid" property="mid"/>
<result column="mname" property="mname"/>
<association property="country" javaType="com.chengzi.beans.Country"
select="selectCountryById3"
column="countryId"/>
</resultMap>
<select id="selectMinisterById2" resultMap="ministerMapper2">
SELECT mid,mname,countryId from minister where mid = #{**}
</select>
11.4 自关联查询
所谓自关联是指，自己即充当一方，又充当多方，是1:n或n:1的变型。例如，对于新闻栏目NewsColumn，可以充当一方，即父栏目，也可以充当多方，即子栏目。而反映到DB表中，只有一张表，这张表中具有一个外键，用于表示该栏目的父栏目。一级栏目没有父栏目，所以可以将其外键值设为0，而子栏目则具有外键值。
为了便于理解，将自关联分为两种情况来。一种是当作1:n，即当前类作为一方，其包含多方的集合域属性。一种是当作n:1，即当前类作为多方，其包含一方的域属性。
以新闻栏目为例。由于Column是DBMS中的关键字，为了避免误解，将新闻栏目实体类定义为NewsLabel。
11.4 .1 以一对多方式处理
以一对多方式处理，即一方可以看到多方。该处理方式的应用场景比较多，例如在页面上点击父栏目，显示出其子栏目。再如，将鼠标定位在窗口中的某菜单项上会显示其所有子菜单项等。
根据查询需求的不同，又可以分为两种情况：一种是查询出指定栏目的所有子孙栏目，一种是查询出指定栏目及其所有子孙栏目。
A、查询指定栏目的所有子孙栏目
根据指定的id，仅查询出其所有子栏目。当然，包括其所有辈份的孙子栏目。即，给出的查询id实际为父栏目id。
1、实体类
package com.chengzi.beans;
import java.util.Set;
public class NewLabel {
private Integer id;
private String name;
//关联属性
private Set<NewLabel> children;
public NewLabel() {
}
public NewLabel(Integer id, String name, Set<NewLabel> children) {
this.id = id;
this.name = name;
this.children = children;
}
//省略getter/setter和string
}
2、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.chengzi.dao.INewsLabelDao">
<resultMap id="newsLabelMapper" type="com.chengzi.beans.NewLabel">
<id column="id" property="id"/>
<result column="name" property="name"/>
<collection property="children" ofType="com.chengzi.beans.NewLabel"
select="selectChildrenByParentId"
column="id"/>
</resultMap>
<select id="selectChildrenByParentId" resultMap="newsLabelMapper">
select id,name from newslabel where pid = #{xx}
</select>
</mapper>
这里通过select语句的递归调用实现查询所有下级栏目的功能。查询结果的集合数据< collection/>来自于递归调用的selectChildrenByParentId查询。与第一次进行该查询不同的是，第一次的pid动态参数值来自于调用方法传递来的实参，而< collection/>中查询语句的pid动态参数值来自于上一次的查询结果的id值
3、dao接口
public interface INewsLabelDao {
List<NewLabel> selectChildrenByParentId(int pid);
}
4、测试类
public class MyTest4 {
private INewsLabelDao iNewsLabelDao;
private SqlSession sqlSession;
@Before
public void before() throws IOException {
sqlSession = MybatisUtils.getSqlSession();
iNewsLabelDao = sqlSession.getMapper(INewsLabelDao.class);
}
@After
public void after() {
sqlSession.close();
}
@Test
public void testSelectChildrenByParentId() {
List<NewLabel> newLabels = iNewsLabelDao.selectChildrenByParentId(2);
newLabels.forEach(x -> System.out.println(x));
}
}
log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
[log4j-demo] 2019-09-15 15:06:34,373 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ==> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:06:34,397 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ==> Parameters: 2(Integer)
[log4j-demo] 2019-09-15 15:06:34,415 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ====> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:06:34,417 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ====> Parameters: 3(Integer)
[log4j-demo] 2019-09-15 15:06:34,419 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:06:34,419 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Parameters: 5(Integer)
[log4j-demo] 2019-09-15 15:06:34,420 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <====== Total: 0
[log4j-demo] 2019-09-15 15:06:34,420 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:06:34,421 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Parameters: 6(Integer)
[log4j-demo] 2019-09-15 15:06:34,422 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <====== Total: 0
[log4j-demo] 2019-09-15 15:06:34,422 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <==== Total: 2
[log4j-demo] 2019-09-15 15:06:34,423 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ====> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:06:34,423 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ====> Parameters: 4(Integer)
[log4j-demo] 2019-09-15 15:06:34,424 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:06:34,425 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Parameters: 7(Integer)
[log4j-demo] 2019-09-15 15:06:34,425 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <====== Total: 0
[log4j-demo] 2019-09-15 15:06:34,427 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:06:34,427 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Parameters: 8(Integer)
[log4j-demo] 2019-09-15 15:06:34,428 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <====== Total: 0
[log4j-demo] 2019-09-15 15:06:34,428 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:06:34,429 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Parameters: 9(Integer)
[log4j-demo] 2019-09-15 15:06:34,429 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <====== Total: 0
[log4j-demo] 2019-09-15 15:06:34,429 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <==== Total: 3
[log4j-demo] 2019-09-15 15:06:34,430 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <== Total: 2
NewLabel{id=3, name='NBA', children=[NewLabel{id=5, name='火箭', children=[]}, NewLabel{id=6, name='湖人', children=[]}]}
NewLabel{id=4, name='CBA', children=[NewLabel{id=7, name='北京金隅', children=[]}, NewLabel{id=8, name='浙江广厦', children=[]}, NewLabel{id=9, name='青岛双星', children=[]}]}
Process finished with exit code 0
B、查询指定栏目及其所有子孙栏目
这里的查询结果，即要包含指定id的当前栏目，还包含其所有辈份的孙子栏目。即给出的id实际为当前要查询的栏目的id。
1、xml
<select id="selectChildrenByParentId" resultMap="newsLabelMapper">
select id,name from newslabel where pid = #{xx}
</select>
<resultMap id="newsLabelMapper2" type="com.chengzi.beans.NewLabel">
<id column="id" property="id"/>
<result column="name" property="name"/>
<collection property="children" ofType="com.chengzi.beans.NewLabel"
select="selectChildrenByParentId"
column="id"/>
</resultMap>
<select id="selectNewLabelsById" resultMap="newsLabelMapper2">
select id,name from newslabel where id = #{xx}
</select>
2、dao接口
List<NewLabel> selectNewLabelsById(int i);
3、测试类
@Test
public void testSelectNewLabelsById() {
List<NewLabel> newLabels = iNewsLabelDao.selectNewLabelsById(2);
newLabels.forEach(x -> System.out.println(x));
}
4、结果
log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
[log4j-demo] 2019-09-15 15:21:09,995 [DEBUG] com.chengzi.dao.INewsLabelDao.selectNewLabelsById:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ==> Preparing: select id,name from newslabel where id = ?
[log4j-demo] 2019-09-15 15:21:10,022 [DEBUG] com.chengzi.dao.INewsLabelDao.selectNewLabelsById:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ==> Parameters: 2(Integer)
[log4j-demo] 2019-09-15 15:21:10,037 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ====> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:21:10,037 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ====> Parameters: 2(Integer)
[log4j-demo] 2019-09-15 15:21:10,038 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:21:10,039 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Parameters: 3(Integer)
[log4j-demo] 2019-09-15 15:21:10,039 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ========> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:21:10,040 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ========> Parameters: 5(Integer)
[log4j-demo] 2019-09-15 15:21:10,040 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <======== Total: 0
[log4j-demo] 2019-09-15 15:21:10,042 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ========> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:21:10,042 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ========> Parameters: 6(Integer)
[log4j-demo] 2019-09-15 15:21:10,043 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <======== Total: 0
[log4j-demo] 2019-09-15 15:21:10,043 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <====== Total: 2
[log4j-demo] 2019-09-15 15:21:10,043 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:21:10,043 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Parameters: 4(Integer)
[log4j-demo] 2019-09-15 15:21:10,044 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ========> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:21:10,045 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ========> Parameters: 7(Integer)
[log4j-demo] 2019-09-15 15:21:10,046 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <======== Total: 0
[log4j-demo] 2019-09-15 15:21:10,046 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ========> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:21:10,047 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ========> Parameters: 8(Integer)
[log4j-demo] 2019-09-15 15:21:10,048 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <======== Total: 0
[log4j-demo] 2019-09-15 15:21:10,049 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ========> Preparing: select id,name from newslabel where pid = ?
[log4j-demo] 2019-09-15 15:21:10,049 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ========> Parameters: 9(Integer)
[log4j-demo] 2019-09-15 15:21:10,050 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <======== Total: 0
[log4j-demo] 2019-09-15 15:21:10,050 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <====== Total: 3
[log4j-demo] 2019-09-15 15:21:10,051 [DEBUG] com.chengzi.dao.INewsLabelDao.selectChildrenByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <==== Total: 2
[log4j-demo] 2019-09-15 15:21:10,051 [DEBUG] com.chengzi.dao.INewsLabelDao.selectNewLabelsById:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <== Total: 1
NewLabel{id=2, name='体育新闻', children=[NewLabel{id=3, name='NBA', children=[NewLabel{id=5, name='火箭', children=[]}, NewLabel{id=6, name='湖人', children=[]}]}, NewLabel{id=4, name='CBA', children=[NewLabel{id=9, name='青岛双星', children=[]}, NewLabel{id=7, name='北京金隅', children=[]}, NewLabel{id=8, name='浙江广厦', children=[]}]}]}
Process finished with exit code 0
11.4.2 以多对一方式处理
以多对一方式处理，即多方可以看到一方。该处理方式的应用场景，例如在网页上显示当前页面的站内位置。
1、定义实体类
public class NewLabel {
private Integer id;
private String name;
private NewLabel parent;
public NewLabel() {
}
public NewLabel(Integer id, String name, NewLabel parent) {
this.id = id;
this.name = name;
this.parent = parent;
}
//省略setter/getter/tostring
@Override
public String toString() {
return "NewLabel{" +
"id=" + id +
", name='" + name + '\'' +
", parent=" + parent +
'}';
}
}
2、xml
<resultMap id="newsLabelMapper3" type="com.chengzi.beans.NewLabel">
<id column="id" property="id"/>
<result column="name" property="name"/>
<association property="parent" javaType="com.chengzi.beans.NewLabel"
select="selectParentByParentId"
column="pid"/>
</resultMap>
<select id="selectParentByParentId" resultMap="newsLabelMapper3">
select id,name,pid from newslabel where id = #{aa}
</select>
3、dao接口
NewLabel selectParentByParentId(int pid);
4、测试类
@Test
public void selectParentByParentId() {
NewLabel parent = iNewsLabelDao.selectParentByParentId(3);
System.out.println(parent);
}
5、结果
log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
[log4j-demo] 2019-09-15 15:51:52,380 [DEBUG] com.chengzi.dao.INewsLabelDao.selectParentByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ==> Preparing: select id,name,pid from newslabel where id = ?
[log4j-demo] 2019-09-15 15:51:52,405 [DEBUG] com.chengzi.dao.INewsLabelDao.selectParentByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ==> Parameters: 3(Integer)
[log4j-demo] 2019-09-15 15:51:52,438 [DEBUG] com.chengzi.dao.INewsLabelDao.selectParentByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ====> Preparing: select id,name,pid from newslabel where id = ?
[log4j-demo] 2019-09-15 15:51:52,438 [DEBUG] com.chengzi.dao.INewsLabelDao.selectParentByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ====> Parameters: 2(Integer)
[log4j-demo] 2019-09-15 15:51:52,439 [DEBUG] com.chengzi.dao.INewsLabelDao.selectParentByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Preparing: select id,name,pid from newslabel where id = ?
[log4j-demo] 2019-09-15 15:51:52,440 [DEBUG] com.chengzi.dao.INewsLabelDao.selectParentByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ======> Parameters: 0(Integer)
[log4j-demo] 2019-09-15 15:51:52,441 [DEBUG] com.chengzi.dao.INewsLabelDao.selectParentByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <====== Total: 0
[log4j-demo] 2019-09-15 15:51:52,441 [DEBUG] com.chengzi.dao.INewsLabelDao.selectParentByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <==== Total: 1
[log4j-demo] 2019-09-15 15:51:52,441 [DEBUG] com.chengzi.dao.INewsLabelDao.selectParentByParentId:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <== Total: 1
NewLabel{id=3, name='NBA', parent=NewLabel{id=2, name='体育新闻', parent=null}}
Process finished with exit code 0
11.5 多对多关联查询
一个学生可以选多门课程，而一门课程可以由多个学生选。这就是典型的多对多关系关系。所以，所谓多对多关系，其实是由两个互反的一对多关系组成。一般情况下，多对多关系都会通过一个中间表来建立，例如选课表。
1、实体类
public class Student {
private Integer sid;
private String sname;
private Set<Course> courses;
public Student() {
}
public Student(Integer sid, String sname, Set<Course> courses) {
this.sid = sid;
this.sname = sname;
this.courses = courses;
}
//省略getter/setter方法
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", sname='" + sname + '\'' +
", courses=" + courses +
'}';
}
}
public class Course {
private Integer cid;
private String cname;
private Set<Student> students;
public Course () {}
public Course(Integer cid, String cname, Set<Student> students) {
this.cid = cid;
this.cname = cname;
this.students = students;
}
//省略getter/setter方法
@Override
public String toString() {
return "Course{" +
"cid=" + cid +
", cname='" + cname + '\'' +
", students=" + students +
'}';
}
}
2、dao接口
public interface IStudentCourseDao {
Student selectStudentById(int id);
}
3、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.chengzi.dao.IStudentCourseDao">
<resultMap id="studentMapper" type="com.chengzi.beans.Student">
<id column="sid" property="sid"/>
<result column="sname" property="sname"/>
<collection property="courses" ofType="com.chengzi.beans.Course">
<id column="cid" property="cid"/>
<result column="cname" property="cname"/>
</collection>
</resultMap>
<select id="selectStudentById" resultMap="studentMapper">
select s.sid,s.sname,c.cid,c.cname
from t_student s ,t_course c, t_s_c sc
where s.sid = sc.sid and c.cid = sc.cid and s.sid = #{oo}
</select>
</mapper>
5、测试类
public class MyTest5 {
private IStudentCourseDao iStudentCourseDao;
private SqlSession sqlSession;
@Before
public void before() throws IOException {
sqlSession = MybatisUtils.getSqlSession();
iStudentCourseDao = sqlSession.getMapper(IStudentCourseDao.class);
}
@After
public void after() {
sqlSession.close();
}
@Test
public void test(){
Student student = iStudentCourseDao.selectStudentById(1);
System.out.println(student);
}
}
6、结果
log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
[log4j-demo] 2019-09-15 16:50:14,406 [DEBUG] com.chengzi.dao.IStudentCourseDao.selectStudentById:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ==> Preparing: select s.sid,s.sname,c.cid,c.cname from t_student s ,t_course c, t_s_c sc where s.sid = sc.sid and c.cid = sc.cid and s.sid = ?
[log4j-demo] 2019-09-15 16:50:14,440 [DEBUG] com.chengzi.dao.IStudentCourseDao.selectStudentById:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - ==> Parameters: 1(Integer)
[log4j-demo] 2019-09-15 16:50:14,464 [DEBUG] com.chengzi.dao.IStudentCourseDao.selectStudentById:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:142) - <== Total: 2
Student{sid=1, sname='张三', courses=[Course{cid=2, cname='JavaEE', students=null}, Course{cid=1, cname='JavaSE', students=null}]}
Process finished with exit code 0
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
