JFINAL在处理SQL分页时比较粗暴,基本逻辑就是在原有的SQL基础上包裹一层分页的SELECT语句,具体的实现代码可以查 Dialect 的相关子类;Sqlserver对应的是 SqlServerDialect;分页的SQL处理代码如下:
public String forPaginate(int pageNumber, int pageSize, StringBuilder findSql) { int end = pageNumber * pageSize; if (end <= 0) { end = pageSize; } int begin = (pageNumber - 1) * pageSize; if (begin < 0) { begin = 0; } StringBuilder ret = new StringBuilder(); ret.append("SELECT * FROM ( SELECT row_number() over (order by tempcolumn) temprownumber, * FROM "); ret.append(" ( SELECT TOP ").append(end).append(" tempcolumn=0,"); ret.append(findSql.toString().replaceFirst("(?i)select", "")); ret.append(")vip)mvp where temprownumber>").append(begin); return ret.toString(); }
这代码基本上可以解决90%的分页问题,但是如果查询语句中使用了 WITH AS 短语则会报错。因为 分页语句要放在WITH AS 短语 后面的 SELECT
解决这个问题可以写个子类重写该方法,在子类中加入对WITH AS的处理;
-
- 先判断是否以WITH开头,将SQL拆成WITH AS部分,与SELECT部分
-
- SELECT部分SQL通过父类的forPaginate函数处理,处理结束后再拼接WITH AS 部分
public class SqlServerDialectFix extends SqlServerDialect { /** * sql.replaceFirst("(?i)select", "") 正则中带有 "(?i)" 前缀,指定在匹配时不区分大小写 * 修复WITH AS 开头 */ public String forPaginate(int pageNumber, int pageSize, StringBuilder findSql) { String tempSql = findSql.toString(); //临时SQL String tempSubSql = tempSql; int pos = -1; while (tempSubSql.toUpperCase().trim().startsWith("WITH")) { //WITH开头,提取WITH部分 int indexOfAs = tempSubSql.indexOf("AS"); //第一个AS位置 String temp = tempSubSql.substring(indexOfAs, tempSubSql.length()); char[] chars = temp.toCharArray(); Stack<Integer> stack = null; int i = 0; for (char aChar : chars) { i++; if (aChar == '(') { if (stack == null) { stack = new Stack<Integer>(); } stack.push(0); } if (aChar == ')') { if (stack != null) { stack.pop(); } } if (stack != null && stack.size() == 0) { pos = indexOfAs + i; tempSubSql = tempSubSql.substring(pos,tempSubSql.length()); break; } } } if (pos != -1) { String with = tempSql.substring(0, pos); //WITH部分 return with + super.forPaginate(pageNumber, pageSize, new StringBuilder(tempSql.substring(pos, tempSql.length()))); } return super.forPaginate(pageNumber, pageSize, findSql); } }
最后在configPlugin 中设置数据源插件 ActiveRecordPlugin 的Dialect为SqlServerDialectFix。
JFINAL 的 SqlServerDialect 处理 WITH AS 语句分页问题