java.sql.SQLException : Invalid handle

Question

我正在尝试自学如何用 java 连接到 msaccess 数据库。我设置了一个类来 Access 数据库，如下

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public abstract class AccessDBConnect2 {
    public static Connection connect(){
        String fileName = "C:/Users/Bridget/Documents/EmployeeSys.accdb";
        Connection con = null;
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+fileName;
            con = DriverManager.getConnection(url,"","");
        } catch (Exception e) {
            // Handle exceptions    ...
            System.out.println(e.toString());
            System.out.println("A problem accessing the database");
            e.printStackTrace();
        } finally {
            try { if(con!=null) {con.close();} } catch (Exception e) {}
        }
        return con;
    }
public static void closeConnection(Connection conn){
    try{
        conn.close();
    }catch (Exception e){

    }
}

然后我的代码只是尝试从表中选择所有内容。我已经在 msAccess 中创建了表，并且代码似乎通过上面代码中的 connect 方法没有任何问题，表明它正在查找数据库并在某种程度上 Access 它。当我使用连接调用prepareStatement时出现问题，即代码行:

stm = conn.prepareStatement(sql);

完整代码为:

import java.sql.*;
public class Program2{
public static void main(String[] args) {
        try{
            // Load the JDBC driver
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();

        // Establishing db connection
        Connection conn = AccessDBConnect.connect();

        // Displaying all records from employee file
        System.out.println("Display records of all employees");
        display(conn);

        // Closing the connection
        AccessDBConnect.closeConnection(conn);
    }catch (Exception e){
        System.out.println("Error");
    }
}

// Display details of all employees
public static void display(Connection conn){
    PreparedStatement stm = null;
    // SQL statement
    String sql = "SELECT * FROM Employee";
    ResultSet rs;
    try {
        stm = conn.prepareStatement(sql);   // Prepare the SQL statement
        rs = stm.executeQuery();            // Execture the SQL statement

        // Navigate through the ResultSet and print
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String gender = rs.getString("gender");
            String address = rs.getString("address");

            System.out.println("ID: \t \t" + id);
            System.out.println("Name: \t \t" + name);
            System.out.println("Gender: \t" + gender);
            System.out.println("Address: \t" + address);
            System.out.println(" ");
        }

    // Closing the resultSet
    rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public void test(){
    int a = "hello";
}

}

Answer 1

您收到错误是因为当您尝试调用 .prepareStatement 时连接已关闭。您的 AccessDBConnect2 类包含一个 finally block ，该 block 在连接返回之前关闭连接。修复该类，使其保持连接打开状态。

顺便说一句，JDBC-ODBC 桥已从 Java 8 中删除，并且实际上已过时。您可能对这个替代方案感兴趣:

Manipulating an Access database from Java without ODBC