CODEWITHSUNDEEP
javasqlsqlite
Ondrej Tokar
Ondrej Tokar

Reputation: 5080

ExecuteBatch is not executing just once, but after every record. Why?

Here is my code:

    prodsQuery = dbc
                .connect()
                .prepareStatement(
                        "INSERT INTO 'duplicates' (C_ContactID, C_EmailAddress, 
C_DataSourceID, C_DateCreated)"
                                + "VALUES (?,?,?,?);");
        // for (Record x : records) {

        for (int i = startAtRecord; i <= records.size(); i++) {
            prodsQuery.setInt(1, records.get(i).getContactID());
            prodsQuery.setString(2, records.get(i).getEmail());
            prodsQuery.setString(3, records.get(i).getDataSourceID());
            prodsQuery.setString(4, records.get(i).getDateCreated());

            // addBatch is better than executeUpdate or executeQuery in this
            // case
            prodsQuery.addBatch();
            // save number of record in case of failure
            BufferedWriter out = new BufferedWriter(new FileWriter(
                    "data\\resumerecord.txt"));
            out.write(i + "\n");
            out.close();
            // execute batch every some records, in case of failure to start
            // at some point
            if (i % 5000 == 0) {
                prodsQuery.executeBatch();
                System.out.println("Batch was executed.");

            }

        }
        prodsQuery.executeBatch();
        prodsQuery.close();

I want to be effective thats why I would like to execute batch every 5000 records. But while I was testing and accidentally I turned off the program, then looked into DB and found that it stopped at record 7589. That means it is adding records one by one.

Why?

Shouldn't it insert whole chunks inside?

I thought that is the reason why batch is more effective.

Isn't there any other way how to store the ID of the record where it finished inserting? Maybe read it from db when program starts.

Upvotes: 2

Views: 1756

Answers (2)

SparkOn
SparkOn

Reputation: 8956

You should execute the batch insert inside a transaction. When executed inside a transaction you can make sure that either all updates are executed, or none are updated.

You should always run SQL query with auto-commit mode disabled even with JDBC Batch insert and update and do commit() explicitly.

something like this 

try{
    connection.setAutoCommit(false);
    // add to batch and execute batch
    connection.commit();
    } catch (SQLException e) {
      e.printStackTrace();
    }
finally{
//closing statements
   }

Upvotes: 1

CL.
CL.

Reputation: 180172

As long as you are in autocommit mode, each individual INSERT statement will have its own automatic transaction. This also applies to the statements executed by executeBatch; that function will not create a single transaction for you.

Committing a transaction is the most time-consuming part of this program, so it's likely that aborting the program will happen somewhere in the middle of executeBatch's loop.

To reduce the transaction overhead, disable autocommit mode, and manually call commit on the connection.

Upvotes: 1

Related Questions