my dog learned polymorphism*
The moose likes JDBC and the fly likes transaction size and commit Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "transaction size and commit" Watch "transaction size and commit" New topic
Author

transaction size and commit

Rolf Johansson
Ranch Hand

Joined: Feb 25, 2004
Posts: 32
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:



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
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

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 ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Rolf Johansson
Ranch Hand

Joined: Feb 25, 2004
Posts: 32
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
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

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
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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 loopand then set the parameters and execute it for each iteration.
[ January 25, 2005: Message edited by: David Harkness ]
 
Don't get me started about those stupid light bulbs.
 
subject: transaction size and commit