1. One-to-one

The common attributes of the

tag are as follows:

  • Property: Returns a type parameter, usually the same as the variable name of the entity class
  • Select: id of another mapping query that MyBatis will execute in addition to get the results of nested objects
  • Column: the column name (or alias name). The result of the column in the main query is used as the parameter of the nested query as follows: column={propl=coll, prop2=col2}, propl and prop2 are used as parameters of the nested query.
  • FetchType: indicates the data loading mode. The optional values are lazy and eager, which are lazy and active respectively

Create two solid tables

/ / the employees table
public class Employee {
    private Integer id;
    private String lastName;
    private String gender;
    private String email;
    private Department dept; // One-to-one, one employee for one company
}
/ / table
public class Department {
    private Integer id;
    private String departmentName;
}
Copy the code
  1. Use query 2 tables to associate object mapping
<resultMap type="com.mybatis.domain.Employee" id="myEmp">
    <id column="id" property="id"/>
    <result column="last_name" property="lastName"/>
    <result column="gender" property="gender"/>
    <result column="email" property="email"/ > <! -- Association object --> <association property="dept" javaType="com.mybatis.domain.Department"> <! Employee dept_id = Department ID --> <id column="dept_id" property="id"/>
        <result column="department_name" property="departmentName"/> </association> </resultMap> <! -- resultMap specifies the use of the result set defined above, the query uses the associated query, the query column must correspond to the column above --> <select ID ="getEmpAndDeptById" resultMap="myEmp">
    select e.id,e.last_name,e.gender,e.email,e.dept_id,d.id,d.department_name
     from tbl_employee e,tbl_department d
     where e.dept_id = d.id and e.id=#{id}
</select>
Copy the code
  1. In the nested query mode, the sub-query depends on the results returned by the upper-level query
/ / the corresponding DepartmentMapper
<select id="getDeptById" resultType="com.mybatis.domain.Department">
        select * from tbl_department
        where id = #{id}
</select>

/ / the corresponding EmployeeMapper
<mapper namespace="com.mybatis.mapper.EmployeeMapperPlus">
    <resultMap type="com.mybatis.domain.Employee" id="myEmpByStep">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/ > <! Use the select attribute to specify the method used in the second step, and specify the value of the parameter passed through column, which is the data of the first step query --> <association property="dept" 
            select="com.mybatis.mapper.DepartmentMapper.getDeptById"
            column="dept_id"> </association> </resultMap> <! Select * from tBL_employee; select * from tBL_employee"getEmpAndDeptByStep" resultMap="myEmpByStep">
        select id,last_name,gender,email,dept_id
         from tbl_employee
         where id = #{id}
    </select>
</mapper>
Copy the code

One to many

There are two ways to use the
tag

  1. High code reuse, correct main table paging query (nested query)

QuestionMapper.xml

<mapper namespace="com.xxx.modules.xxx.mapper.QuestionMapper">

<resultMap id="BaseResultMap" type="com.xxx.modules.xxx.entity.Question" >
    <id column="id" 		 property="id"      jdbcType="VARCHAR" />
    <result column="content" property="content" jdbcType="VARCHAR" />
    <result column="type"    property="type"    jdbcType="VARCHAR" />
    <result column="sort"    property="sort"    jdbcType="INTEGER" />
    <collection property="options" javaType="java.util.ArrayList" 
      ofType="com.xxx.modules.xxx.entity.QuestionOption"
      select="com.xxx.modules.xxx.mapper.QuestionOptionMapper.selectList" 
      column="{qid=id,sort=sort}"/ > <! Select * from javaType; select * from javaType; select * from javaType; select * from javaType; The value of select: namespace of the corresponding XML + ID of the code fragment in the corresponding XML. Column is passed as the parameter of the SELECT statement. If only one parameter ID is passed, it can be abbreviated as column="id"--> </resultMap> <! --> <select id="selectList" resultMap="BaseResultMap">
    SELECT pq.id, pq.content, pq.type, pq.sort
    FROM question AS pq
</select>
Copy the code

QuestionOptionMapper.xml

<mapper namespace="com.xxx.modules.xxx.mapper.QuestionOptionMapper"> <! --> <select id="selectList" resultType="QuestionOption"> SELECT pqo.id, pqo.content, pqo.sort FROM question_option AS pqo <where> pqo.qid = #{qid} <! -- variable name qid corresponds to qid --> <! If collection passes only one argument column="id"#{xyz} --> </where </select>Copy the code
  1. Only one SQL query needs to be executed, the main table paging query is not correct (join table query)

QuestionMapper.xml

<mapper namespace="com.xxx.modules.xxx.mapper.QuestionMapper">

<resultMap id="BaseResultMap" type="com.xxx.modules.xxx.entity.Question" >
    <id column="id" 		 property="id"      jdbcType="VARCHAR" />
    <result column="content" property="content" jdbcType="VARCHAR" />
    <result column="type"    property="type"    jdbcType="VARCHAR" />
    <result column="sort"    property="sort"    jdbcType="INTEGER" />
    <collection property="options" javaType="java.util.ArrayList" ofType="com.xxx.modules.data.entity.QuestionOption">
        <id column="o_id" property="id" jdbcType="VARCHAR" />
        <result column="o_content" property="content" jdbcType="VARCHAR" />
        <result column="o_sort" property="sort" jdbcType="INTEGER"/> </collection> <! -- column aliases o_id,o_content,o_sort -- column aliases o_id,o_content,o_sort --> <select id="selectList" resultMap="BaseResultMap"> SELECT pq.id, pq.content, pq.type, pq.sort ,pqo.id AS oid ,pqo.content AS ocontent ,pqo.sort AS osort <! FROM question AS pq LEFT JOIN question_option pqo ON pq.id = pqo.qid <! --> <where> </where> </select>Copy the code