public class SortAndZip {
public static void main() {
String targetTable1 = "targetTable1 ";
String targetTable2 = "targetTable2 ";
Connection conn = null;
PreparedStatement typePs = null;
PreparedStatement postPs = null;
ResultSet postRs = null;
ResultSet typeRs = null;
try {
conn = JDBCUtils.getConnection();
String[] keyStr = getKeyStr();
String postSql = " select " + keyStr[0] + " from " + targetTable1 + " where code = " + "'111'";
String typeSql = " select " + keyStr[1] + " from " + targetTable2 + " where code = " + "'111'";
typePs = conn.prepareStatement(typeSql);
postPs = conn.prepareStatement(postSql);
postRs = postPs.executeQuery();
typeRs = typePs.executeQuery();
String[] postKey = keyStr[0].split(",");
String[] typeKey = keyStr[1].split(",");
while (postRs.next()) {
ArrayList postStr = new ArrayList<String>();
for (String k : postKey) {
postStr.add(postRs.getString(k));
}
System.out.println(StringUtils.join(postStr.toArray(), ","));
}
while (typeRs.next()) {
ArrayList typeStr = new ArrayList<String>();
for (String k : typeKey) {
typeStr.add(postRs.getString(k));
}
System.out.println(StringUtils.join(typeStr.toArray(), ","));
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.closeResultSetResource(postRs, typeRs);
JDBCUtils.closeStatementResource(postPs, typePs);
JDBCUtils.closeConnResource(conn);
}
}
倒数第12行(typeStr.add(postRs.getString(k)))正常情况下应该用typeStr.add(typeRs.getString(k)),正是重复用了postRs导致报错。
原因分析:
一个Statement对象同时只能有一个结果集在活动,就是说即使没有调用ResultSet的close()方法,只要打开第二个结果集就隐含着对上一个结果集的关闭,而上方代码中postRs用了两次,postRs第一次用后,再次调用postRs时,postRs就会关闭,就会报错;在JDBC3.0中,可以配置相关参数使其不关闭;而且ResultSet存储查询结果,ResultSet只能读取一次,不能够来回的滚动读取,当然也可以创建可来回滚动ResultSet
注:
JDBCUtils.closeResultSetResource(postRs, typeRs);
JDBCUtils.closeStatementResource(postPs, typePs);
JDBCUtils.closeConnResource(conn);
getKeyStr() ;这些是我自定义的一些类和方法