How to implement a mybaits paging plugin

Foreword:

Paging is often used in java web projects. Taking the commonly used persistence layer framework mybatis as an example, it does not provide native physical paging functions related interfaces, but mybaits provides corresponding plug-in functions to facilitate us to do some corresponding Here we choose mysql as the database. Under normal circumstances, we will directly use third-party plug-ins such as mybatis-helper and mybatis-plus. They all provide the paging function. If you don’t use these types How are we going to do it?

accomplish:

First of all, when we talk about paging in mysql, we will think of the limit keyword; then, when the front desk uses paging, there needs to be a total number of pages. Behind the total number of pages is the total number of entries and how many entries per page; the key point: paging through plug-ins + Total number
This is a simple demo

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.RequiredArgsConstructor;
import lombok.experimental.Delegate;
import org.apache.ibatis.binding.MapperMethod;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author yangrd
 * @date 2021/12/7
 */
@RequiredArgsConstructor

@Component
@Intercepts({@Signature(
        type = Executor.class,
        method = "query",
        args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class})})
public class MyTestInterceptor implements Interceptor {

    private final JdbcTemplate jdbcTemplate;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        BoundSql boundSql = (BoundSql) invocation.getArgs()[5];

        String tempSql = boundSql.getSql();

        MapperMethod.ParamMap<Object> parameterObject = (MapperMethod.ParamMap<Object>) boundSql.getParameterObject();
        List<String> argNames = boundSql.getParameterMappings().stream().map(ParameterMapping::getProperty).collect(Collectors.toList());
        Object[] args = argNames.stream().map(parameterObject::get).toArray();

        Long count = count(tempSql, args);

        Field field = BoundSql.class.getDeclaredField("sql");
        field.setAccessible(true);
        field.set(boundSql, String.format("%s limit 1, 10", tempSql));

        return Page.of(count, (ArrayList<?>) invocation.proceed());
    }

    @Data
    @AllArgsConstructor(staticName = "of")
    public static class Page<T> implements List<T> {

        private Long total;

        @Delegate
        private List<T> list;

    }

    private Long count(String tempSql, Object[] args) {

        String countSql = String.format("select count(*) from (%s) t", tempSql);
        return jdbcTemplate.queryForObject(countSql, Long.class, args);
    }
}

Summarize:

This is an extremely simple demo. It should be used as a starting point. For example, it needs to be perfected to determine whether paging is needed, which page, and multiple entries per page. But it shows us the core principle of how to paging in mybatis. I think we are in This is the end of this example, but it would be great if it can bring you some thoughts outside of this example.