数据库分页(有时候忘了):
mysql:
public String getLimitString(String sql, Integer offset, Integer limit) {
sql = sql.trim();
sql+=" limit "+offset+","+limit;
return sql;
}
sqlserver:
public String getLimitString(String sql, Integer offset, Integer limit) {
sql = sql.trim();
int i=sql.indexOf("order");
String sqlfinal=sql.substring(0, i);
String order=sql.substring(i);
StringBuffer pageSql = new StringBuffer(sql.length() + 100);
// 其实这里还是有一点问题的,就是排序问题,指定死了,有解决的提供一下,等复习到Hibernate看看Hibernat内部是如何实现的。
pageSql.append("select * from(select a.*,row_number() over ("+order+") rownum from( ");
pageSql.append(sqlfinal);
pageSql.append(") a )b where rownum> " + offset + " and rownum <= " + (offset + limit));
return pageSql.toString();
}
oracle:
public String getLimitString(String sql, Integer offset, Integer limit) {
sql = sql.trim();
StringBuffer pagingSelect = new StringBuffer(sql.length() + 100);
pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
pagingSelect.append(sql);
pagingSelect.append(" ) row_ ) where rownum_ > ").append(offset).append(" and rownum_ <= ").append(offset + limit);
return pagingSelect.toString();
}