MySQL分页SQL和ORACLE分页SQL

最新推荐文章于 2024-07-24 08:15:05 发布

sev7en

最新推荐文章于 2024-07-24 08:15:05 发布

阅读量769

点赞数

分类专栏：数据库学习-MySql 数据库学习-ORACLE

数据库学习-ORACLE 同时被 2 个专栏收录

29 篇文章 0 订阅

订阅专栏

数据库学习-MySql

0 篇文章 0 订阅

订阅专栏

MySQL分页SQL和ORACLE分页SQL

(2009-09-10 11:46:52)

转载▼

标签：

it

分类： Oracle/Mysql

MySQL:

select * from table(表名) limit startPos,pageSize

注：

1，startPos:定义当前页起始位置注意：当前页的起始位置只能在当前页确定之后才能定义

2，pageSize:定义每页显示数据的条数

3，举例说明：

public List findAll(String lfrxm, String xsxm) {

List infoList = null;
StringBuffer sql = new StringBuffer();

   sql
     .append("select l.djid,l.lfrxm,l.lfrxb, f_get_syscode('xingbie',l.lfrxb) lfrxbname,x.xsxm,z.ssid,l.yyfrgx ,f_get_syscode('guanxi',l.yyfrgx) yyfrgxname,l.lfsj,l.lksj,l.zjmc ,f_get_syscode('zhengjianmingcheng',l.zjmc) zjmcname,l.zjh from lfrydj as l,xsrz as z,xsjbxx as x");
   sql.append(" where l.xh = x.xh and l.xh = z.xh ");
   if (lfrxm != null && !lfrxm.equals("")) {
    sql.append(" and w.lfrxm='").append(lfrxm).append("' ");
   }
   if (xsxm != null && !xsxm.equals("")) {
    sql.append(" and x.xsxm='").append(xsxm).append("' ");
   }

try {
infoList = dbutil.selectMapList(sql.toString());

   } catch (DAOSystemException e) {
   } catch (DAONoRecordException e) {
   } catch (Exception e) {
    e.printStackTrace();
    Log.add_info("查询数据失败");
   }
   return infoList;
}

public List search(String lfrxm, String xsxm, int startPos, int pageSize) {
   List infoList = null;
   StringBuffer sql = new StringBuffer();
   sql
     .append("select l.djid,l.lfrxm,l.lfrxb, f_get_syscode('xingbie',l.lfrxb) lfrxbname,x.xsxm,z.ssid,l.yyfrgx ,f_get_syscode('guanxi',l.yyfrgx) yyfrgxname,l.lfsj,l.lksj,l.zjmc ,f_get_syscode('zhengjianmingcheng',l.zjmc) zjmcname,l.zjh from lfrydj as l,xsrz as z,xsjbxx as x");
   sql.append(" where l.xh = x.xh and l.xh = z.xh");
   if (lfrxm != null && !lfrxm.equals("")) {
    sql.append(" and w.lfrxm='").append(lfrxm).append("' ");
   }
   if (xsxm != null && !xsxm.equals("")) {
    sql.append(" and x.xsxm='").append(xsxm).append("' ");
   }

sql.append(" limit " + startPos + " , " + pageSize + "");

System.out.println("sql=====================" + sql.toString());

   try {
    infoList = dbutil.selectMapList(sql.toString());
   } catch (DAOSystemException e) {
   } catch (DAONoRecordException e) {
   } catch (Exception e) {
    Log.add_info("查询数据失败");
   }
   return infoList;
}

}

public static void search(String lfrxm, HttpServletRequest request,
String xsxm) {

WlryglEntity entity = new WlryglEntity();

// 查询所有数据
List totalList = entity.findAll(lfrxm, xsxm);

   System.out.println(totalList + "777777777777777777777777777777");
   // 定义总共有多少条数据
   int totalRecords;
   try {
    totalRecords = totalList.size();
   } catch (Exception e) {
    totalRecords = 1;
   }

   // 定义每页显示数据的条数
   int pageSize = 6;
   // 定义当前页
   int pageNO;
   String pageNumber = String.valueOf(
     request.getParameter("pageNO") != null ? request
       .getParameter("pageNO") : "").trim();
   if (pageNumber == null || pageNumber.equals("")) {
    pageNO = 1;
   } else {
    try {
     pageNO = Integer.parseInt(pageNumber);
    } catch (NumberFormatException e) {
     pageNO = 1;
    }
    if (pageNO <= 0) {
     pageNO = 1;
    }
   }
   // 定义总共有多少页
   int totalPages = totalRecords % pageSize == 0 ? totalRecords / pageSize
     : totalRecords / pageSize + 1;
   // 当前页大于总页数的时候
   if (pageNO > totalPages) {
    pageNO = totalPages;
   }
   // 定义当前页起始位置注意：当前页的起始位置只能在当前页确定之后才能定义
   int startPos = (pageNO - 1) * pageSize;

   List infoList = entity.search(lfrxm, xsxm, startPos, pageSize);
   request.setAttribute("pageNO", pageNO);
   request.setAttribute("totalPages", totalPages);
   request.setAttribute("totalRecords", totalRecords);
   request.setAttribute("pageSize", pageSize);
   request.setAttribute("infoList", infoList);

}

Oracle:

"select * from (select asTable.*,rownum row_id from ("
+ SQL+ ") asTable where rownum<=" + endRow
+ ") where row_id>" + startRow"

注：

1，rownum是oracle的伪列，在sql语句中标记行号；但是简单的把它认为是行号也是不正确的，因为使用rownum=1或者rownum>1或者用上between and是无效果的。我们只能在语句中使用rownum<10这样的语句来返回结果集，它的作用可以看作类似于mssql中的top。

3，startRow：当前页开始位置 endRow：当前页的结束位置

4，SQL：她才是你要真正查询的SQL。

5，举例说明：

public List findcjbgxx(String zkzh, String czry, String czrq,
    String usercode, HttpServletRequest request) {
   StringBuffer sql = new StringBuffer();
   String fenyesql = null;
   List fenyeList = null;

   // 分页处理
   int pageSize = 6; // 每页记录数
   int currentPage = 1; // 当前页数
   int startRow; // 开始位置
   int endRow; // 结束位置
   if (request.getParameter("page") != null
     && request.getParameter("page").trim().length() > 0) {// 默认为首页
    currentPage = Integer.parseInt(request.getParameter("page"));
   }
   startRow = (currentPage - 1) * pageSize;
   endRow = currentPage * pageSize;

   sql
     .append("select zkzh,kcdm,ksqh,kszcj,hgbj,wjbj,qkbj,qxdm,czry,to_char(czrq,'yyyy-mm-dd') czrq,spry,to_char(sprq,'yyyy-mm-dd') sprq from kj_kscj_bg ");
   sql.append(" where 1=1 ");
   if (zkzh != null && !zkzh.equals("")) {
    sql.append("and zkzh='").append(zkzh).append("' ");
   }
   if (czry != null && !czry.equals("")) {
    sql.append("and czry='").append(czry).append("' ");
   }
   if (czrq != null && !czrq.equals("")) {
    sql.append("and to_char(czrq,'yyyy-mm-dd') ='").append(czrq)
      .append("' ");
   }

   fenyesql = "select * from (select asTable.*,rownum row_id from ("
     + sql.toString() + ") asTable where rownum<=" + endRow
     + ") where row_id>" + startRow;

try {

fenyeList = dbutil.selectMapList(fenyesql);
} catch (DAOSystemException e) {

   } catch (DAONoRecordException e) {
    e.printStackTrace();
    Log.logtxt_warn(usercode, "查询数据异常", e);
   }

request.setAttribute("pageSize", new Integer(pageSize));

request.setAttribute("currentPage", new Integer(currentPage));
return fenyeList;

}