How do you do an extended insert using JDBC without building strings?

Question

I've got an application that parses log files and inserts a huge amount of data into database. It's written in Java and talks to a MySQL database over JDBC. I've experimented with different ways to insert the data to find the fastest for my particular use case. The one that currently seems to be the best performer is to issue an extended insert (e.g. a single insert with multiple rows), like this:

INSERT INTO the_table (col1, col2, ..., colN) VALUES
(v1, v2, v3, ..., vN),
(v1, v2, v3, ..., vN),
...,
(v1, v2, v3, ..., vN);

The number of rows can be tens of thousands.

I've tried using prepared statements, but it's nowhere near as fast, probably because each insert is still sent to the DB separately and the tables needs to be locked and whatnot. My colleague who worked on the code before me tried using batching, but that didn't perform well enough either.

The problem is that using extended inserts means that as far as I can tell I need to build the SQL string myself (since the number of rows is variable) and that means that I open up all sorts of SQL injection vectors that I'm no where intelligent enough to find myself. There's got to be a better way to do this.

Obviously I escape the strings I insert, but only with something like str.replace("\"", "\\\""); (repeated for ', ? and \), but I'm sure that isn't enough.

Answer 1

prepared statements + batch insert:

PreparedStatement stmt = con.prepareStatement(
"INSERT INTO employees VALUES (?, ?)");

stmt.setInt(1, 101);
stmt.setString(2, "Paolo Rossi");
stmt.addBatch();

stmt.setInt(1, 102);
stmt.setString(2, "Franco Bianchi");
stmt.addBatch();

// as many as you want   
stmt.executeBatch();

Answer 2

I would try batching your inserts and see how that performs.

Have a read of this (http://www.onjava.com/pub/a/onjava/excerpt/javaentnut_2/index3.html?page=2) for more information on batching.