广场 › 编程语言 › Mybatis自关联查询一对多查询的实现示例

Mike 发表于 2021-8-17 11:51:42

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 Set<NewsLabel>children;
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<NewsLabel> getChildren() {
return children;
}
public void setChildren(Set<NewsLabel> children) {
this.children = children;
}

@Override
public String toString() {
return "NewsLabel [id=" + id + ", name=" + name + ", children="
+ children + "]";
}

}
定义sql接口:
public interface INewsLabelDao {
List<NewsLabel> selectChildByParentId(int pid);
}
mapper.xml文件，在递归里面使用本身sql：
<?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="dao.INewsLabelDao">
<resultMap type="beans.NewsLabel" id="newsLabelMapper">
<id column="id" property="id"/>
<result column="name" property="name"/>
<collection property="children"
ofType="NewsLabel"
select="selectChildByParentId"
column="id"/>
</resultMap>
<select id="selectChildByParentId" resultMap="newsLabelMapper">
select id,name from newslabel where pid=#{xxx}
</select>
</mapper>
测试类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(){
List<NewsLabel>children=dao.selectChildByParentId(2);
for(NewsLabel newsLabel:children){
System.out.println(newsLabel);
}
}
@After
public void after(){
if(sqlSession!=null){
sqlSession.close();
}
}

}
结果：


NewsLabel ], NewsLabel ]]]
NewsLabel ], NewsLabel ], NewsLabel ]]]
这样的写法只能选出子孙栏目，不能将自己的信息输出。
查询指定目录以及指定子孙目录
添加一个sql的接口：
List<NewsLabel> selectSelfAndChildByParentId(int pid);
mapper文件里面实现,在resultMap里面递归调用另一个sql，最外层的sql只执行一次，这样就可以实现查询自身一次，递归查询子孙栏目的功能：
<!-- 筛选出自己以及子孙栏目-->
<select id="selectChildByParentId2" resultMap="newsLabelMapper2">
select id,name from newslabel where pid=#{ooo}
</select>
<resultMap type="beans.NewsLabel" id="newsLabelMapper2">
<id column="id" property="id"/>
<result column="name" property="name"/>
<collection property="children"
ofType="NewsLabel"
select="selectChildByParentId2"
column="id"/>
</resultMap>
<select id="selectSelfAndChildByParentId" resultMap="newsLabelMapper2">
select id,name from newslabel where id=#{xxx}
</select>
单元测试：
@Test
public void TestselectSelfAndChildrenLabelById(){
List<NewsLabel> children = dao.selectSelfAndChildByParentId(2);
for (NewsLabel newsLabel : children) {
System.out.println(newsLabel);
}
}
结果：


2018-07-16 11:17:16,667 - org.apache.ibatis.transaction.jdbc.JdbcTransaction -450 DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction- Setting autocommit to false on JDBC Connection
2018-07-16 11:17:16,669 - dao.INewsLabelDao.selectSelfAndChildByParentId -452 DEBUG dao.INewsLabelDao.selectSelfAndChildByParentId- ==>Preparing: select id,name from newslabel where id=?
2018-07-16 11:17:16,704 - dao.INewsLabelDao.selectSelfAndChildByParentId -487 DEBUG dao.INewsLabelDao.selectSelfAndChildByParentId- ==> Parameters: 2(Integer)
2018-07-16 11:17:16,722 - dao.INewsLabelDao.selectChildByParentId2 -505 DEBUG dao.INewsLabelDao.selectChildByParentId2- ====>Preparing: select id,name from newslabel where pid=?
2018-07-16 11:17:16,723 - dao.INewsLabelDao.selectChildByParentId2 -506 DEBUG dao.INewsLabelDao.selectChildByParentId2- ====> Parameters: 2(Integer)
2018-07-16 11:17:16,726 - dao.INewsLabelDao.selectChildByParentId2 -509 DEBUG dao.INewsLabelDao.selectChildByParentId2- ======>Preparing: select id,name from newslabel where pid=?
2018-07-16 11:17:16,726 - dao.INewsLabelDao.selectChildByParentId2 -509 DEBUG dao.INewsLabelDao.selectChildByParentId2- ======> Parameters: 3(Integer)
2018-07-16 11:17:16,727 - dao.INewsLabelDao.selectChildByParentId2 -510 DEBUG dao.INewsLabelDao.selectChildByParentId2- ========>Preparing: select id,name from newslabel where pid=?
2018-07-16 11:17:16,728 - dao.INewsLabelDao.selectChildByParentId2 -511 DEBUG dao.INewsLabelDao.selectChildByParentId2- ========> Parameters: 5(Integer)
2018-07-16 11:17:16,729 - dao.INewsLabelDao.selectChildByParentId2 -512 DEBUG dao.INewsLabelDao.selectChildByParentId2- <========      Total: 0
2018-07-16 11:17:16,732 - dao.INewsLabelDao.selectChildByParentId2 -515 DEBUG dao.INewsLabelDao.selectChildByParentId2- ========>Preparing: select id,name from newslabel where pid=?
2018-07-16 11:17:16,732 - dao.INewsLabelDao.selectChildByParentId2 -515 DEBUG dao.INewsLabelDao.selectChildByParentId2- ========> Parameters: 6(Integer)
2018-07-16 11:17:16,733 - dao.INewsLabelDao.selectChildByParentId2 -516 DEBUG dao.INewsLabelDao.selectChildByParentId2- <========      Total: 0
2018-07-16 11:17:16,734 - dao.INewsLabelDao.selectChildByParentId2 -517 DEBUG dao.INewsLabelDao.selectChildByParentId2- <======      Total: 2
2018-07-16 11:17:16,734 - dao.INewsLabelDao.selectChildByParentId2 -517 DEBUG dao.INewsLabelDao.selectChildByParentId2- ======>Preparing: select id,name from newslabel where pid=?
2018-07-16 11:17:16,734 - dao.INewsLabelDao.selectChildByParentId2 -517 DEBUG dao.INewsLabelDao.selectChildByParentId2- ======> Parameters: 4(Integer)
2018-07-16 11:17:16,736 - dao.INewsLabelDao.selectChildByParentId2 -519 DEBUG dao.INewsLabelDao.selectChildByParentId2- ========>Preparing: select id,name from newslabel where pid=?
2018-07-16 11:17:16,736 - dao.INewsLabelDao.selectChildByParentId2 -519 DEBUG dao.INewsLabelDao.selectChildByParentId2- ========> Parameters: 7(Integer)
2018-07-16 11:17:16,738 - dao.INewsLabelDao.selectChildByParentId2 -521 DEBUG dao.INewsLabelDao.selectChildByParentId2- <========      Total: 0
2018-07-16 11:17:16,738 - dao.INewsLabelDao.selectChildByParentId2 -521 DEBUG dao.INewsLabelDao.selectChildByParentId2- ========>Preparing: select id,name from newslabel where pid=?
2018-07-16 11:17:16,739 - dao.INewsLabelDao.selectChildByParentId2 -522 DEBUG dao.INewsLabelDao.selectChildByParentId2- ========> Parameters: 8(Integer)
2018-07-16 11:17:16,741 - dao.INewsLabelDao.selectChildByParentId2 -524 DEBUG dao.INewsLabelDao.selectChildByParentId2- <========      Total: 0
2018-07-16 11:17:16,742 - dao.INewsLabelDao.selectChildByParentId2 -525 DEBUG dao.INewsLabelDao.selectChildByParentId2- ========>Preparing: select id,name from newslabel where pid=?
2018-07-16 11:17:16,742 - dao.INewsLabelDao.selectChildByParentId2 -525 DEBUG dao.INewsLabelDao.selectChildByParentId2- ========> Parameters: 9(Integer)
2018-07-16 11:17:16,743 - dao.INewsLabelDao.selectChildByParentId2 -526 DEBUG dao.INewsLabelDao.selectChildByParentId2- <========      Total: 0
2018-07-16 11:17:16,744 - dao.INewsLabelDao.selectChildByParentId2 -527 DEBUG dao.INewsLabelDao.selectChildByParentId2- <======      Total: 3
2018-07-16 11:17:16,744 - dao.INewsLabelDao.selectChildByParentId2 -527 DEBUG dao.INewsLabelDao.selectChildByParentId2- <====      Total: 2
2018-07-16 11:17:16,745 - dao.INewsLabelDao.selectSelfAndChildByParentId -528 DEBUG dao.INewsLabelDao.selectSelfAndChildByParentId- <==      Total: 1
NewsLabel ], NewsLabel ]]], NewsLabel ], NewsLabel ], NewsLabel ]]]]]
到此这篇关于Mybatis自关联查询一对多查询的实现示例的文章就介绍到这了,更多相关Mybatis 一对多查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！
原文链接：https://segmentfault.com/a/1190000039134263

文档来源：http://www.zzvips.com/article/180555.html

查看完整版本: Mybatis自关联查询一对多查询的实现示例