mybatis uses collection to implement nested queries

When writing the mybatis query statement, you need to write a query interface to find all employees of all departments of all branches of the group. Display the id and name of the branch, the id and name of each branch department, and the id and name of each department person. Finally, the data structure of json displayed on the web page is

{
  "data": {[
      {"pkid": 1, "name": "Demo company","depts": []}
      {"pkid": 3,"name": "Communication company",
        "depts": [
         {"pkid": 1,"fgsid": 3,"name": "Information Department"
           "employees": [
          {"pkid": "1","fgsid": "3","name": "administrators"},
         {"pkid":"1","fgsid": "3","name":"APP Test"},
          {"pkid": "1","fgsid": "3","name":"administrators" }
      ]}
      ]}

First of all, we will build a branch like Bransh,

class Branch{
int pkid;
String name;
The construction method of write class
 Write get and set methods
}

Create a department class

class Dept{
int pkid;
int fgsid; / / branch id
String name;
Construction method of class
 get and set methods
}

Create an employee class

class Employee{
int pkid;
int fgsid;
int bmid;
String name;
get and set methods;
Construction method of class
}

Write such sql directly in the mapper.xml configuration file.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"      
 "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="com.dawn.push.mapper.messageMapper">
    //First, find out all the employees in all the departments of a branch company
    <select id="queryOneBranchAllDept" resultMap="deptMap">
        select
        b.pkid,b.name,b.fgsid,e.pkid,e.fgsid,e.bmid,e.name
        from dggc1.dbo.bd_bm b,dggc1.dbo.bd_emp e
        where b.pkid=e.bmid and b.fgsid=#{fgsid};
    </select>
    //Find out all branches
<select id="queryAllbranch" resultMap="branchMap">
 select f.pkid,f.name
 from dggc1.dbo.bd_fgs f
</select>
//This is a result set of employees in all departments of a branch company
<resultMap id="deptMap" type="com.pojo.Dept">
<id column="pkid" property="pkid" jdbcType="SMALLINT" />
<result column="fgsid" property="fgsid" jdbcType="SMALLINT" />
<result column="name" property="name" jdbcType="VARCHAR" />
 <collection property="employees" ofType="com.pojo.Employee">
       <result property="pkid" column="pkid" />
       <result property="fgsid" column="fgsid" />
       <result property="bmid" column="bmid" />
       <result property="name" column="xm" />
   </collection>
 </resultMap>
  //This is a collection of all employees in all departments of all branches
 <resultMap id="branchMap" type="com.pojo.Branch">
<id column="pkid" property="pkid" jdbcType="SMALLINT" />
<result column="fid" property="fid" jdbcType="SMALLINT" />
<result column="name" property="mc" jdbcType="VARCHAR" />
       <collection property="depts" ofType="com.dawn.push.pojo.Dept"
       select="queryOneBranchAllDept" column="{fgsid=pkid}">
      //select here is a query statement that uses the above query to query the collection of all employees in all departments of a branch company. Here, colum is followed by the query criteria. The branch id of the branch to be queried is the parameter fgsid to be entered in the query onebranchalldept query method.
          <id column="pkid" property="pkid" jdbcType="SMALLINT" />
         <result column="fgsid" property="fgsid" jdbcType="SMALLINT" />
         <result column="name" property="mc" jdbcType="VARCHAR" />
     </collection>
     </resultMap>
</mapper>

This enables nesting. You can also reuse sql statements. If you have any other questions, please let me know.

Tags: SQL xml Apache Mybatis

Posted on Tue, 05 May 2020 06:11:37 -0400 by Slip