如题，扩展这个功能的需求是：遇到一些慢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一起吧，重在思路。
好了，有参考价值就点个赞呗~ 有特殊业务自行改造吧~
