Mybatis [18] – mybatis self association many to one query mode

Time:2021-2-6

Note: the code is hosted inGitHubThe address is:https://github.com/Damaer/Mybatis-LearningThe project ismybatis-14-oneself-many2one, need to be self fetched, need to configure Maven environment andmysqlEnvironment(sqlStatement inresourceNexttest.sqlIf you think it’s useful, you can light a little star.

docsifyThe document address is at:https://damaer.github.io/Mybatis-Learning/#/

Now there is a database query needs, given the current news columnid, hope to find out the parent column, parent’s parent column and other information.

The data table is designed as follows:
Mybatis [18] - mybatis self association many to one query mode

Entity class design:

package beans;

import java.util.Set;

public class NewsLabel {
  private Integer id;
  private String name;
  private NewsLabel parent;
  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 NewsLabel getParent() {
    return parent;
  }
  @Override
  public String toString() {
    return "NewsLabel [id=" + id + ", name=" + name + ", parent=" + parent
            + "]";
  }
  public void setParent(NewsLabel parent) {
    this.parent = parent;
  }

}

SQL query interface definition:

public interface INewsLabelDao {
  NewsLabel selectParentByParentId(int pid);
}

sqlDefined inmapper.xmlIn the file, we can see that when we query, we call theidby“selectParentByParentId”SQL, return the result to do a mapping(resultMap),resultMapOfidyes“newsLabelMapper”,“newsLabelMapper”In addition toidMapping andnameMapping, there’s another one<association/>, which defines the definition of association relationship.

  • property="parent": indicates that the property of the mapping isparent
  • javaType="NewsLabel": indicates that the type of the mapping isNewsLabel
  • column="pid": usepidPass it in as a parameter and query again.
  • select="selectParentByParentId": queryparentProperty to execute the statement
<?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="NewsLabel" id="newsLabelMapper">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <association property="parent"
                     javaType="NewsLabel"
                     select="selectParentByParentId"
                     column="pid"/>
    </resultMap>
    <select id="selectParentByParentId" resultMap="newsLabelMapper">
        select id,name,pid from newslabel where id=#{xxx}
    </select>
</mapper>

Test category:

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(){
    NewsLabel children=dao.selectParentByParentId(7);

    System.out.println(children);

  }
  @After
  public void after(){
    if(sqlSession!=null){
      sqlSession.close();
    }
  }

}

The results are as follows:

[service] 2018-07-16 11:54:10,123 - dao.INewsLabelDao.selectParentByParentId -683  [main] DEBUG dao.INewsLabelDao.selectParentByParentId  - ==>  Preparing: select id,name,pid from newslabel where id=? 
[service] 2018-07-16 11:54:10,154 - dao.INewsLabelDao.selectParentByParentId -714  [main] DEBUG dao.INewsLabelDao.selectParentByParentId  - ==> Parameters: 7(Integer)
[service] 2018-07-16 11:54:10,174 - dao.INewsLabelDao.selectParentByParentId -734  [main] DEBUG dao.INewsLabelDao.selectParentByParentId  - ====>  Preparing: select id,name,pid from newslabel where id=? 
[service] 2018-07-16 11:54:10,174 - dao.INewsLabelDao.selectParentByParentId -734  [main] DEBUG dao.INewsLabelDao.selectParentByParentId  - ====> Parameters: 4(Integer)
[service] 2018-07-16 11:54:10,181 - dao.INewsLabelDao.selectParentByParentId -741  [main] DEBUG dao.INewsLabelDao.selectParentByParentId  - ======>  Preparing: select id,name,pid from newslabel where id=? 
[service] 2018-07-16 11:54:10,181 - dao.INewsLabelDao.selectParentByParentId -741  [main] DEBUG dao.INewsLabelDao.selectParentByParentId  - ======> Parameters: 2(Integer)
[service] 2018-07-16 11:54:10,183 - dao.INewsLabelDao.selectParentByParentId -743  [main] DEBUG dao.INewsLabelDao.selectParentByParentId  - ========>  Preparing: select id,name,pid from newslabel where id=? 
[service] 2018-07-16 11:54:10,183 - dao.INewsLabelDao.selectParentByParentId -743  [main] DEBUG dao.INewsLabelDao.selectParentByParentId  - ========> Parameters: 0(Integer)
[service] 2018-07-16 11:54:10,184 - dao.INewsLabelDao.selectParentByParentId -744  [main] DEBUG dao.INewsLabelDao.selectParentByParentId  - <========      Total: 0
[service] 2018-07-16 11:54:10,184 - dao.INewsLabelDao.selectParentByParentId -744  [main] DEBUG dao.INewsLabelDao.selectParentByParentId  - <======      Total: 1
[service] 2018-07-16 11:54:10,184 - dao.INewsLabelDao.selectParentByParentId -744  [main] DEBUG dao.INewsLabelDao.selectParentByParentId  - <====      Total: 1
[service] 2018-07-16 11:54:10,184 - dao.INewsLabelDao.selectParentByParentId -744  [main] DEBUG dao.INewsLabelDao.selectParentByParentId  - <==      Total: 1
Newslabel [id = 7, name = Beijing Jinou, parent = newslabel [id = 4, name = CBA, parent = newslabel [id = 2, name = sports news, parent = null]]]

[about the author]:
Qin Huai, the official account of Qin Huai grocery store, is not in the right place for a long time. It’s a great encouragement and recognition for me to ask for a compliment, which makes me more motivated to continue to write good articles.