transaction size and commit

[Rolf Johansson]

Hi:
I need to perform a commit after N records have been inserted. After each insert, I increase a counter, then call commit() if N records are inserted. Is this the most efficient way, or does JDBC have an "internal" way of knowing that N records have been inserted, then "automatically" committing them?
Right now, I have this:

rptDataConn = dbConnection(dataSourceName);  // connect to DB
rptDataConn.setAutoCommit(false);            // shut off auto commit
stmt = rptDataConn.createStatement();    
// format insert statement in "insertSQL"
rows = stmt.executeUpdate(insertSQL);        // perform insert
if (totalRowsInserted++ > insertsPerTransaction) {
  rptDataConn.commit();                      // perform insert if needed
}

Is there a need for me to keep track of the number of inserts I have done, or can JDBC do that for me and perform the commit for me?
Thanks.
- Rolf.

[Shailesh Chandra]

Rolf,

is really your requirement to commit after every n records.
What about transactions.

Suppose you have hundred record and you commit after
every 10 records and just after inserting 55 records
there is an error and there would be 50 records
commited.

how will you keep track of such records.

won't you rollback all inserted statements ???

thanks
Shailesh
[ January 24, 2005: Message edited by: Shailesh Chandra ]

[Rolf Johansson]

In this case I don't need to rollback. The source data can be re-processed any number of times without damage.

So, my question is, does JDBC have an internal method of automatically committing N records, or do I need to keep track of the number of records inserted and explicitly perform a commit?

[Shailesh Chandra]

I dont think there is such facility in JDBC whick commits at every N records.

But in your case I will recommend to use execute batch after you add N records in your statements/prepared statement.

thanks
Shailesh

[David Harkness]

JDBC now has an executeBatch() method that will increase performance by sending the whole batch to the database at once. I haven't used it (only indirectly through Hibernate), but I bet it's fairly easy to use. It's fairly recent, so depending on your JDK you may not have access to it.

Otherwise, there is no internal mechanism to do what you're doing. Besides, what you're doing (counting insert statements and issuing commit() when you hit the limit) is not inefficient in itself, so I wouldn't worry about it.

What I would definitely do is use PreparedStatements. They will allow the DB to parse the SQL syntax once and then reissue the same statement, substituting new values each time. You prepare the statement with ? placeholders for the parameters outside the loop

insert into ... values ( ?, ?, ?, ... ? )

and then set the parameters and execute it for each iteration.

PreparedStatement stmt = conn.prepareStatement(sqlWithPlaceHolders);
for ( int i = 0 ; i < numRows ; i++ ) {
    stmt.clearParameters();
    stmt.setInt(1, id[i]);
    stmt.setString(2, name[i]);
    stmt.setDate(3, birthDate[i]);
    ...
    int insertCount = stmt.execute();
    // Increment row count and possibly commit
}

[ January 25, 2005: Message edited by: David Harkness ]