Java中有多少个JDBC连接？

How many JDBC connections in Java?

提问by Epitaph

I have a Java program consisting of about 15 methods. And, these methods get invoked very frequently during the exeuction of the program. At the moment, I am creating a new connection in every method and invoking statements on them (Database is setup on another machine on the network).

我有一个包含大约 15 个方法的 Java 程序。并且，这些方法在程序执行期间被非常频繁地调用。目前，我正在每个方法中创建一个新连接并在它们上调用语句（数据库设置在网络上的另一台机器上）。

What I would like to know is: Should I create only one connection in the main method and pass it as an argument to all the methods that require a connection object since it would significantly reduce the number of connections object in the program, instead of creating and closing connections very frequently in every method.

我想知道的是：我是否应该在 main 方法中只创建一个连接并将其作为参数传递给所有需要连接对象的方法，因为它会显着减少程序中的连接对象数量，而不是创建并且在每种方法中都非常频繁地关闭连接。

I suspect I am not using the resources very efficiently with the current design, and there is a lot of scope for improvement, considering that this program might grow a lot in the future.

我怀疑我在当前的设计中没有非常有效地使用资源，并且考虑到该程序将来可能会增长很多，因此还有很大的改进空间。

采纳答案by Neil Coffey

Yes, you should consider re-using connections rather than creating a new one each time. The usual procedure is:

是的，您应该考虑重用连接而不是每次都创建一个新连接。通常的程序是：

• make some guess as to how many simultaneous connections your database can sensibly handle (e.g. start with 2 or 3 per CPU on the database machine until you find out that this is too few or too many-- it'll tend to depend on how disk-bound your queries are)
• create a poolof this many connections: essentially a class that you can ask for "the next free connection" at the beginning of each method and then "pass back" to the pool at the end of each method
• your getFreeConnection() method needs to return a free connection if one is available, else either (1) create a new one, up to the maximum number of connections you've decided to permit, or (2) if the maximum are already created, wait for one to become free
• I'd recommend the Semaphore class to manage the connections; I actually have a short article on my web site on managing a resource pool with a Semaphorewith an example I think you could adapt to your purpose

• 猜测一下您的数据库可以合理处理多少个同时连接（例如，从数据库机器上的每个 CPU 2 或 3 个开始，直到您发现这太少或太多——这往往取决于磁盘-绑定您的查询是）
• 创建一个包含这么多连接的池：本质上是一个类，您可以在每个方法开始时请求“下一个空闲连接”，然后在每个方法结束时“传回”到池中
• 如果有可用连接，您的 getFreeConnection() 方法需要返回一个可用连接，否则要么 (1) 创建一个新连接，最多达到您决定允许的最大连接数，或者 (2) 如果已经创建了最大连接数，等待一个人变得自由
• 我建议使用 Semaphore 类来管理连接；我实际上在我的网站上有一篇关于使用信号量管理资源池的简短文章，我认为你可以适应你的目的

A couple of practical considerations:

几个实际考虑：

• For optimum performance, you need to be careful not to "hog" a connection while you're not actually using it to run a query. If you take a connection from the pool once and then pass it to various methods, you need to make sure you're not accidentally doing this.
• Don't forget to return your connections to the pool! (try/finally is your friend here...)
• On many systems, you can't keep connections open 'forever': the O/S will close them after some maximum time. So in your 'return a connection to the pool' method, you'll need to think about 'retiring' connections that have been around for a long time(build in some mechanism for remembering, e.g. by having a wrapper objectaround an actual JDBC Connection object that you can use to store metrics such as this)
• You may want to consider using prepared statements.
• Over time, you'll probably need to tweak the connection pool size

• 为了获得最佳性能，您需要注意不要在实际上没有使用连接来运行查询时“占用”连接。如果您从池中获取一次连接，然后将其传递给各种方法，则需要确保您没有意外地这样做。
• 不要忘记将您的连接返回到池中！（尝试/终于是你的朋友在这里......）
• 在许多系统上，您不能“永远”保持连接打开：操作系统将在最长时间后关闭它们。因此，在您的“返回到池的连接”方法中，您需要考虑“退休”已经存在很长时间的连接（构建某种记忆机制，例如通过在实际 JDBC 周围使用包装器对象您可以使用连接对象来存储诸如此类的指标）
• 您可能需要考虑使用准备好的语句。
• 随着时间的推移，您可能需要调整连接池大小

回答by OscarRyz

You should use a connection pool for that.

您应该为此使用连接池。

That way you could ask for the connection and release it when you are finish with it and return it to the pool

这样你就可以请求连接并在完成连接后释放它并将其返回到池中

If another thread wants a new connection and that one is in use, a new one could be created. If no other thread is using a connection the same could be re-used.

如果另一个线程想要一个新连接并且该连接正在使用中，则可以创建一个新连接。如果没有其他线程正在使用连接，则可以重用相同的连接。

This way you can leave your app somehow the way it is ( and not passing the connection all around ) and still use the resources properly.

这样你就可以以某种方式离开你的应用程序（而不是到处传递连接）并且仍然正确地使用资源。

Unfortunately first class ConnectionPools are not very easy to use in standalone applications ( they are the default in application servers ) Probably a microcontainer ( such as Sping ) or a good framework ( such as Hibernate ) could let you use one.

不幸的是，一流的 ConnectionPools 在独立应用程序中不是很容易使用（它们是应用程序服务器中的默认设置） 可能一个微容器（例如 Sping）或一个好的框架（例如 Hibernate）可以让您使用它们。

They are no too hard to code one from the scratch though.

不过，从头开始编写一个代码并不难。

:)

:)

This google searchwill help you to find more about how to use one.

这个谷歌搜索将帮助您找到更多关于如何使用一个的信息。

Skim through

快速浏览

回答by Nemi

You can either pass in the connection or better yet use something like Jakarta Database Connection Pooling. http://commons.apache.org/dbcp/

您可以传入连接，也可以更好地使用 Jakarta 数据库连接池之类的东西。 http://commons.apache.org/dbcp/

回答by MarkR

If your application is single-threaded, or does all its database operations from a single thread, it's ok to use a single connection. Assuming you don't need multiple connections for any other reason, this would be by far the simplest implementation.

如果您的应用程序是单线程的，或者从单个线程执行其所有数据库操作，则可以使用单个连接。假设您出于任何其他原因不需要多个连接，这将是迄今为止最简单的实现。

Depending on your driver, it may also be feasible to share a connection between threads - this would be ok too, if you trust your driver not to lie about its thread-safety. See your driver documentation for more info.

根据您的驱动程序，在线程之间共享连接也可能是可行的 - 如果您相信您的驱动程序不会对其线程安全性撒谎，这也可以。有关更多信息，请参阅您的驱动程序文档。

Typically the objects below "Connection" cannot safely be used from multiple threads, so it's generally not advisable to share ResultSet, Statement objects etc between threads - by far the best policy is to use them in the same thread which created them; this is normally easy because those objects are not generally kept for too long.

通常情况下，“Connection”下面的对象不能安全地从多个线程中使用，因此通常不建议在线程之间共享 ResultSet、Statement 对象等——到目前为止，最好的策略是在创建它们的同一个线程中使用它们；这通常很容易，因为这些对象通常不会保存太久。

回答by Peter Lawrey

Many JDBC drivers do connection pooling for you, so there is little advantage doing additional pooling in this case. I suggest you check the documentation for you JDBC driver.

许多 JDBC 驱动程序为您做连接池，所以在这种情况下做额外的池没有什么好处。我建议您查看 JDBC 驱动程序的文档。

Another approach to connection pools is to

连接池的另一种方法是

• Have one connection for all database access with synchronised access. This doesn't allow concurrency but is very simple.
• Store the connections in a ThreadLocal variable (override initialValue()) This works well if there is a small fixed number of threads.

• 为所有具有同步访问的数据库访问建立一个连接。这不允许并发，但非常简单。
• 将连接存储在一个 ThreadLocal 变量中（覆盖 initialValue()） 如果有少量固定线程，这很有效。

Otherwise, I would suggest using a connection pool.

否则，我建议使用连接池。