在 Java 线程中使用 PreparedStatements 是否正确？

Is this use of PreparedStatements in a Thread in Java correct?

提问by ArturPhilibin

I'm still an undergrad just working part time and so I'm always trying to be aware of better ways to do things. Recently I had to write a program for work where the main thread of the program would spawn "task" threads (for each db "task" record) which would perform some operations and then update the record to say that it has finished. Therefore I needed a database connection object and PreparedStatement objects in or available to the ThreadedTask objects.

我仍然是一名本科生，只是兼职工作，所以我一直在努力寻找更好的做事方式。最近我不得不编写一个工作程序，其中程序的主线程将产生“任务”线程（对于每个数据库“任务”记录），这些线程将执行一些操作，然后更新记录以表示它已完成。因此，我需要一个数据库连接对象和 PreparedStatement 对象在 ThreadedTask 对象中或可用于 ThreadedTask 对象。

This is roughly what I ended up writing, is creating a PreparedStatementobject per thread a waste? I thought static PreparedStatmentscould create race conditions...

这大致是我最终写的内容，为PreparedStatement每个线程创建一个对象是一种浪费吗？我认为静态PreparedStatments可以创造竞争条件......

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

A′s version never gets execed..

A 的版本永远不会被执行..

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

这个线程安全吗？创建和销毁PreparedStatement始终相同的对象不是一种巨大的浪费吗？

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

回答by Thilo

I believe it is not a good idea to share database connections (and prepared statements) between threads. JDBC does not require connections to be thread-safe, and I would expect most drivers to not be.

我认为在线程之间共享数据库连接（和准备好的语句）不是一个好主意。JDBC 不要求连接是线程安全的，我希望大多数驱动程序不是。

Give every thread its own connection (or synchronize on the connection for every query, but that probably defeats the purpose of having multiple threads).

为每个线程提供自己的连接（或在每个查询的连接上同步，但这可能会破坏拥有多个线程的目的）。

Is creating and destroying PreparedStatement objects that are always the same not a huge waste?

创建和销毁始终相同的 PreparedStatement 对象不是一种巨大的浪费吗？

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

并不真地。大多数工作发生在服务器上，如果您使用相同的 SQL 语句，它们将被缓存并在那里重新使用。一些 JDBC 驱动程序还支持语句缓存，因此甚至可以重用客户端语句句柄。

You could see substantial improvement by using batched queries instead of (or in addition to) multiple threads, though. Prepare the query once, and run it for a lot of data in a single big batch.

不过，通过使用批处理查询而不是（或除此之外）多线程，您可以看到显着的改进。准备一次查询，并在一个大批量中为大量数据运行它。

回答by BalusC

The threadsafety is not the issue here. All looks syntactically and functionally fine and it should work for about half a hour. Leaking of resources is however the real issue here. The application will crash after about half a hour because you never close them after use. The database will in turn sooner or later close the connection itself so that it can claim it back.

线程安全不是这里的问题。一切在语法和功能上看起来都很好，应该可以运行大约半小时。然而，资源泄漏是这里的真正问题。该应用程序将在大约半小时后崩溃，因为您在使用后永远不会关闭它们。数据库迟早会关闭连接本身，以便它可以收回它。

That said, you don't need to worry about caching of preparedstatements. The JDBC driver and the DB will take care about this task. Rather worry about resource leaking and make your JDBC code as solid as possible.

也就是说，您无需担心准备语句的缓存。JDBC 驱动程序和 DB 将负责这项任务。而是担心资源泄漏并使您的 JDBC 代码尽可能可靠。

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 connecting performance, make use of a connection pool like c3p0(this by the way does not mean that you can change the way how you write the JDBC code; always acquire andclose the resources in the shortest possible scopein a try-finallyblock).

为了提高连接性能，请使用像c3p0这样的连接池（顺便说一下，这并不意味着您可以改变编写 JDBC 代码的方式；始终在块中的尽可能短的范围内获取和关闭资源）。try-finally

回答by Trevor Tippins

You're best to use a connection pool and get each thread to request a connection from the pool. Create your statements on the connection you're handed, remembering to close it and so release it back to the pool when you're done. The benefit of using the pool is that you can easily increase the number of available connections should you find that thread concurrency is becoming an issue.

最好使用连接池，并让每个线程从池中请求连接。在您收到的连接上创建您的语句，记住关闭它，并在完成后将其释放回池中。使用池的好处是，如果您发现线程并发正在成为一个问题，您可以轻松地增加可用连接的数量。