oracle 在 ConnectionPooling 中移交给 WebApp 之前验证连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1436830/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Validating Connection Before Handing over to WebApp in ConnectionPooling
提问by Priyank
I have connection pooling implemented in spring using Oracle Data Source. Currently we are facing an issue where connections are becoming invalid after a period of time. (May be Oracle is dropping those idle connections after a while). Here are my questions:
我在春季使用 Oracle 数据源实现了连接池。目前我们面临着连接在一段时间后失效的问题。（可能是 Oracle 在一段时间后丢弃这些空闲连接）。以下是我的问题：
- Can Oracle database be configured to drop idle connections automatically after a specific period of time. Since we expect those connections to lie idle for a while; if there is any such configuration; it may be happening.
In our connection pooling properties in spring we didn't have "validateConnection" property. I understand that it validates the connection before handing it over to web application? But does that mean that if a connection passes validateConnection test then it'll always connect to database correctly. I ask this, as I read following problem here: http://forum.springsource.org/showthread.php?t=69759
If suppose validateConnection doesn't do the whole 9 yards of ensuring that connection is valid, is there any other option like "testBeforBorrow" in DBCP , which runs a test query to ensure that connection is active before handing it over to webapp?
- Oracle 数据库是否可以配置为在特定时间段后自动删除空闲连接。因为我们预计这些连接会闲置一段时间；如果有任何这样的配置；它可能正在发生。
在我们春季的连接池属性中，我们没有“validateConnection”属性。我知道它会在将连接移交给 Web 应用程序之前验证连接？但这是否意味着如果连接通过了 validateConnection 测试，那么它将始终正确连接到数据库。我问这个，因为我在这里读到以下问题：http: //forum.springsource.org/showthread.php?t=69759
如果假设 validateConnection 没有完成确保连接有效的整个 9 码，那么 DBCP 中是否还有其他选项，例如“testBeforBorrow”，它运行测试查询以确保连接在将其移交给 webapp 之前处于活动状态？
I'll be grateful if you could provide answers to one ore more queries listed above.
如果您能为上面列出的一个或多个查询提供答案，我将不胜感激。
Cheers
干杯
回答by skaffman
You don't say what application server you are using, or how you are configuring the datasource, so I can't give you specific advice.
你没有说你使用的是什么应用服务器，或者你是如何配置数据源的，所以我不能给你具体的建议。
Connection validation often sounds like a good idea, but you have to be careful with it. For example, we once used it in our JBoss app servers to validate connections in the pool before handing them to the application. This Oracle-proprietary mechanism used the ping()method on the Oracle JDBC driver, which checks that the connection is still alive. It worked fine, but it turns out that ping()executes "select 'x' from dual' on the server, which is a surprisingly expensive query when it's run dozens of times per second.
连接验证通常听起来是个好主意，但您必须小心使用。例如，我们曾经在 JBoss 应用服务器中使用它来验证池中的连接，然后再将它们交给应用程序。这种 Oracle 专有机制使用了ping()Oracle JDBC 驱动程序上的方法，该方法检查连接是否仍然有效。它工作得很好，但结果是在服务器上ping()执行 " select 'x' from dual'，当它每秒运行几十次时，这是一个非常昂贵的查询。
So the moral is, if you have a high-traffic server, be verycareful with connection validation, it can actually bring your database server to its knees.
所以道德是，如果你有一个高流量的服务器，在连接验证时要非常小心，它实际上会让你的数据库服务器瘫痪。
As for DBCP, that has the ability to validate connections as their borrowed from the pool, as well as returned to the pool, and you can tell it what SQL to send to the database to perform this validation. However, if you're not using DBCP for your connection pooling, then that's not much use to you. C3PO does something similar.
至于 DBCP，它能够验证从池中借来的连接以及返回到池中的连接，并且您可以告诉它向数据库发送什么 SQL 以执行此验证。但是，如果您没有将 DBCP 用于连接池，那么这对您没有多大用处。C3PO 做了类似的事情。
If you're using an app server's data source mechanism, then you have to find out if you can configure that to validate connections, and that's specific to your server.
如果您使用的是应用服务器的数据源机制，那么您必须确定是否可以配置它来验证连接，这特定于您的服务器。
One last thing: Spring isn't actually involved here. Spring just uses the DataSourcethat you give it, it's up to the DataSourceimplementation to perform connection validation.
最后一件事：Spring 实际上并不涉及这里。Spring 仅使用DataSource您提供的 ，由DataSource实现来执行连接验证。
回答by Priyank
Configuration of data source "was" as follows:
数据源“was”的配置如下：
<bean id="datasource2"
class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName">
<value>org.apache.commons.dbcp.BasicDataSource</value>
</property>
<property name="url">
<value>ORACLE URL</value>
</property>
<property name="username">
<value>user id</value>
</property>
<property name="password">
<value>user password</value>
</property>
<property name="initialSize" value="5"/>
<property name="maxActive" value="20"/>
</bean>
have changed it to:
已将其更改为：
<bean id="connectionPool1" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close">
<property name="connectionCachingEnabled" value="true" />
<property name="URL">
<value>ORACLE URL</value>
</property>
<property name="user">
<value>user id</value>
</property>
<property name="password">
<value>user password</value>
</property>
<property name="connectionCacheProperties">
<value>
MinLimit:1
MaxLimit:5
InitialLimit:1
ConnectionWaitTimeout:120
InactivityTimeout:180
ValidateConnection:true
</value>
</property>
</bean>
