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).
在多个请求*享一个连接有什么价值吗?这个应用程序的请求几乎都是“只读的”，而且来得相当快(尽管请求的数据相当小)。
9 个解决方案
#1
14
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那样)，那么很好，否则，只使用容器提供的任何东西。
#2
22
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:
我知道这一点，因为我曾经认为这是一个好主意。问题是双重的:
- 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.
- 所有线程(每个线程有一个servlet请求)都将共享相同的连接。因此，每次将处理一个请求。这是非常缓慢的，即使你只是坐在一个浏览器中，依靠F5键。尝试一下:这个东西听起来很抽象，但是它是经验和可测试的。
- 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).
- 如果连接因任何原因中断，init方法将不再被调用(因为servlet将不会被从服务中取出)。不要试图通过在doGet或doPost中设置try-catch来解决这个问题，因为这样你就会陷入地狱(类似于不被要求编写应用服务器)。
- 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.
- 与人们的想法相反，您不会遇到事务问题，因为事务开始与线程关联，而不仅仅是连接。我可能错了，但既然这是一个糟糕的解决方案，那就别多虑了。
Why Connection Pool
为什么连接池
Connection pools give you a whole bunch of advantages, but most of all they solve the problems of
连接池为您提供了大量的优势，但大多数都解决了这些问题。
- Making a real database connection is costly. The connection pool always has a few extra connections around and gives you one of those.
- 建立一个真实的数据库连接是非常昂贵的。连接池总是有一些额外的连接，并为您提供其中的一个。
- If the connections fail, the connection pool knows how to open a new one
- 如果连接失败，连接池知道如何打开一个新的连接
- 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.
- 非常重要:每个线程都有自己的连接。这意味着线程处理应该在的地方:在DB级别。DBs非常高效，可以轻松地处理并发请求。
- Other stuff (like centralizing location of JDBC connect strings, etc.), but there are millions of articles, books, etc. on this
- 其他的东西(比如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 finally blocks 100x more than catch blocks. Truer words never spoken...
在您的服务委托(doPost, doGet, doDisco，无论什么)中启动的调用堆栈中，您应该得到一个连接，然后您应该做正确的事情，并在最后一个块中返回它。我应该提一下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。你的战争应该是可移动性的，“只需部署”。我认为这基本上是错误的。如果您使用容器提供的连接池，您的应用程序将可以部署到跨多台机器的容器以及Java EE规范提供的所有花哨的东西上。是的，必须编写特定于容器的部署描述符，但这是EE方式，mon。
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)。这听起来完全牵强和荒谬，所以这可能是真的。当这样的事情发生的时候，把你应该做的一切都扔进垃圾箱，做你能做的任何事情。这就是我们有时得到的报酬:
#3
4
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中获得连接，运行查询，然后在最后一个块中关闭连接。(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可以管理连接超时并从中恢复。如果你的数据库在任何一段时间内都宕机了，那么你将不得不重新启动你的应用程序。
#4
2
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.
一旦这种情况消失了，就像John建议的那样，在需要的时候保持连接的开放时间。
#5
2
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连接对象的池。将这些对象存储在某种列表中，例如ArrayList。每个对象都有一个私有的“ishire”布尔值，这是上一次使用它的时间和一个连接。当您需要连接时，您从池中请求一个连接。池将为您提供第一个可用的连接，检查is租用变量，或者创建一个新的连接并将其添加到池中。一定要设置时间戳。完成连接后，只需将其返回到池中，该池将设置为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.
为了避免连接经常与数据库连接在一起，您可以创建一个worker线程，该线程偶尔会通过池查看最后一次使用连接的时间。如果它足够长，它可以关闭该连接并将其从池中删除。
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.
使用这种方法的好处是，您没有等待连接对象连接到数据库的长时间等待。您已经建立的连接可以被重用。您可以根据您认为应用程序将会有多忙来设置连接的数量。
#6
1
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/doPost methods.
只要需要，就应该保持数据库连接的打开时间，这取决于您正在做的操作，可能在doGet/doPost方法的范围内。
#7
1
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,你可以看看帮助您管理连接,PreparedStatement,等。除非你有非常紧密的“lightweightness”需求,使用Spring JDBC支持,例如,将简化代码很多,你不是*使用春天的其他部分。
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
#8
1
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等)中获得来自数据源的连接。
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和许多其他连接池可以完成您所需要的工作。在池连接时，您可能想要池化语句和preparedstatement;另外，如果您像您所指出的那样是一个阅读重环境，您可能想要使用像ehcache这样的东西来缓存一些结果。
BR,
~A
BR,~
#9
0
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()中打开它使它对所有请求都可用，当有并发请求时会发生什么?
#1
14
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那样)，那么很好，否则，只使用容器提供的任何东西。
#2
22
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:
我知道这一点，因为我曾经认为这是一个好主意。问题是双重的:
- 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.
- 所有线程(每个线程有一个servlet请求)都将共享相同的连接。因此，每次将处理一个请求。这是非常缓慢的，即使你只是坐在一个浏览器中，依靠F5键。尝试一下:这个东西听起来很抽象，但是它是经验和可测试的。
- 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).
- 如果连接因任何原因中断，init方法将不再被调用(因为servlet将不会被从服务中取出)。不要试图通过在doGet或doPost中设置try-catch来解决这个问题，因为这样你就会陷入地狱(类似于不被要求编写应用服务器)。
- 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.
- 与人们的想法相反，您不会遇到事务问题，因为事务开始与线程关联，而不仅仅是连接。我可能错了，但既然这是一个糟糕的解决方案，那就别多虑了。
Why Connection Pool
为什么连接池
Connection pools give you a whole bunch of advantages, but most of all they solve the problems of
连接池为您提供了大量的优势，但大多数都解决了这些问题。
- Making a real database connection is costly. The connection pool always has a few extra connections around and gives you one of those.
- 建立一个真实的数据库连接是非常昂贵的。连接池总是有一些额外的连接，并为您提供其中的一个。
- If the connections fail, the connection pool knows how to open a new one
- 如果连接失败，连接池知道如何打开一个新的连接
- 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.
- 非常重要:每个线程都有自己的连接。这意味着线程处理应该在的地方:在DB级别。DBs非常高效，可以轻松地处理并发请求。
- Other stuff (like centralizing location of JDBC connect strings, etc.), but there are millions of articles, books, etc. on this
- 其他的东西(比如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 finally blocks 100x more than catch blocks. Truer words never spoken...
在您的服务委托(doPost, doGet, doDisco，无论什么)中启动的调用堆栈中，您应该得到一个连接，然后您应该做正确的事情，并在最后一个块中返回它。我应该提一下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。你的战争应该是可移动性的，“只需部署”。我认为这基本上是错误的。如果您使用容器提供的连接池，您的应用程序将可以部署到跨多台机器的容器以及Java EE规范提供的所有花哨的东西上。是的，必须编写特定于容器的部署描述符，但这是EE方式，mon。
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)。这听起来完全牵强和荒谬，所以这可能是真的。当这样的事情发生的时候，把你应该做的一切都扔进垃圾箱，做你能做的任何事情。这就是我们有时得到的报酬:
#3
4
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中获得连接，运行查询，然后在最后一个块中关闭连接。(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可以管理连接超时并从中恢复。如果你的数据库在任何一段时间内都宕机了，那么你将不得不重新启动你的应用程序。
#4
2
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.
一旦这种情况消失了，就像John建议的那样，在需要的时候保持连接的开放时间。
#5
2
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连接对象的池。将这些对象存储在某种列表中，例如ArrayList。每个对象都有一个私有的“ishire”布尔值，这是上一次使用它的时间和一个连接。当您需要连接时，您从池中请求一个连接。池将为您提供第一个可用的连接，检查is租用变量，或者创建一个新的连接并将其添加到池中。一定要设置时间戳。完成连接后，只需将其返回到池中，该池将设置为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.
为了避免连接经常与数据库连接在一起，您可以创建一个worker线程，该线程偶尔会通过池查看最后一次使用连接的时间。如果它足够长，它可以关闭该连接并将其从池中删除。
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.
使用这种方法的好处是，您没有等待连接对象连接到数据库的长时间等待。您已经建立的连接可以被重用。您可以根据您认为应用程序将会有多忙来设置连接的数量。
#6
1
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/doPost methods.
只要需要，就应该保持数据库连接的打开时间，这取决于您正在做的操作，可能在doGet/doPost方法的范围内。
#7
1
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,你可以看看帮助您管理连接,PreparedStatement,等。除非你有非常紧密的“lightweightness”需求,使用Spring JDBC支持,例如,将简化代码很多,你不是*使用春天的其他部分。
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
#8
1
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等)中获得来自数据源的连接。
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和许多其他连接池可以完成您所需要的工作。在池连接时，您可能想要池化语句和preparedstatement;另外，如果您像您所指出的那样是一个阅读重环境，您可能想要使用像ehcache这样的东西来缓存一些结果。
BR,
~A
BR,~
#9
0
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()中打开它使它对所有请求都可用，当有并发请求时会发生什么?