一、实例需求
什么是自关联,即自己与自己具有一对多,多对一的关系,本篇我们主要以新闻模板实例来进行分析;需求就是新闻分为体育新闻和娱乐新闻,体育新闻下面又分为NBA、CBA,NBA下面又有子新闻,但是它们都属于新闻存贮于新闻表中,新闻表中每一个新闻都有一个父级id,父级新闻与子新闻之间是一对多的关系,子级新闻与父级新闻是多对一的关系。
二、数据库表news建立
2.1、创建表
DROP TABLE IF EXISTS `news`;
CREATE TABLE `news` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`name` varchar(15) COLLATE utf8_bin NOT NULL,
`pid` int(2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
2.2、在数据库表中插入初始化数据
insert into `news`(`id`,`name`,`pid`) values
(1,'体育新闻',0),
(2,'娱乐新闻',0),
(3,'NBA',1),
(4,'CBA',1),
(5,'赵丽颖嫁人',2),
(6,'范冰冰罚款',2),
(7,'乔丹',3),
(8,'贝克汉姆',3),
(9,'姚明',4),
(10,'毕福剑',4),
(11,'李嵩',4);
2.3、数据结构分析
一级新闻:体育新闻、娱乐新闻
二级新闻:NBA、CBA、赵丽颖嫁人、范冰冰罚款
三级新闻:乔丹、贝克汉姆、姚明、毕福剑、李嵩
三、一对多自关联
3.1 、News实体类定义
private int id;
private String name;
private int pid;
private List<News> childrenNews;
@Override
public String toString() {
return "News{" +
"id=" + id +
", name='" + name + '\'' +
", pid=" + pid +
", childrenNews=" + childrenNews +
'}';
}
public List<News> getChildrenNews() {
return childrenNews;
}
public void setChildrenNews(List<News> childrenNews) {
this.childrenNews = childrenNews;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getPid() {
return pid;
}
public void setPid(int pid) {
this.pid = pid;
}
3.2、mapper接口中的方法(查询子孙news但是不包括指定pid的news)
List<News> selectAllChildrenByPid(int pid);
3.3、mapper.xml中的配置
<!--//一对多:查找子孙news不包含他本身-->
<select id="selectAllChildrenByPid" resultMap="childrens">
select * from news where pid = #{pid};
</select>
<resultMap id="childrens" type="com.achuan.demo.entity.linklearning.News">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
<result column="pid" jdbcType="INTEGER" property="pid"/>
<!--注意:必须要是ofType,不能是javaType-->
<collection property="childrenNews" ofType="com.achuan.demo.entity.linklearning.News"
select="selectAllChildrenByPid" column="id"/>
</resultMap>
逻辑顺序:1,selectAllChildrenByPid方法调用该statement执行指定的sql语句获取该pid对应的news的所有属性;2,通过column将上一步中获取的news对象中的id,并以该id作为下一次selectAllChildrenByPid语句中的pid索引进行statement查询3、直到查询到最后以及子节点即中止selectAllChildrenByPid方法,这就是一个典型的递归实例,4、最后查询得到pid对应的所有的子孙news的实例对象,但不包含pid对应的父级news对象。
3.4、需求一(查询子孙news但是不包括指定pid的news)测试结果
@Test
public void test01(){
List<News> newsList = newsMapper.selectAllChildrenByPid(1);
for (News news : newsList) {
System.out.println(news);
}
}
16:02:18,431 DEBUG - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
16:02:18,452 DEBUG - PooledDataSource forcefully closed/removed all connections.
16:02:18,452 DEBUG - PooledDataSource forcefully closed/removed all connections.
16:02:18,452 DEBUG - PooledDataSource forcefully closed/removed all connections.
16:02:18,452 DEBUG - PooledDataSource forcefully closed/removed all connections.
16:02:18,601 DEBUG - Opening JDBC Connection
16:02:18,791 DEBUG - Created connection 1861781750.
16:02:18,793 DEBUG - ==> Preparing: select * from news where pid = ?;
16:02:18,829 DEBUG - ==> Parameters: 1(Integer)
16:02:18,859 DEBUG - ====> Preparing: select * from news where pid = ?;
16:02:18,860 DEBUG - ====> Parameters: 3(Integer)
16:02:18,861 DEBUG - ======> Preparing: select * from news where pid = ?;
16:02:18,862 DEBUG - ======> Parameters: 7(Integer)
16:02:18,863 DEBUG - <====== Total: 0
16:02:18,864 DEBUG - ======> Preparing: select * from news where pid = ?;
16:02:18,864 DEBUG - ======> Parameters: 8(Integer)
16:02:18,866 DEBUG - <====== Total: 0
16:02:18,866 DEBUG - <==== Total: 2
16:02:18,866 DEBUG - ====> Preparing: select * from news where pid = ?;
16:02:18,867 DEBUG - ====> Parameters: 4(Integer)
16:02:18,869 DEBUG - ======> Preparing: select * from news where pid = ?;
16:02:18,869 DEBUG - ======> Parameters: 9(Integer)
16:02:18,871 DEBUG - <====== Total: 0
16:02:18,872 DEBUG - ======> Preparing: select * from news where pid = ?;
16:02:18,872 DEBUG - ======> Parameters: 10(Integer)
16:02:18,879 DEBUG - <====== Total: 0
16:02:18,879 DEBUG - ======> Preparing: select * from news where pid = ?;
16:02:18,880 DEBUG - ======> Parameters: 11(Integer)
16:02:18,881 DEBUG - <====== Total: 0
16:02:18,882 DEBUG - <==== Total: 3
16:02:18,882 DEBUG - <== Total: 2
News{id=3, name='NBA', pid=1, childrenNews=[News{id=7, name='乔丹', pid=3, childrenNews=[]}, News{id=8, name='贝克汉姆', pid=3, childrenNews=[]}]}
News{id=4, name='CBA', pid=1, childrenNews=[News{id=9, name='姚明', pid=4, childrenNews=[]}, News{id=10, name='毕福剑', pid=4, childrenNews=[]}, News{id=11, name='李嵩', pid=4, childrenNews=[]}]}
Process finished with exit code 0
观察debug日志结果我们可以看出查询结果并没有包含pid它本省的数据详情
3.5、查询pid所有子孙包括它自己
//查询指定news的所有子孙news包括它自己
List<News> selectAllChildrenByPid(int pid);
News selectNewsById(int id);
3.6、mapper.xml文件配置
<!--//一对多:查找子孙news不包含他本身-->
<select id="selectAllChildrenByPid" resultMap="childrens">
select * from news where pid = #{pid};
</select>
<resultMap id="childrens" type="com.achuan.demo.entity.linklearning.News">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
<result column="pid" jdbcType="INTEGER" property="pid"/>
<!--注意:必须要是ofType,不能是javaType-->
<collection property="childrenNews" ofType="com.achuan.demo.entity.linklearning.News"
select="selectAllChildrenByPid" column="id"/>
</resultMap>
<!--//一对多:查找子孙news包含他本身-->
<select id="selectNewsById" resultMap="childrens">
select * from news where id = #{id};
</select>
如上图,需求二的逻辑执行顺序是:1、先执行selectNewsById方法获取id对应news对象的所有属性,2、再将获取news对象中的id作为selectAllChildrenByPid中的pid执行需求一中的递归逻辑,最后获得需求二中要求的结果。
3.7、需求二执行结果
16:14:10,659 DEBUG - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
16:14:10,690 DEBUG - PooledDataSource forcefully closed/removed all connections.
16:14:10,690 DEBUG - PooledDataSource forcefully closed/removed all connections.
16:14:10,690 DEBUG - PooledDataSource forcefully closed/removed all connections.
16:14:10,690 DEBUG - PooledDataSource forcefully closed/removed all connections.
16:14:10,874 DEBUG - Opening JDBC Connection
16:14:11,102 DEBUG - Created connection 1861781750.
16:14:11,104 DEBUG - ==> Preparing: select * from news where id = ?;
16:14:11,136 DEBUG - ==> Parameters: 1(Integer)
16:14:11,177 DEBUG - ====> Preparing: select * from news where pid = ?;
16:14:11,177 DEBUG - ====> Parameters: 1(Integer)
16:14:11,179 DEBUG - ======> Preparing: select * from news where pid = ?;
16:14:11,180 DEBUG - ======> Parameters: 3(Integer)
16:14:11,182 DEBUG - ========> Preparing: select * from news where pid = ?;
16:14:11,183 DEBUG - ========> Parameters: 7(Integer)
16:14:11,185 DEBUG - <======== Total: 0
16:14:11,186 DEBUG - ========> Preparing: select * from news where pid = ?;
16:14:11,186 DEBUG - ========> Parameters: 8(Integer)
16:14:11,187 DEBUG - <======== Total: 0
16:14:11,188 DEBUG - <====== Total: 2
16:14:11,188 DEBUG - ======> Preparing: select * from news where pid = ?;
16:14:11,188 DEBUG - ======> Parameters: 4(Integer)
16:14:11,191 DEBUG - ========> Preparing: select * from news where pid = ?;
16:14:11,191 DEBUG - ========> Parameters: 9(Integer)
16:14:11,193 DEBUG - <======== Total: 0
16:14:11,194 DEBUG - ========> Preparing: select * from news where pid = ?;
16:14:11,195 DEBUG - ========> Parameters: 10(Integer)
16:14:11,197 DEBUG - <======== Total: 0
16:14:11,198 DEBUG - ========> Preparing: select * from news where pid = ?;
16:14:11,198 DEBUG - ========> Parameters: 11(Integer)
16:14:11,200 DEBUG - <======== Total: 0
16:14:11,200 DEBUG - <====== Total: 3
16:14:11,202 DEBUG - <==== Total: 2
16:14:11,202 DEBUG - <== Total: 1
News{id=1, name='体育新闻', pid=0, childrenNews=[News{id=3, name='NBA', pid=1, childrenNews=[News{id=7, name='乔丹', pid=3, childrenNews=[]}, News{id=8, name='贝克汉姆', pid=3, childrenNews=[]}]}, News{id=4, name='CBA', pid=1, childrenNews=[News{id=9, name='姚明', pid=4, childrenNews=[]}, News{id=10, name='毕福剑', pid=4, childrenNews=[]}, News{id=11, name='李嵩', pid=4, childrenNews=[]}]}]}
Process finished with exit code 0
四、多对一自关联(通过news的id查询它的所有父级元素属性包括它自己)
4.1、news对应的实体类(NewLabel)
private int id;
private String name;
private int pid;
private NewLabel newLabel;
@Override
public String toString() {
return "NewLabel{" +
"id=" + id +
", name='" + name + '\'' +
", pid=" + pid +
", newLabel=" + newLabel +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getPid() {
return pid;
}
public void setPid(int pid) {
this.pid = pid;
}
public NewLabel getNewLabel() {
return newLabel;
}
public void setNewLabel(NewLabel newLabel) {
this.newLabel = newLabel;
}
4.2、mapper接口方法
NewLabel selectParentNewsById(int id);
4.3、mapper.xml配置
<!--多对一:通过子级元素查找父亲news内容-->
<select id="selectParentNewsById" resultMap="newLabel">
select * from news where id = #{id};
</select>
<resultMap id="newLabel" type="com.achuan.demo.entity.linklearning.NewLabel">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
<result column="pid" jdbcType="INTEGER" property="pid"/>
<association property="newLabel" javaType="com.achuan.demo.entity.linklearning.NewLabel"
select="selectParentNewsById" column="pid"/>
</resultMap>
执行逻辑:1)selectParentNewsById方法获取指定id的news属性;2)将news属性中的pid通过column又传递到selectParentNewsById方法中作为新的id进行查找;3)这个递归逻辑会一直往上一级查询直到查到最顶级的父节点
4.4、测试结果
@Test
public void test03(){
NewLabel newLabel = newsMapper.selectParentNewsById(8);
System.out.println(newLabel);
}
16:26:08,471 DEBUG - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
16:26:08,494 DEBUG - PooledDataSource forcefully closed/removed all connections.
16:26:08,494 DEBUG - PooledDataSource forcefully closed/removed all connections.
16:26:08,494 DEBUG - PooledDataSource forcefully closed/removed all connections.
16:26:08,494 DEBUG - PooledDataSource forcefully closed/removed all connections.
16:26:08,658 DEBUG - Opening JDBC Connection
16:26:08,865 DEBUG - Created connection 1861781750.
16:26:08,867 DEBUG - ==> Preparing: select * from news where id = ?;
16:26:08,902 DEBUG - ==> Parameters: 8(Integer)
16:26:08,931 DEBUG - ====> Preparing: select * from news where id = ?;
16:26:08,931 DEBUG - ====> Parameters: 3(Integer)
16:26:08,933 DEBUG - ======> Preparing: select * from news where id = ?;
16:26:08,933 DEBUG - ======> Parameters: 1(Integer)
16:26:08,935 DEBUG - ========> Preparing: select * from news where id = ?;
16:26:08,936 DEBUG - ========> Parameters: 0(Integer)
16:26:08,937 DEBUG - <======== Total: 0
16:26:08,938 DEBUG - <====== Total: 1
16:26:08,938 DEBUG - <==== Total: 1
16:26:08,938 DEBUG - <== Total: 1
NewLabel{id=8, name='贝克汉姆', pid=3, newLabel=NewLabel{id=3, name='NBA', pid=1, newLabel=NewLabel{id=1, name='体育新闻', pid=0, newLabel=null}}}