这又2种分页查询的sql语句,你看看吧select top 10 * from tableName where id not in (select top 20 id from tableName) order by id) order by id select * from (select row_number() over(order by id) as row, * from tableName) as Rank where row between 21 and 30 order by id中间的表名,列名,排序列名换你的表里面信息就可以
这是我以前做过的一个新闻发布系统中所用到的分页算法:public List getOnePageNews(int pageNo, int pageSize) { // pageNo:当前请求的页码(第几页), pageSize:当前页面所要显示的记录条数 StringBuffer sqlStr = new StringBuffer(); sqlStr.append("select * from News_Info"); sqlStr.append(" order by news_id desc"); sqlStr.append(" limit ?,?"); Connection conn = null; try { conn = Database.getConnection(); PreparedStatement preparedStatement = conn.prepareStatement(sqlStr .toString()); int i = 1; preparedStatement.setInt(i++, (pageNo - 1) * pageSize); preparedStatement.setInt(i++, pageNo * pageSize); ResultSet rs = preparedStatement.executeQuery(); return DTOPopulator.populate(rs, News.class); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { Database.releaseConnection(conn); } return new ArrayList(); }至于在JSP页面里调用该方法然后将List中的记录显示出来的代码就不用我再贴出来了吧。
where id not in (select top 20 id from tableName) order by id)
order by id
select * from
(select row_number() over(order by id) as row, * from tableName) as Rank
where row between 21 and 30 order by id中间的表名,列名,排序列名换你的表里面信息就可以
StringBuffer sqlStr = new StringBuffer();
sqlStr.append("select * from News_Info");
sqlStr.append(" order by news_id desc");
sqlStr.append(" limit ?,?");
Connection conn = null;
try {
conn = Database.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sqlStr
.toString());
int i = 1;
preparedStatement.setInt(i++, (pageNo - 1) * pageSize);
preparedStatement.setInt(i++, pageNo * pageSize);
ResultSet rs = preparedStatement.executeQuery(); return DTOPopulator.populate(rs, News.class);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
Database.releaseConnection(conn);
}
return new ArrayList();
}至于在JSP页面里调用该方法然后将List中的记录显示出来的代码就不用我再贴出来了吧。
当数据量大时是select 耗费大还是得到结果集rs.getXXX()的耗费大了?