Mybatis select record encapsulation

Time:2021-6-10

Select record encapsulation

  • Returns a list collection. Resulttype writes the type of the elements in the collection
<!-- public List<Employee> getEmpsByLastNameLike(String lastName); -->
<!-- Resulttype: if a collection is returned, write the type of the elements in the collection -- >
<select id="getEmpsByLastNameLike" resultType="com.atguigu.mybatis.bean.Employee">
    select * from tbl_employee where last_name like #{lastName}
</select>
  • Returns the map of a record, the key is the column name, and the value is the corresponding value
<!--public Map<String, Object> getEmpByIdReturnMap(Integer id);  -->
<select id="getEmpByIdReturnMap" resultType="map">
    select * from tbl_employee where id=#{id}
</select>
  • Multiple records are encapsulated into a map. The key is the ID and the value is the JavaBean after the record is encapsulated
//@Mapkey: tell mybatis which attribute to use as the key of the map when encapsulating the map
@MapKey("lastName")
public Map<String, Employee> getEmpByLastNameLikeReturnMap(String lastName);
<!--public Map<Integer, Employee> getEmpByLastNameLikeReturnMap(String lastName);  -->
<select id="getEmpByLastNameLikeReturnMap" resultType="com.atguigu.mybatis.bean.Employee">
    select * from tbl_employee where last_name like #{lastName}
</select>

Automatic mapping configuration

  • Global setting
  1. Automappingbehavior is partial by default, which enables the automatic mapping function; The only requirement is that the column name and JavaBean attribute name are consistent

2. Mapunderscoretocamelcase = true, enable automatic hump naming specification mapping function

  1. Customize resultmap to realize advanced mapping function

Resultmap custom mapping rules

<!-- Customize the encapsulation rules of a JavaBean
    Type: the Java type of the custom rule
    ID: unique ID for easy reference
      -->
    <resultMap type="com.atguigu.mybatis.bean.Employee" id="MySimpleEmp">
        <!-- Specifies the encapsulation rule for the primary key column
        The primary key of ID definition will be optimized;
        Column: specifies which column
        Property: Specifies the corresponding JavaBean property
          -->
        <id column="id" property="id"/>
        <!--  Define common column encapsulation rules -- >
        <result column="last_name" property="lastName"/>
        <!--  Other unspecified columns will be encapsulated automatically: as long as we write resultmap, we will write all the mapping rules
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
    </resultMap>
  • Create table
create table tb_dept (

id int(11) primary key auto_increment,

dept_name varchar(255)

)
  • Add column

    alter table tb_emp add column d_id int(11);
  • Add constraints

    alter table tb_emp add constraint fk_emp_dept foreign key(d_id) references tb_dept(id);

Federated query: cascading attributes encapsulating result sets

Scene 1:
When querying employee, the corresponding department of the employee is also queried; An employee has corresponding department information;

<!-- 
    Scene 1:
        When you query employee, you can also query the Department corresponding to the employee
        Employee===Department
        An employee has corresponding department information;
        id  last_name  gender    d_id     did  dept_name (private Department dept;)
     -->
     
     
    <!--
        Federated query: cascading attributes encapsulating result sets
      -->
    <resultMap type="com.atguigu.mybatis.bean.Employee" id="MyDifEmp">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="did" property="dept.id"/>
        <result column="dept_name" property="dept.departmentName"/>
    </resultMap>

Association is used to define the encapsulation rules of an associated single object;

<!-- 
        Association is used to define the encapsulation rules of an associated single object;
     -->
    <resultMap type="com.atguigu.mybatis.bean.Employee" id="MyDifEmp2">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        
        <!--   Association specifies the associated JavaBean object
        Property = "dept": specifies which property is a federated object
        Javatype: Specifies the type of this property object [cannot be omitted]
        -->
        <association property="dept" javaType="com.atguigu.mybatis.bean.Department">
            <id column="did" property="id"/>
            <result column="dept_name" property="departmentName"/>
        </association>
    </resultMap>

Association step by step query

<!--  Use association to query step by step
        1. First, query employee information according to employee ID
        2. According to D in employee information query_ The ID value goes to the Department table to find out the Department information
        3. Departments are set up among employees;
     -->
     
     <!--  id  last_name  email   gender    d_id   -->
     <resultMap type="com.atguigu.mybatis.bean.Employee" id="MyEmpByStep">
         <id column="id" property="id"/>
         <result column="last_name" property="lastName"/>
         <result column="email" property="email"/>
         <result column="gender" property="gender"/>
         <!--  Association defines encapsulation rules for associated objects
             Select: indicates that the current property is the result of calling the method specified by select
             Column: specifies which column value to pass to this method
             
             Process: use the method specified by select (pass in the value of the column parameter specified by column) to find out the object and encapsulate it to the property specified by property
          -->
         <association property="dept" 
             select="com.atguigu.mybatis.dao.DepartmentMapper.getDeptById"
             column="d_id">
         </association>
     </resultMap>

<!-- DepartmentMapper.xml -->
<mapper namespace="com.atguigu.mybatis.dao.DepartmentMapper">
    <!--public Department getDeptById(Integer id);  -->
    <select id="getDeptById" resultType="com.atguigu.mybatis.bean.Department">
        select id,dept_name departmentName from tbl_dept where id=#{id}
    </select>

Association step by step Query & delay loading

<!--  Delay loading (lazy loading) can be used( Load on demand)
         Employee==>Dept:
             Every time we query the employee object, we will find it together.
             Department information can be queried when we use it;
             Two configurations are added on the basis of segmented query
      -->
    <!-- ==================association============================ -->
    
    <!-- mybatis-config.xml-->
    <!-- The display specifies the value of each configuration that we need to change, even if it is the default. To prevent problems caused by version update -- >
        <setting name="lazyLoadingEnabled" value="true"/>
        <!--  value:false  Indicates on-demand loading; Otherwise, it will always load -- >
        <setting name="aggressiveLazyLoading" value="false"/>

Association set

Nested result sets, using the collection tag to define the attribute encapsulation rules of the associated collection type

Scene 2:
When querying a department, all the employee information corresponding to the Department is also queried: the comments are in departmentmapper.xml

<!-- 
    public class Department {
            private Integer id;
            private String departmentName;
            private List<Employee> emps;
      did  dept_name  ||  eid  last_name  email   gender  
     -->
     
    <!-- Nested result sets, using the collection tag to define the attribute encapsulation rules of the associated collection type -- >
    <resultMap type="com.atguigu.mybatis.bean.Department" id="MyDept">
        <id column="did" property="id"/>
        <result column="dept_name" property="departmentName"/>
        <!-- 
            Collection defines the encapsulation rules for the properties of the associated collection type 
            Of type: Specifies the type of the elements in the collection
        -->
        <collection property="emps" ofType="com.atguigu.mybatis.bean.Employee">
            <!--  Define the encapsulation rules for elements in this collection -- >
            <id column="eid" property="id"/>
            <result column="last_name" property="lastName"/>
            <result column="email" property="email"/>
            <result column="gender" property="gender"/>
        </collection>
    </resultMap>
    <!-- public Department getDeptByIdPlus(Integer id); -->
    <select id="getDeptByIdPlus" resultMap="MyDept">
        SELECT d.id did,d.dept_name dept_name,
                e.id eid,e.last_name last_name,e.email email,e.gender gender
        FROM tbl_dept d
        LEFT JOIN tbl_employee e
        ON d.id=e.d_id
        WHERE d.id=#{id}
    </select>

Collection: segmented query

<!--  Collection: segmented query -- >
    <resultMap type="com.atguigu.mybatis.bean.Department" id="MyDeptStep">
        <id column="id" property="id"/>
        <result column="dept_name" property="departmentName"/>
        <collection property="emps" 
            select="com.atguigu.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId"
            column="{deptId=id}" fetchType="lazy"></collection>
    </resultMap>

    
    <!--  Extension: values of multiple columns are passed in the past:
            The values of multiple columns are encapsulated and passed through map;
            column="{key1=column1,key2=column2}"
        Fetchtype = "lazy": indicates to use delay loading;
                -Lazy: delay
                -Eager: immediately

Discriminator

Mybatis can use discriminator to determine the value of a column, and then change the encapsulation behavior according to the value of a column

Encapsulate employee:
        If it is found out that it is a girl, the information of the Department will be found out, otherwise it will not be found out;
        If it's a boy, put last_ The value of the name column is assigned to email;
<!-- ======================= Discriminator
    
     <resultMap type="com.atguigu.mybatis.bean.Employee" id="MyEmpDis">
         <id column="id" property="id"/>
         <result column="last_name" property="lastName"/>
         <result column="email" property="email"/>
         <result column="gender" property="gender"/>
         <!--
             Column: Specifies the column name of the decision
             Javatype: the Java type corresponding to the column value -- >
         <discriminator javaType="string" column="gender">
             <!-- Resulttype: Specifies the encapsulated result type; We can't lack it/ resultMap-->
             <case value="0" resultType="com.atguigu.mybatis.bean.Employee">
                 <association property="dept" 
                     select="com.atguigu.mybatis.dao.DepartmentMapper.getDeptById"
                     column="d_id">
                 </association>
             </case>
             <!-- schoolboy; If it's a boy, put last_ The value of the name column is assigned to email; -- >
             <case value="1" resultType="com.atguigu.mybatis.bean.Employee">
                 <id column="id" property="id"/>
                 <result column="last_name" property="lastName"/>
                 <result column="last_name" property="email"/>
                 <result column="gender" property="gender"/>
             </case>
         </discriminator>
     </resultMap>