代码语言：javascript复制
/**
* 根据用户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;
}
