java分页查询(oracle)dao样例

来源：互联网发布：流星网络电视app 编辑：程序博客网时间：2024/05/29 11:48
/** * 根据用户ID和时间范围查询条件得到相关记录    * @author Bimy * @created 2015年6月23日 * @lastModified  * @param empCode     员工ID * @param beginDate   开始时间 * @param endDate     结束时间 * @param currentPage 当前页面 * @param pageSize    每页行数 * @return QueryResult * void */public QueryResult getPayInfoBySearch(String empCode,Date beginDate,Date endDate,int currentPage,int pageSize) throws Exception{QueryResult qr  = new QueryResult();String sql      = "select * from ... where em.emp_oid='"+empCode+"'";                  //根据员工ID设置查询语句String countSql = "select count(*) totalCount from ... where em.emp_oid='"+empCode+"'; //设置查询数目的语句if(beginDate!=null){    //如果有开始时间则加上开始时间约束String bgDate = format.format(beginDate);sql = sql + "and em.cost_datetime>=to_date('"+ bgDate +" 00:00:00', 'yyyy-mm-dd hh24:mi:ss')";countSql = countSql + "and em.cost_datetime>=to_date('"+ bgDate +" 00:00:00', 'yyyy-mm-dd hh24:mi:ss')";}if(endDate!=null){      //如果有结束时间则加上结束书剑约束String edDate = format.format(endDate);sql = sql + "and em.cost_datetime<=to_date('"+ edDate +" 23:59:59', 'yyyy-mm-dd hh24:mi:ss')";countSql = countSql + "and em.cost_datetime<=to_date('"+ edDate +" 23:59:59', 'yyyy-mm-dd hh24:mi:ss')";}Connection conn = DBConnection.getConnection();Statement stmt  = conn.createStatement();ResultSet countRS = stmt.executeQuery(countSql);if(countRS.next()) {    //根据查询的数目填写分页的页码信息qr.setTotalCount(countRS.getInt("totalCount"));qr.setMaxPage((int)Math.ceil((double)countRS.getInt("totalCount")/(double)pageSize)); qr.setCurrentPage(currentPage);qr.setPageSize(pageSize);if(qr.getMaxPage() < qr.getCurrentPage()){  //若最大可先显示页码小于当前应页码则直接返回return qr;}}else{return null;}//进行分页查询String querySql = "select * from(select * from(select t.*,row_number() over(order by COST_DATETIME desc) as rownumber from("+ sql +") t) p where p.rownumber>"+((currentPage-1)*pageSize)+") where rownum<="+pageSize+"";ResultSet queryRS = stmt.executeQuery(querySql);List<Object> resultList = new ArrayList<Object>();String account ="";String description ="";String payMoney ="";String originalMoney ="";String payTime ="";while(queryRS.next()){    //将查询结果放入VO中PayRecordVO record = new PayRecordVO();account       = queryRS.getString("COST_REMAIN");description   = queryRS.getString("COST_DESNAME");                        ...record.setEmpCode(empCode);                        ...            resultList.add(record);}qr.setResultList(resultList);stmt.close();conn.close();return qr;}
0 0