Creation of a prepared statement inside a loop

Question

For clarification: I know that it's the right thing to create the PreparedStatement outside the loop. I've asked this question just out of curiosity.

---

Let's assume that I'm creating a PreparedStatement inside a loop with always the same SQL query.

final String sql = "INSERT INTO ...";
while (condition) {
   ...
   PreparedStatement statement = connection.prepareStatement(sql);
   // Fill values of the prepared statement
   // Execute statement
   ...
}

Is this useless since the PreparedStatement object is always created anew? Or does the underlying database recognise that it's always the same SQL query with which the PreparedStatement is created and reuses it?

Solution 1

Some drivers do cache prepared statements, yes. For example, skim this Oracle documentation: http://docs.oracle.com/cd/B10501_01/java.920/a96654/stmtcach.htm

I don't believe there's anything that requires this to be true for all drivers, although certainly it seems like a likely feature of many JDBC drivers. It sounds like MySQL might not do this: How to use MySQL prepared statement caching?

That said, if you really want to use prepared statements efficiently, it seems like hanging on to an instance of a prepared statement that you use on each loop iteration makes a lot more sense.

Solution 2

1. If you are using the same PreparedStatement throughout the loop, then its better you keep the PreparedStatement outside the loop.

2. If you have sql statment which keeps changing inside the loop, then only its worth using it in the loop.

3. Moreover if its keep changing, then just use Statement instead of PreparedStatement, else the very purpose of PreparedStatement is lost as you keep changing it.

Solution 3

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

(or)

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

Solution 4

It is useless to create it everytime. See the link http://www.theserverside.com/news/1365244/Why-Prepared-Statements-are-important-and-how-to-use-them-properly

Solution 5

Also try disable autocommit with Connection.setAutoCommit(false) and that you use PreparedStatement.executeBatch()

View more solutions