In the actual development, the operation of database often involves multiple tables, which involves the relationship between objects in object-oriented.

For multi table operations, MyBatis provides association mapping. Through association mapping, the association relationship between objects can be well handled.

What you need to know

1. Types of relationship

Database:

In a relational database, there are three kinds of association relations among multiple tables, namely, one to one, one to many and many to many

1. One to one: introduce the opposite primary key as a foreign key in either party;
2. One to many: on the "many" side, add the primary key of the "one" side as the foreign key;
3. Many to many: generates an intermediate relation table, introduces two primary keys of tables as foreign keys, and two primary keys become union primary keys or use new fields as primary keys.

java

1. One to one: define objects of opposite type in this class, such as attribute B of type B in class A and attribute a of type A in class B; (two-way one-to-one)

2. One to many: when a class a type corresponds to multiple class B types, it is necessary to introduce class B objects in the form of sets in class A, and define class a attribute a in class B;

3. Many to many: define the set of type B in class A, and the set of type A in class B.

2. Associated query method

MyBatis loads Association objects in two ways: nested queries and nested results.

Use

The project directory is as shown in the figure, in which the red mark is required for this time. This time, I mainly discuss one to many relationship mapping. If you are not familiar with the xml version of mybatis, please go to
Application of data layer framework -- Mybatis (1) CRUD of data based on XML Mapping File

1. Create entity class

Create entity class: create entity classes AdminDetail.java and AdminInfo.java in com.lomtom.mybaris.entity package

1,AdminDetail.java
/**
 * @Author: LOMTOM
 * @Date: 2020/4/26
 * @Time: 16:43
 * @Email: lomtom@qq.com
 */
@Data
public class AdminInfo {
    private int id;

    private String name;

    private String pwd;

    private AdminDetail ad;

    public AdminInfo() {
    }

    public AdminInfo(String name, String pwd) {
        this.name = name;
        this.pwd = pwd;
    }
}

2,AdminInfo.java
/**
 * @Author: LOMTOM
 * @Date: 2020/4/26
 * @Time: 16:42
 * @Email: lomtom@qq.com
 */
@Data
public class AdminDetail {
    private int id;

    private String address;

    private String realName;

    public AdminDetail() {
    }

    public AdminDetail(String address, String realName) {
        this.address = address;
        this.realName = realName;
    }
}

2. Create XML file for SQL mapping

Create the XML files adminDetailMapper.xml and adminInfoMapper.xml of SQL mapping in the package com.lomtom.mybaris.mapper,

• Configure the < Insert > element to insert records into the data table.
• Configure the < Select > element to insert records into the data table.

1,adminDetailMapper.xml
<mapper namespace="com.lomtom.mybatis.mapper.adminDetailMapper">
    <insert id="addAdminDetail" parameterType="AdminDetail"
        keyProperty="id" useGeneratedKeys="true">
        insert into admin_detail(address ,realName) values (#{address},#{realName})
    </insert>
</mapper>

2,adminInfoMapper.xml
<mapper namespace="com.lomtom.mybatis.mapper.adminInfoMapper">
    <insert id="addAdminInfo" parameterType="AdminInfo">
        insert into admin_info(id, name, pwd) values( #{ad.id}, #{name}, #{pwd})
    </insert>


    <select id="getAdminInfo" parameterType="int" resultMap="getAdminInfoMap">
        select ai.id, name, pwd, address, realName from admin_info ai, admin_detail ad where ai.id=ad.id and ai.id=#{id}
    </select>
    <!--nested result mappings ,Last result returned,see select Element's resultMap attribute-->
    <resultMap type="com.lomtom.mybatis.entity.AdminInfo" id="getAdminInfoMap">
        <id property="id" column="id" />
        <result property="name" column="name"/>
        <result property="pwd" column="pwd"/>
        <association property="ad" javaType="com.lomtom.mybatis.entity.AdminDetail">
            <id property="id" column="id"/>
            <result property="address" column="address"/>
            <result property="realName" column="realName"/>
        </association>
    </resultMap>


    <select id="getAdminInfo2" parameterType="int"
            resultMap="getAdminInfo2Map">
        select * from admin_info where id=#{id}
    </select>
    <select id="getAdminDetail" parameterType="int" resultType="AdminDetail">
        select * from admin_detail where id=#{id}
    </select>
    <resultMap type="AdminInfo" id="getAdminInfo2Map">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="pwd" column="pwd"/>
        <association property="ad" column="id" select="getAdminDetail"/>
    </resultMap>
</mapper>

Among them, insert keyProperty="id" useGeneratedKeys="true" to realize auto increment. The above two query methods are nested query and nested result respectively

3. Register XML files for SQL mapping

Register adminDetailMapper.xml and adminInfoMapper.xml in the XML configuration file mybatis-config.xml.

    <mappers>
        <mapper resource="com/lomtom/mybatis/mapper/adminDetailMapper.xml"/>
        <mapper resource="com/lomtom/mybatis/mapper/adminInfoMapper.xml"/>
    </mappers>

4. Create table

Create two tables, admin detail and admin info. Pay attention to the dependency relationship between the two tables. You can create two tables first and associate them

1,admin_detail
	SET NAMES utf8mb4;
	SET FOREIGN_KEY_CHECKS = 0;
	
	-- ----------------------------
	-- Table structure for admin_detail
	-- ----------------------------
	DROP TABLE IF EXISTS `admin_detail`;
	CREATE TABLE `admin_detail`  (
	  `id` int(0) NOT NULL AUTO_INCREMENT,
	  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
	  `realName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
	  PRIMARY KEY (`id`) USING BTREE
	) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
	
	SET FOREIGN_KEY_CHECKS = 1;

2,admin_info
	SET NAMES utf8mb4;
	SET FOREIGN_KEY_CHECKS = 0;
	
	-- ----------------------------
	-- Table structure for admin_info
	-- ----------------------------
	DROP TABLE IF EXISTS `admin_info`;
	CREATE TABLE `admin_info`  (
	  `id` int(0) NOT NULL AUTO_INCREMENT,
	  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
	  `pwd` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
	  PRIMARY KEY (`id`) USING BTREE,
	  CONSTRAINT `admin_info_ibfk_1` FOREIGN KEY (`id`) REFERENCES `admin_detail` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
	) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
	
	SET FOREIGN_KEY_CHECKS = 1;

5. Test one-to-one association mapping

Add the following to the test class to test the insert and query we just wrote

    @Test
    public void testAddAdminInfo() {
        SqlSession sqlSession= MybatisUtils.getSession();
        //Create AdminDetail object
        AdminDetail adminDetail = new AdminDetail("Changsha, Hunan", "Yanger egg");
        //Create AdminInfo object
        AdminInfo adminInfo = new AdminInfo("Yanger egg", "123456");
        //Insert a record into the data table admin? Detail
        sqlSession.insert("addAdminDetail", adminDetail);
        //Set AdminDetail object associated with AdminInfo object
        adminInfo.setAd(adminDetail);
        //Insert records into data table admin info
        sqlSession.insert("addAdminInfo", adminInfo);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testGetAdminInfoById(){
        SqlSession sqlSession= MybatisUtils.getSession();
        AdminInfo adminInfo = sqlSession.selectOne("getAdminInfo",4);
        System.out.println(adminInfo);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testGetAdminInfoById2(){
        SqlSession sqlSession= MybatisUtils.getSession();
        AdminInfo adminInfo = sqlSession.selectOne("getAdminInfo2",4);
        System.out.println(adminInfo);
        sqlSession.commit();
        sqlSession.close();
    }

Problems you may have

Problem 1: prompt missing constructor

Description:

Cause: org.apache.ibatis.executor.ExecutorException: No constructor found in com.lomtom.mybatis.entity.AdminInfo matching [java.lang.Integer, java.lang.String, java.lang.String, java.lang.String, java.lang.String]

Analysis: we clearly use lombok, why do we still prompt for the lack of constructors? That's because we use complex bean s here, lombok can't recognize it by itself
Solution: create empty constructors for AdminDetail.java and AdminInfo.java

Write at the end

Attention to the official account: Boao Si Yuan, wonderful content is not bad.

Your support is the author's greatest motivation