Mybatis自关联查询一对多查询的实现示例-eolink官网

Mybatis自关联查询一对多查询的实现示例

注：代码已托管在github上，地址是：https://github.com/Damaer/Mybatis-Learning ，项目是mybatis-13-oneself-one2many，需要自取，需要配置maven环境以及mysql环境(sql语句在resource下的test.sql中)，觉得有用可以点个小星星。

docsify文档地址在：https://damaer.github.io/Mybatis-Learning/#/

所谓自关联查询，是指自己既然充当一方，又充当多方。比如新闻栏目的数据表，自己可以是父栏目，也可以是多方，子栏目。在数据表里面实现就是一张表，有一个外键pid，用来表示该栏目的父栏目，一级栏目没有父栏目的，可以将其外键设置为0。

DB表如下：

查询指定栏目的所有子孙栏目

查询指定目录的所有子孙目录，我们需要使用递归的思想，查出当前栏目之后，需要将当前栏目的id作为下一级栏目的pid。

实体类NewsLabel.java,使用一对多的关系：

import java.util.Set;

public class NewsLabel {

private Integer id;

private String name;

private Setchildren;

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public Set getChildren() {

return children;

}

public void setChildren(Set children) {

this.children = children;

}

@Override

public String toString() {

return "NewsLabel [id=" + id + ", name=" + name + ", children="

+ children + "]";

}

定义sql接口:

public interface INewsLabelDao {

List selectChildByParentId(int pid);

}

mapper.xml文件，在递归里面使用本身sql：

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

ofType="NewsLabel"

select="selectChildByParentId"

column="id"/>

select id,name from newslabel where pid=#{xxx}

测试类MyTest.java:

public class MyTest {

private INewsLabelDao dao;

private SqlSession sqlSession;

@Before

public void Before(){

sqlSession=MyBatisUtils.getSqlSession();

dao=sqlSession.getMapper(INewsLabelDao.class);

}

@Test

public void TestselectMinisterById(){

Listchildren=dao.selectChildByParentId(2);

for(NewsLabel newsLabel:children){

System.out.println(newsLabel);

}

@After

public void after(){

if(sqlSession!=null){

sqlSession.close();

}

结果：

NewsLabel [id=3, name=NBA, children=[NewsLabel [id=5, name=火箭, children=[]], NewsLabel [id=6, name=湖人, children=[]]]]

NewsLabel [id=4, name=CBA, children=[NewsLabel [id=7, name=北京金瓯, children=[]], NewsLabel [id=8, name=浙江广夏, children=[]], NewsLabel [id=9, name=青岛双星, children=[]]]]

这样的写法只能选出子孙栏目，不能将自己的信息输出。

添加一个sql的接口：

List selectSelfAndChildByParentId(int pid);

mapper文件里面实现,在resultMap里面递归调用另一个sql，最外层的sql只执行一次，这样就可以实现查询自身一次，递归查询子孙栏目的功能：

select id,name from newslabel where pid=#{ooo}

ofType="NewsLabel"

select="selectChildByParentId2"

column="id"/>

select id,name from newslabel where id=#{xxx}

单元测试：

@Test

public void TestselectSelfAndChildrenLabelById(){

List children = dao.selectSelfAndChildByParentId(2);

for (NewsLabel newsLabel : children) {

System.out.println(newsLabel);

}

结果：

[service] 2018-07-16 11:17:16,667 - org.apache.ibatis.transaction.jdbc.JdbcTransaction -450 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@5bb21b69]

[service] 2018-07-16 11:17:16,669 - dao.INewsLabelDao.selectSelfAndChildByParentId -452 [main] DEBUG dao.INewsLabelDao.selectSelfAndChildByParentId - ==> Preparing: select id,name from newslabel where id=?

[service] 2018-07-16 11:17:16,704 - dao.INewsLabeltVZfjDao.selectSelfAndChildByParentId -487 [main] DEBUG dao.INewsLabelDao.selectSelfAndChildByParentId - ==> Parameters: 2(Integer)

[service] 2018-07-16 11:17:16,722 - dao.INewsLabelDao.selectChildByParentId2 -505 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ====> Preparing: select id,name from newslabel where pid=?

[service] 2018-07-16 11:17:16,723 - dao.INewsLabelDao.selectChildByParentId2 -506 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ====> Parameters: 2(Integer)

[service] 2018-07-16 11:17:16,726 - dao.INewsLabelDao.selectChildByParentId2 -509 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ======> Preparing: select id,name from newslabel where pid=?

[service] 2018-07-16 11:17:16,726 - dao.INewsLabelDao.selectChildByParentId2 -509 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ======> Parameters: 3(Integer)

[service] 2018-07-16 11:17:16,727 - dao.INewsLabelDao.selectChildByParentId2 -510 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ========> Preparing: select id,name from newslabel where pid=?

[service] 2018-07-16 11:17:16,728 - dao.INewsLabelDao.selectChildByParentId2 -511 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ========> Parameters: 5(Integer)

[service] 2018-07-16 11:17:16,729 - dao.INewsLabelDao.selectChildByParentId2 -512 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - <======== Total: 0

[service] 2018-07-16 11:17:16,732 - dao.INewsLabelDao.selectChildByParentId2 -515 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ========> Preparing: select id,name from newslabel where pid=?

[service] 2018-07-16 11:17:16,732 - dao.INewsLabelDao.selectChildByParentId2 -515 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ========> Parameters: 6(Integer)

[service] 2018-07-16 11:17:16,733 - dao.INewsLabelDao.selectChildByParentId2 -516 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - <======== Total: 0

[service] 2018-07-16 11:17:16,734 - dao.INewsLabelDao.selectChildByParentId2 -517 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - <====== Total: 2

[service] 2018-07-16 11:17:16,734 - dao.INewsLabelDao.selectChildByParentId2 -517 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ======> Preparing: select id,name from newslabel where pid=?

[service] 2018-07-16 11:17:16,734 - dao.INewsLabelDao.selectChildByParentId2 -517 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ======> Parameters: 4(Integer)

[service] 2018-07-16 11:17:16,736 - dao.INewsLabelDao.selectChildByParentId2 -519 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ========> Preparing: select id,name from newslabel where pid=?

[service] 2018-07-16 11:17:16,736 - dao.INewsLabelDao.selectChildByParentId2 -519 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ========> Parameters: 7(Integer)

[service] 2018-07-16 11:17:16,738 - dao.INewsLabelDao.selectChildByParentId2 -521 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - <======== Total: 0

[service] 2018-07-16 11:17:16,738 - dao.INewsLabelDao.selectChildByParentId2 -521 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ========> Preparing: select id,name from newslabel where pid=?

[service] 2018-07-16 11:17:16,739 - dao.INewsLabelDao.selectChildByParentId2 -522 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ========> Parameters: 8(Integer)

[service] 2018-07-16 11:17:16,741 - dao.INewsLabelDao.selectChildByParentId2 -524 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - <======== Total: 0

[service] 2018-07-16 11:17:16,742 - dao.INewsLabelDao.selectChildByParentId2 -525 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ========> Preparing: select id,name from newslabel where pid=?

[service] 2018-07-16 11:17:16,742 - dao.INewsLabelDao.selectChildByParentId2 -525 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - ========> Parameters: 9(Integer)

[service] 2018-07-16 11:17:16,743 - dao.INewsLabelDao.selectChildByParentId2 -526 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - <======== Total: 0

[service] 2018-07-16 11:17:16,744 - dao.INewsLabelDao.selectChildByParentId2 -527 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - <====== Total: 3

[service] 2018-07-16 11:17:16,744 - dao.INewsLabelDao.selectChildByParentId2 -527 [main] DEBUG dao.INewsLabelDao.selectChildByParentId2 - <==== Total: 2

[service] 2018-07-16 11:17:16,745 - dao.INewsLabelDao.selectSelfAndChildByParentId -528 [main] DEBUG dao.INewsLabelDao.selectSelfAndChildByParentId - <== Total: 1

NewsLabel [id=2, name=体育新闻, children=[NewsLabel [id=3, name=NBA, children=[NewsLabel [id=6, name=湖人, children=[]], NewsLabel [id=5, name=火箭, children=[]]]], NewsLabel [id=4, name=CBA, children=[NewsLabel [id=7, name=北京金瓯, children=[]], NewsLabel [id=8, name=浙江广夏, children=[]], NewsLabel [id=9, name=青岛双星, children=[]]]]]]