Play with the advanced features of Mybatis: Take your data operations to the next level

Article directory

- Dynamic SQL
- caching mechanism
- Plug-in mechanism
- custom type conversion
- Summarize

The advanced features of Mybatis can help us operate the database more flexibly, including dynamic SQL, caching mechanism, plug-in mechanism, custom type conversion, etc. Learning these features allows us to make better use of Mybatis and improve the efficiency and quality of data operations.

The future path is determined by oneself, and the height of career is determined by oneself.

Dynamic SQL

In dynamic SQL, Mybatis provides a variety of tags to help us build dynamic SQL statements, including:

: Used for conditional judgment, you can dynamically add query conditions for the WHERE statement.
: dynamically generate WHERE statement, which can filter out unnecessary conditions.
: Dynamically generate UPDATE statements, which can update the fields that need to be modified.
: Similar to the switch statement in Java, different SQL statements can be generated according to different conditions.
: It is used to loop through the collection, and the elements in the collection can be used as parameters of the SQL statement for batch operations.

Example:

<update id="updateByPrimaryKeySelective" parameterType="com.youlai.mybatis.domain.SysUser">
        update sys_user
        <set>
                <if test="username != null">
                    username = #{username,jdbcType=VARCHAR},
                </if>
                <if test="nickname != null">
                    nickname = #{nickname,jdbcType=VARCHAR},
                </if>
                <if test="gender != null">
                    gender = #{gender,jdbcType=TINYINT},
                </if>
                <if test="password != null">
                    password = #{password,jdbcType=VARCHAR},
                </if>
                <if test="deptId != null">
                    dept_id = #{deptId,jdbcType=INTEGER},
                </if>
                <if test="avatar != null">
                    avatar = #{avatar,jdbcType=VARCHAR},
                </if>
                <if test="mobile != null">
                    mobile = #{mobile,jdbcType=VARCHAR},
                </if>
                <if test="status != null">
                    status = #{status,jdbcType=TINYINT},
                </if>
                <if test="email != null">
                    email = #{email,jdbcType=VARCHAR},
                </if>
                <if test="deleted != null">
                    deleted = #{deleted,jdbcType=TINYINT},
                </if>
                <if test="createTime != null">
                    create_time = #{createTime,jdbcType=TIMESTAMP},
                </if>
                <if test="updateTime != null">
                    update_time = #{updateTime,jdbcType=TIMESTAMP},
                </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>

Optimization techniques for dynamic SQL:

Try to use the tag to generate the WHERE statement, which can avoid redundant AND or OR keywords.
When using the tag for batch operations, try to use the batch mode instead of a simple loop to execute one by one.
For commonly used dynamic SQL statements, you can use the caching mechanism provided by Mybatis for optimization.
Try to avoid using the select * statement in dynamic SQL, but explicitly specify the fields to be queried.
Try to use precompiled statements in dynamic SQL to improve the execution efficiency of SQL statements.

Let yourself continue to improve and make your career better and better.

Cache mechanism

The difference between the first level cache and the second level cache
The first-level cache is the cache enabled by Mybatis by default. It is a cache shared within the same SqlSession. It can be cached by using the unique identifier (id) of the query statement and query parameters as the key, which can effectively avoid repeated queries. The life cycle of the first-level cache is the same as that of SqlSession. When SqlSession is closed, its cache will also be emptied.
The second-level cache is the global cache of Mybatis, which can be shared by multiple SqlSessions, and is suitable for scenarios where data needs to be shared across SqlSessions. Its storage method is to put the data in a HashMap, the key is the combination of the unique identifier of the query statement and the query parameters, and the value is the result of the query. The life cycle of the second-level cache is the same as that of the entire application. It can share query results among multiple SqlSessions of the application. However, for this reason, once its data is modified, it needs to manually refresh the cache, otherwise it will cause data inconsistent.
How to configure and use the cache

Configuration method:

mybatis:
  configuration:
    cache-enabled: true #Enable secondary cache (default enabled)
    local-cache-scope: statement #Turn off the first level cache (open by default)
# local-cache-scope: session #Open the first level cache (default open)

How to use:

<mapper namespace="com.youlai.mybatis.mapper.SysUserMapper">
    <cache type="com.youlai.mybatis.service.MyCustomCache"/
</mapper>

Custom cache implementation, local storage is used here, redis is recommended for distributed

@Component
@Slf4j
public class MyCustomCache implements Cache, Serializable {<!-- -->

    private String id;
    private Map<Object, Object> cache = new ConcurrentHashMap<>();

    public MyCustomCache() {<!-- -->
    }

    public MyCustomCache(String id) {<!-- -->
        this.id = id;
    }

    @Override
    public String getId() {<!-- -->
        log.info("Get cache ID: {}", id);
        return id;
    }

    @Override
    public void putObject(Object key, Object value) {<!-- -->
        log.info("Add cache key:{},value:{}",key,value);
        cache. put(key, value);
    }

    @Override
    public Object getObject(Object key) {<!-- -->
        log.info("Get cache key: {}", key);
        return cache. get(key);
    }

    @Override
    public Object removeObject(Object key) {<!-- -->
        log.info("Delete cache key: {}", key);
        return cache. remove(key);
    }

    @Override
    public void clear() {<!-- -->
        log.info("clear the cache");
        cache. clear();
    }

    @Override
    public int getSize() {<!-- -->
        log.info("Get the number of caches: {}", cache.size());
        return cache. size();
    }

    @Override
    public ReadWriteLock getReadWriteLock() {<!-- -->
        log.info("Take the lock");
        ReentrantReadWriteLock readWriteLock = new ReentrantReadWriteLock(true);
        return readWriteLock;
    }

    private void writeObject(ObjectOutputStream out) throws IOException {<!-- -->
        out.writeObject(cache);
    }

    private void readObject(ObjectInputStream in) throws IOException, ClassNotFoundException {<!-- -->
        cache = (Map<Object, Object>) in. readObject();
    }

}

Do what you love and be who you want to be.

Plug-in mechanism

The Mybatis plug-in can intercept the specified method during execution, enhance or modify it. The principle is to use the JDK dynamic proxy mechanism to proxy the interface of Mybatis, so as to realize the interception and modification of the SQL execution process, which can realize some complex functions such as:

Intercept and modify SQL statements to realize the function of dynamic SQL.
Statistics of SQL execution time are convenient for performance optimization.
Realize the general pagination query function.
Encrypt or decrypt query results.

Steps to customize the plugin:

Implement the Interceptor interface and rewrite the intercept method.
Write the interception logic in the intercept method, and enhance or modify the method that needs to be intercepted.
In the plugin method, instantiate the interceptor and return the proxy object.
Add plugins to Mybatis configuration.

The following is an example of statistical SQL execution time:

@Intercepts({<!-- -->
        @Signature(type = StatementHandler. class, method = "query", args = {<!-- -->Statement. class, ResultHandler. class})
})
@Slf4j
public class MybatisPlugin implements Interceptor {<!-- -->
    @Override
    public Object intercept(Invocation invocation) throws Throwable {<!-- -->
        // TODO: Intercept logic
        log.info("Enter interceptor");
        long startTime = System. currentTimeMillis();
        Object result = invocation. proceed();
        long endTime = System. currentTimeMillis();
        long sqlTime = endTime - startTime;
        StatementHandler statementHandler = (StatementHandler)invocation. getTarget();
        String sql = statementHandler.getBoundSql().getSql();
        ParameterHandler parameterHandler = statementHandler. getParameterHandler();
        log.info("Execute sql: {}, parameter: {}, spent {} milliseconds", sql, parameterHandler.getParameterObject().toString(), sqlTime);
        return result;
    }

    @Override
    public Object plugin(Object target) {<!-- -->
        log.info("Generate proxy object");
        return Interceptor. super. plugin(target);
    }

    @Override
    public void setProperties(Properties properties) {<!-- -->
        log.info("Set plugin properties");
        Interceptor. super. setProperties(properties);
    }
}

add plugin

@Component
public class MySqlSessionFactoryBeanCustomizer implements SqlSessionFactoryBeanCustomizer{<!-- -->
    @Override
    public void customize(SqlSessionFactoryBean factoryBean) {<!-- -->
        //register plugin
        factoryBean.setPlugins(new MybatisPlugin());
    }

}

Keep enthusiasm and curiosity, and constantly pursue progress and innovation.

Custom type conversion

Type conversion in Mybatis refers to the conversion between Java types and database types. In Mybatis, we can use TypeHandler to implement custom type conversion.

The steps to customize TypeHandler are as follows:

Create a class that implements the org.apache.ibatis.type.TypeHandler interface.
Use the @MappedJdbcTypes annotation on the class to specify the JDBC type to convert.
Use the @MappedTypes annotation on the class to specify the Java types to convert.
Override the methods in the TypeHandler interface to implement custom type conversion logic.

Example:

@MappedJdbcTypes(JdbcType. VARCHAR)
@MappedTypes(String. class)
@Slf4j
public class MyStringTypeHandler implements TypeHandler<String> {<!-- -->

    @Override
    public void setParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {<!-- -->
        log.info("Convert Java-type parameters to JDBC-type parameters and set them in PreparedStatement");
        ps.setString(i, parameter);
    }

    @Override
    public String getResult(ResultSet rs, String columnName) throws SQLException {<!-- -->
        log.info("Get data through column name {}, convert the result in JDBC type to java type", columnName);
        return rs. getString(columnName);
    }

    @Override
    public String getResult(ResultSet rs, int columnIndex) throws SQLException {<!-- -->
        log.info("Get data through index, convert the result in JDBC type to java type");
        return rs. getString(columnIndex);
    }

    @Override
    public String getResult(CallableStatement cs, int columnIndex) throws SQLException {<!-- -->
        log.info("Get data through index, convert the result in JDBC type to java type");
        return cs. getString(columnIndex);
    }
}

@Component
public class MySqlSessionFactoryBeanCustomizer implements ConfigurationCustomizer {<!-- -->

    @Override
    public void customize(Configuration configuration) {<!-- -->
        configuration.getTypeHandlerRegistry().register(String.class, new MyStringTypeHandler());
    }
}

In the above example, we have customized a TypeHandler that converts the Java String type to the JDBC VARCHAR type.

Complete code: https://gitee.com/youlaiorg/youlai-learning.git

Summary

This article introduces the advanced features of Mybatis, including dynamic SQL optimization techniques, caching mechanism, plug-in mechanism and custom type conversion. Dynamic SQL optimization techniques include using the tag to generate a WHERE statement, using the tag to use the batch mode as much as possible for batch operations, etc. The caching mechanism includes a first-level cache and a second-level cache, which can be turned on or off through configuration files. The plug-in mechanism can intercept the specified method during the execution of Mybatis, and enhance or modify it. Custom type conversion can convert between Java types and database types.

Keep learning and growing to achieve a win-win situation of self-worth and career development.