/** * 查询结果的总数,常用的地方就是查询分页时的总记录数 * @param sql:原始sql语句! * @return 查询结果的总数 * @throws SQLException */ public int getsqlcount(String sql) throws SQLException{ DataBase db = new DataBase(); this.con = db.getCon(); String nsql = "select count(*) from (" + sql + ")"; System.out.println("查询总数的SQL:--->"+nsql); try { st = con.createStatement(); rs = st.executeQuery(nsql); if (rs.next()) { return rs.getInt(1); } else { return 0; } } catch (SQLException e) { System.out.println("执行查询总数sql异常,不能执行!"); throw new SQLException(e.toString()); } finally { try { if(con!=null) con.close(); if(st!=null) st.close(); if(rs!=null) rs.close(); System.out.println("siyu--getsqlcount-->关闭连接成功!"); } catch (Exception e) { e.printStackTrace(); System.out.println("关闭数据连接有误!!"); } } } /** * 查找分页的sql语句的数据 * @param sql 原始sql语句! * @param currentpage 当前页 * @param pagesize 每页大小 * @return 返回满足该条件的结果集,以List<String[]> 形式返回 */ public List<String[]> getsqllistset(String sql, int currentpage, int pagesize) throws SQLException { if (currentpage <= 0) { currentpage = 1; } if (pagesize < 1 || pagesize > 100) { pagesize = 10; } int startrow = (currentpage - 1) * pagesize; String nsql = "select * from (select a.*, rownum r from (" + sql + ") a where rownum <= " + (startrow + pagesize) + ") where r > " + startrow; System.out.println("分页sql语句:" + nsql); try { DataBase db = new DataBase(); this.con = db.getCon(); st = con.createStatement(); rs = st.executeQuery(nsql); ResultSetMetaData rsmd = rs.getMetaData(); int numberOfColumns = rsmd.getColumnCount(); System.out.println(numberOfColumns); List<String[]> list = new ArrayList<String[]>(); while (rs.next()) { String[] str = new String[numberOfColumns - 1]; for (int r = 1; r < numberOfColumns; r++) { str[r - 1] = rs.getString(r); } list.add(str); } return list; } catch (SQLException e) { e.printStackTrace(); System.out.println("执行查询总数sql异常,不能执行!"); throw new SQLException(e); } catch (Exception e) { e.printStackTrace(); System.out.println("查询数据结果异常,不能执行!"); return null; } finally { try { if(con!=null) con.close(); if(st!=null) st.close(); if(rs!=null) rs.close(); System.out.println("siyu--getsqllistset-->关闭连接成功!"); } catch (Exception e) { System.out.println("关闭数据连接有误!!"); } } } /** * 查找分页的sql语句的数据 * @param sql 原始sql语句! * @param currentpage 当前页 * @param pagesize 每页大小 * @return 返回满足该条件的结果集,以List<<Map<String,Object>> 形式返回 */ public List<Map<String,Object>> getsqlmapset(String sql, int currentpage,int pagesize) throws SQLException { if (currentpage <= 0) { currentpage = 1; } if (pagesize < 1 || pagesize > 100) { pagesize = 10; } int startrow = (currentpage - 1) * pagesize; String nsql = "select * from (select a.*, rownum r from (" + sql + ") a where rownum <= " + (startrow + pagesize) + ") where r > " + startrow; System.out.println("分页sql语句:" + nsql); try { DataBase db = new DataBase(); this.con = db.getCon(); st = con.createStatement(); rs = st.executeQuery(nsql); List<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); ResultSetMetaData rsmd = rs.getMetaData(); int numberOfColumns = rsmd.getColumnCount(); while (rs.next()) { Map<String,Object> rsTree = new HashMap<String, Object>(numberOfColumns); for (int r = 1; r < numberOfColumns; r++) { rsTree.put(rsmd.getColumnName(r), rs.getObject(r)); } list.add(rsTree); } return list; } catch (SQLException e) { e.printStackTrace(); System.out.println("执行查询总数sql异常,不能执行!"); throw new SQLException(e); } catch (Exception e) { e.printStackTrace(); System.out.println("查询数据结果异常,不能执行!"); return null; } finally { try { if(con!=null) con.close(); if(st!=null) st.close(); if(rs!=null) rs.close(); System.out.println("siyu--getsqlmapset-->关闭连接成功!"); } catch (Exception e) { System.out.println("关闭数据连接有误!!"); } } } |
|