Mapper auto mapping

In mybatis, there are three modes of automatic mapping:

  • None means that automatic mapping is not enabled
  • Partial means that only non nested resultmaps are automatically mapped
  • Full means that all resultmaps are automatically mapped

The default automatic mapping mode is partial.
In the mybatis global configuration file, set the automatic mapping mode in the Setting tab

<! -- global configuration parameters -- >
<settings>
    <setting name="autoMappingBehavior" value="PARTIAL" />
</settings>

If you do not want to use automatic mapping in some resultmaps, you can set the automapping property to false in the resultmap

<select id="findUserById" parameterType="Long" resultMap="UserResult" autoMapping="false">
    select id,name,email from t_user where id=#{id}
</select>

Here, the automapping attribute ignores the “outomapping behavior” mapping pattern in the global configuration file.

If the Java wrapper class uses hump naming rules, the mapunderscoretocamelcase property should be enabled, and the automatic mapping mechanism can automatically map the field names of non hump naming methods found by SQL query with the attributes in the Java wrapper class

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

Mapper configures dynamic SQL statements

Select different SQL statements according to some query conditions:

<select id="findUserList" parameterType="cn.com.mybatis.po.UserQueryVo" resultType="cn.com.mybatis.po.User">
    select * from user
    <where>
        <if test="UserQuertVo != null">
            <if test="UserQueryVo.gender != null and UserQueryVo.gender !=''">
                and user.sex=#{UserQueryVo.gender}
            </if>
            <if test="UserQueryVo.username != null and UserQueryVo.username != ''">
                and user.username like '%${UserQueryVo.username}%'
            </if>
        </if>
     </where>
</select>

When the “< where >” tag is used to wrap the if conditional statement, the first “and” in the query condition will be ignored.

Encapsulate SQL statements with strong reusability into SQL fragments, but dynamic SQL statements are not supported in SQL fragments<where>label:

<sql id="queryUserWhere">
    <! -- SQL statements to be reused -- >
</sql>

Reference SQL mapping configuration:

<select id="findUserList" parameterType="cn.com.mybatis.po.UserQueryVo" resultType="cn.com.mybatis.po.User">
    select * from user
    <where>
        <include refid="queryUserWhere"></include>
        <! -- other SQL fragments may also be referenced here -- >
     </where>
</select>

In addition to its own mapper file, each SQL mapping configuration can also refer to the SQL fragment in the external mapper file. Just fill in the ID name of the SQL fragment in the refid attribute and add the namespace information of the mapper file.

Sometimes a query statement may contain multiple query information, such as user users who query multiple IDS:

select * from user where id in (2.4.6)

At this point, if you want to configure such statements in mapper file, you can define a Java wrapper class whose attribute is a list collection containing multiple ID information

public class UserQueryVo{
    Private list < integer > IDS; // contains multiple ID information
    public List<Integer> getIds(){
        return ids;
    }
    public void setIds(List<Integer> ids){
        this.ids = ids;
    }
}

Configure a SQL fragment in mapper and introduce it into the query SQL map

<sql id="queryUserWhere">
    <if test="ids!=null">
        <foreach collection="ids" item="user_id" open="and id in (" close=")" separator=",">
        #{user_id}
        </foreach>
    </if>
</sql> 

<select id="findUserList" parameterType="cn.com.mybatis.po.UserQueryVo" resultType="cn.com.mybatis.po.User">
    select * from user
    <where>
        <include refid="query_user_where"></include>
    </where>
</select>

The “and” in the SQL fragment is used to splice statements with one or more query conditions. When this statement is the first query condition, the first “and” will be masked because of the existence of “< where >”.

One to one query

When implementing one-to-one queries, resulttype is recommended.
When you use resultmap, you need to define a resultmap for the data output from the mapping. The process is a little cumbersome. You can use it if you have special requirements for query results (for example, JavaBeans contain other JavaBeans).

Using resulttype to implement

For example, query the information of a purchase batch and the user who created the batch. A batch corresponds to a user. The batch table is named batch.
First, create the Java entity class batch corresponding to batch table batch

package cn.com.mybatis.po;  
  
import java.util.Date;  
import java.util.List;  
public class Batch {  
    private int batch_id;  
    private int cus_id;  
    private String number;  
    private Date createtime;  
    private String note;    
    public int getBatch_id() {  
        return batch_id;  
    }  
    public void setBatch_id(int batch_id) {  
        this.batch_id = batch_id;  
    }  
    //Set and other methods of omitting get
}

Create a parent class with batch as the parent class, and then append the user information:

package cn.com.mybatis.po;  
  
public class BatchCustomer extends Batch {  
    private String username;  
    private String acno;  
    public String getUsername() {  
        return username;  
    }  
    public void setUsername(String username) {  
        this.username = username;  
    }  
    public String getAcno() {  
        return acno;  
    }  
    public void setAcno(String acno) {  
        this.acno = acno;  
    }  
  
}

And then in the UserMapper.xml In the mapping file, query statements of type select are defined

<select id="findBatchCustomer" resultType="cn.com.mybatis.po.BatchCustomer">  
    SELECT  
    batch.*,customer.username,customer.acno  
    FROM batch INNER JOIN customer  
    ON batch.cus_id = customer.cus_id 
</select>

Write test method:

@Test  
public void testBatchCustomer() throws Exception{  
    SqlSession sqlSession = dataConnection.getSqlSession();  
    List<BatchCustomer> batchCustomerList = sqlSession.selectList("test.findBatchCustomer");  
    if(batchCustomerList != null){  
        BatchCustomer batchCustomer = null;  
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
        for (int i=0;i<batchCustomerList.size();i++){  
            batchCustomer = batchCustomerList.get(i);  
            System.out.println ("card number is"+ batchCustomer.getAcno () + "is named"+ batchCustomer.getUsername () + ": \ \ n in"+ sdf.format ( batchCustomer.getCreatetime ()) + "purchased batch No+ batchCustomer.getNumber () + ";  
        }  
    }  
    sqlSession.close();  
}

Using resultmap to implement

Use resultmap to map other entity classes wrapped in an entity class.
Create a batchitem batch class that encapsulates batch attributes and customer entity classes

package cn.com.mybatis.po;  
  
import java.util.Date;  
import java.util.List;  
public class BatchItem {  
    private int batch_id;  
    private int cus_id;  
    private String number;  
    private Date createtime;  
    private String note;  
    private Customer customer;   
    public int getBatch_id() {  
        return batch_id;  
    }  
    public void setBatch_id(int batch_id) {  
        this.batch_id = batch_id;  
    }  
}

SQL mapping file:

<! -- one to one query, using resultmap to implement -- >  
<resultMap id="BatchInfoMap" type="cn.com.mybatis.po.BatchItem">  
    <id column="batch_id" property="batch_id"/>  
    <result column="cus_id" property="cus_id"/>  
    <result column="number" property="number"/>  
    <result column="createtime" property="createtime" javaType="Date"/>  
    <result column="note" property="note"/>  
    <association property="customer" javaType="cn.com.mybatis.po.Customer">  
        <id column="cus_id" property="cus_id"/>  
        <result column="username" property="username"/>  
        <result column="acno" property="acno"/>  
        <result column="gender" property="gender"/>  
        <result column="phone" property="phone"/>  
    </association>  
</resultMap>  
  
<select id="findBatchCustomerToMap" resultMap="BatchInfoMap">  
    SELECT  
    batch.*,customer.username,customer.acno  
    FROM batch INNER JOIN customer  
    ON batch.cus_id = customer.cus_id 
</select>

Write test class:

@Test  
public void testBatchCustomerToMap() throws Exception{  
    SqlSession sqlSession = dataConnection.getSqlSession();  
    List<BatchItem> batchItemList = sqlSession.selectList("test.findBatchCustomerToMap");  
    if(batchItemList != null){  
        BatchItem batchItem = null;  
        Customer customer = null;  
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
        for (int i=0;i<batchItemList.size();i++){  
            batchItem = batchItemList.get(i);  
            customer = batchItem.getCustomer();  
            System.out.println ("card number is"+ customer.getAcno () + "is named"+ customer.getUsername () + ": \ \ n in"+ sdf.format ( batchItem.getCreatetime ()) + "purchased batch No+ batchItem.getNumber () + ";  
        }  
    }  
}

One to many query

For example, query the information of a user in a batch and what financial products are available.
Modify the batch class:

package cn.com.mybatis.po;  
  
import java.util.Date;  
import java.util.List;  
public class Batch {  
    private int batch_id;  
    private int cus_id;  
    private String number;  
    private Date createtime;  
    private String note;  
    //Ordering information of financial products contained in the batch
    private List<BatchDetail> batchDetials;  
    //Get and set methods omitted
}

The ordering information entities of financial products are as follows:

package cn.com.mybatis.po;  
  
import java.util.List;  
  
public class BatchDetail {  
    private int id;  
    private int batch_id;  
    private int product_id;  
    private int product_num;  
    private FinacialProduct finacialProduct;  
    //Get and set methods omitted 
}

Write the SQL configuration and use the extends tag to inherit the resultmap named batchinfomap in the above one-to-one query

<! -- one to many -- >  
<resultMap  
  id="BatchAndBatchDetailResultMap" type="cn.com.mybatis.po.BatchItem" extends="BatchInfoMap">  
    <collection  
  property="batchDetails" ofType="cn.com.mybatis.po.BatchDetail">  
        <! -- ID: unique identification of order details -- >  
  <id column="id" property="id"/>  
        <result column="batch_id" property="batch_id"/>  
        <result column="product_id" property="product_id"/> 
        <result column="product_num" property="product_num"/>  
    </collection>  
</resultMap>  
  
<select id="findBatchAndBatchDetail" resultMap="BatchAndBatchDetailResultMap">  
    SELECT  
        batch.*,  
        customer.username,customer.acno,  
        batchdetail.product_id,  
        batchdetail.product_num  
    FROM ((batch  
    INNER JOIN customer  
    ON batch.cus_id = customer.cus_id)  
    INNER JOIN batchdetail  
    ON batch.batch_id = batchdetail.batch_id)  
</select>

Test query results:

@Test  
public void testfindBatchAndBatchDetail() throws Exception{  
  
    SqlSession sqlSession=dataConnection.getSqlSession();  
    //Call the method of usermapper  
  BatchItem batchItem=  
            sqlSession.selectOne(  
                    "test" +  
                            ".findBatchAndBatchDetail");  
    if(batchItem!=null){  
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
        Customer customer =  batchItem.getCustomer (); // retrieve the user information of the batch  
  //Take out the information of financial products ordered in this batch  
  List<BatchDetail> batchDetails = batchItem.getBatchDetails();  
        System.out.println ("card number is"+ customer.getAcno () + "is named"  
  + customer.getUsername () + ": \ \ n in"  
  + sdf.format ( batchItem.getCreatetime ()) + "purchased batch No  
  + batchItem.getNumber () + ", the details of which are as follows:);  
        BatchDetail batchDetail = null;  
        if(batchDetails!=null){  
            for (int i = 0; i < batchDetails.size(); i++) {  
                batchDetail = batchDetails.get(i);  
                System.out.println ("ID is"+ batchDetail.getProduct_ id()  
                        +"Financial products"+ batchDetail.getProduct_ Num () + "share");  
            }  
        }  
    }  
    sqlSession.close();  
}

Association and collocation are used here, one to map a single entity object and one to map a collection object.

Many to many queries

Query all users and the detailed information of all financial products in the corresponding batch orders of users.
The modified customer user class contains user information and all batch information under the user

package cn.com.mybatis.po;  
  
import java.io.Serializable;  
import java.util.List;  
  
public class Customer implements Serializable{  
    private int cus_id;  
    private String username;  
    private String acno;  
    private String gender;  
    private String phone;  
    private List<Batch> batchList;  
    //Get and set methods omitted
}

The modified batch batch information class contains single batch information and batch details list

package cn.com.mybatis.po;  
  
import java.util.Date;  
import java.util.List;  
public class Batch {  
    private int batch_id;  
    private int cus_id;  
    private String number;  
    private Date createtime;  
    private String note;  
    private List<BatchDetail> batchDetials; 
    //Get and set methods omitted
}

The modified batch detail class contains a single batch detail and the corresponding financial product reference:

package cn.com.mybatis.po;  
  
import java.util.List;  
  
public class BatchDetail {  
    private int id;  
    private int batch_id;  
    private int product_id;  
    private int product_num;  
    private FinacialProduct  finacialProduct;  
    //Get and set methods omitted
}

The newly added finacialproduct product category includes various attributes of financial products:

package cn.com.mybatis.po;  
  
import java.util.Date;  
  
public class FinacialProduct {  
    private int id;  
    private String name;  
    private double price;  
    private String detail;  
    private String imgpath;  
    private Date invattime;  
    //Get and set methods omitted
  
}

Write mapper mapping file:

<resultMap  
  id="UserAndProductsResultMap" type="cn.com.mybatis.po.Customer">  
      <! -- customer information -- >  
  <result column="username" property="username"/>  
      <result column="acno" property="acno"/>  
  
      <! -- batch order information, one customer corresponds to multiple orders -- >  
  <collection property="batchList" ofType="cn.com.mybatis.po.Batch">  
          <id column="batch_id" property="batch_id"/>  
          <result column="cus_id" property="cus_id"/>  
          <result column="number" property="number"/>  
          <result column="createtime" property="createtime" javaType="java.util.Date"/>  
          <result column="note" property="note"/>  
  
          <collection property="batchDetials" ofType="cn.com.mybatis.po.BatchDetail">  
              <! -- ID: unique identification of order details -- >  
  <id column="id" property="id"/>  
              <result column="batch_id" property="batch_id"/>  
              <result column="product_id" property="product_id"/>  
              <result column="product_num" property="product_num"/>  
  
              <association property="finacialProduct" javaType="cn.com.mybatis.po.FinacialProduct">  
                  <id column="product_id" property="id"/>  
                  <result column="name" property="name"/>  
                  <result column="price" property="price"/>  
                  <result column="detail" property="detail"/>  
              </association>  
          </collection>  
      </collection>  
  </resultMap>  
  <select id="findUserAndProducts" resultMap="UserAndProductsResultMap">  
      SELECT  
  BATCH.*,  
  CUSTOMER.username,  
  CUSTOMER.acno,  
  BATCHDETAIL.product_id,  
  BATCHDETAIL.product_num,  
  FINACIAL_PRODUCTS.name,  
  FINACIAL_PRODUCTS.detail,  
  FINACIAL_PRODUCTS.price  
FROM  
  BATCH,  
  CUSTOMER,  
  BATCHDETAIL,  
  FINACIAL_PRODUCTS  
WHERE BATCH.cus_id = CUSTOMER.cus_id  
AND BATCHDETAIL.batch_id=BATCH.batch_id  
AND FINACIAL_PRODUCTS.product_id=BATCHDETAIL.product_id;  
  </select>

Delay loading

In mybatis, multi table joint queries are usually performed, but sometimes not all the union query results are used immediately. This “on-demand query” mechanism can be implemented by using delayed loading.

Turn on load delay function:

//Configure the setting property in the global configuration file
<configuration>
    <settings>
        <! -- turn on the delay load switch -- >
        <setting name="lazyLoadingEnable" value="true" />
        <! -- change active load to on demand load -- >
        <setting name="aggressiveLazyLoading" value="false" />
    </settings>
</configuration>

For example, query the information of all batch orders, and each batch order will be associated with the query user, and the user information will be delayed to load. First, define the SQL configuration that only queries the information of all batch orders

<select id="findBatchUserLazyLoading" resultMap="BatchUserLazyLoadingResultMap">
    select * from batch
</select>

<! -- delayed loading resultmap -- >
<resultMap id="BatchUserLazyLoadingResultMap" type="cn.com.mybatis.po.BatchItem">
    <! -- map and configure the order information -- >
    <id column="batch_id" property="batch_id" />
    <result column="cus_id" property="cus_id" />
    <result column="number" property="number" />
    <result column="createtime" property="createtime" javaType="java.util.Date" />
    <result column="note" property="note" />
    <! -- delay loading user information -- >
    <association property="customer" javaType="cn.com.mybatis.po.Customer" select="findCustomerById" column="cus_id">
    </association>
</resultMap>

Where select is used to specify Mapper.xml The ID of a select tag pair in the configuration file.
Column refers to the column associated with user information query in order information.

Finally, configure to delay loading the SQL to be executed to obtain user information

<select id="findCustomerById" parameterType="int" resultType="cn.com.mybatis.po.Customer">
    select * from customer where cus_id=#{id}
</select>

The input parameter is the field information defined in the column of association.

Write test method:

@Test  
    public void testFindBatchCustomerLazyLoading() throws Exception{  
          
        SqlSession sqlSession=dataConn.getSqlSession();
        //Call the method of usermapper to get all order information (the associated user information is not loaded)  
          List<BatchItem> batchItemList=sqlSession.selectList("findBatchUserLazyLoading"); 
          BatchItem batchItem = null;
          Customer customer = null;
        for (int i = 0; i < batchItemList.size(); i++) {  
            batchItem = batchItemList.get(i);  
            System.out.println ("Order No.:"+ batchItem.getNumber ());
            //Only when getcustomer is executed will the user information be queried. Here, delayed loading is implemented  
            customer=batchItem.getCustomer();  
            System.out.println (subscriber name:+ customer.getUsername ());  
        }  
        sqlSession.close();
    }

To sum up, using the delayed loading method, first execute a simple query SQL (it is better to query a single table, or associated query), and then load other information of the associated query as needed.

Mapper dynamic proxy

Add a new one CustomerMapper.xml For example, the SQL configuration for adding, deleting, modifying, and querying customer is as follows:

<?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="cn.com.mybatis.mapper.CustomerMapper">    
    <! -- Query users -- >
    <select id="findCustomerById" parameterType="int" resultType="cn.com.mybatis.po.Customer">  
      SELECT * FROM CUSTOMER WHERE cus_id=#{cus_id}
    </select>  
    <! -- new users -- >   
    <insert id="insertCustomer" parameterType="cn.com.mybatis.po.Customer">    
        INSERT INTO CUSTOMER(username,acno,gender,phone) 
            value(#{username},#{acno},#{gender},#{phone})  
    </insert>
    <! -- delete user -- >  
    <delete id="deleteCustomer" parameterType="java.lang.Integer">  
        DELETE FROM CUSTOMER WHERE cus_id=#{cus_id}
    </delete>  
    <! -- modify user -- >  
    <update id="updateCustomerAcNo" parameterType="cn.com.mybatis.po.Customer" >
        UPDATE CUSTOMER SET acno = #{acno} WHERE cus_id=#{cus_id}
    </update>
</mapper>

Where the path in the namespace is the path of the mapper proxy interface to be created.

use CustomerMapper.xml To create the customermapper interface:

package cn.com.mybatis.mapper;

import cn.com.mybatis.po.Customer;
public interface CustomerMapper {

    public Customer findCustomerById(int id) throws Exception;  
      
    public void insertCustomer(Customer customer) throws Exception;  
      
    public void deleteCustomer(int id) throws Exception;  
      
    public void updateCustomerAcNo(Customer customer) throws Exception;  
}

Test the dynamic proxy effect:

@Test  
    public void testFindCustomerOnMapper() throws Exception{  
        SqlSession sqlSession=dataConn.getSqlSession();    
          
        //Get mapper proxy  
        CustomerMapper customerMapper=sqlSession.getMapper(CustomerMapper.class);
        //Query method for executing mapper proxy object
        Customer customer=customerMapper.findCustomerById(1);
        System.out.println (user name:+ customer.getUsername ()+"|"
                +Card number:+ customer.getAcno ());
        sqlSession.close();
    }

The principle of getmapper method of sqlsession class is that according to the type of mapper proxy interface and Mapper.xml File content, create a mapper interface implementation class, which implements the specific add, delete, modify and query methods.