How to handle complex result set mapping relationship in MyBatis

Article directory

• foreword
• 1. Preparation
• Two, resultMap handles the mapping relationship between fields and attributes
• 3. Many-to-one mapping
• • 0. Process the mapping relationship in cascade mode
  • 1. Use association to process mapping relationship
  • 2. Step by step query to solve the many-to-one relationship
  • • (1) Query employee information
    • (2) Query department information according to the department id corresponding to the employee
    • lazy loading
• Three, one-to-many relationship processing
• • 0. Use collection to maintain one-to-many relationship
  • 1. Step by step query
  • • (1) Query department information
    • (2) Query all employees in the department according to the department id
    • (3) Test class

Foreword

In development, we don’t always operate on a single table. According to the design principle of the database table, to conform to a certain paradigm, it is necessary to split the table of a certain scene.
In terms of business, it may belong to the same business. However, the storage of tables in the database may involve table splitting.
The creation of the design here directly creates two tables:

1. Preparations

1. For how to build the test environment of MyBatis, there is a detailed introduction in the blog post from 0 to 1 in the analysis of the idea of building a MyBatis instance, so I will not expand it one by one here.
2. Create two tables in the MySQL database as the environment basis for this test.
   On the basis of the first step, we import two tables. One is the t_dept and the other is the t_emp table
   t_dept table

DROP TABLE IF EXISTS `t_dept`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t_dept` (
  `did` int NOT NULL,
  `dept_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`did`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_dept`
--

LOCK TABLES `t_dept` WRITE;
/*!40000 ALTER TABLE `t_dept` DISABLE KEYS */;
INSERT INTO `t_dept` VALUES (1,'development group'),(2,'ai group'),(3,'operation and maintenance group');
/*!40000 ALTER TABLE `t_dept` ENABLE KEYS */;
UNLOCK TABLES;

t_emp table:

DROP TABLE IF EXISTS `t_emp`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t_emp` (
  `eid` int NOT NULL,
  `emo_name` varchar(45) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `did` int DEFAULT NULL,
  PRIMARY KEY (`eid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_emp`
--

LOCK TABLES `t_emp` WRITE;
/*!40000 ALTER TABLE `t_emp` DISABLE KEYS */;
INSERT INTO `t_emp` VALUES (1,'lucy',23,'0','[email protected]',1),(2,'jack',24,'1','[email protected]' ,1),(3,'smith',22,'1','[email protected]',2),(4,'zhangsan',46,'1','[email protected]',2 ),(5,'limu',47,'1','[email protected]',3),(6,'fong',23,'1','[email protected]',3);
/*!40000 ALTER TABLE `t_emp` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

Let’s take a quick look at the data in the database:

3. Create mapper, pojo, mapping files
   The overall project structure is:
   
   mapper interface:

public interface DeptMapper {<!-- -->
}
public interface EmpMapper {<!-- -->
}

pojo:

//dept
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Dept{<!-- -->
    private Integer did;
    private String deptName;
}
//emp
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp {<!-- -->
    private Integer eid;
    private String empName;
    private Integer age;
    private String sex;
    private String email;
}

Here I directly use lombok to dynamically generate getter and setter methods
The method of use is to import dependencies directly in the pom.xml file

 <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.26</version>
    </dependency>

xxxMapper.xml

<!--DeptMapper.xml-->
<mapper namespace="com.fckey.mybatis.mapper.DeptMapper">
</mapper>
<!--EmpMapper.xml-->
<mapper namespace="com.fckey.mybatis.mapper.EmpMapper">
</mapper>

2. resultMap handles the mapping relationship between fields and attributes

If the field name is inconsistent with the attribute name in the entity class, but the field name and attribute name can be changed through the hump rule. Or it cannot be transformed. There are corresponding processing methods.
The following ones conform to the camel case rule:

For those that can be mapped by hump rules, there is a unique way to solve the mapping problem. You can directly configure mapUnderscoreToCamelCase in the mybatis-config.xml global configuration file to support this relational mapping

 <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

There is also a more general approach to solving the mapping problem:

1. Give all checked fields an alias

<!-- List<Emp> getAll();-->
    <select id="getAll" resultType="emp">
        select eid, emp_name empName, age, sex, email from t_emp
    </select>

2. Customize resultmap to define mapping rules
   In resultMap, set the attribute name -> field name one by one, and then add resultMap="the id of the resultMap"select tag >

<!--
        resultMap sets a custom mapping relationship
        id unique identifier
        type mapped entity type

        Sub-label: id sets the mapping relationship of the primary key, and result sets other mapping relationships
            property sets the property name in the mapping relationship, which must be the property name of the entity class type set by the type property
            column sets the field name in the mapping relationship, which must be the field name queried by the SQL statement

        If you use resultMap, all attributes need to be set
-->
    <resultMap id="empResultMap" type="Emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
    </resultMap>

    <select id="getAll" resultMap="empResultMap">
        select * from t_emp
    </select>

3. Many-to-one mapping

Such as: query employee information and the department information corresponding to the employee

For the maintenance of many-to-many and one-to-many mapping relationships at the Java entity class level. Need to add the List attribute to the “one” pojo, and add “one” to the “many” pojo
That is to say, in the Dept class, add private List emps; in the Emp class, add private Dept dept; .

Let’s take a look at how to solve the many-to-one mapping relationship in mybatis, and what are the processing methods?

For many-to-one relationships, for SQL queries, it is a fixed multi-table join query. No matter which orm framework requires handwritten sql, this step is indispensable.

 select * from t_emp left join t_dept
            on t_emp.eid = t_dept.did WHERE t_emp.eid = #{eid}

The key is how a specific orm framework handles the mapping relationship. The following introduces several common methods of processing mapping in MyBatis.
For the above, it has been written that if the fields are inconsistent, you can use the resultmap method to solve it.

0, cascade processing mapping relationship

In EmpMapper.xml:

<!-- many-to-one mapping relationship, method 1: cascade attribute assignment -->
    <resultMap id="getEmpAndDeptResultMapOne" type="Emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <result property="dept.did" column="did"></result>
        <result property="dept.deptName" column="dept_name"></result>
    </resultMap>

<!-- Emp getEmpAndDept(@Param("eid") Integer eid);-->
    <select id="getEmpAndDept" resultMap="getEmpAndDeptResultMapOne">
        select * from t_emp left join t_dept
            on t_emp.eid = t_dept.did WHERE t_emp.eid = #{eid}
    </select>

notice:
IDEA may become popular, but it doesn’t matter.

In the EmpMapper class

public interface EmpMapper {<!-- -->
    /**
     * Query employees and their corresponding department information
     */
    Emp getEmpAndDept(@Param("eid") Integer eid);
}

in test class

 /**
     * Handle many-to-one mapping relationship
     * a> cascade attribute assignment
     * b> association
     * c> step by step query
     */
    @Test
    public void testGetEmpAndDept(){
        SqlSession sqlSession = SqlSessionUtils. getSqlSession();
        EmpMapper mapper = sqlSession. getMapper(EmpMapper. class);
        Emp empAndDept = mapper. getEmpAndDept(1);
        System.out.println(empAndDept);
    }

Finally, dept was found out.

1. Use association to process mapping relationship

This change in handling the mapping relationship is to define the association tag that needs to deal with the many-to-one relationship.
EmpMapper.xml

<resultMap id="empDeptMap" type="Emp">
    <id column="eid" property="eid"></id>
    <result column="ename" property="ename"></result>
    <result column="age" property="age"></result>
    <result column="sex" property="sex"></result>
    <association property="dept" javaType="Dept">
        <id column="did" property="did"></id>
        <result column="dname" property="dname"></result>
    </association>
</resultMap>
<!--Emp getEmpAndDeptByEid(@Param("eid") int eid);-->
<select id="getEmpAndDeptByEid" resultMap="empDeptMap">
    select emp.*,dept.* from t_emp emp left join t_dept dept on emp.did =
dept.did where emp.eid = #{eid}
</select>

The result is still the same as above.

Generally, we use association to perform many-to-one relationship mapping, which is more vivid.

2. Step by step query to solve the many-to-one relationship

For the above process, the SQL we write is multi-table joint query SQL, and what is sent to the database is a query request. Then can we send two, and then splicing the results?

(1) Query employee information

In the EmpMapper class

public interface EmpMapper {<!-- -->
/**
* Query employee information by step-by-step query * @param eid
* @return
*/
Emp getEmpByStep(@Param("eid") int eid);
}

EmpMapper.xml:

<!-- getEmpAndDeptByStepTwo is the full class name of this sql statement -->
    <resultMap id="getEmpAndDeptByStepResultMap" type="Emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <!--
                select: Set the unique identifier of sql for step-by-step query (namespace.SQLId or full class name of mapper interface. Method name)
                column: the condition of step-by-step query
                fetchType: When the global delay record is enabled, the effect of lazy loading can be manually controlled through this property
                fetchType: "lazy/eager" lazy means lazy loading, eager means immediate loading
        -->
        <association property="dept"
                     select="com.fckey.mybatis.mapper.DeptMapper.getEmpAndDeptByStepTwo"
                     column="did"
                     fetchType="lazy">
        </association>
    </resultMap>

    <select id="getEmpByStep" resultMap="getEmpAndDeptByStepResultMap">
        select * from t_emp where eid = #{eid}
    </select>

(2) Query department information according to the department id corresponding to the employee

DeptMapper interface:

public interface DeptMapper {<!-- -->
/**
* The second step of the step-by-step query: query the department information according to the did corresponding to the employee
*/
Dept getEmpDeptByStep(@Param("did") int did);
}

DeptMapper.xml:

<!-- Dept getEmpAndDeptByStepTwo(Integer did);-->
<!-- Step by step query can achieve lazy loading -->
    <select id="getEmpAndDeptByStepTwo" resultType="Dept">
        select * from t_dept where did = #{did}
    </select>

Come and test it out, find out

@Test
    public void testGetEmpByStep() throws IOException {<!-- -->
        SqlSession sqlSession = getSqlSession();
        EmpMapper mapper = sqlSession. getMapper(EmpMapper. class);
        Emp empByStep = mapper. getEmpByStep(1);
        System.out.println(empByStep);
    }

Lazy loading

We know that for step-by-step query, sending two query statements each time may cause time loss, but step-by-step query is not without advantages.
Advantages of step-by-step query: Lazy loading can be implemented, but global configuration information must be set in the core configuration file.
The first setting method that can achieve coarse strength is in the global configuration file mybatis-config.xml:

• lazyLoadingEnabled: Global switch for lazy loading. When enabled, all associated objects are lazy loaded
• aggressiveLazyLoading: When enabled, any method call will load all properties of the object. Otherwise, each attribute is loaded on demand
  
  At this point, on-demand loading can be realized, and only the corresponding sql will be executed for whatever data is obtained. At this time, you can set whether the current step-by-step query uses lazy loading through the fetchType attribute in association and collection, fetchType="lazy (lazy loading)|eager (immediate loading)".

Suppose, I am designing a scene now, and the query is the gender of an employee, which does not involve the query of the department. If the department is detected together, the description is loaded immediately. On the contrary, it is lazy loading.
Lazy loading turned on:

Load now to open:

Through the fetchType parameter, you can manually control lazy loading or immediate loading, otherwise it is determined whether to delay loading or immediate loading according to the attributes of the global configuration.

3. One-to-many relationship processing

Requirement: Find the department and employee information in the department according to the department id

To query one-to-many and many-to-one relationships, you need to add the List attribute to the “one” pojo, and add the List attribute to the “many” pojo Add “one”.
That is to say, in the Dept class, you need to add private List emps;; in the Emp class, you need to add private Dept dept;. Then add get and set methods to them, rewrite the constructor and toString()

0. Use collection to maintain one-to-many relationship

DeptMapper interface:

public interface DeptMapper {<!-- -->
    /*
    Get all employee information in the department
     */
    Dept getDeptAndEmp(@Param("did") Integer did);
}

DeptMapper.xml

 <resultMap id="deptAndEmpResultMap" type="Dept">
        <id property="did" column="did"></id>
        <result property="deptName" column="dept_name"></result>
<!--
            collection: handle one-to-many mapping relationship
            ofType: Indicates the type of data stored in the collection corresponding to the attribute
-->
        <collection property="emps" ofType="Emp">
            <id property="eid" column="eid"></id>
            <result property="empName" column="emp_name"></result>
            <result property="age" column="age"></result>
            <result property="sex" column="sex"></result>
            <result property="email" column="email"></result>
        </collection>
    </resultMap>
<!-- Dept getDeptAndEmp(@Param("did") Integer did);-->
    <select id="getDeptAndEmp" resultMap="deptAndEmpResultMap">
        select * from t_dept left join t_emp on t_dept.did = t_emp.did where t_dept.did = #{did}
    </select>

Test class to test;

 @Test
    public void testGetDeptAndEmp() throws IOException {<!-- -->
        SqlSession sqlSession = getSqlSession();
        DeptMapper mapper = sqlSession. getMapper(DeptMapper. class);
        Dept dept = mapper. getDeptAndEmp(1);
        System.out.println(dept.getDeptName());
        dept.getEmps().forEach(System.out::println);
    }

The final result is obtained;

1. Step by step query

In processing one-to-many relationships, there are also distributed queries, and in step-by-step queries, there are also lazy loading and immediate loading.

(1) Query department information

DeptMapper interface

public interface DeptMapper {<!-- -->
    /**
     * Step-by-step query query department and all its employee information
     * The first step is to query department information
     */
    Dept getDeptAndEmoByStepOne(@Param("did") Integer did);
}

DeptMapper.xml

 <!-- step by step query -->
    <resultMap id="deptAndEmoByStepOneMap" type="Dept">
        <id property="did" column="did"></id>
        <result property="deptName" column="dept_name"></result>
        <collection property="emps"
                    select="com.fckey.mybatis.mapper.EmpMapper.getDeptAndEmpByStepTwo"
                    column="did"
                    fetchType="lazy"
        >
        </collection>
    </resultMap>
    <!-- Dept getDeptAndEmoByStepOne(@Param("did") Integer did);-->
    <select id="getDeptAndEmoByStepOne" resultMap="deptAndEmoByStepOneMap">
        select * from t_dept where did = #{did}
    </select>

(2) Query all employees in the department according to the department id

EmpMapper

public interface EmpMapper {<!-- -->
    /**
     * Step-by-step query query department and all its employee information
     * The first step is to query department information
     * The second step is based on querying employee information
     */
    List<Emp> getDeptAndEmpByStepTwo(@Param("did") Integer did);
}

EmpMapper.xml

<!-- step by step query -->
<!-- List<Emp> getDeptAndEmpByStepTwo(@Param("did") Integer did);-->
    <select id="getDeptAndEmpByStepTwo" resultType="Emp">
        select * from t_emp where did = #{did}
    </select>

(3) Test class

 @Test
    public void testGetDeptAndEmp() throws IOException {<!-- -->
        SqlSession sqlSession = getSqlSession();
        DeptMapper mapper = sqlSession. getMapper(DeptMapper. class);
        Dept dept = mapper. getDeptAndEmp(1);
        System.out.println(dept.getDeptName());
        System.out.println("=====================");
        dept.getEmps().forEach(System.out::println);
    }

Then, the lazy loading mode is set, and the running results are as follows

Then, someone asked, there is one-to-many, many-to-one, what about the many-to-many relationship? For the many-to-many relationship, it is necessary to extract the third table, which is essentially a one-to-many, many-to-one variant.