SQL-Escaping and bulk-insert in Java

Question

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?

You leave yourself at a high risk if you dont use the PreparedSQL.... — Justin Pihony
– Justin Pihony, Commented Apr 2, 2012 at 20:00
I think this answer about bulk inserts with PreparedStatement is what you are looking for: stackoverflow.com/a/6892457/1272477 — mguymon
– mguymon, Commented Apr 2, 2012 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
– VirtualVoid, Commented Apr 2, 2012 at 20:25
possible duplicate of Bulk insert in Java using prepared statements batch update — Gray
– Gray, Commented Apr 2, 2012 at 22:46

SHUNMUGA RAJ PRABAKARAN · Accepted Answer · 2013-01-04 09:32:40Z

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

Luca · Accepted Answer · 2012-04-02 20:12:22Z

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

Collectives™ on Stack Overflow

SQL-Escaping and bulk-insert in Java

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related