Java_JDBC 番外_嵌套Statement

 

Class.forName("com.mysql.jdbc.Driver");
ct = DriverManager.getConnection("jdbc:mysql://***:3306/mud","**","**");
sm = ct.createStatement();
String sql = "select re_id from recruit_exp";
rs1 = sm.executeQuery(sql);

while(rs1.next())
{
// rs1.next();rs1.next();
int id = rs1.getInt("re_id");
// System.out.println(id);
String sql1 = "select cc_name from ccjhdb where cc_id = "+id;
rs2 = sm.executeQuery(sql1);                <——问题根源
if(!rs2.next())
continue;
else{
String name = rs2.getString("cc_name");
// System.out.println(name);
String sql2 = "update recruit_exp set re_name = '"+name+"' where re_id = '"+id+"'";
sm2.executeUpdate(sql2); 
}

当rs2也使用sm进行操作获得结果集，此时由sm产生的资源rs1会被覆盖相当于在此把rs1.close()

所以，对于不同的ResultSet要创立不同Statement,好在一个Connection可以构建多个Statement

用以下实例就不会出问题了。

 

Class.forName("com.mysql.jdbc.Driver");
ct = DriverManager.getConnection("jdbc:mysql://***:3306/mud","**","**");
sm1 = ct.createStatement();
sm2 = ct.createStatement();
String sql = "select re_id from recruit_exp";
rs1 = sm1.executeQuery(sql);

while(rs1.next())
{
// rs1.next();rs1.next();
int id = rs1.getInt("re_id");
// System.out.println(id);
String sql1 = "select cc_name from ccjhdb where cc_id = "+id;
rs2 = sm2.executeQuery(sql1);
if(!rs2.next())
continue;
else{
String name = rs2.getString("cc_name");
// System.out.println(name);
String sql2 = "update recruit_exp set re_name = '"+name+"' where re_id = '"+id+"'";
sm2.executeUpdate(sql2);
}