An sql query tree structure data

Current problem: how to query the data of provinces, cities, districts and counties in the same table through an SQL statement,
And the provincial level includes all the city level lists, and the city level includes the county level data.

Idea: using nested query of mybatis collection

The data format is as follows:

Java entity classes are as follows:

public class Area implements Serializable{

	private static final long serialVersionUID = 1L;

	/**
	 * Primary key id
	 */
	private Integer id;
	/**
	 * Area name
	 */
    private String name;
    /**
     * Area encoding
     */
    private Integer areaId;
    /**
     * Upper region code
     */
    private Integer parentId;
    /**
    * List of next level regions
    */
    private List<Area> subAreas;

    ...getter and setter Method...
}

xml:

Examples of supporting two levels of nesting

  <!-- Same table cascade query(Supports two levels of nested queries) -->
  <resultMap type="cn.edu.ntu.entity.Area" id="areasOther">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="area_id" jdbcType="INTEGER" property="areaId" />
    <result column="parent_id" jdbcType="INTEGER" property="parentId" />
    <collection property="subAreas" ofType="cn.edu.ntu.entity.Area" columnPrefix="b">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="area_id" jdbcType="INTEGER" property="areaId" />
        <result column="parent_id" jdbcType="INTEGER" property="parentId" />
    </collection>
    <!-- Use columnPrefix Simplify writing, or you need to write
    	<collection property="subAreas" ofType="cn.edu.ntu.entity.Area" >
	        <id column="bid" jdbcType="INTEGER" property="id" />
	        <result column="bname" jdbcType="VARCHAR" property="name" />
	        <result column="barea_id" jdbcType="INTEGER" property="areaId" />
	        <result column="bparent_id" jdbcType="INTEGER" property="parentId" />
	    </collection>
	     column Value and sql Named correspondence in statement
     -->
  </resultMap>

  <!--Because it's a one to many relationship collection-->
  <select id="queryAllCity" resultMap="areasOther">
    select     
	    a.id, a.name, a.area_id, 
	    b.id bid, b.name bname, b.area_id barea_id
    from area a 
    left join area b on b.parent_id = a.area_id
    where a.parent_id is null
  </select>

Test method:

public class MybatisTest {

	@Test
	public void test(){
		ApplicationContext application = 
            new ClassPathXmlApplicationContext("classpath*:spring/applicationContext.xml");
		
		AreaMapper areaMapper = application.getBean(AreaMapper.class);
		List<Area> areaList = areaMapper.queryAllCity();
		System.out.println(areaList.size());
		System.out.println(areaList.get(0).getSubAreas().size());
		System.out.println(JsonUtils.objectToJson(areaList));
		
	}
}

Operation result:

Conclusion:

From the above test data, it can be seen that two layers of nesting are supported, but if you want to nest another layer of < Collection > in < resultmap > with id "areasOther", the purpose is to support three layers of nesting, and do not know how to operate temporarily???

Failed attempts:

  <!-- Same table cascade query(Test to support three-tier nested query) -->
  <resultMap type="cn.edu.ntu.entity.Area" id="areasOther">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="area_id" jdbcType="INTEGER" property="areaId" />
    <result column="parent_id" jdbcType="INTEGER" property="parentId" />
    <collection property="subAreas" ofType="cn.edu.ntu.entity.Area" columnPrefix="b">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="area_id" jdbcType="INTEGER" property="areaId" />
        <result column="parent_id" jdbcType="INTEGER" property="parentId" />
        <collection property="subAreas" ofType="cn.edu.ntu.entity.Area" columnPrefix="c">
            <id column="id" jdbcType="INTEGER" property="id" />
            <result column="name" jdbcType="VARCHAR" property="name" />
            <result column="area_id" jdbcType="INTEGER" property="areaId" />
            <result column="parent_id" jdbcType="INTEGER" property="parentId" />
        </collection>
    </collection>
    <!-- Use columnPrefix Simplify writing, or you need to write
    	<collection property="subAreas" ofType="cn.edu.ntu.entity.Area" >
	        <id column="bid" jdbcType="INTEGER" property="id" />
	        <result column="bname" jdbcType="VARCHAR" property="name" />
	        <result column="barea_id" jdbcType="INTEGER" property="areaId" />
	        <result column="bparent_id" jdbcType="INTEGER" property="parentId" />
	    </collection>
	     column Value and sql Named correspondence in statement
     -->
  </resultMap>

  <!--Because it's a one to many relationship collection-->
  <select id="queryAllCity" resultMap="areasOther">
    select     
	    a.id, a.name, a.area_id, 
	    b.id bid, b.name bname, b.area_id barea_id,
        c.id cid, c.name cname, c.area_id carea_id
    from area a 
    left join area b on b.parent_id = a.area_id
    left join area c on c.parent_id = b.area_id
    where a.parent_id is null
  </select>

The above case did not succeed. I don't know if it is the sql statement writing problem of queryAllCity. Please give me some advice!

 

Another way to support multiple nesting:

<!-- Same table cascade query (Support multiple nesting) -->
  <resultMap type="cn.edu.ntu.entity.Area" id="areas">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="area_id" jdbcType="INTEGER" property="areaId" />
    <result column="parent_id" jdbcType="INTEGER" property="parentId" />
    <collection property="subAreas" ofType="cn.edu.ntu.entity.Area" column="area_id" 
        select="queryCityByParentId">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="area_id" jdbcType="INTEGER" property="areaId" />
        <result column="parent_id" jdbcType="INTEGER" property="parentId" />
    </collection>
  </resultMap>

  <!--Because it's a one to many relationship collection-->
  <select id="queryCityByParentId" parameterType="java.lang.Integer" resultMap="areas">
    select
    	a.id, a.name, a.area_id, a.parent_id 
    from area a where a.parent_id = #{area_id}
  </select>
  
  <!--Because it's a one to many relationship collection-->
  <select id="queryCity" resultMap="areas">
    select
    	a.id, a.name, a.area_id, a.parent_id  
    from area a where a.parent_id is null 
  </select>

In the above way, the query statement specified by select is called in the collection circularly, and the query condition is the data of column. Finally, the query result is put into the property specified by property

Execution result:

 

Source path: https://gitee.com/wpfc/websocket/blob/master/src/test/java/cn/dq/MybatisTest.java

Reference resources: http://blog.csdn.net/u012485016/article/details/64500972

Tags: SQL Java xml Mybatis

Posted on Wed, 01 Apr 2020 00:22:45 -0700 by Nukeum66