Video link: https://www.bilibili.com/video/BV1VP4y1c7j7?p=48&spm_id_from=pageDriver

preparation

(1) New maven project

See the following steps for details: https://editor.csdn.net/md/?articleId=123185097

(2) Create two forms in sql: t_emp,t_dept

t_dept form:

t_emp form:

Add a did to connect with the primary key of the dept form.

Insert some test data:

(3) Create mapper, pojo and mapping files

mapper interface

public interface EmpMapper {
}

public interface DeptMapper {
}

pojo

Dept pojo:

package com.atguigu.mybatis.pojo;

public class Dept {
    private Integer did;
    private String deptName;

    @Override
    public String toString() {
        return "Dept{" +
                "did=" + did +
                ", deptName='" + deptName + '\'' +
                '}';
    }

    public Integer getDid() {
        return did;
    }

    public void setDid(Integer did) {
        this.did = did;
    }

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    public Dept() {
    }

    public Dept(Integer did, String deptName) {
        this.did = did;
        this.deptName = deptName;
    }
}

Emp pojo :

package com.atguigu.mybatis.pojo;

/**
 * @author Chen Ruoyi
 * @date 2022/3/1 9:07 PM
 */
public class Emp {
    private Integer eid;
    private String empName;
    private Integer age;
    private String sex;
    private String email;

    public Emp() {
    }

    public Emp(Integer eid, String empName, Integer age, String sex, String email) {
        this.eid = eid;
        this.empName = empName;
        this.age = age;
        this.sex = sex;
        this.email = email;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "eid=" + eid +
                ", empName='" + empName + '\'' +
                ", age=" + age +
                ", sex='" + sex + '\'' +
                ", email='" + email + '\'' +
                '}';
    }

    public Integer getEid() {
        return eid;
    }

    public void setEid(Integer eid) {
        this.eid = eid;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

xxxMapper.xml

DeptMapper.xml

<?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="com.atguigu.mybatis.mapper.DeptMapper">

</mapper>

EmpMapper.xml

<?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="com.atguigu.mybatis.mapper.EmpMapper">

</mapper>

1. 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 complies with the rules of the database (use), Attribute names in entity classes conform to Java rules (using humps)

At this time, the mapping relationship between the field name and the attribute in the entity class can also be handled in the following two ways

• a> You can alias the field to ensure consistency with the attribute name in the entity class
• b> You can set a global configuration information mapUnderscoreToCamelCase in the core configuration file of MyBatis, which can be automatically updated when querying the data in the table_ The field name of type is converted to hump
  For example: field name user_name, mapUnderscoreToCamelCase is set, and the field name will be converted to userName

1.1 use alias to ensure that the field name is consistent with the attribute name

As in sql, use field name and attribute name (such as emp_name empName) to make them consistent.

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

1.2 set the mapping relationship of resultMap one by one

In resultMap, set attribute name - > field name one by one, and then add resultMap = "id of corresponding resultMap" in the select tab

<!--
        resultMap Set custom mapping relationship
        id      Unique identification
        type    Mapped entity type

        Sub label: id Set the mapping relationship of the primary key, result Set other mapping relationships
            property    Set the attribute name in the mapping relationship, which must be type The property name of the entity class type set by the property
            column      Set the field name in the mapping relationship, which must be sql Field name queried by statement

        If used resultMap，All properties 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="getAllEmp" resultMap="empResultMap">
        select * from t_emp
    </select>

1.3 configuring mapUnderscoreToCamelCase

In the global configuration file mybatis config XML, add the following attribute configuration information:

<!--    set up MyBatis Full play configuration-->
    <settings>
<!--        Automatically map underscores to humps, such as emp_name -> empName -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

2. Many to one mapping

For example, query employee information and the Department information corresponding to the employee

⚠️ To query one to many and many to one relationships, you need to add the list < many > attribute to the pojo of "one" and "one" to the pojo of "many".
⚠️ That is, in the Dept class, add private list < Emp > EMPs;; In the Emp class, add private Dept dept Dept;. Then add get and set methods to each of them, and rewrite the constructor and toString()

2.1 cascade processing of mapping relationship

EmpMapper. In 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>

In EmpMapper class

public interface EmpMapper {
    /**
     * Query employee and its corresponding department information
     */
    Emp getEmpAndDept(@Param("eid") Integer eid);
}

In the test class:

    /**
     * Handle many to one mapping
     * 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(3);
        System.out.println(empAndDept);
    }

2.2 using association to handle mapping 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>

2.3 step by step query

(1) Query employee information

In the EmpMapper class:

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

EmpMapper.xml

<!--    com.atguigu.mybatis.mapper.DeptMapper.getEmpAndDeptByStepTwo It's this one sql The full class name of the 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: Step by step query settings sql Unique identification of( namespace.SQLId or mapper Full class name of the interface.Method name)
        column: Conditions for step-by-step query
        fetchType: When global delay logging is enabled, you can manually control the effect of delayed loading through this attribute
        fetchType: "lazy/eager" lazy Indicates delayed loading, eager Indicates immediate loading
-->
        <association property="dept"
                     select="com.atguigu.mybatis.mapper.DeptMapper.getEmpAndDeptByStepTwo"
                     column="did"
                     fetchType="eager">
        </association>
    </resultMap>
    
<!--    Emp getEmpAndDeptByStepOne(@Param("eid") Integer eid);-->
    <select id="getEmpAndDeptByStepOne" resultMap="getEmpAndDeptByStepResultMap">
        select * from t_emp where eid = #{eid}
    </select>

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

DeptMapper interface:

public interface DeptMapper {
	/**
	* Step 2 of 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 realize lazy loading-->
    <select id="getEmpAndDeptByStepTwo" resultType="Dept">
        select * from t_dept where did = #{did}
    </select>

⭕ Delayed loading

Advantages of step-by-step query: delayed loading can be realized, but the global configuration information must be set in the core configuration file:

• Lazyloading enabled: global switch for deferred loading. When on, all associated objects are loaded late
• aggressiveLazyLoading: when on, any method call will load all the properties of the object. Otherwise, each attribute is loaded on demand

At this point, on-demand loading can be realized. What data is obtained, only the corresponding sql will be executed. At this time, you can set whether the current step-by-step query uses deferred loading through the fetchType attribute in association and collection. fetchType = "lazy | eager"

Through the fetchType parameter, you can manually control delayed loading or immediate loading. Otherwise, you can decide whether to delay loading or immediate loading according to the properties of the global configuration.

3. One to many mapping relationship

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 < many > attribute to the pojo of "one" and "one" to the pojo of "many".
⚠️ That is, in the Dept class, add private list < Emp > EMPs;; In the Emp class, add private Dept dept Dept;. Then add get and set methods to each of them, and rewrite the constructor and toString()

3.1 collection

DeptMapper interface

public interface DeptMapper {
    /**
     * Obtain the information of the Department and all employees 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
            ofType: Indicates the type of data stored in the collection corresponding to this 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

    @Test
    public void testGetDeptAndEmp(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        Dept dept = mapper.getDeptAndEmp(1);
        System.out.println(dept);
    }

3.2 step by step query

(1) Query department information

DeptMapper interface

public interface DeptMapper {
    /**
     * Query the information of the Department and all its employees step by step
     * Step 1: 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.atguigu.mybatis.mapper.EmpMapper.getDeptAndEmpByStepTwo"
                    column="did">
        </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 {
    /**
     * Query the information of the Department and all its employees step by step
     * Step 1: query department information
     * Step 2: query employee information according to
     */
    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 testGetDeptAndEmpBySteps(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        Dept dept = mapper.getDeptAndEmoByStepOne(2);
        System.out.println(dept.getDeptName());
        System.out.println("-----****************======Split line=======-----****************");
        System.out.println(dept);
    }