先记录一下几个测试结果
测试环境
MySQL：mysql-connector-java-5.1.29.jar
Java：1.7.0_67
测试A
代码：
PreparedStatement pstmt = conn
.prepareStatement("select id from world.city limit 3");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
}
System.out.println();
PreparedStatement pstmt2 = conn
.prepareStatement("select countrycode from world.city limit 3");
ResultSet rs2 = pstmt2.executeQuery();
while (rs2.next()) {
System.out.println(rs2.getString(1));
}
System.out.println(pstmt.isClosed());
System.out.println(rs.isClosed());
System.out.println(pstmt2.isClosed());
System.out.println(rs2.isClosed());
System.out.println(conn.isClosed());
System.out.println();
conn.close();
System.out.println(pstmt.isClosed());
System.out.println(rs.isClosed());
System.out.println(pstmt2.isClosed());
System.out.println(rs2.isClosed());
System.out.println(conn.isClosed());
System.out.println();结果：
false false false false false true true true true true
结论：
connection关闭时会关闭关联的ResultSet和PreparedStatement。
测试B
代码：
PreparedStatement pstmt = null;
ResultSet rs1 = null;
ResultSet rs2 = null;
Statement st = null;
ResultSet rs3 = null;
ResultSet rs4 = null;
try {
pstmt = conn.prepareStatement(
"select name from world.city where name like ? limit 3");
pstmt.setString(1, "a%");
rs1 = pstmt.executeQuery();
rs2 = null;
while (rs1.next()) {
System.out.println(rs1.getString(1));
pstmt.setString(1, "b%");
rs2 = pstmt.executeQuery();
while (rs2.next()) {
System.out.println(rs2.getString(1));
}
}
}
catch (Exception e) {
e.printStackTrace();
}
try {
st = conn.createStatement();
rs3 = st.executeQuery(
"select name from world.city where name like 'a%' limit 3");
rs4 = null;
while (rs3.next()) {
System.out.println(rs3.getString(1));
rs4 = st.executeQuery(
"select name from world.city where name like 'b%' limit 3");
while (rs4.next()) {
System.out.println(rs4.getString(1));
}
}
}
catch (Exception e) {
e.printStackTrace();
}
System.out.println(pstmt.isClosed());
System.out.println(rs1.isClosed());
System.out.println(rs2.isClosed());
System.out.println(st.isClosed());
System.out.println(rs3.isClosed());
System.out.println(rs4.isClosed());
System.out.println(conn.isClosed());
System.out.println();结果：
java.sql.SQLException: Operation not allowed after ResultSet closed java.sql.SQLException: Operation not allowed after ResultSet closed false true false false true false false
结论：
同一个Statement或PreparedStatement对象只能关联一个激活的ResultSet。
测试C
代码：
PreparedStatement pstmt = conn
.prepareStatement("select id from world.city limit 3");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
}
System.out.println();
PreparedStatement pstmt_old = pstmt;
pstmt = conn.prepareStatement("select name from world.city where name like ? limit 3");
pstmt.setString(1, "a%");
ResultSet rs2 = pstmt.executeQuery();
while (rs2.next()) {
System.out.println(rs2.getString(1));
}
System.out.println(pstmt.isClosed());
System.out.println(rs.isClosed());
System.out.println(pstmt_old.isClosed());
System.out.println(rs2.isClosed());
System.out.println(conn.isClosed());
System.out.println();
pstmt.close();
System.out.println(pstmt.isClosed());
System.out.println(rs.isClosed());
System.out.println(pstmt_old.isClosed());
System.out.println(rs2.isClosed());
System.out.println(conn.isClosed());
System.out.println();
conn.close();
System.out.println(pstmt.isClosed());
System.out.println(rs.isClosed());
System.out.println(pstmt_old.isClosed());
System.out.println(rs2.isClosed());
System.out.println(conn.isClosed());
System.out.println();结果：
false false false false false true false false true false true true true true true
结论：
如果PreparedStatement对象没有关闭就关联了其他PreparedStatement，则旧的PreparedStatement和ResultSet不会关闭，直到Connection关闭。在使用连接池的情况下，可能造成内存泄露。
比较好的做法：
使用PreparedStatement代替Statement，一方面，多次提高了多次执行语句的效率，另一方面，防止拼接SQL可能造成的注入攻击
一定要注意关闭PreparedStatement，在使用连接池的情况下尤其要注意。Java7以上建议使用try-with-resource语句，简洁。Java7以下注意关闭时也要try-catch，并尽量按照ResultSet、PreparedStatement、Connection的顺序关闭。
不要传递ResultSet，使用RowSet。
