JFinal使用技巧-数据库分页查询支持传入总行数扩展

如题,扩展这个功能的需求是:遇到一些慢SQL有因为 select count(*) 导致CPU暴涨的,在索引已经加过的情况下,还不得劲。
话不多说,上码~ 

JF是支持接管分页查询的 config.getDialect().takeOverDbPaginate 

我编写代码的时候,发现扩展DbPro更简单,就使用扩展 DbPro 了:

import com.jfinal.plugin.activerecord.*;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.Callable;

/**
 * 扩展 MyDbPro
 * @author 杜福忠
 */
public class MyDbPro extends DbPro {
    private static final ThreadLocal<Long> paginate_totalRow = new ThreadLocal();

    public static Long setPaginateTotalRow(Long totalRow){
        Long ret = getPaginateTotalRow();
        paginate_totalRow.set(totalRow);
        return ret;
    }
    public static void removePaginateTotalRow(){
        paginate_totalRow.remove();
    }
    public static Long getPaginateTotalRow(){
        return paginate_totalRow.get();
    }

    public MyDbPro() {
    }

    public MyDbPro(String configName) {
        super(configName);
    }

    /**
     * @param totalRow 总行数
     */
    public static <T>T paginate(Long totalRow, Callable<T> callable){
        Long former = setPaginateTotalRow(totalRow);
        try {
            return callable.call();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            if (Objects.nonNull(former)){
                setPaginateTotalRow(former);
            } else {
                removePaginateTotalRow();
            }
        }
    }

    protected Page<Record> doPaginateByFullSql(Config config, Connection conn, int pageNumber, int pageSize, Boolean isGroupBySql, String totalRowSql, StringBuilder findSql, Object... paras) throws SQLException {
        if (pageNumber < 1 || pageSize < 1) {
            throw new ActiveRecordException("pageNumber and pageSize must more than 0");
        }
        if (config.getDialect().isTakeOverDbPaginate()) {
            return config.getDialect().takeOverDbPaginate(conn, pageNumber, pageSize, isGroupBySql, totalRowSql, findSql, paras);
        }

        long totalRow;
        if (Objects.nonNull(paginate_totalRow.get())){
            totalRow = paginate_totalRow.get();
        } else {
            List result = query(config, conn, totalRowSql, paras);
            int size = result.size();
            if (isGroupBySql == null) {
                isGroupBySql = size > 1;
            }

            if (isGroupBySql) {
                totalRow = size;
            } else {
                totalRow = (size > 0) ? ((Number)result.get(0)).longValue() : 0;
            }
        }

        if (totalRow == 0) {
            return new Page<Record>(new ArrayList<Record>(0), pageNumber, pageSize, 0, 0);
        }

        int totalPage = (int) (totalRow / pageSize);
        if (totalRow % pageSize != 0) {
            totalPage++;
        }

        if (pageNumber > totalPage) {
            return new Page<Record>(new ArrayList<Record>(0), pageNumber, pageSize, totalPage, (int)totalRow);
        }

        // --------
        String sql = config.getDialect().forPaginate(pageNumber, pageSize, findSql);
        List<Record> list = find(config, conn, sql, paras);
        return new Page<Record>(list, pageNumber, pageSize, totalPage, (int)totalRow);
    }
}


分页查询我一般都使用 Record 感觉更方便, 
但是分享嘛 Model也一起写了吧,BaseModel代码和上面差不多:

import com.jfinal.plugin.activerecord.*;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public abstract class BaseModel<M extends Model<M>> extends Model<M> {

    protected Page<M> doPaginateByFullSql(Config config, Connection conn, int pageNumber, int pageSize, Boolean isGroupBySql, String totalRowSql, StringBuilder findSql, Object... paras) throws Exception {
        if (pageNumber < 1 || pageSize < 1) {
            throw new ActiveRecordException("pageNumber and pageSize must more than 0");
        }
        if (config.getDialect().isTakeOverModelPaginate()) {
            return config.getDialect().takeOverModelPaginate(conn, _getUsefulClass(), pageNumber, pageSize, isGroupBySql, totalRowSql, findSql, paras);
        }

        long totalRow;
        Long paginateTotalRow = MyDbPro.getPaginateTotalRow();
        if (Objects.nonNull(paginateTotalRow)){
            totalRow = paginateTotalRow;
        } else {
            List result = CPI.query(config, conn, totalRowSql, paras);
            int size = result.size();
            if (isGroupBySql == null) {
                isGroupBySql = size > 1;
            }

            if (isGroupBySql) {
                totalRow = size;
            } else {
                totalRow = (size > 0) ? ((Number) result.get(0)).longValue() : 0;
            }
        }
        if (totalRow == 0) {
            return new Page<M>(new ArrayList<M>(0), pageNumber, pageSize, 0, 0);   // totalRow = 0;
        }

        int totalPage = (int) (totalRow / pageSize);
        if (totalRow % pageSize != 0) {
            totalPage++;
        }

        if (pageNumber > totalPage) {
            return new Page<M>(new ArrayList<M>(0), pageNumber, pageSize, totalPage, (int)totalRow);
        }

        // --------
        String sql = config.getDialect().forPaginate(pageNumber, pageSize, findSql);
        List<M> list = find(config, conn, sql, paras);
        return new Page<M>(list, pageNumber, pageSize, totalPage, (int)totalRow);
    }
}


再加个拦截器吧,很多时候一个Action就是一个分页查询,
@Before(PaginateInterceptor.class)
https://jfinal.com/doc/4-3
加个拦截器代码会简洁一些:

import com.jfinal.aop.Interceptor;
import com.jfinal.aop.Invocation;

public class PaginateInterceptor implements Interceptor {
    private String totalRowKey = "totalRow";

    public void intercept(Invocation inv) {
        Long totalRow = inv.getController().getLong(totalRowKey);
        MyDbPro.paginate(totalRow, () -> {
            inv.invoke();
            return null;
        });
    }
}



OK,搞定,进入测试环节:
Record分页可以直接用,Model分页还得写个子类。。。
来吧:

public class TestModel extends BaseModel<TestModel> {
    //测试使用, dao不建议写在这个位置
    public static final TestModel dao = new TestModel().dao();

}

执行类(直接用main方法进入了哈): PS:https://jfinal.com/doc/5-15

import com.jfinal.plugin.activerecord.*;
import com.jfinal.plugin.druid.DruidPlugin;

public class MyDbProTest {
    public static void main(String[] args) {
        DruidPlugin dp = new DruidPlugin(...);
        ActiveRecordPlugin arp = new ActiveRecordPlugin(dp);
        //注意设置 MyDbPro
        arp.setDbProFactory(configName -> new MyDbPro(configName));
        arp.addMapping("account", TestModel.class);
        dp.start();
        arp.start();
        try {
            //分页的总行, null 时会从数据库计算,可由前端自行缓存请求时传入参数,避免频繁查询
            Long totalRow = 6L;
            //页码
            int pageNumber = 2;
            //页面大小
            int pageSize = 5;
            String select = "SELECT * ";
            String sqlExceptSelect = "FROM account";

            //执行指定分页的总行的分页查询
            //Record
            Page<Record> page = MyDbPro.paginate(totalRow, () ->
                //普通分页查询例子
                Db.paginate(pageNumber, pageSize, select, sqlExceptSelect));
            System.out.println(page);
            System.out.println(page.getList());

            System.out.println("----------");

            //Model
            Page<TestModel> paginate = MyDbPro.paginate(totalRow, () ->
                    //普通分页查询例子
                    TestModel.dao.paginate(pageNumber, pageSize, select, sqlExceptSelect));
            System.out.println(paginate);
            System.out.println(paginate.getList());
        }finally {
            dp.stop();
        }
    }
}


上面注意事项:

注意设置 MyDbPro:
arp.setDbProFactory(configName -> new MyDbPro(configName));

代码中使用:
MyDbPro.paginate(totalRow, XXXX

totalRow是分页的总行, null 时会从数据库计算,可由前端自行缓存,请求时传入参数,避免频繁select count(*)查询。
(后端缓存需要在业务代码中写缓存逻辑,取totalRow值,再放入即可

按说应该新建一个PaginateKit类的,现在代码就合在MyDbPro一起吧,重在思路。

bing_dun_dun_200k.jpg
好了,有参考价值就点个赞呗~ 有特殊业务自行改造吧~

评论区

JFinal

2022-02-12 16:13

优化很深入,省去每次查询总行数,能提升很大的效率,点赞收藏一波

山东小木

2022-02-13 14:12

这是带条件的还是带条件的?

杜福忠

2022-02-13 15:08

@山东小木 支持前端传入,用法和以前一模一样,不用太多改变,如果Action里面只有一个分页查询,可以使用拦截器@Before(PaginateInterceptor.class) 参数有totalRow就行。
如果有多个分页处理,手动MyDbPro.paginate(传入总行数即可。
由前端缓存 总行数,查询条件改没改变,前端自然是知道的,前端查询条件如果改变,传入的总行数传null或者不传值即可,都会刷新最新的总行数

北流家园网

2022-02-14 08:55

非常好。想问下:config.getDialect().takeOverDbPaginate 这个怎么使用的?你写的这个技巧可不可这样理解,就是把第一次查询的总行数写入缓存,后面翻页的时候,就直接取缓存中的行数?如果数据库有新增或者删除了,这个缓存中的总行数会同步更新吗?

杜福忠

2022-02-14 09:57

@北流家园网 是的缓存了,只不过是缓存在前端了,和缓存一样,增删都需要更新缓存,需要置为null才会获取最新的

山东小木

2022-02-14 10:23

这个totalRow应该不是这个表所有数据的总数 应该是符合这次查询条件下的总数哈?

杜福忠

2022-02-14 10:41

@山东小木 老师是的,分页的总行,和以前的SQL查询结果是一样的,在select count(*)位置做了if判断,其他都是一样的。
本来想对paras.toString再md5一下做个全局后台缓存来着,写代码时觉得前端传入值更方便轻量些,就让传入值了

杜福忠

2022-02-14 10:43

@北流家园网 config.getDialect().takeOverDbPaginate 和前面分享的数据库增加乐观锁扩展差不多,再重写isTakeOverModelPaginate() 和takeOverDbPaginate方法就可以了

zeroabc

2022-02-18 16:03

思来想去,这前端提供total数的方法短时内的查询效果是不错的,代码改动小,但是时间长了会出问题。有个想法,可不可以结合cache来做呢,select count where sql 作为key存数量,但是怎么维护数量是个问题,靠后台逻辑处理增删改查变动数量不太好弄,key是完整sql,定期运行刷新数量?

杜福忠

2022-02-18 17:38

@zeroabc totalRow 总行数这个值的来源,如果想在后端缓存起来。
第一次查得总行后,存起来供下次使用就行。
至于咋存咋取值,完全就是业务处理了,没有最好只有符合业务情况的才是好,可以CacheKit,可以Redis,可以ConcurrentHashMap。
可以为该业务或请求加个参数拦截器,拦截查询参数作为键都是可以的。
MyDbPro.paginate(totalRow,XX
总的来看,前端缓存totalRow相对折中灵活一些。比如查询按钮会设置totalRow为null。长时间停留在页面,js还可以定时执行设置totalRow为null。刷新页面也会设置totalRow为null。这些都是后话了,需要看场景再决定咋缓存totalRow

zeroabc

2022-02-18 21:14

@杜福忠 确实是的,你分享的这个方法是很容易实践的,改动小。就是不知道大厂们是怎么应对这个大表查询count的性能问题的,之前项目遇到过,也是暴力提升数据库性能解决的,还好不是我们公司买单