Last batch from prepared statment won't get written unless I close the connection.

Question

I'm doing a small project including a connection to sqlite. I write to two tables with a one to many relation.

The last batch to the second table won't get written unless I explicitly closes the connection. I find it strange since the database is in auto-commit mode (i.e. connection.commit() throws an error).

I would like to keep the connection open since I'll do quite some calls to the write method.

Is there anything beside commit() I can do to force a write to the disk?

Here's a close to minimal code example, note that if I uncomment the conn.close() it will work as I want to.

Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:result");
Statement stat = conn.createStatement();

stat.executeUpdate("create table if not exists " +
            "a (a1, aID INTEGER PRIMARY KEY ASC)");

stat.executeUpdate("create table if not exists " +
            "b (aID, b1, attributePathID INTEGER PRIMARY KEY ASC)");

PreparedStatement pa = conn.prepareStatement(
            "insert into a (a1) values (?);");

PreparedStatement pb = conn.prepareStatement(
            "insert into b (aID,b1) values (?,?);");

int[] aSet = new int[] {10,20,30};
int[] bSet = new int[] {1,2};


for(int ai : aSet){
    pa.setInt(1,ai);
    pa.execute();

    ResultSet rs = pa.getGeneratedKeys();
    rs.next();
    long aID = rs.getLong(1);

    for(int bi : bSet){
        pb.setLong(1,aID);
        pb.setInt(2,bi);
        pb.execute();
        }
    }
    //conn.close();

Answer 1

What about if you use executeUpdate rather than execute?

Answer 2

Haven't worked with jdbc and sqllite much, but this should work:

conn.setAutoCommit(false);
for(int bi : bSet){
    pb.setLong(1,aID);
    pb.setInt(2,bi);
    pb.executeUpdate();
    }
conn.commit();
conn.setAutoCommit(true);

Also, take a look at http://www.zentus.com/sqlitejdbc/ which has a nice example similar to yours.