java - 自动提交开关的并发症

当我运行代码时，我只会不断收到错误：


  “ ResultSet未打开。请确认自动提交已关闭。”


我该如何解决？

我无法正常工作。我确定它与con.setAutoCommint(false);或executeQuery有关。

package database_console;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import static jdk.nashorn.internal.objects.NativeString.split;
import java.sql.*;
import java.sql.ResultSet;
import java.util.Random;

public class Database_console {

    public static void main(String[] args) {
        String url = "jdbc:derby://localhost:1527/English Words";
        try {
            String host = "jdbc:derby://localhost:1527/English Words";
            String uName = "User";
            String uPass= "password";
            Connection con = DriverManager.getConnection(host, uName, uPass);
            con.setAutoCommit(false);

            Statement stmt = con.createStatement();

            String SQL1 = "SELECT * FROM ENGLISH_1";
            String SQL2 = "SELECT * FROM ENGLISH_2";
            String SQL3 = "SELECT * FROM ENGLISH_3";
            String SQL4 = "SELECT * FROM ENGLISH_4";
            String SQL5 = "SELECT * FROM ENGLISH_5";

            ResultSet rs1 = stmt.executeQuery( SQL1);
            ResultSet rs2 = stmt.executeQuery( SQL2);
            ResultSet rs3 = stmt.executeQuery( SQL3);
            ResultSet rs4 = stmt.executeQuery( SQL4);
            ResultSet rs5 = stmt.executeQuery( SQL5);

            while(rs1.next()) {
                String 1 = rs1.getString("1");
                String 2 = rs2.getString("2");
                String 3 = rs3.getString("3");
                String 4 = rs4.getString("4");
                String 5 = rs5.getString("5");
                con.commit();
                System.out.println( 1 + " " + 2 + " " + 3 " " + 4 " " 5 + " ");
            }
        }
        catch (SQLException err) {
            System.out.println(err.getMessage());
        }
    }
}

最佳答案

两件事：首先，COMMIT关闭以BEGIN TRANSACTION开始的事务。你什么都没有。

其次，当您使用JDBC执行SELECT语句时，您需要执行每个SQL语句，然后检索并关闭其结果集，然后再开始下一个语句。您显示的代码尝试创建一堆ResultSet对象，然后逐个读取它们。你做不到。™

重要提示：完成操作后，不要忘记关闭每个ResultSet。结果集可能是数据库服务器上的稀缺资源。

例如，尝试这个。

String SQL1 = "SELECT col1, col2, col3 FROM ENGLISH_1";
String SQL2 = "SELECT col1, col2, col3 FROM ENGLISH_2";

Statement stmt = con.createStatement( );

ResultSet rs1 = stmt.executeQuery( SQL1 );
while(rs1.next()) {
  String s1 = rs1.getString(1);
  String s2 = rs1.getString(2);
  String s3 = rs1.getString(3);
  System.out.println( s1 + " " + s2 + " " + s3);
}
rs1.close();

ResultSet rs2 = stmt.executeQuery( SQL2 );
while(rs2.next()) {
  String t1 = rs2.getString(1);
  String t2 = rs2.getString(2);
  String t3 = rs2.getString(3);
  System.out.println( t1 + " " + t2 + " " + t3);
}
rs2.close();