JDBC SQL Database is locked?

Question

I am writing a program that uses a local SQL database to store data.

I am using the driver found here: https://bitbucket.org/xerial/sqlite-jdbc

I am trying to read from the database and put the contents of tableName into a JTable like this:

public Object[][] getTable(String tableName){
    int columns = getColumnNumber(tableName);
    int rows = getRowNumber(tableName);
    String[] columnNames = getColumnNames(tableName);
    Object[][] table = new Object[rows][columns];
    try{
        Connection connection = DriverManager.getConnection("jdbc:sqlite:" + dbName + ".db");
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("select * from " + tableName);
        for(int r = 0; r < rows; r++){
            rs.next();
            for (int c = 0; c < columns; c++){
                table[r][c] = rs.getString(columnNames[c]);
            }
        }
        return table;           
    }catch(Exception e){
        System.out.println("ERROR CREATING TABLE ARRAY");
        e.printStackTrace();
        return null;
    }
}

Then later I try to come back and add a row to the table:

public boolean addRow(String tableName, Object[] values){
    if(!isDataAcceptable(tableName, values))
        return false;
    try{
        String stmt = "insert into " + tableName + " values (";
        for(int c = 0; c < values.length; c++){
            if(values[c] instanceof String)
                stmt += "'" + values[c] + "'";
            else
                stmt += values[c];
            if(c == (values.length - 1))
                    stmt += ");";
            else
                stmt += ", ";
        }
        System.out.println(stmt);
        Connection connection = DriverManager.getConnection("jdbc:sqlite:" + dbName + ".db");
        Statement statement = connection.createStatement();
        statement.executeUpdate(stmt);
        return true;
    }catch(Exception e){
        System.out.println("ERROR INSERTING ROW");
        e.printStackTrace();
        return false;
    }
}

I then want to update the JTable I created before with the new row.

When I try to add the row however it causes an exception:

java.sql.SQLException: database is locked

that points to the line:

statement.executeUpdate(stmt);

...in the addRow() method.

Why is the database locked and how do I unlock it to write to it?

Solution

Why is the database locked and how do I unlock it to write to it?

The database is most likely locked in addRow because the previous call to getTable did not close the resultset. The code also fails to close the database connection objects which will cause a resource leak.

The basic fix is to call close() on the rs and connection objects, but you need to do it the right way to make your code reliable.

Here's the recommended way to do it in Java 7 using "try with resources" syntax:

try (Connection connection = 
         DriverManager.getConnection("jdbc:sqlite:" + dbName + ".db"),
     Statement stmt = connection.createStatement(),
     ResultSet rs = stmt.executeQuery("select * from " + tableName)) {
  for (int r = 0; r < rows; r++) {
    rs.next();
    for (int c = 0; c < columns; c++) {
      table[r][c] = rs.getString(columnNames[c]);
    }
  }
  return table;           
} catch (Exception e) {
    System.out.println("ERROR CREATING TABLE ARRAY");
    e.printStackTrace();
    return null;
}

You can also do this by calling close explicitly in a finally block, but it is more verbose, and the code can be difficult to get right if you have related resources that need to be closed ... like here.