在进行数据库操作时,resultset结果集出现自动关闭错误。
如下代码:先根据第一个结果集结果进行再次查询。相当于双表查询
出现问题为第二个结果集遍历结束后,第一个结果集自动关闭,不进行下一个循环
package hospital;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class TT {
<span style="white-space:pre"> </span>public static void main(String args[]) throws SQLException
<span style="white-space:pre"> </span>{
<span style="white-space:pre"> </span>TT.test();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>/*
<span style="white-space:pre"> </span> * 统计各种数据用
<span style="white-space:pre"> </span> *
<span style="white-space:pre"> </span> * */
<span style="white-space:pre"> </span>static void test() throws SQLException
<span style="white-space:pre"> </span>{
<span style="white-space:pre"> </span>String sql1="",sql2="";
<span style="white-space:pre"> </span>Connection conn;
<span style="white-space:pre"> </span>String Case ;
<span style="white-space:pre"> </span>int scan,uniid;
<span style="white-space:pre"> </span>int i;
<span style="white-space:pre"> </span>int[] rating=new int[7];
<span style="white-space:pre"> </span>conn = help.getConnection("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:lidc");
<span style="white-space:pre"> </span>Statement st=conn.createStatement();//创建Statement,
<span style="white-space:pre"> </span>//统计lidc数据集中CT扫描的参数
<span style="white-space:pre"> </span>sql1="select case,scan,uninodID from unindouleinfo";<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>ResultSet rs1 = null;
<span style="white-space:pre"> </span>ResultSet rs2 = null;
<span style="white-space:pre"> </span>rs1=st.executeQuery(sql1);//第一个结果集
<span style="white-space:pre"> </span>while(rs1.next())
<span style="white-space:pre"> </span>{<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>Case=rs1.getString(1);//将第一个参数赋值给Case
<span style="white-space:pre"> </span>scan=rs1.getInt(2);<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>uniid=rs1.getInt(3);
<span style="white-space:pre"> </span>sql2="select subtlety from markednoduleinfodiagnosis where Case='"+Case+"'and scan="+scan+" and uninodID="+uniid;<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>rs2=st.executeQuery(sql2);//第二个结果集
<span style="white-space:pre"> </span>i=0;
<span style="white-space:pre"> </span>while(rs2.next() && i<=7)
<span style="white-space:pre"> </span>{<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>rating[i]=rs2.getInt(1);//获得结果集第一列的值
<span style="white-space:pre"> </span>i++;
<span style="white-space:pre"> </span>}<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>System.out.println("case="+Case+";scan="+scan+";id="+uniid);//输出对应的结果
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}<span style="white-space:pre"> </span>
}
报错情况:
出错原因为Resultset 关闭
增加调试代码:
package hospital;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class TT {
public static void main(String args[]) throws SQLException
{
TT.test();
}
/*
* 统计各种数据用
*
* */
static void test() throws SQLException
{
String sql1="",sql2="";
Connection conn = null;
ResultSet rs1 = null,rs2 = null;
Statement st=null;
String Case ;
int scan,uniid;
int i;
int[] rating=new int[7];
try{
conn = help.getConnection("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:lidc");
st=conn.createStatement();//创建Statement,
//统计lidc数据集中CT扫描的参数
sql1="select case,scan,uninodID from unindouleinfo";
rs1=st.executeQuery(sql1);//第一个结果集
while(rs1.next())
{
Case=rs1.getString(1);//将第一个参数赋值给Case
scan=rs1.getInt(2);
uniid=rs1.getInt(3);
sql2="select subtlety from markednoduleinfodiagnosis where Case='"+Case+"'and scan="+scan+" and uninodID="+uniid;
rs2=st.executeQuery(sql2);//第二个结果集
i=0;
while(rs2.next() && i<=7)
{
rating[i]=rs2.getInt(1);//获得结果集第一列的值
i++;
}
System.out.println("case="+Case+";scan="+scan+";id="+uniid);//输出对应的结果
}
}catch(SQLException e){
e.printStackTrace();
}finally{
if(rs2!=null){
try{
System.out.println("rs2关闭");
rs2.close();
}catch(SQLException e){
System.out.println("rs2关闭出错");
e.printStackTrace();
}
}
if(rs1 !=null){
try{
System.out.println("rs1关闭");
rs1.close();
}catch(SQLException e){
System.out.println("rs1关闭出错");
e.printStackTrace();
}
}
if(st!=null){
try{
System.out.println("st关闭");
st.close();
}catch(SQLException e){
System.out.println("st关闭出错");
e.printStackTrace();
}
}
if(conn!=null){
try{
System.out.println("conn关闭");
conn.close();
}catch(SQLException e){
System.out.println("conn关闭出错");
e.printStackTrace();
}
}
}
}
}
输出结果:
显示为rs1关闭出错:原因在于,正常情况下如果使用Statement执行完一个查询,又去执行另一个查询时这时候第一个查询的结果集就会被关闭,也就是说,所有的Statement的查询对应的结果集是一个.。所以,我们应该为每个查询创建一个statement。
package hospital;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class TT {
<span style="white-space:pre"> </span>public static void main(String args[]) throws SQLException
<span style="white-space:pre"> </span>{
<span style="white-space:pre"> </span>TT.test();
<span style="white-space:pre"> </span>}<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>/*
<span style="white-space:pre"> </span> * 统计各种数据用
<span style="white-space:pre"> </span> *
<span style="white-space:pre"> </span> * */
<span style="white-space:pre"> </span>static void test() throws SQLException
<span style="white-space:pre"> </span>{
<span style="white-space:pre"> </span>String sql1="",sql2="";
<span style="white-space:pre"> </span>Connection conn;
<span style="white-space:pre"> </span>String Case;
<span style="white-space:pre"> </span>int scan,uniid;
<span style="white-space:pre"> </span>int i;
<span style="white-space:pre"> </span>int[] rating=new int[7];
<span style="white-space:pre"> </span>conn = help.getConnection("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:lidc");
<span style="white-space:pre"> </span>//创建两个Statement,不会报错
<span style="white-space:pre"> </span>Statement st=conn.createStatement();
<span style="white-space:pre"> </span>Statement st1=conn.createStatement();
<span style="white-space:pre"> </span>//统计lidc数据集中CT扫描的参数
<span style="white-space:pre"> </span>sql1="select case,scan,uninodID from unindouleinfo";
<span style="white-space:pre"> </span>ResultSet rs1=st.executeQuery(sql1);
<span style="white-space:pre"> </span>ResultSet rs2=null;
<span style="white-space:pre"> </span>while(rs1.next())
<span style="white-space:pre"> </span>{<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>Case=rs1.getString(1);
<span style="white-space:pre"> </span>scan=rs1.getInt(2);<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>uniid=rs1.getInt(3);
<span style="white-space:pre"> </span>sql2="select subtlety from markednoduleinfodiagnosis where Case='"+Case+"'and scan="+scan+" and uninodID="+uniid;
<span style="white-space:pre"> </span>rs2=st1.executeQuery(sql2);
i=0;<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>while(rs2.next()&&i<=7)
<span style="white-space:pre"> </span>{<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>rating[i]=rs2.getInt(1);<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>i++;<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>System.out.println("case="+Case+";scan="+scan+";id="+uniid);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}<span style="white-space:pre"> </span>
}
运行结果:
数据输出。