因为需要使用JdbcTemplate来查询,所以自己根据mybatisplus分页插件来封装分页工具类
首先mybatisplus的方言接口为IDialect,然后通过这个接口查找到想要的分页方法
SQLSERVER为例子(plus中的类为:SQLServer2005Dialect),直接全部拿来然后自己封装方法,其中originalSql为原生的SQL,offset为页码(表达式:(pageInfo.getPageNum() - 1) * pageInfo.getPageSize(),limit为每页查询的数量:pageInfo.getPageSize());其他数据库按照方法封装即可。
package com.aadata.dataManage.utils;
public class PageInfoBuildUtil {
/**
* 封装SQLserversql分页
* @param originalSql
* @param offset
* @param limit
* @return
*/
public static String buildPaginationSql(String originalSql, long offset, long limit) {
StringBuilder pagingBuilder = new StringBuilder();
String orderby = getOrderByPart(originalSql);
String distinctStr = "";
String loweredString = originalSql.toLowerCase();
String sqlPartString = originalSql;
if (loweredString.trim().startsWith("select")) {
int index = 6;
if (loweredString.startsWith("select distinct")) {
distinctStr = "DISTINCT ";
index = 15;
}
sqlPartString = sqlPartString.substring(index);
}
pagingBuilder.append(sqlPartString);
// if no ORDER BY is specified use fake ORDER BY field to avoid errors
if (com.baomidou.mybatisplus.core.toolkit.StringUtils.isEmpty(orderby)) {
orderby = "ORDER BY CURRENT_TIMESTAMP";
}
long firstParam = offset + 1;
long secondParam = offset + limit;
String sql = "WITH selectTemp AS (SELECT " + distinctStr + "TOP 100 PERCENT " +
" ROW_NUMBER() OVER (" + orderby + ") as __row_number__, " + pagingBuilder +
") SELECT * FROM selectTemp WHERE __row_number__ BETWEEN " +
//FIX#299:原因:mysql中limit 10(offset,size) 是从第10开始(不包含10),;而这里用的BETWEEN是两边都包含,所以改为offset+1
firstParam + " AND " + secondParam + " ORDER BY __row_number__";
return sql;
}
private static String getOrderByPart(String sql) {
String loweredString = sql.toLowerCase();
int orderByIndex = loweredString.indexOf("order by");
if (orderByIndex != -1) {
return sql.substring(orderByIndex);
} else {
return "";
}
}
}