eclipse连接access结果集自动关闭报错

在进行数据库操作时，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);//将第一个参数赋值给Casescan=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>}

运行结果：

数据输出。