数据库mysql和oracel在应用中的分页实现

来源：互联网发布：淘宝店名的重要性编辑：程序博客网时间：2024/05/21 15:38

oracle:

if (CommonMethod.isTextEqual(pageTurn, Constants.TURN_PAGE_YES)) { //如果传参为要分页

                    sqlBuffer.append("select * from (select dddddd.*,count(*) as row_num from (").append(sql).append(") dddddd <=,").append((Integer.parseInt(currPage)) * pageCount).append(
                              ") kkkkkk where kkkkkk.row_num >= ");
                    sqlBuffer.append((Integer.parseInt(currPage) - 1) * pageCount + 1);
               } else {
                    sqlBuffer.append(sql);
               }

logger.info(sql);
               ps = con.prepareStatement(sqlBuffer.toString());
               rs = ps.executeQuery();
               return CommonMethod.processResultMedaData(rs);}
          finally{
               close(rs,ps,con);
          }

select * from (

select d.*,count(*) as row _num from (

select u_user.*,u_user_org.org_no from u_user left join u_user_org on u_user.user_id = u_user_org.user_id where 1=1

) d where row_num <= currPage*pageCount )

where row_num >= (currPage-1)*pageCount+1;

!!!!上面的大于和小于等于符号不能变，应该先《=再》=这样的数据量小一些

mysql:

try{

               con = dataSource.getConnection();
               StringBuffer sqlBuffer = new StringBuffer("");
               int startIndex = (Integer.parseInt(currPage) - 1) * pageCount;
               if (CommonMethod.isTextEqual(pageTurn, Constants.TURN_PAGE_YES)) {
                    sqlBuffer.append("select * from (select dddddd.* from (").append(sql).append(") dddddd limit ").append(startIndex).append(",").append(pageCount).append(
                              ") kkkkkk ");
               } else {
                    sqlBuffer.append(sql);
               }
               logger.info(sql);
               ps = con.prepareStatement(sqlBuffer.toString());
               rs = ps.executeQuery();
               return CommonMethod.processResultMedaData(rs);}
          finally{
               close(rs,ps,con);
          }

select * from （

select d.*,count(*) as row _num from (

select u_user.*,u_user_org.org_no from u_user left join u_user_org on u_user.user_id = u_user_org.user_id where 1=1

）d limit (currPage-1)*pageCount , pageCount) ;

0 0