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;
    }

I'm not familiar with Batches, but it looks like you are adding a batch for a preparedstatement, but then replacing that preparedstatement with a new one. In the end, you assign prep a new preparedstatement, but don't call to addBatch(), and then you call executeBatch(), so it looks like you are always executing the batch for the last preparedstatement, the one you never called addBatch() before. — Luciano
– Luciano, Commented May 18, 2012 at 22:35
It also looks like you are using your preparedStatement as a regular statement, since you are setting the parameters in the SQL string, instead of using the setters the PreparedStatement class provides. — Luciano
– Luciano, Commented May 18, 2012 at 22:38

Gray · Accepted Answer · 2023-10-09 21:45:46Z

9

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);

edited Oct 9, 2023 at 21:45

answered May 19, 2012 at 10:14

Gray

117k24 gold badges305 silver badges360 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pedro Dusso Over a year ago

I think I have another problem. My INSERT_CPU_USAGE is "INSERT INTO CpuUsage (NODE_ID, TIME_ID, CORE_ID, USER, NICE, SYSMODE, IDLE, IOWAIT, IRQ, SOFTIRQ, STEAL, GUEST) VALUES (?, ?, ?);" As you saw before, I'm passing the node ID, the time and the third "?" is a string from the getCPu with all other values. But this lead me to "3 values for 12 columns" exception. It parsers the insert and compares to the number of ?'s? =/

Gray Over a year ago

Not sure I understand but the number of columns in your INSERT statement should match the number of VALUES. INSERT INTO CpuUsage (NODE_ID, TIME_ID, USER) VALUES (?, ?, ?). If getCPu returns "all of the other values" then you'll need to break them out and set them one by one and have 12 ? fields.

BarbosSergos Over a year ago

Thank you Gray! I spend almost 4 hours trying to figure it out.. Funny thing that I knew adout it and still write incorrect in code :)

Luciano · Accepted Answer · 2012-05-18 22:45:20Z

-1

Besides the comments I wrote, I'm going to risk giving an answer. What if you try something like this:

public boolean saveOrUpdate(MonitoredData mData) {
    try {
        PreparedStatement prep;
        String timeID = this.getTimeLastRowID();
        conn.setAutoCommit(false);
        for (CpuData o : mData.getCpu()) {
            prep = this.conn.prepareStatement(INSERT_CPU_USAGE);
            prep.setObject(1, this.nodeID);
            prep.setObject(2, timeID);
            prep.setObject(3, o);
            prep.addBatch();
            // saveOrUpdateToDatabase(String.format(INSERT_CPU_USAGE,
            // this.nodeID, timeID, o.toString()));
        }
        prep.executeBatch();
        for (DiskData o : mData.getDisk()) {
            prep = this.conn.prepareStatement(INSERT_DISK_USAGE);
            prep.setObject(1, this.nodeID);
            prep.setObject(2, timeID);
            prep.setObject(3, o);
            prep.addBatch();
            // saveOrUpdateToDatabase(String.format(INSERT_DISK_USAGE,
            // this.nodeID, timeID, o.toString()));
        }
        prep.executeBatch();
        for (NetworkData o : mData.getNet()) {
            prep = this.conn.prepareStatement(INSERT_NETWORK_USAGE);
            prep.setObject(1, this.nodeID);
            prep.setObject(2, timeID);
            prep.setObject(3, o);
            prep.addBatch();
            // saveOrUpdateToDatabase(String.format(INSERT_NETWORK_USAGE,
            // this.nodeID, timeID, o.toString()));

        }
        prep.executeBatch();
        prep = this.conn.prepareStatement(INSERT_MEMORY_USAGE);
        prep.setObject(1, this.nodeID);
        prep.setObject(2, timeID);
        prep.setObject(3, o);
        prep.executeUpdate();
        // saveOrUpdateToDatabase(String.format(INSERT_MEMORY_USAGE,
        // this.nodeID, timeID, mData.getMem().toString()));

        conn.setAutoCommit(true);
        return true;

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

You may need to modify those SQL query defined in the constants.

answered May 18, 2012 at 22:45

Luciano

8,5925 gold badges36 silver badges58 bronze badges

3 Comments

Gray Over a year ago

This suffers from the same bug as the OP. He cannot prepare multiple statements that way. See my post.

Luciano Over a year ago

You are right, my intention was to set the assignment before the for, but I forgot to move it while copy pasting his code. Sorry

Gray Over a year ago

You sure you don't want to delete or edit this dude?

Collectives™ on Stack Overflow

Java PreparedStatement.executeBatch() in SQLite not working

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related