[java] Reusing a PreparedStatement multiple times

in the case of using PreparedStatement with a single common connection without any pool, can I recreate an instance for every dml/sql operation mantaining the power of prepared statements?

I mean:

for (int i=0; i<1000; i++) {
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setObject(1, someValue);
    preparedStatement.executeQuery();
    preparedStatement.close();
}

instead of:

PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i=0; i<1000; i++) {
    preparedStatement.clearParameters();
    preparedStatement.setObject(1, someValue);
    preparedStatement.executeQuery();
}
preparedStatement.close();

my question arises by the fact that I want to put this code into a multithreaded environment, can you give me some advice? thanks

This question is related to java jdbc prepared-statement

The answer is


The loop in your code is only an over-simplified example, right?

It would be better to create the PreparedStatement only once, and re-use it over and over again in the loop.

In situations where that is not possible (because it complicated the program flow too much), it is still beneficial to use a PreparedStatement, even if you use it only once, because the server-side of the work (parsing the SQL and caching the execution plan), will still be reduced.

To address the situation that you want to re-use the Java-side PreparedStatement, some JDBC drivers (such as Oracle) have a caching feature: If you create a PreparedStatement for the same SQL on the same connection, it will give you the same (cached) instance.

About multi-threading: I do not think JDBC connections can be shared across multiple threads (i.e. used concurrently by multiple threads) anyway. Every thread should get his own connection from the pool, use it, and return it to the pool again.


Examples related to java

Under what circumstances can I call findViewById with an Options Menu / Action Bar item? How much should a function trust another function How to implement a simple scenario the OO way Two constructors How do I get some variable from another class in Java? this in equals method How to split a string in two and store it in a field How to do perspective fixing? String index out of range: 4 My eclipse won't open, i download the bundle pack it keeps saying error log

Examples related to jdbc

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' Hibernate Error executing DDL via JDBC Statement Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] MySQL JDBC Driver 5.1.33 - Time Zone Issue Spring-Boot: How do I set JDBC pool properties like maximum number of connections? Where can I download mysql jdbc jar from? Print the data in ResultSet along with column names How to set up datasource with Spring for HikariCP? java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver Exception occurring. Why? java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname

Examples related to prepared-statement

Using setDate in PreparedStatement How to use an arraylist as a prepared statement parameter Where's my invalid character (ORA-00911) How can prepared statements protect from SQL injection attacks? Using "like" wildcard in prepared statement What does "if (rs.next())" mean? Java: Insert multiple rows into MySQL with PreparedStatement What does a question mark represent in SQL queries? PreparedStatement with list of parameters in a IN clause Using prepared statements with JDBCTemplate