This article is the summary note of the book “MyBatis: Technical Principles and Practice”.

The previous article summarized the configuration of MyBatis and explained the various configuration items in detail. It mentioned the mapper, which is the most powerful tool of MyBatis and the most used tool.

Through the mapper, it is easy to add, delete, change and check the data. We abstract the key points of these operations: transferring query parameters, assembling query conditions in various scenarios, associating query, mapping query results to Java Bean objects or collections, etc. In addition, the performance of data query can be improved through lazy loading and caching.

Article Index:

  1. Introduction to JDBC and MyBatis
  2. All configurations of MyBatis
  3. Mapper knows all about it
  4. Reflection and dynamic proxy fundamentals

This article concludes by listing the main elements of the mapper, the configuration items and functions provided by each element, and then focusing on parameters, result mapping, lazy loading, caching, dynamic SQL and other functions.

The main element of the mapper

The mapper consists of a Java interface that defines the caller’s interface and an XML file (or annotations) that is the core file for configuring the mapper, including the following elements:

  • Select query statement, you can customize parameters, return a result set;
  • Insert statement that returns an integer representing the number of inserts;
  • Update statement that returns an integer representing the number of updates.
  • Delete Delete statement that returns an integer indicating the number of deleted items.
  • SQL allows you to define a portion of SQL and then reference it everywhere;
  • ResultMap is used to describe loading objects from the database result set, and can also configure the association relationship.
  • Cache Specifies the cache configuration for a given namespace.

Add, delete, change, search operations

To find the

Parameters need to be defined before the SELECT statement is executed, and powerful mapping rules or automatic mappings are provided to bind the returned result set to Java beans after execution.

The select element has a number of configuration items, which are briefly described below:

  • ParamterType: Indicates the parameter type passed in. The value can bea basic type, a map, or a custom Java bean.
  • ResultType: returns the resultType, which can bea basic type or a custom Java bean.
  • ResultMap: It is the most complex element that can be configured with mapping rules, cascading, and typeHandler. The ResultType and ResultType cannot exist together.
  • FlushCache: specifies whether to flush the local cache and level-2 cache after the SQL call. The value is used to update the cache. The default value is false.
  • UseCache: enables level-2 cache. By default, level-1 cache is enabled.
  • Timeout: Sets the timeout parameter. An exception will be thrown when the timeout occurs, in seconds.
  • FetchSize: Set the total number of records to be fetched;

For example, to obtain user information according to the meter chat number:

<select id="findByMiliao" parameterType="string" resultType="User">
		select
		u.*
		from mxt_user u
		where u.miliao=#{miliao}
</select>
Copy the code

In the previous configuration, there was a setting item autoMappingBehavior, which defaults to automatic mapping. The result set of the nested result set mapping is not defined. There is also a setting called mapUnderscoreToCamelCase, which, when set to true, automatically maps database field names named “underscore” to POJOs named “camel”.

There are three ways to pass multiple parameters:

  • Use the Map parameter;
  • Use annotations to pass;
  • Using Java beans;

Use annotations as follows:

public List<Role> findRoleByNameAndNote(@Param("roleName") String rolename,
@Param("note") String note);
Copy the code

If Map is used to transfer parameters, service readability will be lost and future expansion and maintenance will be inconvenient. Therefore, it is not recommended. If the number of parameters <=5, annotations are recommended, because too many parameters will bring difficulties to the caller. If the number of parameters is greater than 5, you are advised to use JavaBean.

Mapping result sets using resultMap is described separately later.

insert

Properties and select are mostly the same. Here are three different properties:

  • KeyProperty: Specifies which columns are primary keys, separated by commas if they are joint primary keys.
  • KeyColumn: Specifies which column is the primary key and cannot be shared with the keyProperty.
  • UseGeneratedKeys: Whether to use automatic growth. Default is false.

When useGeneratedKeys is set to true, the Java Bean ID value is backfilled at insert time and the primary key value is retrieved from the returned object.

If you want to set the value of the primary key based on some special relationship, you can use the selectKey tag within the INSERT tag. For example, if the T_role is not recorded, you need to set it to 1, otherwise the maximum ID is + 2:

<insert id="insertRole" useGeneratedKeys="true" keyProperty="id" >
    <selectKey keyProperty="id" resultType="int" order="before">
        select if(max(id) is null,1,max(id)+2) as newId from t_role
    </selectKey> 
</insert>
Copy the code
Update and delete

It’s pretty simple. I don’t want to tell you more.

parameter

You’ve already covered parameter passing, and you can specify the types of parameters to have the corresponding typeHandler handle them.

#{age , javaType=int , jdbcType=NUMERIC }
Copy the code

You can also set the accuracy of some numerical parameters

#{price, javaType=double , jdbcType=NUMERIC , numericScale=2 }
Copy the code

Columns (col1,col2,col3) are used to pass columns as columns (col1,col2,col3). In most cases, a precompiled statement will be created.

select ${columns} from t_tablename
Copy the code

However, pay attention to SQL security to prevent SQL injection.

SQL elements

Definition:

<sql id="role_columns">
    id,role_name,note
</sql>
Copy the code

Use:

<include refid="role_columns">
    <property name="prefix" value="r" />
</include>
Copy the code

Results the mapping

Element is introduced

ResultMap is the most complex element in MyBatis. It is used to define mapping rules, cascading updates, custom type converters, etc.

Consists of the following elements:

<resultMap>
    <constructor> <! -- Config constructor -->
        <idArg/>
        <arg/>
    </constructor>
    <id/> <! Select primary key from primary key;
    <result/> <! -- Configure mapping rules -->
    <association/> <! -- One-on-one -->
    <collection/> <! -- One to many -->
    <discriminator> <! -- Discriminator cascade -->
        <case/>
    </discriminator>
</resultMap>
Copy the code

An entity that does not have a constructor with no arguments needs to be configured using constructor:

<resultMap id="role" type="com.xiaomi.kfs.mcc.core.domain.Role">
    <constructor>
        <idArg column="id" javaType="int"/>
        <arg column="role_name" javaType="string"/>
    </constructor>
</resultMap>
Copy the code

Id specifies the primary key column, result specifies the mapping rule for the database field and POJO attributes:

<resultMap id="role" type="com.xiaomi.kfs.mcc.core.domain.Role">
    <id property="id" column="id" />
    <result property="roleName" column="role_name" />
    <result property="note" column="note" />
</resultMap>
Copy the code

Association and Collection are used to configure cascading relationships, one-to-one and one-to-many, respectively. In practice, many-to-many relationships are rarely used because they are complicated, and one-to-many relationships are used to decompose them into bidirectional relationships.

The discriminator is used ina situation where, for example, men and women go to a physical checkup, and it is not appropriate to send a male to a gynecology checkup. The discriminator allows the discriminator to return different objects based on gender.

The configuration of cascading relationship is more than this demonstration, you can see the documentation for understanding.

Lazy loading

The advantage of cascading is that it is easy to obtain data, but sometimes it is not necessary to obtain all data. In this way, more SQL is executed and performance deteriorates. To solve this problem, lazy loading is required.

The slogs in MyBatis configuration have two global parameters, lazyLoadingEnabled and The introduction to sivelazyloading. The slogs in MyBatis configuration have two global parameters, lazyLoadingEnabled and The introduction to Sivelazyloading. The slogs in MyBatis configuration have two global parameters, lazyLoadingEnabled and The introduction to Sivelazyloading, the introduction to MyBatis configuration has two global parameters, lazyLoadingEnabled and the introduction to Sivelazyloading. Otherwise it will only load on demand.

The slogs about an aggressive loading property, like the student object associated with an object like this:

When accessing student information, the health condition will also be found out according to the discriminator; MyBatis will also look up student ID information when accessing course grades, because by default MyBatis is loaded hierarchally. Do not need the guider id to load the student ID information while accessing the student id. Set the saggressivelazyLoading to false to delay loading data as needed.

The above two attributes are global. You can also add the association and Collection elements with the attribute value fetchType, which has two values: eager and lazy.

The cache

When the configuration cache is not displayed, only level 1 cache is enabled. Level 1 cache is relative to the same SqlSession. When the parameters and SQL are identical, the same SqlSession object is used to call the same Mapper method, and the SQL will be executed only once.

If it is a different SqlSession object, because different SQLSessions are isolated from each other, that is, using the same Mapper, parameters and methods, the SQL will be sent to the database again to execute.

The poJOs must be serializable and only need to be configured as follows:

<cache />
Copy the code

Many of these Settings are default, with the following properties to be configured:

  • Eviction: represents cache reclamation policy, the possible values are LRU least used, FIFO FIRST in, FIRST out, SOFT SOFT reference, WEAK WEAK reference.
  • FlushInterval: flushInterval, in milliseconds. If this parameter is not configured, the cache is flushed only when the SQL is being executed.
  • Size: indicates the number of references. It represents the maximum number of objects that can be stored in the cache. Do not set the value too large.
  • ReadOnly: indicates that the cached data can only be read and cannot be modified.

On a large server, may use a dedicated cache server, such as Redis cache, can be realized by org. Apache. Ibatis. Cache. The cache very convenient interface implementation:

public interface Cache {
    String getId(a); // Cache number
    void putObject(Object var1, Object var2); // Save the object
    Object getObject(Object var1); // Get the object
    Object removeObject(Object var1); // Remove the object
    void clear(a); // Clear the cache
    int getSize(a); // Get the cache object size
    ReadWriteLock getReadWriteLock(a); // Get the cache read/write lock
}
Copy the code

Dynamic SQL

Most of the time, need to assemble query conditions according to different scenarios, MyBatis provides dynamic assembly of SQL statements.

It mainly provides the following elements:

  • If: judge statement, but conditional branch judge;
  • Choose (when, otherwise) : multi-condition branching judgment;
  • Trim (WHERE, set) : Handles some SQL assembly problems;
  • Foreach: loop statements, commonly used in listing conditions such as in statements;
  • Bind: use the OGNL expression to customize a context variable for easy use;

Trim can handle concatenation of and and commas, as shown in the following example:

<select id="findRoles" parameterType="string" >
  select id,role_name,note from t_role
  <trim prefix="where" prefixOverrides="and">
      <if test="roleName! =null and roleName! = "">
        and role_name like concat('%',#{roleName},'%')
      </if>
  </trim>
</select>
Copy the code

Alternatively, you can use the set element to set the updated list of fields:

<update id="updateRole" parameterType="role">
    update t_role
    <set>
        <if test="roleName! =null and roleName! = "">
            role_name=#{roleName},
        </if>
        <if test="note! =null and note! = "">
            note=#{note}
        </if>
    </set>
    where id=#{id}
</update>
Copy the code

The next article will introduce the parsing and operation principle of MyBatis.

Please scan the qr code below and follow my personal wechat official account ~