注：代碼已托管在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 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 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 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: 1NewsLabel [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 一對多查詢內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！