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.
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 ]