java - Java JDBC SQLite 数据库被带有 where 子句的选择锁定

Question

我正在调用这个方法：

ArrayList<String> selectFilteredJump(String owner, String fromDate, String toDate, String env) {
    ArrayList<String> a = new ArrayList<>();
    Connection c;
    Statement stmt;

    StringBuilder whereClause = new StringBuilder("WHERE a.Date BETWEEN '"+fromDate+ "' and '" + toDate+"'");
    if (!env.equals(constants.EMPTY) ) {
        whereClause.append(" and a.Environment = '").append(env).append("'");
    }

    if (!owner.equals(constants.EMPTY) ) {
        whereClause.append(" and a.UserId = '").append(owner).append("'");
    }

    String sql = "SELECT a.TicketID, a.Subject, a.UserID, a.Date, a.Solution, a.Comments," +
                 "a.Environment, (SELECT UserName FROM Users WHERE UserID = a.UserID) AS UserName FROM Tickets a " +
                 whereClause + " order by 4 asc;";
    System.out.println(sql);
    try {
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:" + this.dbPath + this.dbName) ;
        c.setAutoCommit(false);
        stmt = c.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            a.add(rs.getString("TicketID"));
            a.add(rs.getString("Subject"));
            a.add(rs.getString("UserID"));
            a.add(rs.getString("Date"));
            a.add(rs.getString("Solution"));
            a.add(rs.getString("Comments"));
            a.add(rs.getString("Environment"));
            a.add(rs.getString("UserName"));
        }
        rs.close();
        stmt.close();
        c.close();
    }
    catch (Exception e) {
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
    }
    return a;
}

一切都正确执行，但似乎我的数据库没有明显的原因保持锁定状态。

另一方面，如果我用这种方法替换数据库在执行后就可以了：

    ArrayList<String> selectAllJump() {
    if (constants.DEBUG_MODE) System.out.println("selectAllJump");
    ArrayList<String> a = new ArrayList<>();
    Connection c;
    Statement stmt;
    try {
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:" + this.dbPath + this.dbName);
        c.setAutoCommit(false);
        stmt = c.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT a.TicketID, a.Subject, a.UserID, a.Date, a.Solution, a.Comments," +
                "a.Environment, (SELECT UserName FROM Users WHERE UserID = a.UserID) AS UserName FROM Tickets a " +
                "order by 4 asc;");
        while (rs.next()) {
            a.add(rs.getString("TicketID"));
            a.add(rs.getString("Subject"));
            a.add(rs.getString("UserID"));
            a.add(rs.getString("Date"));
            a.add(rs.getString("Solution"));
            a.add(rs.getString("Comments"));
            a.add(rs.getString("Environment"));
            a.add(rs.getString("UserName"));
        }
        rs.close();
        stmt.close();
        c.close();
    }
    catch (Exception e) {
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
    }
    return a;
}

我还尝试使用特定配置打开连接（如下所示）：

c = DriverManager.getConnection("jdbc:sqlite:" + this.dbPath + this.dbName,setConfig().toProperties()) ;

private SQLiteConfig setConfig() {
    SQLiteConfig config = new SQLiteConfig();
    config.enforceForeignKeys(true);
    config.setTempStore(SQLiteConfig.TempStore.MEMORY);
    config.setCacheSize(1000);
    config.setReadOnly(true);
    return config;
}

仍然是同样的错误：

org.sqlite.SQLiteException: [SQLITE_BUSY] 数据库文件被锁定（数据库被锁定）

欢迎任何想法。

谢谢你。

标签： javasqlitejdbclocked