多线程JDBC(Multithreaded JDBC)

多线程JDBC(Multithreaded JDBC)

在架构上，用多线程处理JDBC的最佳方式是什么？ 我有许多线程同时访问数据库。 通过单个连接和语句，我收到以下错误消息：

org.postgresql.util.PSQLException：此ResultSet已关闭。

我应该使用多个连接，多个语句还是有更好的方法？ 我的初步想法是每个线程使用一个语句来保证每个语句只有一个结果集。

Architecturally what is the best way to handle JDBC with multiple threads? I have many threads concurrently accessing the database. With a single connection and statement I get the following error message:

org.postgresql.util.PSQLException: This ResultSet is closed.

Should I use multiple connections, multiple statements or is there a better method? My preliminary thought was to use one statement per thread which would guarantee a single result set per statement.

最满意答案

您应该为每个任务使用一个连接。 如果使用连接池，则不能使用由其他连接准备的预准备语句。 由连接创建的所有对象（ResultSet，PreparedStatements）在连接返回到池后都无效。

所以，它是相似的

public void getSomeData() { Connection conn = datasource.getConnection(); PreparedStatement st; try { st = conn.prepareStatement(...); st.execute(); } finally { close(st); close(conn); } }

所以在这种情况下，所有的DAO对象都不是连接，而是实际上是可连接连接工厂的DataSource对象（java.sql.DataSource）。 在每一种方法中，你首先得到连接，做所有的工作并且关闭连接。 你应该尽可能快地返回连接池。 连接返回后，它可能不是物理关闭的，但重新初始化（所有活动事务关闭，所有会话变量销毁等）

You should use one connection per task. If you use connection pooling you can't use prepared statements prepared by some other connection. All objects created by connection (ResultSet, PreparedStatements) are invalid for use after connection returned to pool.

So, it's alike

public void getSomeData() { Connection conn = datasource.getConnection(); PreparedStatement st; try { st = conn.prepareStatement(...); st.execute(); } finally { close(st); close(conn); } }

So in this case all your DAO objects take not Connection, but DataSource object (java.sql.DataSource) which is poolable connection factory indeed. And in each method you first of all get connection, do all your work and close connection. You should return connection to pool as fast as possible. After connection returned it may not be physically closed, but reinitialized (all active transactions closed, all session variables destroyed etc.)