Java 使用 Spring 和 DBCP 处理 JDBC 连接的正确方法是什么？

What's the proper way to handle JDBC connections with Spring and DBCP?

提问by Alex Ciminian

I'm using the Spring MVC to build a thin layer on top of a SQL Server database. When I began testing, it seems that it doesn't handle stress very well :). I'm using Apache Commons DBCPto handle connection pooling and the data source.

我正在使用 Spring MVC 在 SQL Server 数据库之上构建一个薄层。当我开始测试时，它似乎不能很好地处理压力:)。我使用Apache Commons DBCP来处理连接池和数据源。

When I first attempted ~10-15 simultaneous connections, it used to hang and I'd have to restart the server (for dev I'm using Tomcat, but I'm gonna have to deploy on Weblogic eventually).

当我第一次尝试大约 10-15 个同时连接时，它曾经挂起，我必须重新启动服务器（对于开发人员，我使用的是 Tomcat，但最终我将不得不部署在 Weblogic 上）。

These are my Spring bean definitions:

这些是我的 Spring bean 定义：

<bean id="dataSource" destroy-method="close"
      class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="url" value="[...]"/>
    <property name="username" value="[...]" />
    <property name="password" value="[...]" />
</bean>

<bean id="partnerDAO" class="com.hp.gpl.JdbcPartnerDAO">
    <constructor-arg ref="dataSource"/>
</bean>

<!-- + other beans -->

And this is how I use them:

这就是我使用它们的方式：

// in the DAO
public JdbcPartnerDAO(DataSource dataSource) {
    jdbcTemplate = new JdbcTemplate(dataSource);
}

// in the controller
@Autowired
private PartnerDAO partnerDAO;

// in the controller method
Collection<Partner> partners = partnerDAO.getPartners(...);

After reading around a little bit, I found the maxWait, maxActiveand maxIdleproperties for the BasicDataSource(from GenericObjectPool). Here comes the problem. I'm not sure how I should set them, performance-wise. From what I know, Spring should be managing my connections so I shouldn't have to worry about releasing them.

围绕阅读一点点后，我发现了maxWait，maxActive并且maxIdle为性能的BasicDataSource（从GenericObjectPool）。问题来了。我不确定我应该如何设置它们，性能方面。据我所知，Spring 应该管理我的连接，所以我不必担心释放它们。

<bean id="dataSource" destroy-method="close"
      class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="url" value="[...]"/>
    <property name="username" value="[...]" />
    <property name="password" value="[...]" />
    <property name="maxWait" value="30" />
    <property name="maxIdle" value="-1" />
    <property name="maxActive" value="-1" />
</bean>

First, I set maxWait, so that it wouldn't hang and instead throw an exception when no connection was available from the pool. The exception message was:

首先，我设置了maxWait，这样它就不会挂起，而是在池中没有可用连接时抛出异常。异常消息是：

Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object

无法获得 JDBC 连接；嵌套异常是 org.apache.commons.dbcp.SQLNestedException：无法获得连接，池错误超时等待空闲对象

There are some long-running queries, but the exception was thrown regardless of the query complexity.

有一些长时间运行的查询，但无论查询复杂度如何，都会抛出异常。

Then, I set maxActive and maxIdle so that it wouldn't throw the exceptions in the first place. The default values are 8 for maxActiveand maxIdle(I don't understand why); if I set them to -1 there are no more exceptions thrown and everything seemsto work fine.

然后，我设置 maxActive 和 maxIdle 以便它不会首先抛出异常。默认值是8maxActive和maxIdle（我不明白为什么）; 如果我将它们设置为 -1，则不会再抛出异常，并且一切似乎都正常。

Considering that this app should support a large number of concurrent requests is it ok to leave these settings to infinite? Will Spring actually manage my connections, considering the errors I was receiving? Should I switch to C3P0considering it's kinda dead?

考虑到这个应用程序应该支持大量并发请求，可以将这些设置保留为无限吗？考虑到我收到的错误，Spring 真的会管理我的连接吗？考虑到它有点死，我应该切换到C3P0吗？

采纳答案by Jon Freedman

As you already found out, the default dbcp connection pool is 8 connections, so if you want to run 9 simultaneous queries one of them will be blocked. I suggest you connect to your database and run exec sp_who2which will show you what is connected, and active, and whether any queries are being blocked. You can then confirm whether the issue is on the db or in your code.

正如您已经发现的那样，默认的 dbcp 连接池是 8 个连接，因此如果您想同时运行 9 个查询，其中一个将被阻止。我建议您连接到您的数据库并运行exec sp_who2，这将显示已连接和活动的内容，以及是否有任何查询被阻止。然后您可以确认问题是在数据库上还是在您的代码中。

As long as you are using Spring's JdbcTemplate family of objects your connections will be managed as you expect, and if you want to use a raw DataSource make sure you use DataSourceUtilsto obtain a Connection.

只要您使用 Spring 的 JdbcTemplate 系列对象，您的连接就会按照您的预期进行管理，如果您想使用原始数据源，请确保使用DataSourceUtils来获取连接。

One other suggestion - prior to Spring 3, don't ever using JdbcTemplate, stick to SimpleJdbcTemplate, you can still access the same methods using SimpleJdbcTemplate.getJdbcOperations(), but you should find yourself writing much nicer code using generics, and remove the need to ever create JdbcTemplate/NamedParameterJdbcTemplate instances.

另一个建议 - 在 Spring 3 之前，永远不要使用 JdbcTemplate，坚持使用 SimpleJdbcTemplate，您仍然可以使用SimpleJdbcTemplate.getJdbcOperations()访问相同的方法，但是您应该发现自己使用泛型编写了更好的代码，并且不再需要创建 JdbcTemplate/NamedParameterJdbcTemplate 实例。

回答by Kingsly

Let's change the perspective.

让我们换个角度。

but the exception was thrown regardless of the query complexity

但无论查询复杂度如何，都会抛出异常

It could be because the table or the records in the table, which you are querying against has been locked (by some other active transaction) and hence it times out.

这可能是因为您要查询的表或表中的记录已被锁定（被其他活动事务锁定），因此超时。

Try running the same query from SQLServer Client and if it takes a long time, then you can be sure that it is the table or record lock that is causing this.

尝试从 SQLServer 客户端运行相同的查询，如果它需要很长时间，那么您可以确定是表或记录锁定导致了这种情况。

回答by stoweesh

DBCP maxWaitparameter should be defined in milliseconds. 30ms is very low value, consider increasing it to 30000ms and try again.

DBCPmaxWait参数应以毫秒为单位定义。30ms 是非常低的值，请考虑将其增加到30000ms 并重试。