0. Case environment

0.1 case data initialization sql

CREATE DATABASE /*!32312 IF NOT EXISTS*/`mybatis_db` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mybatis_db`;
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Creation time',
  `price` int(11) DEFAULT NULL COMMENT 'Price',
  `remark` varchar(100) DEFAULT NULL COMMENT 'remarks',
  `user_id` int(11) DEFAULT NULL COMMENT 'user id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
insert  into `orders`(`id`,`createtime`,`price`,`remark`,`user_id`) values (1,'2014-06-26 16:55:43',2000,'nothing',2),(2,'2021-02-23 16:55:57',3000,'nothing',3),(3,'2021-02-23 16:56:21',4000,'nothing',2);
DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL COMMENT 'Role name',
  `desc` varchar(100) DEFAULT NULL COMMENT 'Role description',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*Data for the table `role` */

insert  into `role`(`id`,`name`,`desc`) values (1,'general manager','Under one person'),(2,'CFO',NULL);

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

/*Data for the table `user` */

insert  into `user`(`id`,`username`,`age`,`address`) values (2,'pdd',26,NULL),(3,'UZI',19,'Shanghai 11'),(4,'RF',19,NULL);

/*Table structure for table `user_role` */

DROP TABLE IF EXISTS `user_role`;

CREATE TABLE `user_role` (
  `user_id` int(11) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `user_role` */

insert  into `user_role`(`user_id`,`role_id`) values (2,2),(2,1),(3,1);

0.2 entity class

0.2.1 User.java

public class User {

    private Integer id;

    private String username;

    private Integer age;

    private String address;


    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", age=" + age +
                ", address='" + address + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Integer getAge() {
        return age;
    }

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

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public User() {
    }

    public User(Integer id, String username, Integer age, String address) {
        this.id = id;
        this.username = username;
        this.age = age;
        this.address = address;
    }

}

0.2.2 Order.java

public class Order {
    private Integer id;
    private Date createtime;
    private Integer price;
    private String remark;
    private Integer userId;

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", createtime=" + createtime +
                ", price=" + price +
                ", remark='" + remark + '\'' +
                ", userId=" + userId +
                '}';
    }
  	public Order() {
    }
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public Integer getPrice() {
        return price;
    }

    public void setPrice(Integer price) {
        this.price = price;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public Order(Integer id, Date createtime, Integer price, String remark, Integer userId) {
        this.id = id;
        this.createtime = createtime;
        this.price = price;
        this.remark = remark;
        this.userId = userId;
    }
}

0.2.3 Role.java

public class Role {
    private Integer id;
    private String name;
    private String desc;

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", desc='" + desc + '\'' +
                '}';
    }
	public Role() {
    }
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDesc() {
        return desc;
    }

    public void setDesc(String desc) {
        this.desc = desc;
    }

    public Role(Integer id, String name, String desc) {
        this.id = id;
        this.name = name;
        this.desc = desc;
    }
}

1. ResultMap

1.1 basic use

We can use the resultMap tag to customize the mapping rules of result set and entity class attributes.

    <!--
        resultMap Used to customize the mapping of result sets and entity classes
            Properties:
                id Equivalent to this resultMap Unique identification of
                type Used to specify which entity class to map to
        id Label is used to specify the mapping rule of the primary key column
            Properties:
                property Attribute name to map
                column  Corresponding column name
        result The label is used to specify the mapping rules for normal columns
            Properties:
                property Attribute name to map
                column Corresponding column name
    -->
    <resultMap id="orderMap" type="com.sangeng.pojo.Order" >
        <id column="id" property="id"></id>
        <result column="createtime" property="createtime"></result>
        <result column="price" property="price"></result>
        <result column="remark" property="remark"></result>
        <result column="user_id" property="userId"></result>
    </resultMap>

	<!--Use our custom mapping rules-->
    <select id="findAll" resultMap="orderMap">
        SELECT id,createtime,price,remark,user_id  FROM ORDERS
    </select>
	

1.2 automatic mapping

When map result is defined, it is automatically enabled by default. That is, if the column name of the result set is the same as our attribute name, it will be mapped automatically. We only need to write the mapping relationship of special cases.

For example:

The following writing method will have the same effect as the above writing method, because the attribute names of other attributes and the column names of the result set are the same and will be mapped automatically.

    <resultMap id="orderMap" type="com.sangeng.pojo.Order" >
        <result column="user_id" property="userId"></result>
    </resultMap>
	<!--Use our custom mapping rules-->
    <select id="findAll" resultMap="orderMap">
        SELECT id,createtime,price,remark,user_id  FROM ORDERS
    </select>

If necessary, you can choose to turn off automatic mapping and set the autoMapping property of resultMap to false.

For example:

    <resultMap id="orderMap" type="com.sangeng.pojo.Order" autoMapping="false">
        <id column="id" property="id"></id>
        <result column="createtime" property="createtime"></result>
        <result column="price" property="price"></result>
        <result column="remark" property="remark"></result>
        <result column="user_id" property="userId"></result>
    </resultMap>

1.3 inheritance mapping relationship

We can use the extensions attribute of resultMap to specify a resultMap, so as to reuse the repeated mapping relationship configuration.

For example:

  	<!--Define a parent mapping for others resultMap inherit-->
	<resultMap id="baseOrderMap" type="com.sangeng.pojo.Order" >
        <id column="id" property="id"></id>
        <result column="createtime" property="createtime"></result>
        <result column="price" property="price"></result>
        <result column="remark" property="remark"></result>
    </resultMap>
	<!--inherit baseOrderMap，Then you only need to write your own unique mapping relationship-->
    <resultMap id="orderMap" type="com.sangeng.pojo.Order" autoMapping="false" extends="baseOrderMap">
        <result column="user_id" property="userId"></result>
    </resultMap>

2. Multi table query

Sometimes we need to query the data of multiple tables to get the results we want.

We can directly write a SQL query associated with multiple tables. You can also make multiple queries step by step to get the results we need.

Mybatis provides the corresponding configuration, which makes it easier for us to carry out the corresponding query and corresponding result set processing.

2.1 multi table Association query

2.1.1 one to one relationship

There is a one-to-one relationship between two entities. (for example, we need to query the order and the data of the user who placed the order. The order here is one-to-one relative to the user.)

For example:

The method is defined as follows

    //The order is queried according to the order id, and the information of the ordering user is also required to be queried
    Order findById(Integer id);

Because it is expected that the Order can also contain the data of the ordering user, an attribute can be added to the Order

private User user;

The SQL statement is as follows

SELECT 
	o.id,o.`createtime`,o.`price`,o.`remark`,o.`user_id`,u.`id` uid,u.`username`,u.`age`,u.`address`
FROM 
	orders o,USER u
WHERE
	o.`user_id` = u.`id`
	AND o.id = 2

Result set

We can encapsulate the result set in the following two ways.

2.1.1.1 use ResultMap to map all fields

You can use ResultMap to set the mapping rules for the properties of the user object.

① resultMap definition is mainly used to set mapping rules for the attributes of user objects

 	<resultMap id="baseOrderMap" type="com.sangeng.pojo.Order" >
        <id column="id" property="id"></id>
        <result column="createtime" property="createtime"></result>
        <result column="price" property="price"></result>
        <result column="remark" property="remark"></result>
    </resultMap>

    <resultMap id="orderMap" type="com.sangeng.pojo.Order" autoMapping="false" extends="baseOrderMap">
        <result column="user_id" property="userId"></result>
    </resultMap>

    <!--Order and User Associated mapping-->
    <resultMap id="orderUserMap" type="com.sangeng.pojo.Order" autoMapping="false" extends="orderMap">
        <result property="user.id" column="uid"></result>
        <result property="user.username" column="username"></result>
        <result property="user.age" column="age"></result>
        <result property="user.address" column="address"></result>
    </resultMap>

② Use the defined resultMap

<!--According to the order id In order to query the order, it is required to query the information of the user who placed the order-->
    <select id="findById" resultMap="orderUserMap">
        SELECT
            o.`id`,o.`createtime`,o.`price`,o.`remark`,o.`user_id`,u.`id` uid,u.`username`,u.`age`,u.`address`
        FROM
            orders o,`user` u
        WHERE
            o.id = #{id} AND
            o.`user_id`=u.`id`
    </select>

2.1.1.2 use association in ResultMap

You can use the sub tag association in ResultMap to set the mapping rules of associated entity classes

① Define resultMap

 	<resultMap id="baseOrderMap" type="com.sangeng.pojo.Order" >
        <id column="id" property="id"></id>
        <result column="createtime" property="createtime"></result>
        <result column="price" property="price"></result>
        <result column="remark" property="remark"></result>
    </resultMap>

    <resultMap id="orderMap" type="com.sangeng.pojo.Order" autoMapping="false" extends="baseOrderMap">
        <result column="user_id" property="userId"></result>
    </resultMap>

    <!--Order and User Associated mapping (using association)-->
    <resultMap id="orderUserMapUseAssociation" type="com.sangeng.pojo.Order" autoMapping="false" extends="orderMap">
        <association property="user" javaType="com.sangeng.pojo.User">
            <id property="id" column="uid"></id>
            <result property="username" column="username"></result>
            <result property="age" column="age"></result>
            <result property="address" column="address"></result>
        </association>
    </resultMap>

② Using resultMap

<!--According to the order id In order to query the order, it is required to query the information of the user who placed the order-->
    <select id="findById" resultMap="orderUserMapUseAssociation">
        SELECT
            o.`id`,o.`createtime`,o.`price`,o.`remark`,o.`user_id`,u.`id` uid,u.`username`,u.`age`,u.`address`
        FROM
            orders o,`user` u
        WHERE
            o.id = #{id} AND
            o.`user_id`=u.`id`
    </select>

2.1.2 one to many relationship

There is a one to many relationship between two entities. (for example, we need to query the user and the role information of the user. The users here are one to many relative to the role.)

For example:

The method is defined as follows

    //The user is queried according to the id, and the role information of the user is also required to be queried
    User findById(Integer id);

Because it is expected that the User can also contain the role information of the User, a property can be added to the User

//    The role the user has
    private List<Role> roles;

The SQL statement is as follows

SELECT 
	u.`id`,u.`username`,u.`age`,u.`address`,r.id rid,r.name,r.desc
FROM 
	USER u,user_role ur,role r
WHERE 
	u.id=ur.user_id AND ur.role_id = r.id
	AND u.id = 2

Result set

We can encapsulate the result set in the following way.

2.1.2.1 use collection in ResultMap

You can use the sub tag association in ResultMap to set the mapping rules of associated entity classes

① Define ResultMap

	<!--definition User Basic attribute mapping rules-->
	<resultMap id="userMap" type="com.sangeng.pojo.User">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="age" column="age"></result>
        <result property="address" column="address"></result>
    </resultMap>
	
    <resultMap id="userRoleMap" type="com.sangeng.pojo.User"  extends="userMap">
        <collection property="roles" ofType="com.sangeng.pojo.Role" >
            <id property="id" column="rid"></id>
            <result property="name" column="name"></result>
            <result property="desc" column="desc"></result>
        </collection>
    </resultMap>

② Using ResultMap

    
    <select id="findById" resultMap="userRoleMap" >
        SELECT 
            u.`id`,u.`username`,u.`age`,u.`address`,r.id rid,r.name,r.desc
        FROM 
            USER u,user_role ur,role r
        WHERE 
            u.id=ur.user_id AND ur.role_id = r.id
            AND u.id = #{id}
    </select>

The final packaging results are as follows:

2.2 step by step query

If there is a need for multi table query, we can also choose to query the data we want by multiple queries. Mybatis also provides the corresponding configuration.

For example, we need to query the User and the role information of the User. We can choose to query the User table first to query the User information. Then query the associated role information.

2.2.1 implementation steps

The specific steps are as follows:

① Define query method

Because we need to query in two steps: 1 Query User 2 The Role is queried according to the user's id, so we need to define the following two methods and write the corresponding label first

1. Query User

    //Query the user according to the user name, and it is required to query the role information of the user
    User findByUsername(String username);

    <!--Query users by user name-->
    <select id="findByUsername" resultType="com.sangeng.pojo.User">
        select id,username,age,address from user where username = #{username}
    </select>

2. According to user_id query Role

public interface RoleDao {
	//Query the role according to userId
    List<Role> findRoleByUserId(Integer userId);
}

    <!--according to userId Role of query-->
    <select id="findRoleByUserId" resultType="com.sangeng.pojo.Role">
        select 
            r.id,r.name,r.desc
        from 
            role r,user_role ur
        where 
            ur.role_id = r.id
            and ur.user_id = #{userId}
    </select>

② Configure step-by-step query

The desired effect is to call the findByUsername method, and the query result contains the role information. Therefore, we can set the RestltMap of findByUsername method and specify step-by-step query

    <resultMap id="userMap" type="com.sangeng.pojo.User">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="age" column="age"></result>
        <result property="address" column="address"></result>
    </resultMap>
    <!--
           select Attribute: specifies which query is used to query the data of the current attribute. Writing method: package name.Interface name.Method name
           column Property: sets which column of data in the current result set is used as the select The query method specified by the property requires parameters
       -->
	<resultMap id="userRoleMapBySelect" type="com.sangeng.pojo.User" extends="userMap">
        <collection property="roles"
                    ofType="com.sangeng.pojo.Role"
                    select="com.sangeng.dao.RoleDao.findRoleByUserId"
                    column="id">
        </collection>
    </resultMap>

Specify findByUsername to use the resultMap we just created

    <!--Query users by user name-->
    <select id="findByUsername" resultMap="userRoleMapBySelect">
        select id,username,age,address from user where username = #{username}
    </select>

2.2.2 setting demand loading

We can set on-demand loading, so that we can query the associated data when we need to use the associated data in our code.

There are two ways to configure: global configuration and local configuration

1. Local configuration

   Set the fetchType property to lazy

   	<resultMap id="userRoleMapBySelect" type="com.sangeng.pojo.User" extends="userMap">
           <collection property="roles"
                       ofType="com.sangeng.pojo.Role"
                       select="com.sangeng.dao.RoleDao.findRoleByUserId"
                       column="id" fetchType="lazy">
           </collection>
       </resultMap>
   

2. Global configuration

   Set lazyloading enabled to true

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

3. Paging query - PageHelper

We can use PageHelper to help us realize the requirements of paging query. There is no need to splice SQL related parameters in SQL, and it is very convenient to obtain paging related data such as total pages and total entries.

3.1 implementation steps

① Define query methods and generate corresponding labels

	List<User> findAll();

    <select id="findAll" resultType="com.sangeng.pojo.User">
        select id,username,age,address from user
    </select>

② Introduce dependency

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>4.0.0</version>
</dependency>

③ Configuring the Mybatis core configuration file to use the paging plug-in

 <plugins>
        <!-- Note: the plug-in configuration of paging assistant is in the general library mapper before -->
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <!-- Designated dialect -->
            <property name="dialect" value="mysql"/>
        </plugin>
    </plugins>

④ Start paging query

We only need to set paging parameters before using the query method

    //Set paging parameters
	UserDao userDao = session.getMapper(UserDao.class);
    //Set paging query parameters
    PageHelper.startPage(1,1);
    List<User> users = userDao.findAll();
    System.out.println(users.get(0));

If you need to obtain paging related data such as total pages and total entries, you only need to create a pageinfo object and pass in the returned value just queried as a constructor parameter. Then use the pageinfo object to get it.

	PageInfo<User> pageInfo = new PageInfo<User>(users);
    System.out.println("Total:"+pageInfo.getTotal());
	System.out.println("Total pages:"+pageInfo.getPages());
	System.out.println("Current page:"+pageInfo.getPageNum());
	System.out.println("Display length per page:"+pageInfo.getPageSize());

3.2 paging problem of one to many table query

When we perform one to many multi table queries, if PageHelper is used for paging. There will be incomplete associated data. We can use step-by-step query to solve this problem.

4.Mybatis cache

Mybatis's cache actually stores the previously found data into the memory (map). If you still check the same thing next time, you can get it directly from the cache, so as to improve efficiency.

Mybatis can be divided into the first level cache and the second level cache. The first level cache (enabled by default) is the sqlsession level cache. L2 cache is equivalent to mapper level cache.

4.1 L1 cache

Several situations where L1 cache will not be used
1. Call the same method, but the parameters passed in are different
2. The parameters of the same method are the same, but another SqlSession is used
3. If the same table is added or deleted after the query, the cache on the sqlSession will be emptied
4. If you manually call the clearCache method of SqlSession to clear the cache, the cache will not be used later

4.2 L2 cache

Note: the data will enter the L2 cache only after the sqlsession calls close or commit.

4.2.1 enable L2 cache

① Global on

Configure in Mybatis core configuration file

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

② Local opening

Set the cache label in the mapper mapping file where the L2 cache is to be enabled

<?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.sangeng.dao.RoleDao">
    <cache></cache>
</mapper>

4.2.2 suggestions for use

The L2 cache will not be used in actual development.