There is a cache implementation in Mybatis, which is divided into primary cache and secondary cache. However, L1 cache is useless because it is based on sqlSession, which is newly created every time the method is executed. The L2 cache is based on the namespace, and it is impossible to leave it. Is there a way to provide a custom caching mechanism?

Executor is the executor in Mybatis. All queries call its List query() method. We can intercept it here, do not let it execute the following query actions, and directly return from the cache.

In this class, we first get the cache tag and cache Key in the parameters to query Redis. If hit, return; Miss, and then execute its own method.

@Intercepts({@Signature(method = "query", type = Executor.class,args = {
        MappedStatement.class,Object.class,RowBounds.class,ResultHandler.class})})
//BeanFactoryAware yes Spring Interface in. The purpose is to obtain jedisService of Bean
public class ExecutorInterceptor implements Interceptor,BeanFactoryAware{
    private JedisServiceImpl jedisService;
    
    @SuppressWarnings("unchecked")
    public Object intercept(Invocation invocation) throws Throwable {
        if (invocation.getTarget() instanceof CachingExecutor) {
            //obtain CachingExecutor All parameters
            Object[] params = invocation.getArgs();
            //The second parameter is the parameter of the business method
            Map<String,Object> paramMap = (Map<String, Object>) params[1];
            String isCache = paramMap.get("isCache").toString();
            //Judge whether the cache is needed and get the cached data Key To query Redis
            if (isCache!=null && "true".equals(isCache)) {
                String cacheKey = paramMap.get("cacheKey").toString();
                String cacheResult = jedisService.getString(cacheKey);
                if (cacheResult!=null) {
                    System.out.println("Hit Redis cache,Direct return.");
                    return JSON.parseObject(cacheResult, new TypeReference<List<Object>>(){});
                }else {
                    return invocation.proceed();
                }
                
            }
            
        }
        return invocation.proceed();
    }
    
    //Return proxy object
    public Object plugin(Object target) {
        if (target instanceof Executor) {
            return Plugin.wrap(target, this);
        }
        return target;
    }

    public void setProperties(Properties properties) {}

    public void setBeanFactory(BeanFactory beanFactory) throws BeansException {
        jedisService = (JedisServiceImpl) beanFactory.getBean("jedisServiceImpl");
    }

} 

The above methods only get data from the cache, but when to add data to the cache? You can't call Redis methods in every business method. It's embarrassing if you change Redis to another database in the future. Recall the whole process of Mybatis execution method. How does it get the return value after submitting and executing the SQL?

2,ResultSetHandler

No impression? return resultSetHandler. handleResultSets(ps); The resultSetHandler is the object of the DefaultResultSetHandler instance. It is responsible for parsing and returning the data queried from the database, so we can put it in Redis after returning.

@Intercepts({@Signature(method = "handleResultSets", 
        type = ResultSetHandler.class,args = {Statement.class})})
public class ResultSetHandlerInterceptor implements Interceptor,BeanFactoryAware{

    private JedisServiceImpl jedisService;
    @SuppressWarnings("unchecked")
    public Object intercept(Invocation invocation) throws Throwable {
        Object result = null;
        if (invocation.getTarget() instanceof DefaultResultSetHandler) {
            //Execute the method first to get the result set
            result = invocation.proceed();      
            DefaultResultSetHandler handler = (DefaultResultSetHandler) invocation.getTarget();
            
            //Getting the member attributes through reflection is to finally get the parameters of the business method
            Field boundsql_field = getField(handler, "boundSql");
            BoundSql boundSql = (BoundSql)boundsql_field.get(handler);
            Field param_field = getField(boundSql, "parameterObject");
            Map<String,Object> paramMap = (Map<String, Object>) param_field.get(boundSql);
            
            String isCache = paramMap.get("isCache").toString();
            if (isCache!=null && "true".equals(isCache)) {
                String cacheKey = paramMap.get("cacheKey").toString();
                String cacheResult = jedisService.getString(cacheKey);
                //If there is no data in the cache, add it
                if (cacheResult==null) {
                    jedisService.setString(cacheKey, JSONObject.toJSONString(result));
                }
            }
        }
        return result;
    }
    public Object plugin(Object target) {
        if (target instanceof ResultSetHandler) {
            return Plugin.wrap(target, this);
        }
        return target;
    }
    private Field getField(Object obj, String name) {
        Field field = ReflectionUtils.findField(obj.getClass(), name);
        field.setAccessible(true);
        return field;
    }
    public void setProperties(Properties properties) {}
    public void setBeanFactory(BeanFactory beanFactory) throws BeansException {
        jedisService = (JedisServiceImpl) beanFactory.getBean("jedisServiceImpl");
    }
} 

Through these two interceptors, you can implement custom caching. Of course, the processing logic depends on your own business, but the general process is like this. The most important thing is the design of cacheKey, how to achieve universality and uniqueness. But why do you say that? Imagine that if we need to clear the cache after performing the UPDATE operation, what rules are used to clear it? Also, if the granularity of the cacheKey is too coarse, how can different parameter values of the same query method be distinguished? This requires careful consideration to design this field.

public @ResponseBody List<User> queryAll(){
    Map<String,Object> paramMap = new HashMap<>();
    paramMap.put("isCache", "true");
    paramMap.put("cacheKey", "userServiceImpl.getUserList");
    List<User> userList = userServiceImpl.getUserList(paramMap);
    return userList;
}  

Basically, every application has the function of paging. From the perspective of database, pagination is to determine the number of entries from which to start. For example, in MySQL, we can select * from user limit 0,10.

In the program, we can't add limit to every SQL statement. It's troublesome to change the database that doesn't support limit. At the same time, 0 and 10 after limit are not invariable, which depends on our page logic.

After parsing BoundSql, Mybatis starts calling the StatementHandler.prepare() method to build precompiled objects, set parameter values and submit SQL statements. Our goal is to modify the SQL statements in BoundSql before this. Let's first look at the definition of interceptor.

@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", 
                        args = {Connection.class,Integer.class})})
public class PageInterceptor implements Interceptor {
    
    public Object intercept(Invocation invocation) throws Throwable {
        return invocation.proceed();
    }
    public Object plugin(Object target) {
        if (target instanceof RoutingStatementHandler) {
            return Plugin.wrap(target, this);
        }
        return target;
    }
} 

Then, the first step is to create a Page object. It is responsible for recording and calculating the starting position and total number of data, so as to display paging friendly through calculation on the Page.

public class Page {
    public Integer start;//The first data in the current page List Location in,Start from 0
    public static final Integer pageSize = 10;//Number of entries per page
    public Integer totals;//Total records
    public boolean needPage;//Whether paging is required  
    
    public Page(int pages) {
        setNeedPage(true);
        start = (pages-1)*Page.pageSize;
    }
    public boolean isNeedPage() {
        return needPage;
    }
    public void setNeedPage(boolean needPage) {
        this.needPage = needPage;
    }
} 

To get various parameters from the target object, first judge whether paging is required

@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", 
                        args = {Connection.class,Integer.class})})
public class PageInterceptor implements Interceptor {
    
    public Object intercept(Invocation invocation) throws Throwable {
        if (invocation.getTarget() instanceof StatementHandler) {
            
            StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
            Field delegate_field = getField(statementHandler, "delegate");
            
            StatementHandler preparedHandler = (StatementHandler)delegate_field.get(statementHandler);          
            Field mappedStatement_field = getField(preparedHandler, "mappedStatement");
            
            MappedStatement mappedStatement = (MappedStatement) mappedStatement_field.get(preparedHandler); 
            
            Field boundsql_field = getField(preparedHandler, "boundSql");
            BoundSql boundSql = (BoundSql)boundsql_field.get(preparedHandler);
            
            String sql = boundSql.getSql();
            Object param = boundSql.getParameterObject();
            
            if (param instanceof Map) {
                Map paramObject = (Map)param;
                if (paramObject.containsKey("page")) {
                    //Determine whether paging is required
                    Page page = (Page)paramObject.get("page");
                    if (!page.isNeedPage()) {
                        return invocation.proceed();
                    }
                    Connection connection = (Connection) invocation.getArgs()[0];
                    setTotals(mappedStatement,preparedHandler,page,connection,boundSql);
                    sql = pageSql(sql, page);
                    Field sql_field = getField(boundSql, "sql");
                    sql_field.setAccessible(true);
                    sql_field.set(boundSql, sql);
                }
            }
        }
        return invocation.proceed();
    }
} 

In fact, one paging function is designed to query twice. Once, the SQL itself is labeled with Limit, and once it is not labeled with Limit, and it should be a Count statement to obtain the total number of entries. Therefore, the setTotals method is involved. The purpose of this method is to obtain the total number of data, which involves several key points.

• Modify the original SQL to Count statement.
• Modify the return value type of the original method.
• Execute SQL.
• Change the modified SQL and return value types back.

private void setTotals(MappedStatement mappedStatement,StatementHandler preparedHandler,
                        Page page,Connection connection,BoundSql boundSql){
            
    //Original return value type
    Class<?> old_type = Object.class;
    ResultMap resultMap = null;
    List<ResultMap> resultMaps = mappedStatement.getResultMaps();
    if (resultMaps!=null && resultMaps.size()>0) {
        resultMap = resultMaps.get(0);
        old_type = resultMap.getType();
        //Modify the return value type to Integer,Because we get the total number
        Field type_field = getField(resultMap, "type");
        type_field.setAccessible(true);
        type_field.set(resultMap, Integer.class);
    }
    
    //modify SQL by count sentence
    String old_sql = boundSql.getSql();
    String count_sql = getCountSql(old_sql);
    
    Field sql_field = getField(boundSql, "sql");
    sql_field.setAccessible(true);
    sql_field.set(boundSql, count_sql);
    
    //implement SQL And set the total number to Page object
    Statement statement =  prepareStatement(preparedHandler, connection);
    List<Object> resObjects = preparedHandler.query(statement, null);
    int result_count = (int) resObjects.get(0);
    page.setTotals(result_count);
        
    /**
     * It is also important to modify the sql and return types back
     */
    Field sql_field_t = getField(boundSql, "sql");
    sql_field_t.setAccessible(true);
    sql_field_t.set(boundSql, old_sql);
    
    Field type_field = getField(resultMap, "type");
    type_field.setAccessible(true);
    type_field.set(resultMap, old_type);
}
private String getCountSql(String sql) {    
    int index = sql.indexOf("from");    
    return "select count(1) " + sql.substring(index);    
}

4,Limit

After obtaining the total number of entries, you need to modify the SQL once, adding Limit. Finally, execute and return the result.

String sql = boundSql.getSql();

//add Limit，from start start
sql = pageSql(sql, page);
Field sql_field = getField(boundSql, "sql");
sql_field.setAccessible(true);
sql_field.set(boundSql, sql);


private String pageSql(String sql, Page page) {
    StringBuffer sb = new StringBuffer();
    sb.append(sql);
    sb.append(" limit ");
    sb.append(page.getStart());
    sb.append("," + Page.pageSize);
    return sb.toString();
} 

Finally, it can be called directly in the business method. Of course, remember to pass the Page parameter.

public @ResponseBody List<User> queryAll(HttpServletResponse response) throws IOException {
        
    Page page = new Page(1);
    Map<String,Object> paramMap = new HashMap<>();
    paramMap.put("isCache", "true");
    paramMap.put("cacheKey", "userServiceImpl.getUserList");
    paramMap.put("page", page);
    
    List<User> userList = userServiceImpl.getUserList(paramMap);
    for (User user : userList) {
        System.out.println(user.getUsername());
    }
    System.out.println("Total number of data:"+page.getTotals());
    return userList;
} 
--------------------------------
Guan Xiaoyu
 Little Luna
 Linser
 Little Luban
 Total number of data:4 

This paper focuses on the actual use of plug-ins in Mybatis. In daily development, caching and paging are basically common function points. You can highly customize your own caching mechanism, including the timing of caching, the design of cache Key, the setting of expiration Key, etc.. For paging, you should also be more aware of their implementation logic, so that you will have more choices in the selection in the future.