用Spring和DBCP处理JDBC连接的正确方法是什么? [英] What's the proper way to handle JDBC connections with Spring and DBCP?

问题描述

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

当我第一次尝试同时建立约10-15个连接时，它曾经挂起，我不得不重新启动服务器(对于开发人员，我正在使用Tomcat，但最终我将不得不在Weblogic上进行部署.) /p>

这些是我的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 -->

这就是我的使用方式:

// 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(...);

经过一番阅读后，我发现

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

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

有一些长期运行的查询，但是无论查询的复杂程度如何，都会引发异常.

然后，我设置了maxActive和maxIdle，这样它就不会首先抛出异常. maxActive和maxIdle的默认值为8(我不明白为什么)；如果我将它们设置为-1，则不会再抛出任何异常，并且所有似乎都可以正常工作.

考虑到该应用程序应支持大量并发请求，是否可以将这些设置保留为无限?考虑到我收到的错误，Spring会实际管理我的连接吗?考虑到它已经死了，我应该切换到 C3P0 吗?

解决方案

您已经发现，默认的dbcp连接池为8个连接，因此，如果您要同时运行9个查询，则其中之一将被阻止.我建议您连接到数据库并运行exec sp_who2，它将显示连接状态和活动状态，以及是否阻止了任何查询.然后，您可以确认问题是在数据库上还是在代码中.

只要您使用的是Spring的JdbcTemplate系列对象，就可以按期望的方式管理连接，如果要使用原始DataSource，请确保使用Apache Commons DBCP to handle connection pooling and the data source.

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).

These are my Spring bean definitions:

<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, maxActive and maxIdle properties 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.

<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:

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

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 maxActive and maxIdle (I don't understand why); if I set them to -1 there are no more exceptions thrown and everything seems to work fine.

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 C3P0 considering it's kinda dead?

解决方案

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_who2 which 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.

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 DataSourceUtils to obtain a Connection.

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和DBCP处理JDBC连接的正确方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！