原理很简单,传入要查询的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;
}
}