CODEWITHSUNDEEP
javasqlescapingbulkinsert
VirtualVoid
VirtualVoid

Reputation: 1023

SQL-Escaping and bulk-insert in Java

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?

Upvotes: 0

Views: 694

Answers (2)

SHUNMUGA RAJ PRABAKARAN
SHUNMUGA RAJ PRABAKARAN

Reputation: 608

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();

Upvotes: 1

Luca
Luca

Reputation: 4273

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

Upvotes: 0

Related Questions