MyBatis delayed loading

Introduction

In the previous article, multi table query is introduced. In practice, we often involve multi table joint query, but sometimes, not all query results will be used immediately. Let me give two examples:

• For example, to query the purchase details of a batch of notebook computers, instead of directly displaying the details of the corresponding computer configuration or price of each column of details, wait until the user needs to take out the relevant details of a notebook, and then conduct a single table query
• For example, in a bank, if a user has 50 accounts (for example), then we can query the user's information. Obviously, it is more reasonable to query the details of all the accounts under the user when using

In such a case, the mechanism of delayed loading appears. As the name implies, delayed loading (lazy loading) is to delay the loading of certain information. Such technology also helps us realize the mechanism of "query on demand". In the case of one to many, or many to many

Since the delay loading is mentioned, of course, by the way, immediate loading means to query immediately no matter whether the user needs it or not. This method is suitable for many to one or one to one situations

(1) Necessary preparation

First, configure the basic environment, and then we prepare two tables in the database

User table

CREATE TABLE USER (
 `id`			INT(11)NOT NULL AUTO_INCREMENT,
 `username` 	VARCHAR(32) NOT NULL COMMENT 'User name',
 `telephone`    VARCHAR(11) NOT NULL COMMENT 'Mobile phone',
 `birthday`		DATETIME DEFAULT NULL COMMENT 'Birthday',
 `gender`  		CHAR(1) DEFAULT NULL COMMENT 'Gender',
 `address` 		VARCHAR(256) DEFAULT NULL COMMENT 'address',
  PRIMARY KEY  (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Account table

CREATE TABLE `account` (
  `ID` int(11) NOT NULL COMMENT 'number',
  `UID` int(11) default NULL COMMENT 'User number',
  `MONEY` double default NULL COMMENT 'Amount of money',
  PRIMARY KEY  (`ID`),
  KEY `FK_Reference_8` (`UID`),
  CONSTRAINT `FK_Reference_8` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then create the corresponding entity classes

Class User

public class User implements Serializable {
    private Integer id;
    private String username;
    private String telephone;
    private Date birthday;
    private String gender;
    private String address;
    //For one to many relationship mapping, the primary table entity should contain the set reference of the secondary table entity
    private List<Account> accounts;
	...... Please add get set and toString Method
}

Class Account

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
    //The slave table entity should contain an object reference of the master table entity
    private User user;
    ...... Please add get set and toString Method
}

UserMapper.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="cn.ideal.mapper.UserMapper">

    <!-- Definition User Of resultMap-->
    <resultMap id="userAccountMap" type="User">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="telephone" column="telephone"></result>
        <result property="birthday" column="birthday"></result>
        <result property="gender" column="gender"></result>
        <result property="address" column="address"></result>
        <collection property="accounts" ofType="account">
            <id property="id" column="aid"></id>
            <result property="uid" column="uid"></result>
            <result property="money" column="money"></result>
        </collection>
    </resultMap>
    
    <!-- Query all users and display corresponding account information -->
    <select id="findAll" resultMap="userAccountMap">
       SELECT u.*,a.id as aid,a.uid,a.money FROM user u LEFT OUTER JOIN account a on u.id = a.uid;
    </select>

    <!-- according to id Query users -->
    <select id="findById" parameterType="INT" resultType="User">
        select * from user where id = #{uid}
    </select>
    
</mapper>

Create corresponding methods in two interfaces

public interface AccountMapper {
    /**
     * Query all accounts
     * @return
     */
    List<Account> findAll();
}

public interface UserMapper {
    /**
     * Query all user information and display all accounts under the user at the same time
     *
     * @return
     */
    List<User> findAll();

    /**
     * Query user information according to id
     * @param userId
     * @return
     */
    User findById(Integer userId);
}

##(1) Delay loading code implementation

First of all, let me show you how we used to query users one-on-one, and at the same time, we will also query all the account information corresponding to users

/**
* Test query all
*/
@Test
public void testFindAll() {
	List<User> users= userMapper.findAll();
    for (User user : users) {
        System.out.println("---------------------");
        System.out.println(user);
        System.out.println(user.getAccounts());
    }
}

Effect:

In this way, SQL statements and resultmaps are used to query the information of users and accounts at the same time

So how to implement the delayed loading we mentioned above?

This time, we choose to query the account, and then delay loading the user's information

(1) Modify AccountMapper.xml

The first thing that needs to be modified is the Account mapping configuration file. You can see that when querying, we still define a resultMap, encapsulate the Account first, and then associate the User through the association, in which select and column are used to delay the loading of User information

• Select is used to specify the SQL statement to be executed for delayed loading, that is, to specify the id of a select tag pair in a SQL mapping file. Here we specify the method of querying information through id in the user
• Column refers to the column of associated user information query, which is the primary key of associated user, i.e. id

<mapper namespace="cn.ideal.mapper.AccountMapper">
	<!-- Define encapsulation Account and User Of resultMap -->
    <resultMap id="userAccountMap" type="Account">
        <id property="id" column="id"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <!-- Configuration encapsulation User Content
            select: Unique identification of the query user
            column: User base id Required parameter value when querying
        -->
        <association property="user" column="uid" javaType="User" select="cn.ideal.mapper.UserMapper.findById"></association>
    </resultMap>

    <!-- Query all accounts according to -->
    <select id="findAll" resultMap="userAccountMap">
        SELECT * FROM account
    </select>
</mapper>

(2) First test code

We only need to perform all methods of account query to see if we can achieve our effect

@Test
public void testFindAll(){
    List<Account> accounts = accountMapper.findAll();
}

(3) Execution effect

You can see that all three SQL statements have been executed. Why?

This is because we need to turn on the delay loading function before testing the method

(4) Delay loading function

We can go to the official website, how to configure and enable such a function

After consulting the documents, we know that if you want to start the delay loading function, you need to configure the setting property in the general configuration file SqlMapConfig.xml, that is, set the switch of delay loading lazyloading enable to teue. Because it is on-demand loading, you need to change the active loading to passive loading, that is, change the aggressiveLazyLoading to false

Of course, since the version of MyBatis I imported here is 3.4.5, the default value is false. In fact, it doesn 't need to be set, but we still write it out

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

Note: if you use typeAliases to configure aliases, you must put the typeAliases label after it

(5) Retest

Still only query methods are executed

@Test
public void testFindAll(){
    List<Account> accounts = accountMapper.findAll();
}

Execution effect

This time, only one command to query account was executed

What about when users want to view the users corresponding to each account? This is also called on-demand query. You only need to add the corresponding acquisition method during the test

@Test
public void testFindAll(){
    List<Account> accounts = accountMapper.findAll();
    for (Account account : accounts){
        System.out.println("----------------------------");
        System.out.println(account);
        System.out.println(account.getUser());
    }
}

Execute it.

As you can see, we have achieved the goal of delaying loading

summary

In the above test, we have implemented delayed loading, and briefly summarize the following steps:

• ① : execute the corresponding mapper method, that is, execute the corresponding SQL configuration with the id value of findAll in mapper in the above example, and only query the account information

• ② : in the program, when you traverse the queried accounts and call the getUser() method, the delay load starts

  • List<Account> accounts = accountMapper.findAll();

• ③ : delay loading, call the corresponding SQL configuration with the id value of findById in the mapping file, and get the information of the corresponding user

As you can see, we can directly query multiple tables by using SQL writing methods such as left outer connection

SELECT u.*,a.id as aid,a.uid,a.money FROM user u LEFT OUTER JOIN account a on u.id = a.uid;

However, we can achieve our on-demand query requirements through delayed loading. To sum up, when using, first execute simple SQL, and then load and query other information as required

Ending

If there are any deficiencies in the article, welcome to leave a message and exchange, thank you for your support!

If you can help, then pay attention to me! If you prefer the way of reading WeChat articles, you can pay attention to my public number.