/*** 查询结果的总数,常用的地方就是查询分页时的总记录数
*@paramsql:原始sql语句!
*@return查询结果的总数
*@throwsSQLException*/publicintgetsqlcount(String sql)throwsSQLException{
DataBase db=newDataBase();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()) {returnrs.getInt(1);
}else{return0;
}
}catch(SQLException e) {
System.out.println("执行查询总数sql异常,不能执行!");thrownewSQLException(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语句的数据
*@paramsql 原始sql语句!
*@paramcurrentpage 当前页
*@parampagesize 每页大小
*@return返回满足该条件的结果集,以List 形式返回*/publicListgetsqllistset(String sql,intcurrentpage,intpagesize)throwsSQLException {if(currentpage<=0) {
currentpage=1;
}if(pagesize<1||pagesize>100) {
pagesize=10;
}intstartrow=(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=newDataBase();this.con=db.getCon();
st=con.createStatement();
rs=st.executeQuery(nsql);
ResultSetMetaData rsmd=rs.getMetaData();intnumberOfColumns=rsmd.getColumnCount();
System.out.println(numberOfColumns);
Listlist=newArrayList();while(rs.next()) {
String[] str=newString[numberOfColumns-1];for(intr=1; r
str[r-1]=rs.getString(r);
}
list.add(str);
}returnlist;
}catch(SQLException e) {
e.printStackTrace();
System.out.println("执行查询总数sql异常,不能执行!");thrownewSQLException(e);
}catch(Exception e) {
e.printStackTrace();
System.out.println("查询数据结果异常,不能执行!");returnnull;
}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语句的数据
*@paramsql 原始sql语句!
*@paramcurrentpage 当前页
*@parampagesize 每页大小
*@return返回满足该条件的结果集,以List<> 形式返回*/publicList>getsqlmapset(String sql,intcurrentpage,intpagesize)throwsSQLException {if(currentpage<=0) {
currentpage=1;
}if(pagesize<1||pagesize>100) {
pagesize=10;
}intstartrow=(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=newDataBase();this.con=db.getCon();
st=con.createStatement();
rs=st.executeQuery(nsql);
List>list=newArrayList>();
ResultSetMetaData rsmd=rs.getMetaData();intnumberOfColumns=rsmd.getColumnCount();while(rs.next()) {
MaprsTree=newHashMap(numberOfColumns);for(intr=1; r
rsTree.put(rsmd.getColumnName(r), rs.getObject(r));
}
list.add(rsTree);
}returnlist;
}catch(SQLException e) {
e.printStackTrace();
System.out.println("执行查询总数sql异常,不能执行!");thrownewSQLException(e);
}catch(Exception e) {
e.printStackTrace();
System.out.println("查询数据结果异常,不能执行!");returnnull;
}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("关闭数据连接有误!!");
}
}
}