Java PreparedStatement.executeBatch() in SQLite not working

Question

I have the following method. The commented methods saveOrUpdateToDatabase execute perfectly fine, but I would like to use the executeBatch. What am I missing? The int[]r which receives the executeBatch() results is always empty...

public boolean saveOrUpdate(MonitoredData mData) {
        try {
            PreparedStatement prep;
            String timeID = this.getTimeLastRowID();

            for (CpuData o : mData.getCpu()) {
                prep = this.conn.prepareStatement(String.format(
                        INSERT_CPU_USAGE, this.nodeID, timeID, o.toString()));
                prep.addBatch();
                // saveOrUpdateToDatabase(String.format(INSERT_CPU_USAGE,
                // this.nodeID, timeID, o.toString()));
            }
            for (DiskData o : mData.getDisk()) {
                prep = this.conn.prepareStatement(String.format(
                        INSERT_DISK_USAGE, this.nodeID, timeID, o.toString()));
                prep.addBatch();
                // saveOrUpdateToDatabase(String.format(INSERT_DISK_USAGE,
                // this.nodeID, timeID, o.toString()));

            }
            for (NetworkData o : mData.getNet()) {
                prep = this.conn.prepareStatement(String
                        .format(INSERT_NETWORK_USAGE, this.nodeID, timeID, o
                                .toString()));
                prep.addBatch();
                // saveOrUpdateToDatabase(String.format(INSERT_NETWORK_USAGE,
                // this.nodeID, timeID, o.toString()));

            }

            prep = this.conn.prepareStatement(String.format(
                    INSERT_MEMORY_USAGE, this.nodeID, timeID, mData.getMem()
                            .toString()));
            // saveOrUpdateToDatabase(String.format(INSERT_MEMORY_USAGE,
            // this.nodeID, timeID, mData.getMem().toString()));

            conn.setAutoCommit(false);
            int[] r = prep.executeBatch();
            conn.setAutoCommit(true);

            return true;

        } catch (SQLException ex) {
            Logger.getLogger(HistoricalDatabase.class.getName()).log(
                    Level.SEVERE, null, ex);
        }
        return false;
    }

Answer 1

What am I missing? The int[]r which receives the executeBatch() results is always empty...

You are using the addBatch() method incorrectly. In your code you are doing:

for (CpuData o : mData.getCpu()) {
    // WRONG!! you can not prepare a new query each time
    prep = this.conn.prepareStatement(INSERT_CPU_USAGE);
    prep.setObject(1, this.nodeID);
    prep.addBatch();
}

This replaces the prepared query each time. You should only be calling prepareStatement(...) once per batch. You should be doing something like the following. You will have to change your insert statements to have ? arguments:

PreparedStatement prep = conn.prepareStatement(INSERT_CPU_USAGE);
for (CpuData o : mData.getCpu()) {
    prep.setObject(1, this.nodeID);
    prep.setObject(2, timeID);
    prep.setObject(3, o);
    prep.addBatch();
}
prep.executeBatch();

Notice that only one prepareStatement() call is being used. This has ? SQL argument entities in it that are then assigned in the loop with the prep.setString(1, ...). When the batch is ready to be executed you call prep.executeBatch() but this is without ever replacing the prep prepared statement which you are doing.

If you are trying to perform a series of different insert statements in a row in the same batch then you should look into turning off auto-commit, doing your statements, call commit, and then turn auto-commit back on. Something like:

conn.setAutoCommit(false);
// statements prepared and executed here
// maybe no need for batch operations
...
conn.commit();
conn.setAutoCommit(true);