MySQL分页SQL和ORACLE分页SQL

MySQL分页SQL和ORACLE分页SQL

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

mysql

 

oracle

 

分页sql

 

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;

}

相关文章
相关标签/搜索
每日一句
    每一个你不满意的现在,都有一个你没有努力的曾经。
公众号推荐
   一个历史类的公众号,欢迎关注
一两拨千金