CODEWITHSUNDEEP
javasqljdbcprepared-statement
dimo414
dimo414

Reputation: 48884

Multi-row INSERT vs. repeatedly calling PreparedStatement.executeUpdate()

If I have a table created with the following schema:

CREATE TABLE names (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR);

And a List<String> names of names I want to add to the table, I see two ways of going about this.

The simple, brute force solution is to manually construct the SQL, like so:

String sql = "INSERT INTO names (name) VALUES ("+
             Joiner.on("), (").join(names)+
             ");";
conn.createStatement().execute(sql);

This hits the DB exactly once, and seems intuitive and straightforward. I'm aware there are SQL-Injection concerns, but for the sake of the question let's assume those are addressed already.

The alternative would be to use a prepared statement, and the JDBC docs suggest executing that statement repeatedly, like so:

con.setAutoCommit(false);
PreparedStatement addNames = con.prepareStatement(
   "INSERT INTO names (name) VALUES (?);");

for (String name : names) {
    addNames.setString(1, name);
    addNames.executeUpdate();
    con.commit();
}
con.setAutoCommit(true);

Is it really the case that executing a PreparedStatement repeatedly like this is more desirable than simply constructing the query String and executing it directly?

Upvotes: 3

Views: 3049

Answers (2)

Thomas Mueller
Thomas Mueller

Reputation: 50147

There are two more options:

  • Use PreparedStatement.addBatch(). For many databases, this will reduce the number of server round trips. Not for all databases however (for example, not for the H2 database).

  • Use PreparedStatement, but with multiple rows, in batches: 

    INSERT INTO names (name) VALUES (?), (?), (?), (?), (?), (?), (?), (?);

The second should reduce server roundtrips for all databases, but it is a bit more complex.

Upvotes: 2

user2028750
user2028750

Reputation:

yes its faster its Because each time a java application sends a new SQL statement to the Database,the server checks for syntax errors,prepares a plan for executing the statement and executes it.

If the same statement is sent again,though the DB server checks to see whether it has already recieved one exactly like it. If so the server does not check its syntax and prepare an execution plan for it so the server just executes it.

To take advantage of this database feature,java provides for the use of prepared statement .This feature lets you send send statements to the database server that gets executed repeatedly by accepting the parameter values that are sent to it.That improves the database performance because the database server only has to check the syntax and prepare the execution plan once for each statement.

Upvotes: 0

Related Questions