数据库分页，Oracle 和 mysql

  分页有好几种

     但在数据库中进行分页还是比较可行的方法，在Oracle 中分页是用rownum关键字，而MySQL是用limit关键字，SqlServer 是使用top关键字。

  MySQL 分页代码：

 

  String pageSql= "SELECT * FROM ("+sql+") temp LIMIT "+(pagination.getPageNo()-1)*pagination.getRowsPerPage()+","+pagination.getPageNo()*pagination.getRowsPerPage(); String countSql="SELECT COUNT(*) FROM("+sql+") temp"; System.out.println("pageSql:"+pageSql); System.out.println("countSql:"+countSql); List list = this.getMysqlTemplate().queryForList(pageSql); int count=this.getMysqlTemplate().queryForInt(countSql); pagination.setTotalCount(count);

  在Oracle 中是

     public List queryForPage(String sql, Pagination pagination) throws Exception { int sumRecord = getTotalCount(sql); pagination.setTotalCount(sumRecord); int startRecord = pagination.getStartPosition(); int endRecord = pagination.getStartPosition() + pagination.getRowsPerPage(); String tempSql = "SELECT * FROM ( SELECT A.*, ROWNUM RN FROM ( " + sql + " ) A WHERE ROWNUM <= " + endRecord + " ) WHERE RN >" + startRecord; System.out.println("JDBC:QUERY:" + tempSql); return jdbcTemplate.queryForList(tempSql); } public List queryForPage(String sql, Pagination pagination, Object[] List) throws Exception { int sumRecord = (Integer)jdbcTemplate.queryForInt(getTotalCountsql(sql), List); pagination.setTotalCount(sumRecord); int startRecord = pagination.getStartPosition(); int endRecord = pagination.getStartPosition() + pagination.getRowsPerPage(); String tempSql = "SELECT * FROM ( SELECT A.*, ROWNUM RN FROM ( " + sql + " ) A WHERE ROWNUM <= " + endRecord + " ) WHERE RN >" + startRecord; return jdbcTemplate.queryForList(tempSql, List); }