0

My goal is sql-escaping in bulk-insert query. Eg:

INSERT INTO log VALUES (0,5,-7,'str'), (4,0,0,'str'), (0,0,0,'str');

The code inserts in table about 100-200 records each 30 seconds. (Log pooling).
I didn't find way to use PreparedStatement for bulk-insert, so i had to manually build that query through StringBuilder.
But i have no idea how to escape strings, don't really much want apply something like kludge-fixes (Quotes escaping through regex-replace etc).

Is there any handy way?

VirtualVoid
  • 1,005
  • 3
  • 17
  • 25
  • 1
    You leave yourself at a high risk if you dont use the PreparedSQL.... – Justin Pihony Apr 02 '12 at 20:00
  • 2
    I think this answer about bulk inserts with PreparedStatement is what you are looking for: http://stackoverflow.com/a/6892457/1272477 – mguymon Apr 02 '12 at 20:02
  • mguymon, Probably that's solution. I missed this out. Btw, is this addBatch/executeBatch is Bulk-insert like example above, or set of INSERT queries which will be executed in one pass? – VirtualVoid Apr 02 '12 at 20:25
  • possible duplicate of [Bulk insert in Java using prepared statements batch update](http://stackoverflow.com/questions/6892105/bulk-insert-in-java-using-prepared-statements-batch-update) – Gray Apr 02 '12 at 22:46

2 Answers2

1

Two ways so far i know.

1st Way

Its insert record one by one

final String sql = "INSERT INTO tablename(columnname) Values(?)";

PreparedStatement statement = connection.prepareStatement(sql);

while (condition) {
statement.setString(1,value);
statement.executeUpdate();
}

2nd way

It inserts all record as bulk insert

final String sql = "INSERT INTO tablename(columnname) Values(?)";

PreparedStatement statement = connection.prepareStatement(sql);

while (condition) {
statement.setString(1,value);
statement.addBatch();
}

statement.executeBatch();
SHUNMUGA RAJ PRABAKARAN
  • 608
  • 5
  • 8
0

You need to use PreparedStatement and possibly batch insert. See http://www.exampledepot.com/egs/java.sql/BatchUpdate.html

Luca
  • 4,223
  • 1
  • 21
  • 24