Mybatis novice advanced knowledge points, old bird, please go away

Time:2021-12-9

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

Java’s native way to connect to the database is JDBC. 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 connection

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

  1. Hibernate fully automatic ORM framework weakens SQL and does not even need to consider creating tables. Hibernate will generate tables or even intermediate tables according to objects. Curd generally does not need to write SQL. It’s easy to use. It’s hard to use it well. 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. Formerly known as ibatis, there is also a framework called mybatis plus encapsulated on this basis, which is known as simplified development. It provides a general curd, and there is no need to write SQL for general operations.
  3. JPA is the ORM framework of big spring, which is characterized by the automatic implementation of method logic according to the method name. Dare you believe it? If you don’t believe it, you can take a look at this article “simplicity is beauty! Springboot + JPA”

Some are described belowmybatisNovice advanced knowledge points, old bird, please go away ♂️

nested queries

stayresultMapNested a query in. Through label<association>ofselectProperty complete.selectThe value of is another<select>ID of the query,columnAttribute is an associated field, which is used to implement associated query.

  1. According to user_ ID query user

     <select id="getUserWithAddress2" resultMap="BaseResultWithAddress2Map">
          select * from `user` where user_id = #{userId}
     </select>
  2. A query with ID selectaddressbyuserid is nested in < Association > to query the address of the 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 selectaddressbyuserid: query address details according to 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 an N + 1 problem, that is, execute the query twice. You can use the join table query to solve this problem, and the result set is also used<resultMap>Mapping,<association>Label+resultMapProperties. The specific wording is as follows:

  1. The resultmap property 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 use one to many mapping to<association>change into<collection>, realize the one to many association query of a person with multiple girlfriends.
Myabtis will automatically merge duplicate users, and girlfriends will be mapped to the girlfriends attribute of user 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 better. Take the chestnut of the first nested query. If lazy loading is enabled,
When address is not used, only the SQL query of user will be executed, and the SQL query of address will not be executed.
Only when the address attribute is obtained in use can the SQL query for address be executed. It is also very simple to use:

  1. YML configuration

    mybatis:
      mapper-locations: classpath:mapper/*Mapper.xml
      configuration:
     ##Enable lazy loading
     lazy-loading-enabled: true
     ##False: load on demand
     aggressive-lazy-loading: false
     ##The name of the method that triggered the load
     lazy-load-trigger-methods:
  2. < Association > plusfetchType="lazy"Properties of.

     <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 many problems in implementing lazy loading:

  1. If an error is reported, no serializer found for class org.apache.ibatis.executor.loader.javassist.javassistproxyfactory
    The serialization problem requires annotation on the entity class@JsonIgnoreProperties(value = {"handler"})
  2. If lazy loading fails: check whether it is caused by tostring() of @ data annotation in Lombok
  3. Check whether the global configuration is correct
  4. And those who failed in idea and succeeded in eclipse…

L1 and L2 cache

First level cache: query the data twice in one request and fetch it from the cache for the second time. Mybatis is enabled by default
The L2 cache can be retrieved from the cache after multiple requests to query the same data. It needs to be opened manually

  1. Turn on global configuration:

    mybatis:
      mapper-locations: classpath:mapper/*Mapper.xml
     ##Enable L2 cache
     cache-enabled: true
  2. Add 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 fields during receipt and issue,
For example, databases that do not support utf8mb4 need to be escaped into Unicode character codes supported by utf8 before storing Emoji expressions, and they need to be converted into Emoji expressions after delivery.
Or the user’s password cannot be saved to the database in clear text, and some encryption operations are required for warehousing.
The type processor of mybatis can perform some operations on the data before warehousing and outbound.
Let’s take a chestnut to encrypt the inbound Base64 and decrypt the outbound Base64.

  1. Custom type processor class inheritanceBaseTypeHandlerAbstract class.

    public class MyHandler extends BaseTypeHandler<String> {
     
     //Warehousing encryption
     @Override
     public void setNonNullParameter(PreparedStatement preparedStatement, int i, String s, JdbcType jdbcType) throws SQLException {
         preparedStatement.setString(i,Base64Utils.encodeToString(s.getBytes()));
     }
     //Outbound decryption
     @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. Field addtypeHandlerProperty and points 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>