管理 Java servlet 数据库连接的最佳方式

Best way to manage database connection for a Java servlet

提问by TM.

What is the best way to manage a database connection in a Java servlet?

在 Java servlet 中管理数据库连接的最佳方法是什么？

Currently, I simply open a connection in the init()function, and then close it in destroy().

目前，我只是在init()函数中打开一个连接，然后在destroy().

However, I am concerned that "permanently" holding onto a database connection could be a bad thing.

但是，我担心“永久”保持数据库连接可能是一件坏事。

Is this the correct way to handle this? If not, what are some better options?

这是处理这个问题的正确方法吗？如果没有，有什么更好的选择？

edit: to give a bit more clarification: I have tried simply opening/closing a new connection for each request, but with testing I've seen performance issues due to creating too many connections.

编辑：给出更多说明：我尝试简单地为每个请求打开/关闭一个新连接，但是在测试中我看到了由于创建太多连接而导致的性能问题。

Is there any value in sharing a connection over multiple requests? The requests for this application are almost all "read-only" and come fairly rapidly (although the data requested is fairly small).

通过多个请求共享连接是否有任何价值？这个应用程序的请求几乎都是“只读”的，并且来得相当快（虽然请求的数据相当小）。

采纳答案by Hyman Leow

I actually disagree with using Commons DBCP. You should really defer to the container to manage connection pooling for you.

我实际上不同意使用 Commons DBCP。您真的应该让容器为您管理连接池。

Since you're using Java Servlets, that implies running in a Servlet container, and all major Servlet containers that I'm familiar with provide connection pool management (the Java EE spec may even require it). If your container happens to use DBCP (as Tomcat does), great, otherwise, just use whatever your container provides.

由于您使用的是 Java Servlet，这意味着在 Servlet 容器中运行，并且我熟悉的所有主要 Servlet 容器都提供连接池管理（Java EE 规范甚至可能需要它）。如果您的容器碰巧使用 DBCP（就像 Tomcat 那样），那太好了，否则，只需使用您的容器提供的任何内容。

回答by John Topley

You should only hold a database connection open for as long as you need it, which dependent on what you're doing is probably within the scope of your doGet/doPostmethods.

您应该只在需要时打开数据库连接，这取决于您正在做什么可能在您的doGet/doPost方法范围内。

回答by Hyman Leow

Are you pooling your connections? If not, you probably should to reduce the overhead of opening and closing your connections.

你在汇集你的连接吗？如果没有，您可能应该减少打开和关闭连接的开销。

Once that's out of the way, just keep the connection open for as long as it's need, as John suggested.

一旦解决了这个问题，就按照约翰的建议，在需要时保持连接打开。

回答by Joel

The best way, and I'm currently looking through Google for a better reference sheet, is to use pools.

最好的方法，我目前正在通过谷歌寻找更好的参考表，是使用池。

On initialization, you create a pool that contains X number of SQL connection objects to your database. Store these objects in some kind of List, such as ArrayList. Each of these objects has a private boolean for 'isLeased', a long for the time it was last used and a Connection. Whenever you need a connection, you request one from the pool. The pool will either give you the first available connection, checking on the isLeased variable, or it will create a new one and add it to the pool. Make sure to set the timestamp. Once you are done with the connection, simply return it to the pool, which will set isLeased to false.

在初始化时，您创建一个池，其中包含 X 个与数据库的 SQL 连接对象。将这些对象存储在某种 List 中，例如 ArrayList。这些对象中的每一个都有一个用于“isLeased”的私有布尔值，它上次使用的时间很长，还有一个连接。每当您需要连接时，您都可以从池中请求一个。该池要么为您提供第一个可用连接，检查 isLeased 变量，要么创建一个新连接并将其添加到池中。确保设置时间戳。完成连接后，只需将其返回到池中，池就会将 isLeased 设置为 false。

To keep from constantly having connections tie up the database, you can create a worker thread that will occasionally go through the pool and see when the last time a connection was used. If it has been long enough, it can close that connection and remove it from the pool.

为了避免连接不断地占用数据库，您可以创建一个工作线程，该线程偶尔会通过池并查看上次使用连接的时间。如果它已经足够长，它可以关闭该连接并将其从池中删除。

The benefits of using this, is that you don't have long wait times waiting for a Connection object to connect to the database. Your already established connections can be reused as much as you like. And you'll be able to set the number of connections based on how busy you think your application will be.

使用它的好处是，您不必等待 Connection 对象连接到数据库的等待时间很长。您已经建立的连接可以随心所欲地重复使用。您将能够根据您认为应用程序的繁忙程度来设置连接数。

回答by Vincent Ramdhanie

Usually you will find that opening connections per request is easier to manage. That means in the doPost() or the doGet() method of your servlet.

通常你会发现每个请求打开连接更容易管理。这意味着在 servlet 的 doPost() 或 doGet() 方法中。

Opening it in the init() makes it available to all requests and what happens when you have concurrent requests?

在 init() 中打开它使其可用于所有请求，当您有并发请求时会发生什么？

回答by ScArcher2

I'd use Commons DBCP. It's an Apache project that manages the connection pool for you.

我会使用Commons DBCP。它是一个为您管理连接池的 Apache 项目。

You'd just get your connection in your doGet or doPost run your query and then close the connection in a finally block. (con.close() just returns it to the pool, it doesn't actually close it).

您只需在 doGet 中获取连接或 doPost 运行查询，然后在 finally 块中关闭连接。（con.close() 只是将它返回到池中，它实际上并没有关闭它）。

DBCP can manage connection timeouts and recover from them. The way you are currently doing things if your database goes down for any period of time you'll have to restart your application.

DBCP 可以管理连接超时并从中恢复。如果您的数据库在任何时间段内出现故障，您当前的处理方式将不得不重新启动您的应用程序。

回答by anjanb

A connection pool associated with a Data source should do the trick. You can get hold of the connection from the dataSource in the servlet request method(doget/dopost, etc).

与数据源关联的连接池应该可以解决问题。您可以从 servlet 请求方法（doget/dopost等）中的 dataSource 获取连接。

dbcp, c3p0 and many other connection pools can do what you're looking for. While you're pooling connections, you might want to pool Statements and PreparedStatements; Also, if you're a READ HEAVY environment as you indicated, you might want to cache some of the results using something like ehcache.

dbcp、c3p0 和许多其他连接池可以满足您的需求。在汇集连接时，您可能希望汇集 Statements 和 PreparedStatements；此外，如果您是一个 READ HEAVY 环境，如您所指出的，您可能希望使用 ehcache 之类的东西来缓存一些结果。

BR,
~A

BR，
~A

回答by alex

Pool it.

池它。

Also, if you are doing raw JDBC, you could look into something that helps you manage the Connection, PreparedStatement, etc. Unless you have very tight "lightweightness" requirements, using Spring's JDBC support, for instance, is going to simplify your code a lot- and you are not forced to use any other part of Spring.

此外，如果您正在使用原始 JDBC，您可以研究一些可以帮助您管理 Connection、PreparedStatement 等的东西。除非您有非常严格的“轻量级”要求，否则使用 Spring 的 JDBC 支持，例如，将简化您的代码很多 - 并且您不会被迫使用 Spring 的任何其他部分。

See some examples here:

在此处查看一些示例：

http://static.springframework.org/spring/docs/2.5.x/reference/jdbc.html

http://static.springframework.org/spring/docs/2.5.x/reference/jdbc.html

回答by Dan Rosenstark

As everybody says, you need to use a connection pool. Why? What up? Etc.

正如大家所说，您需要使用连接池。为什么？怎么了？等等。

What's Wrong With Your Solution

您的解决方案有什么问题

I know this since I also thought it was a good idea once upon a time. The problem is two-fold:

我知道这一点，因为我曾经也认为这是一个好主意。问题有两个：

1. All threads (servlet requests get served with one thread per each) will be sharing the same connection. The requests will therefore get processed one at a time. This is very slow, even if you just sit in a single browser and lean on the F5 key. Try it: this stuff sounds high-level and abstract, but it's empirical and testable.
2. If the connection breaks for any reason, the init method will not be called again (because the servlet will not be taken out of service). Do not try to handle this problem by putting a try-catch in the doGet or doPost, because then you will be in hell (sort of writing an app server without being asked).
3. Contrary to what one might think, you will not have problems with transactions, since the transaction start gets associated with the thread and not just the connection. I might be wrong, but since this is a bad solution anyway, don't sweat it.

1. 所有线程（每个 servlet 请求都有一个线程）将共享相同的连接。因此，将一次处理一个请求。这非常慢，即使您只是坐在一个浏览器中并依靠 F5 键。试试看：这些东西听起来很高级和抽象，但它是经验性的和可测试的。
2. 如果连接因任何原因中断，则不会再次调用 init 方法（因为 servlet 不会停止服务）。不要试图通过在 doGet 或 doPost 中放置 try-catch 来处理这个问题，因为那样你会陷入困境（有点像在没有被问到的情况下编写应用服务器）。
3. 与人们可能认为的相反，您不会遇到事务问题，因为事务开始与线程相关联，而不仅仅是连接。我可能是错的，但无论如何这是一个糟糕的解决方案，不要担心。

Why Connection Pool

为什么连接池

Connection pools give you a whole bunch of advantages, but most of all they solve the problems of

连接池为您提供了一大堆优势，但最重要的是它们解决了以下问题

1. Making a real database connection is costly. The connection pool always has a few extra connections around and gives you one of those.
2. If the connections fail, the connection pool knows how to open a new one
3. Very important: every thread gets its own connection. This means that threading is handled where it should be: at the DB level. DBs are super efficient and can handle concurrent request with ease.
4. Other stuff (like centralizing location of JDBC connect strings, etc.), but there are millions of articles, books, etc. on this

1. 建立真正的数据库连接成本很高。连接池总是有一些额外的连接，并为您提供其中之一。
2. 如果连接失败，连接池知道如何打开一个新的
3. 非常重要：每个线程都有自己的连接。这意味着线程在它应该在的地方处理：在数据库级别。DB 非常高效，可以轻松处理并发请求。
4. 其他东西（如 JDBC 连接字符串的集中位置等），但有数百万篇文章、书籍等关于此

When to Get a Connection

何时获得连接

Somewhere in the call stack initiated in your service delegate (doPost, doGet, doDisco, whatever) you should get a connection and then you should do the right thing and return it in a finally block. I should mention that the C# main architect dude said once up a time that you should use finallyblocks 100x more than catchblocks. Truer words never spoken...

在您的服务委托（doPost、doGet、doDisco 等）中启动的调用堆栈中的某个地方，您应该获得一个连接，然后您应该做正确的事情并在 finally 块中返回它。我应该提到 C# 主要架构师曾经说过，你应该使用finally比catch块多 100 倍的块。更真实的话从未说过...

Which Connection Pool

哪个连接池

You're in a servlet, so you should use the connection pool the container provides. Your JNDI code will be completely normal except for how you obtain the connection. As far as I know, all servlet containers have connection pools.

您在 servlet 中，因此您应该使用容器提供的连接池。除了您如何获得连接之外，您的 JNDI 代码将完全正常。据我所知，所有 servlet 容器都有连接池。

Some of the comments on the answers above suggest using a particular connection pool API instead. Your WAR should be portable and "just deploy." I think this is basically wrong. If you use the connection pool provided by your container, your app will be deployable on containers that span multiple machines and all that fancy stuff that the Java EE spec provides. Yes, the container-specific deployment descriptors will have to be written, but that's the EE way, mon.

对上述答案的一些评论建议改用特定的连接池 API。您的 WAR 应该是可移植的并且“只需部署”。我认为这基本上是错误的。如果您使用容器提供的连接池，您的应用程序将可部署在跨越多台机器的容器以及 Java EE 规范提供的所有花哨的东西上。是的，必须编写特定于容器的部署描述符，但这就是 EE 方式，我。

One commenter mentions that certain container-provided connection pools do not work with JDBC drivers (he/she mentions Websphere). That sounds totally far-fetched and ridiculous, so it's probably true. When stuff like that happens, throw everything you're "supposed to do" in the garbage and do whatever you can. That's what we get paid for, sometimes :)

一位评论者提到某些容器提供的连接池不适用于 JDBC 驱动程序（他/她提到了 Websphere）。这听起来完全牵强和荒谬，所以它可能是真的。当这样的事情发生时，把你“应该做”的一切都扔进垃圾桶，尽你所能。这就是我们得到报酬的原因，有时:)