MyBatis 关联查询自关联（一对多、多对一）

所谓自关联是指，自己即充当一方，又充当多方。其实就是普通1:n 和 n:1的变形

1、一对多

1）表结构

-- 新闻栏目 CREATE TABLE `news` ( `id` int(5) NOT NULL AUTO_INCREMENT, `name`
varchar(10) NOT NULL, `pid` int(5) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB
AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2）实体类

// 新闻栏目：当前的新闻栏目被看作是一方，即父栏目 public class News { private Integer id; private
String name; private Set<News> childen; //省略get set属性方法 @Override public String
toString() { return "News [id=" + id + ", name=" + name + ", childen=" +
childen + "]"; } }
3）Dao接口

public interface INewsDao { List<News> selectById(int id);//查询指定id栏目下面的所有子孙菜单
News selectById2(int id);//查询指定id栏目及下面的所有子孙菜单 }
4）mapper.xml文件

 <resultMap type="News" id="newsMapper"> <id
column="id" property="id"/> <result column="name" property="name"/> <collection
property="childen" ofType="News" select="selectById" column="id"/> </resultMap>
<select id="selectById" resultMap="newsMapper"> select id,name from news where
pid = #{xxx} </select>  <select id="selectChilden"
resultMap="newsMapper"> select id,name from news where pid=#{ooo} </select>
<resultMap type="News" id="newsMapper2"> <id column="id" property="id"/>
<result column="name" property="name"/> <collection property="childen"
ofType="News" select="selectChilden" column="id" /> </resultMap> <select
id="selectById2" resultMap="newsMapper2"> select id,name from news where
id=#{xxx} </select>
标签属性可以参考：

MyBatis 关联查询（一对多、多对一）
<http://blog.csdn.net/abc5232033/article/details/79054247>

5）测试输出：

一、 [DEBUG] ==> Preparing: select id,name from news where pid = ? [DEBUG] ==>
Parameters: 1(Integer) [TRACE] <== Columns: id, name [TRACE] <== Row: 3, 内地娱乐
[DEBUG] ====> Preparing: select id,name from news where pid = ? [DEBUG] ====>
Parameters: 3(Integer) [DEBUG] <==== Total: 0 [TRACE] <== Row: 4, 港台娱乐 [DEBUG]
====> Preparing: select id,name from news where pid = ? [DEBUG] ====>
Parameters: 4(Integer) [DEBUG] <==== Total: 0 [DEBUG] <== Total: 2 News [id=3,
name=内地娱乐, childen=[]] News [id=4, name=港台娱乐, childen=[]]二、 [DEBUG] ==>
Preparing: select id,name from news where id=? [DEBUG] ==> Parameters:
2(Integer) [TRACE] <== Columns: id, name [TRACE] <== Row: 2, 体育新闻 [DEBUG] ====>
Preparing: select id,name from news where pid=? [DEBUG] ====> Parameters:
2(Integer) [TRACE] <==== Columns: id, name [TRACE] <==== Row: 5, 篮球 [DEBUG]
======> Preparing: select id,name from news where pid = ? [DEBUG] ======>
Parameters: 5(Integer) [TRACE] <====== Columns: id, name [TRACE] <====== Row:
7, NBA [DEBUG] ========> Preparing: select id,name from news where pid = ?
[DEBUG] ========> Parameters: 7(Integer) [DEBUG] <======== Total: 0 [TRACE]
<====== Row: 8, CBA [DEBUG] ========> Preparing: select id,name from news where
pid = ? [DEBUG] ========> Parameters: 8(Integer) [DEBUG] <======== Total: 0
[DEBUG] <====== Total: 2 [TRACE] <==== Row: 6, 足球 [DEBUG] ======> Preparing:
select id,name from news where pid = ? [DEBUG] ======> Parameters: 6(Integer)
[TRACE] <====== Columns: id, name [TRACE] <====== Row: 9, 意甲 [DEBUG] ========>
Preparing: select id,name from news where pid = ? [DEBUG] ========> Parameters:
9(Integer) [TRACE] <======== Columns: id, name [TRACE] <======== Row: 12, AC米兰
[DEBUG] ==========> Preparing: select id,name from news where pid = ? [DEBUG]
==========> Parameters: 12(Integer) [TRACE] <========== Columns: id, name
[TRACE] <========== Row: 14, 皮尔洛 [DEBUG] ============> Preparing: select
id,name from news where pid = ? [DEBUG] ============> Parameters: 14(Integer)
[DEBUG] <============ Total: 0 [DEBUG] <========== Total: 1 [TRACE] <========
Row: 13, 国际米兰 [DEBUG] ==========> Preparing: select id,name from news where pid
= ? [DEBUG] ==========> Parameters: 13(Integer) [DEBUG] <========== Total: 0
[DEBUG] <======== Total: 2 [TRACE] <====== Row: 10, 西甲 [DEBUG] ========>
Preparing: select id,name from news where pid = ? [DEBUG] ========> Parameters:
10(Integer) [DEBUG] <======== Total: 0 [TRACE] <====== Row: 11, 中超 [DEBUG]
========> Preparing: select id,name from news where pid = ? [DEBUG] ========>
Parameters: 11(Integer) [DEBUG] <======== Total: 0 [DEBUG] <====== Total: 3
[DEBUG] <==== Total: 2 [DEBUG] <== Total: 1 News [id=2, name=体育新闻,
childen=[News [id=5, name=篮球, childen=[News [id=8, name=CBA, childen=[]], News
[id=7, name=NBA, childen=[]]]], News [id=6, name=足球, childen=[News [id=11,
name=中超, childen=[]], News [id=10, name=西甲, childen=[]], News [id=9, name=意甲,
childen=[News [id=12, name=AC米兰, childen=[News [id=14, name=皮尔洛, childen=[]]]],
News [id=13, name=国际米兰, childen=[]]]]]]]]

2、多对一

1）实体类有变化：

去掉了private Set<News> childen;其实应该留着，删掉只是为了便于理解多对一。

添加了private News parent; 肯定只有一个父类栏目

2个成员变量都存在的话，就一个类实现多对一和一对多

// 新闻栏目：当前的新闻栏目被看作是多方，即子栏目 public class News { private Integer id; private
String name; private News parent; //省略get set属性方法 @Override public String
toString() { return "News [id=" + id + ", name=" + name + ", parent=" + parent
+ "]"; } }
2）Dao接口

public interface INewsDao { News selectById(int id);//查询指定id栏目及其所有父辈菜单 }
3）mapper.xml文件

 <resultMap type="News" id="newsMapper"> <id
column="id" property="id"/> <result column="name" property="name"/>
<association property="parent" javaType="News" select="selectById" column="pid"
/> </resultMap> <select id="selectById" resultMap="newsMapper"> select
id,name,pid from news where id = #{xxx} </select>每一个子栏目，肯定只会存在一个父栏目。所以是一对一的关系，用
<association />

4）测试输出：

测试输出： [DEBUG] ==> Preparing: select id,name,pid from news where id = ? [DEBUG]
==> Parameters: 12(Integer) [TRACE] <== Columns: id, name, pid [TRACE] <== Row:
12, AC米兰, 9 [DEBUG] ====> Preparing: select id,name,pid from news where id = ?
[DEBUG] ====> Parameters: 9(Integer) [TRACE] <==== Columns: id, name, pid
[TRACE] <==== Row: 9, 意甲, 6 [DEBUG] ======> Preparing: select id,name,pid from
news where id = ? [DEBUG] ======> Parameters: 6(Integer) [TRACE] <======
Columns: id, name, pid [TRACE] <====== Row: 6, 足球, 2 [DEBUG] ========>
Preparing: select id,name,pid from news where id = ? [DEBUG] ========>
Parameters: 2(Integer) [TRACE] <======== Columns: id, name, pid [TRACE]
<======== Row: 2, 体育新闻, 0 [DEBUG] ==========> Preparing: select id,name,pid
from news where id = ? [DEBUG] ==========> Parameters: 0(Integer) [DEBUG]
<========== Total: 0 [DEBUG] <======== Total: 1 [DEBUG] <====== Total: 1
[DEBUG] <==== Total: 1 [DEBUG] <== Total: 1 News [id=12, name=AC米兰, parent=News
[id=9, name=意甲, parent=News [id=6, name=足球, parent=News [id=2, name=体育新闻,
parent=null]]]]

热门工具换一换

MyBatis 关联查询 自关联 （一对多、多对一）