代码
/**
* 查询结果的总数，常用的地方就是查询分页时的总记录数
* @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("关闭数据连接有误!!");
}
}
}