java - JDBC SQLite 数据库锁定错误

Question

我的 JDBC SQLite 数据库有问题:当尝试删除表时，我收到错误消息，指出表已锁定。奇怪的是，我在不同的地方使用相同的代码得到不同的结果。我知道这里已经提出了这个问题，但我关闭了所有结果集和(准备好的)语句。

这是我的 OfflineDB.java 文件:

public enum OfflineDB {

    UNIQUEINSTANCE;

    private Connection conn = null;

private OfflineDB(){
    try {
        System.out.println("Working Directory = " +
          System.getProperty("user.dir"));
        conn = DriverManager.getConnection("jdbc:sqlite:" + 
                System.getProperty("user.dir") + "\\database.db"); 
        setupDB();
    } catch (SQLException e) {
        System.err.println(e.getMessage());
    }
}
public Connection getConnection(){
    return conn;
}
public void setupDB(){
    Statement stmt = null;
    try {
        stmt = conn.createStatement();
        stmt.closeOnCompletion();
        stmt.execute(CREATE_CAR);
        stmt.execute(CREATE_DTTS);
        stmt.execute(CREATE_CHDT);
        stmt.execute(CREATE_USER);
        stmt.execute(CREATE_CIRCUIT);
        stmt.execute(CREATE_CSD);
        stmt.execute(CREATE_DASHBOARD);
        stmt.execute(CREATE_ICW);
        stmt.execute(CREATE_TILE);
        stmt.execute(CREATE_TSD);
        stmt.execute(CREATE_ISUPDATED);
        stmt.close();

    } catch (SQLException ex) {
        ex.printStackTrace();
    } finally{
        try {
            stmt.close();
        } catch (SQLException | NullPointerException e) {
            e.printStackTrace();
        }
    }
}
public boolean dropTables(){
    Statement stmt = null;
    try {
        stmt = conn.createStatement();
        stmt.closeOnCompletion();
        stmt.execute(DROP_DTTS);
        stmt.execute(DROP_CHDT);

        stmt.execute(DROP_TSD);
        stmt.execute(DROP_CSD);
        stmt.execute(DROP_ICW);
        stmt.execute(DROP_CAR);
        stmt.execute(DROP_CIRCUIT);
        stmt.execute(DROP_DASHBOARD);
        stmt.execute(DROP_TILE);
        stmt.execute(DROP_USER);
        stmt.execute(DROP_ISUPDATED);
        stmt.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
        System.out.println(ex.getErrorCode());
        return false;
    } finally{
        try {
            stmt.close();
        } catch (SQLException | NullPointerException e) {
            e.printStackTrace();
        }
    }
    return true;
}

简单:它只是在创建时打开一个连接并存储该连接。

我对这个类进行了 JUnit 测试，并且该测试运行完美:

@Test
public void testDropTables(){
    Boolean bool = OfflineDB.UNIQUEINSTANCE.dropTables();
    assertTrue(bool);
}

在另一个类中，我尝试运行与此测试相同的代码，但在这里我收到错误消息，指出数据库已锁定:

public boolean syncDatabases(){
    if(!checkIsUpdated()) return false;
    else{
        System.out.println("syncing databases");
        if(!OfflineDB.UNIQUEINSTANCE.dropTables()) return false;
    }
    return true;
}

public boolean checkIsUpdated(){
    boolean updateNeeded = false;
    //check if the online timestamp is later than the offline
    Timestamp online = getLastUpdated(true);
    Timestamp offline = getLastUpdated(false);
    System.out.println("online: " + online + " offline: " + offline);
    if(getLastUpdated(true).after(getLastUpdated(false))) 
        updateNeeded = true;
    return updateNeeded;
}

我还为此编写了一个 JUnit，当我尝试运行测试时，会发生错误，如果我在此测试之后立即运行上一个测试，则不会出现问题。

@Test
public void testSyncDatabases() {
    System.out.println("syncDatabases");
    boolean expResult = true;
    boolean result = SyncMapper.UNIQUEINSTANCE.syncDatabases();
    assertEquals(expResult, result);
}

错误:

org.sqlite.SQLiteException: [SQLITE_LOCKED]  A table in the database is locked (database table is locked)
    at org.sqlite.core.DB.newSQLException(DB.java:909)
    at org.sqlite.core.DB.newSQLException(DB.java:921)
    at org.sqlite.core.DB.execute(DB.java:822)
    at org.sqlite.core.CoreStatement.exec(CoreStatement.java:75)
    at org.sqlite.jdbc3.JDBC3Statement.execute(JDBC3Statement.java:61)
    at Storage.OfflineDB.dropTables(OfflineDB.java:256)
    at Persistency.SyncMapper.syncDatabases(SyncMapper.java:56)
    at Persistency.SyncMapperTest.testSyncDatabases(SyncMapperTest.java:59)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at junit.framework.JUnit4TestAdapter.run(JUnit4TestAdapter.java:38)
    at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.run(JUnitTestRunner.java:535)
    at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.launch(JUnitTestRunner.java:1182)
    at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.main(JUnitTestRunner.java:1033)

Answer 1

显然我错了:仍然有一个结果集没有关闭...对于将来遇到同样问题的人:确保在返回后不要关闭结果集或准备好的语句。就我而言，我在 try/catch 中的 if/else 结构中返回了时间戳，在 if/else 之后我关闭了结果集和准备好的语句，这导致代码永远不会到达这些行。