Mybatis novice advanced knowledge points, veterans, please go away

ORM full name: object relation mapping, translated as: object relation mapping.
The ORM framework is a framework that maps objects and database table fields and provides APIs for CRUD operations.

Java's native way to connect to the database is JDBC, and each operation requires the following 6 steps

1. Load database driver
2. Create connection
3. Create a Statement
4. Execute SQL
5. Processing result set
6. Close the connection

The native method has cumbersome steps and low development efficiency. There are many excellent ORM frameworks on the market:

1. Hibernate Fully automatic ORM framework, weakening SQL, even without considering the creation of tables, Hibernate will generate tables or even intermediate tables according to the object. CURD generally does not need to write sql. It’s easy to use, but difficult to use. Some old projects are still in use.
2. Mybatis semi-automatic ORM framework, the protagonist of this article, is widely used, it supports custom SQL, stored procedures and advanced mapping. The predecessor is ibatis , and there is also a framework MyBatis-Plus encapsulated on this basis called simplified development. Provide general CURD, general operation does not need to write sql.
3. JPA is an ORM framework under the big spring. The feature is that the method logic can be automatically implemented based on the method name. Do you dare to believe it? If you don’t believe you can read this article 161379aa0c5636 "Simplicity is beauty! SpringBoot+JPA "

The following will introduce some mybatis novice advanced knowledge points, old birds please go away 🤦‍♂️

Nested query

Nest a query in resultMap This is done through the select attribute of the <association> select value is another <select> ID query, column attributes associated field, for implementing the associated query.

1. Query user based on user_id

    <select id="getUserWithAddress2" resultMap="BaseResultWithAddress2Map">
         select * from `user` where user_id = #{userId}
    </select>

2. A query with an id of selectAddressByUserId is nested in <association> to query the address of this user.

    <resultMap id="BaseResultWithAddress2Map" type="com.mashu.springmybatis.entity.UserWithAddress">
        <id column="user_id" property="userId" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="email" property="email" jdbcType="VARCHAR"/>
        <association property="address" column="user_id" javaType="com.mashu.springmybatis.entity.Address"
                     select="selectAddressByUserId">
        </association>
    </resultMap>

3. Query with id of selectAddressByUserId: Query address details based on user id:

    <select id="selectAddressByUserId"
            resultMap="com.mashu.springmybatis.mapper.AddressMapper.BaseResultMap">
            select * from address where user_id = #{userId}
    </select>

Nested results

The above query will have the problem of N+1, which is to perform the query twice. You can use the combined table query to solve this problem. The result set also uses the <resultMap> mapping, <association> label + resultMap attribute. The specific wording is as follows:

1. The resultMap attribute of the association tag points to the resultMap of address

   <resultMap id="BaseResultWithAddressMap" type="com.mashu.springmybatis.entity.UserWithAddress">
    <id column="user_id" property="userId" jdbcType="INTEGER"/>
    <result column="name" property="name" jdbcType="VARCHAR"/>
    <result column="email" property="email" jdbcType="VARCHAR"/>
    <association property="address" javaType="com.mashu.springmybatis.entity.Address"
                     resultMap="com.mashu.springmybatis.mapper.AddressMapper.BaseResultMap">
    </association>
   </resultMap>

2. Join table query sql

    <select id="getUserWithAddress" resultMap="BaseResultWithAddressMap">
        select * from `user` u join address a on u.user_id and a.user_id where u.user_id = #{userId}
    </select>

You can also perform one-to-many mapping. <association> to <collection> to realize a one-to-many association query in which a person has multiple girlfriends.
myabtis will automatically merge duplicate users, and girlFriends is mapped to the user's girlFriends attribute as a collection.

    <resultMap id="BaseResultWithGirlFriendsMap" type="com.mashu.springmybatis.entity.UserWithGirlFriends">
        <id column="user_id" property="userId" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="email" property="email" jdbcType="VARCHAR"/>
        <collection property="girlFriends" ofType="com.mashu.springmybatis.entity.GirlFriend">
            <id column="girl_friend_id" property="girlFriendId" jdbcType="INTEGER"/>
            <result column="user_id" property="userId" jdbcType="VARCHAR"/>
            <result column="girl_friend_name" property="name" jdbcType="VARCHAR"/>
            <result column="age" property="age" jdbcType="INTEGER"/>
            <result column="weight" property="weight" jdbcType="INTEGER"/>
            <result column="height" property="height" jdbcType="INTEGER"/>
        </collection>
    </resultMap>

Lazy loading

In addition to the joint table query to solve the N+1 problem, the lazy loading of mybatis seems to be better. Take the chestnut of the first nested query as an example, if lazy loading is turned on,
When address is not used, only the SQL querying user will be executed, but the SQL querying address will not be executed.
The sql querying address will be executed only when the address attribute is gotten in use. It is also very simple to use:

1. yml configuration

   mybatis:
     mapper-locations: classpath:mapper/*Mapper.xml
     configuration:
    ##开启懒加载
    lazy-loading-enabled: true
    ##false：按需加载
    aggressive-lazy-loading: false
    ##触发加载的方法名
    lazy-load-trigger-methods:

2. <association> adds the attribute of fetchType="lazy"

    <resultMap id="BaseResultWithAddress2Map" type="com.mashu.springmybatis.entity.UserWithAddress">
        <id column="user_id" property="userId" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="email" property="email" jdbcType="VARCHAR"/>
        <association fetchType="lazy" property="address" column="user_id" javaType="com.mashu.springmybatis.entity.Address"
                     select="selectAddressByUserId">
        </association>
    </resultMap>

However, there are more problems with lazy loading 😢:

1. If an error is reported No serializer found for class org.apache.ibatis.executor.loader.javassist.JavassistProxyFactory...
   Serialization issues need to add annotations to the entity class @JsonIgnoreProperties(value = {"handler"})
2. If lazy loading fails: check whether it is caused by the toString() annotation of @Data in lombok
3. Check whether the global configuration is correct
4. There are failures in idea and success in eclipce. . .

Primary and secondary cache

Level 1 cache, query data twice in one request, and fetch it from the cache the second time, mybatis is enabled by default
Secondary cache, multiple requests to query the same data, can be retrieved from the cache, need to be opened manually

1. Turn on the global configuration:

   mybatis:
     mapper-locations: classpath:mapper/*Mapper.xml
    ##开启二级缓存
    cache-enabled: true

2. Add the useCache="true" attribute.

    <select id="selectByCache" useCache="true" resultMap="BaseResultMap" parameterType="java.lang.Integer">
    select user_id, name, email
    from user
    where user_id = #{userId,jdbcType=INTEGER}
     </select>

Type processor

Sometimes we do some processing on the fields when entering and leaving the library,
For example, a database that does not support utf8mb4 needs to be escaped to the unicode character encoding supported by utf8 before storing emoji expressions, and needs to be converted into emoji expressions after being released from the database.
Or, the user's password cannot be saved in the database in plain text, and some encryption operations are required for storage.
The type processor of mybatis can do some operations on the data before entering and leaving the library.
Let's take a chestnut to encrypt the mailbox in Base64 and decrypt it out of the library.

1. The custom type processor class inherits the abstract class BaseTypeHandler

   public class MyHandler extends BaseTypeHandler<String> {
    
    //入库加密
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, String s, JdbcType jdbcType) throws SQLException {
        preparedStatement.setString(i,Base64Utils.encodeToString(s.getBytes()));
    }
    //出库解密
    @Override
    public String getNullableResult(ResultSet resultSet, String columnName) throws SQLException {
        String column = resultSet.getString(columnName);
        return new String(Base64Utils.decode(column.getBytes()));
    }
   
    @Override
    public String getNullableResult(ResultSet resultSet, int i) throws SQLException {
        System.out.println(resultSet);
        return null;
    }
   
    @Override
    public String getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        System.out.println(callableStatement);
        return null;
    }
   }

2. Add typeHandler attribute to the field and point to the path of the custom type processor class

    <resultMap id="BaseResultMap" type="com.mashu.springmybatis.entity.User">
        <id column="user_id" property="userId" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="email" property="email" jdbcType="VARCHAR" typeHandler="com.mashu.springmybatis.config.MyHandler"/>
    </resultMap>