Mybatis自關聯查詢一對多查詢的實現示例

Posted on by

註：代碼已托管在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 [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<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);
  }
 }

結果：

[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 一對多查詢內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：