Last week I had to parse thousands of sql insert statements, read from files (file per table), within a Java environment. The files contain complete SQL insert statements, so I used a normal simple Statement object.
Each insert statement was parsed to the normal execute method until I tested with a production set containing file with 220.000 inserts…. it took about 30 minutes. Hmm, that’s too long and such a simple job should run much faster.
....
So to speed it up I switched to batch processing by using the addBatch method for each insert statement and then call the executeBatch afterwards to process it. And…..still 30 minutes! How that’s possible? Google found the answer delivered by O’reilly:

Batching
As you saw in the previous section, PreparedStatement objects eventually become more efficient than their Statement counterparts after 65-125 executions of the same statement. If you’re going to execute a given SQL statement a large number of times, it makes sense from a performance standpoint to use a PreparedStatement object. But if you’re really going to do that many executions of a statement, or perhaps more than 50, you should consider batching. Batching is more efficient because it sends multiple SQL statements to the server at one time. Although JDBC defines batching capability for Statement objects, Oracle supports batching only when Prepared-Statement objects are used. This makes some sense. A SQL statement in a PreparedStatement object is parsed once and can be reused many times. This naturally lends itself to batching.
Source: http://www.onjava.com/pub/a/onjava/excerpt/oraclejdbc_19/index.html?page=2

Hey! Batch processing is not supported by Oracle using the normal Statement object! To comply with the Java API, the methods are available, but do about the same as the normal execute method.

After converting the insert sql strings to prepared statement strings (you know, the syntax with question marks on the params), I could use the PreparedStatement object.
The upload of the 220.000 records now takes an acceptable 45 seconds.

 

Conversion regarding null values

To convert plain sql statements to a prepared statement syntax, simply replace all params by question marks and use the appropriate set param methods (e.g. setString) to pass the param values to the prepared statement. The only problems are null values, because the setNull method needs the sql type as a param. At first I used a hard coded null in the prepared statement syntax. This resulted in lots of prepared statements, because each combination of null values as param values results in a different prepared statement. To solve this problem I now retrieve the metadata of the table with "select * from table where 1 = 0". No rows are returned, but the metadata is. By looping over the columns, the sql types, which are integers, are stored in a Hashtable with the colum name as key. When passing the null value with method setNull, now the sql type can be retrieved using the Hashtable.