问题场景
- 在使用3.2.3版本(14年的时候)
com.github.pagehelper
的PageHelper
类时,源码用的是:
private String getCountSql(String sql) {
return "select count(0) from (" + sql + ") tmp_count";
}
- 因为公司不是直接用了这个jar包,而是拿着源码改进自己维护了一个Page类,所以我觉得当sql比较复杂,比如说带有order by时,这个getCountSql(),可以适当的优化一下。
功能拟定
- 写一个方法,传入原来的sql,返回获得总数的sql,且该sql要足够简单并且可执行。
编码如下
select * from table;
select count(1) from table;
- 因此得考虑获取from下标,所以还得考虑子查询带from的问题。以下是我的编码:
public class CountSql {
public static void main(String args[]) {
String sql = "select \r\n" +
"(select 1 from dual) xx,\r\n" +
"(select 1 from dual order by '1' limit 1) xx2\r\n" +
"from `123` \r\n" +
"where EXISTS (select 1 from dual)\r\n" +
"group by `group`\r\n" +
"order by `group`";
System.out.println(sql);
System.out.println("------------------");
System.out.println(getCountSql(sql));
}
private static String getCountSql(String sql) {
sql = sql.toLowerCase().trim();
int fromIndex = -1, orderIndex = -1, groupIndex = -1;
int count = 0;
for (int i = 0; i < sql.length(); i++) {
char c = sql.charAt(i);
if (c == '(') {
count++;
} else if (c == ')') {
count--;
} else if (c == 'f' && fromIndex == -1
&& count == 0 && isMatch(sql, i, "from")) {
fromIndex = i;
} else if (c == 'o' && orderIndex == -1
&& count == 0 && isMatch(sql, i, "order")) {
orderIndex = i;
} else if (c == 'g' && groupIndex == -1
&& count == 0 && isMatch(sql, i, "group")) {
groupIndex = i;
}
}
if (orderIndex != -1 && orderIndex > groupIndex) {
sql = sql.substring(0, orderIndex);
}
if (groupIndex == -1) {
sql = "select count(1) " + sql.substring(fromIndex);
} else {
sql = "select count(1) from (select 1 " + sql.substring(fromIndex) + ") tbl";
}
return sql;
}
private static boolean isMatch(String source, int index, String compare) {
if (source == null || compare == null) return false;
int size = compare.length();
if (index <= 0 || !isKG(source.charAt(index-1))) return false;
if (index + size >= source.length() || !isKG(source.charAt(index + size))) return false;
return compare.equals(source.substring(index, index + size));
}
private static boolean isKG(char c) {
return c ==' ' || c == '\n' || c == '\r' || c == '\t' || c == '\f';
}
}
- 个人觉得应该没什么大的问题。如果有更好的想法,希望能一起讨论一下。