封装 sqlserver2005 以上的 hibernate 方言 Dialect

原理很简单，传入要查询的SQL 语句，得到所有数据，根据PAGE SIZE 计算ROW_NUMBER, 然后再包装一个SQL语句就可以了。但这里是用Hibernate做，所以直接实现Hibernate Dialect 方言比较好.

package com.ruijie.spl.common.dialect;

/**
* SQLServer2005的Dialect，主要封装2005的高效分页语句
* 
*/
import org.hibernate.dialect.SQLServerDialect;

import com.ruijie.spl.common.exception.RgsplRuntimeException;

public class SQLServer2005Dialect extends SQLServerDialect {

    public SQLServer2005Dialect() {
        super();
    }

    public String getLimitString(String querySelect, int offset, int limit) {
        String[] querySelects = getQuerySelects(querySelect);
        StringBuffer result = new StringBuffer(querySelect.length() + 100);
        result.append("select * from(");
        result.append(querySelects[0]);
        result.append(",row_number()over(");
        result.append(querySelects[2]);
        result.append(") as row");
        result.append(querySelects[1]);
        result.append(") tmp where tmp.row between ");
        result.append(offset+1);
        result.append(" and ");
        result.append(limit);
        return result.toString();
    }

    // 通过sql语句，得到相应的select、from and where、order by三条语句
    private String[] getQuerySelects(String querySelect) {
        String[] result = new String[3];
        if (querySelect.indexOf(" from ") == -1
                || querySelect.indexOf(" order by ") == -1) {
            throw new RgsplRuntimeException("不支持不带from和order by的分页查询语句");
        }
        String select = querySelect.substring(0, querySelect.indexOf(" from "));
//        while (select.endsWith(" ")) {
//            select.substring(0, select.length() - 1);
//        }
        result[0] = select;
        result[1] = querySelect.substring(querySelect.indexOf(" from "),
                querySelect.indexOf(" order by "));
        result[2] = querySelect.substring(querySelect.indexOf(" order by "));
        return result;
    }

    public boolean supportsLimitOffset() {
        return true;
    }

}