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

时间：2022-09-06 09:34:50

注：代码已托管在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表如下：

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

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

 

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

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

  1. import java.util.Set;
  2.  
  3. public class NewsLabel {
  4. private Integer id;
  5. private String name;
  6. private Set<NewsLabel>children;
  7. public Integer getId() {
  8. return id;
  9. }
  10. public void setId(Integer id) {
  11. this.id = id;
  12. }
  13. public String getName() {
  14. return name;
  15. }
  16. public void setName(String name) {
  17. this.name = name;
  18. }
  19. public Set<NewsLabel> getChildren() {
  20. return children;
  21. }
  22. public void setChildren(Set<NewsLabel> children) {
  23. this.children = children;
  24. }
  25.  
  26. @Override
  27. public String toString() {
  28. return "NewsLabel [id=" + id + ", name=" + name + ", children="
  29. + children + "]";
  30. }
  31.  
  32. }

定义sql接口:

  1. public interface INewsLabelDao {
  2. List<NewsLabel> selectChildByParentId(int pid);
  3. }

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

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper
  3. PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4. "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="dao.INewsLabelDao">
  6. <resultMap type="beans.NewsLabel" id="newsLabelMapper">
  7. <id column="id" property="id"/>
  8. <result column="name" property="name"/>
  9. <collection property="children"
  10. ofType="NewsLabel"
  11. select="selectChildByParentId"
  12. column="id"/>
  13. </resultMap>
  14. <select id="selectChildByParentId" resultMap="newsLabelMapper">
  15. select id,name from newslabel where pid=#{xxx}
  16. </select>
  17. </mapper>

测试类MyTest.java:

  1. public class MyTest {
  2. private INewsLabelDao dao;
  3. private SqlSession sqlSession;
  4. @Before
  5. public void Before(){
  6. sqlSession=MyBatisUtils.getSqlSession();
  7. dao=sqlSession.getMapper(INewsLabelDao.class);
  8. }
  9. @Test
  10. public void TestselectMinisterById(){
  11. List<NewsLabel>children=dao.selectChildByParentId(2);
  12. for(NewsLabel newsLabel:children){
  13. System.out.println(newsLabel);
  14. }
  15. }
  16. @After
  17. public void after(){
  18. if(sqlSession!=null){
  19. sqlSession.close();
  20. }
  21. }
  22.  
  23. }

结果：

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的接口：

  1. List<NewsLabel> selectSelfAndChildByParentId(int pid);

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

  1. <!-- 筛选出自己以及子孙栏目-->
  2. <select id="selectChildByParentId2" resultMap="newsLabelMapper2">
  3. select id,name from newslabel where pid=#{ooo}
  4. </select>
  5. <resultMap type="beans.NewsLabel" id="newsLabelMapper2">
  6. <id column="id" property="id"/>
  7. <result column="name" property="name"/>
  8. <collection property="children"
  9. ofType="NewsLabel"
  10. select="selectChildByParentId2"
  11. column="id"/>
  12. </resultMap>
  13. <select id="selectSelfAndChildByParentId" resultMap="newsLabelMapper2">
  14. select id,name from newslabel where id=#{xxx}
  15. </select>

单元测试：

  1. @Test
  2. public void TestselectSelfAndChildrenLabelById(){
  3. List<NewsLabel> children = dao.selectSelfAndChildByParentId(2);
  4. for (NewsLabel newsLabel : children) {
  5. System.out.println(newsLabel);
  6. }
  7. }

结果：

[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.INewsLabelDao.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=[]]]]]]

到此这篇关于Mybatis自关联查询一对多查询的实现示例的文章就介绍到这了,更多相关Mybatis 一对多查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

原文链接：https://segmentfault.com/a/1190000039134263

标签：

相关文章