- /*
- * @(#)SqlServer2005PageHepler.java 0.2 08/09/20
- * Copyright 2009 Zonrong, Inc. All rights reserved.
- */
- import java.util.regex.Matcher;
- import java.util.regex.Pattern;
- /**
- * SQL SERVER 2005 分页辅助类.
- * @author 黄明杰
- * @version 0.2
- */
- public class SqlServer2005PageHepler {
- /**
- * 得到查询总数的SQL
- * @param querySelect 查询SQL
- * @return 得到总数的SQL
- */
- public static String getCountString(String querySelect) {
- querySelect = getLineText(querySelect);
- int orderIndex = getLastOrderInsertPoint(querySelect);
- int formIndex = getAfterFormInsertPoint(querySelect);
- String select = querySelect.substring(0, formIndex);
- //如果SELECT 中包含 group by 或者 distinct 就在外层包含COUNT
- if (select.startsWith("select distinct") || isHaveGroupBy(querySelect)) {
- return new StringBuffer(querySelect.length()).append(
- "select count(1) '_count' from (").append(
- querySelect.substring(0, orderIndex)).append(" ) _t")
- .toString();
- }else {
- return new StringBuffer(querySelect.length()).append(
- "select count (1) '_count' ").append(
- querySelect.substring(formIndex, orderIndex)).toString();
- }
- }
- /**
- * 得到分页的SQL
- * @param offset 偏移量
- * @param limit 限度
- * @return 分页SQL
- */
- public static String getLimitString(String querySelect,int offset, int limit) {
- querySelect = getLineText(querySelect);
- int orderIndex = getLastOrderInsertPoint(querySelect);
- StringBuilder sb = new StringBuilder(querySelect.length())
- .append("select * from (select *,ROW_NUMBER() OVER (")
- .append(querySelect.substring(orderIndex).replaceAll("[^//s,]+//.", ""))
- .append(") _row_num from (")
- .append(querySelect.substring(0, orderIndex)).append(") _t")
- .append(") _t where _t._row_num > ")
- .append(offset == -1 ? "?" : offset)
- .append(" and _t._row_num <= ")
- .append(limit == -1 ? "?" : limit);
- return sb.toString();
- }
- /**
- * 判断是否包含正确group by
- * @return 如果包含返回True否则返回false
- */
- private static boolean isHaveGroupBy(String querySelect){
- int groupIndex = querySelect.toLowerCase().lastIndexOf("group by");
- if (groupIndex != -1 && isBracketCanPartnership(querySelect.substring(groupIndex,querySelect.length()))) {
- return true;
- }
- return false;
- }
- /**
- * 得到最后一个order By的插入点位置
- * @return 返回最后一个order By插入点的位置
- * @throws RuntimeException 如果不存在正确的order by
- */
- private static int getLastOrderInsertPoint(String querySelect){
- int orderIndex = querySelect.toLowerCase().lastIndexOf("order by");
- if (orderIndex == -1
- || !isBracketCanPartnership(querySelect.substring(orderIndex,querySelect.length()))) {
- throw new RuntimeException("SQL 2005 分页必须要有Order by 语句!");
- }
- return orderIndex;
- }
- /**
- * 将SQL语句变成一条不换行语句,并且每个单词的间隔都是1个空格
- * @param sql 需要转化的文本
- * @return 如果sql是NULL返回空,否则返回转化后的SQL
- */
- private static String getLineText(String text) {
- return text.replaceAll("[/r/n]", " ").replaceAll("//s{2,}", " ");
- }
- /**
- * 得到SQL第一个正确的FROM的的插入点
- * @param querySelect 完整的查询语句
- * @return 正确的FROM插入点
- */
- private static int getAfterFormInsertPoint(String querySelect) {
- String regex = "//s+FROM//s+";
- Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
- Matcher matcher = pattern.matcher(querySelect);
- while (matcher.find()) {
- int fromStartIndex = matcher.start(0);
- String text = querySelect.substring(0, fromStartIndex);
- if (isBracketCanPartnership(text)) {
- return fromStartIndex;
- }
- }
- return 0;
- }
- /**
- * 判断括号'()'是否匹配
- * @param text 要判断的文本
- * @return 如果匹配返回TRUE,否则返回FALSE
- */
- private static boolean isBracketCanPartnership(String text) {
- if (text == null
- || (getIndexOfCount(text, '(') != getIndexOfCount(text, ')'))) {
- return false;
- }
- return true;
- }
- /**
- * 得到一个字符在另一个字符串中出现的次数
- * @param text 文本
- * @param ch 字符
- * @return 字符出现在文本的次数.
- */
- private static int getIndexOfCount(String text, char ch) {
- int count = 0;
- for (int i = 0; i < text.length(); i++) {
- count = (text.charAt(i) == ch) ? count + 1 : count;
- }
- return count;
- }
- }
注意:
不支持"UNION" 和“UNION ALL” 这样的结果合并语句,例如:
1. select * from dbo.[user]
2. union
3. select top 1 * from dbo.[user] order by age
主要原因是 UNION 这样的语句没有共同的order by 条件,所以不想支持,如果你需要得到正确的结果你需要这样写,如下:
1. select * from (
2. select * from dbo.[user]
3. union
4. select top 1 * from dbo.[user] order by age
5. ) Q order by id