在不改变报表查询所有数据的sql的基础上,调用一个方法来使sql变成分页查询:(在没有order by 的基础上)
public static void main(String[] args){ StringBuffer sql = new StringBuffer(); sql.append(" select uid,uname,usex,uage "); sql.append(" from person where 1=1 "); String[] fields = { "id","name","sex","age"}; page( sql.toString(), fields); // System.out.println( str.replaceFirst("select", "select rownum as rowno ,") ); }
//分页通用 public String page(String sql,String[] fields){ String json=""; //根据查询条件获得的总数量 String totalsql="select count(1) " +sql.substring(sql.indexOf("from")); int totalSize=getTotalSize(totalsql); System.out.println("分页通用totalSize:"+totalSize); if(totalSize>0){ //每页大小 int pageSize = this.request.getParameter("pageSize");//第几页 String startPage=this.request.getParameter("startPage"); //用来看第几页的数据 int currentPage = 0; if (startPage != null) { currentPage = Integer.parseInt(startPage); } //一共多少页 int pageCount=totalSize%pageSize==0 ? totalSize/pageSize : totalSize/pageSize+1; //从第多少条数据开始,到多少条截止 int startCount=0; int endCount=0; if(currentPage !=0){ startCount=currentPage*pageSize- pageSize + 1; if(currentPage*pageSize <= totalSize){ endCount=currentPage * pageSize; } if(currentPage*pageSize > totalSize){ endCount=totalSize % pageSize+(currentPage-1)*pageSize; } }
//注意,将 select 替换后,sql 变成了 select rownum as rowno ,uid,uname,usex,uage from person where 1=1 sql=sql.replaceFirst("select", "select rownum as rowno ,"); StringBuffer fysql=new StringBuffer(" select * from( "); //注意 fysql 是: select * from( select rownum as rowno ,uid,uname,usex,uage from person where 1=1 and rownum<=10 ) table_alias where table_alias.rowno>=1
fysql.append(sql); fysql.append(" and rownum<= ") .append(endCount) .append(" ) table_alias") .append(" where table_alias.rowno>=") .append(startCount); System.out.println("分页通用sql:"+fysql); List list = getYSList(fysql.toString()); JacksonUtil util=new JacksonUtil(); json=util.writeArrayJSON(fields, list); json="{pager:{pageCount:"+pageCount+",recordCount:"+totalSize+"},data:"+json+"}"; printResult("success", json); }else{ json = "{pager:{pageCount:0,recordCount:0},data:[]}"; printResult("success", json); } return null; }
根据查询条件获得的总数量
public int getTotalSize(String strSql) { Statement stmt = null; ResultSet rs = null; int totalSize=0; try {
//数据库的连接 Class.forName("oracle.jdbc.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:oa", "123", "123"); stmt = conn.createStatement(); rs = stmt.executeQuery(strSql); if(rs.next()) { totalSize=Integer.parseInt(rs.getString(1)); } rs.close(); stmt.close(); conn.close(); } catch (Exception e) { e.printStackTrace(); } finally { if (rs != null) { rs = null; } if (stmt != null) { stmt = null; } if (conn != null){ conn = null; }return totalSize; }
}