Is this using PreparedStatements in a thread in Java correctly?

Question

I still work part time, and so I always try to keep abreast of the best ways to do something. Recently, I had to write a program for work, where the main program thread generated threads of the "task" (for each record "db" task), which would perform some operations, and then update the record to say that it is finished. Therefore, I need database connection objects and PreparedStatement objects available or available for ThreadedTask objects.

Is this about what I wrote in the letter that creates a PreparedStatement object on the stream as waste? I thought static PreparedStatments could create race conditions ...

 Thread A stmt.setInt ();
 Thread B stmt.setInt ();
 Thread A stmt.execute ();  
 Thread B stmt.execute ();  

Version A never starts.

Is this thread safe? Is creating and destroying PreparedStatement objects that are always the same are not huge waste?

 public class ThreadedTask implements runnable { private final PreparedStatement taskCompleteStmt; public ThreadedTask() { //... taskCompleteStmt = Main.db.prepareStatement(...); } public run() { //... taskCompleteStmt.executeUpdate(); } } public class Main { public static final db = DriverManager.getConnection(...); } 

Answer 1

I believe that it is not recommended to share database connections (and prepared operations) between threads. JDBC does not require connections to be thread safe, and I would expect most drivers to not.

Give each thread its own connection (or synchronize the connection for each request, but this probably defeats the goal of having multiple threads).

Creates and destroys PreparedStatement objects that are always the same, not huge waste?

Not really. Most of the work happens on the server and will be cached and reused there if you use the same SQL statement. Some JDBC drivers also support statement caching, so even client-side instruction descriptors can be reused.

You could see a significant improvement using batch requests instead of (or in addition to) multiple threads. Prepare the query once and run it for a large amount of data in one large batch.

Answer 2

Threading problems are not a problem. Everything looks syntactically and functionally normal, and it should work for about half an hour. However, a topical issue is resource leakage. The application will crash in about half an hour, because you never close them after use. The database, in turn, will close the connection sooner or later so that it can return it back.

However, you do not need to worry about caching the finished data. The JDBC driver and database will take care of this task. Rather, worry about leaking resources and make your JDBC code as strong as possible.

 public class ThreadedTask implements runnable { public run() { Connection connection = null; Statement statement = null; try { connection = DriverManager.getConnection(url); statement = connection.prepareStatement(sql); // ... } catch (SQLException e) { // Handle? } finally { if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {} if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {} } } } 

To improve connection performance, use a connection pool, for example c3p0 (this, by the way, does not mean that you can change how you write JDBC code, always purchase and close resources in the shortest area in the try-finally block).

Answer 3

It is best to use a connection pool and get each thread to request a connection from the pool. Create your statement about the connection you submitted, remembering to close it and then release it to the pool when you are done. The advantage of using a pool is that you can easily increase the number of connections available if you find that concurrency flow is becoming a problem.