使用Tomcat 7的新JDBC连接池配置高并发jdbc连接池的使用说明(tomcat JDBC Connection) 09:38:50&&&来源：&&&评论： 点击：
使用Tomcat 7的新JDBC连接池配置高并发jdbc连接池的使用说明(tomcat JDBC Connection)
Tomcat 7 的JDBC连接池实现类为org.apache.tomcat.jdbc.pool，作为替换commons-dbcp的方案。
替换commons-dbcp的原因如下：
commons-dbcp是单线程的，为了线程安全，就要琐住整个连接池，查询验证阶段也要琐住整个连接池。
commons-dbcp速度慢，性能差，多CPU的环境下单线程运行，不支持高并发，在JAVA 6中也不能解决速度和并发的问题。
commons-dbcp实现复杂，超过60个类。tomcat-jdbc-pool核心只有8个类，修改更加简单，只需运行连接池本身，易测试。
commons-dbcp应用静态接口，就意味着并不能用JDK 1.6编译它，在JDK 1.6/1.7中运行时，即使驱动程序支持，所有没有实现的方法也都会抛出NoSuchMethodException异常。
commons-dbcp几乎停滞，很少更新。
作为一个连接池的简单实现不值得重写超过60个类。
Tomcat jdbc连接池实现了一个commons-dbcp没有的公平算法，并且比commons-dbcp性能更好。
Tomcat jdbc连接池实现了异步获取连接，也不需增加额外的线程。
Tomcat jdbc连接池是一个Tomcat的模块，依赖于Tomcat JULI(Tomcat日志框架)
使用javax.sql.PooledConnection接口获取连接。
饥饿算法。如果连接池空了，同时一个线程要获得连接，当一个连接返回到连接池，连接池会将正确的线程唤醒。
除了commons-dbcp连接池，还有其它可以选择的方案，如c3p0,bonecp等,与这些连接池实现相比，Tomcat jdbc pool更突出的功能体现在：
支持多核系统，提供更好的高并发性能。
接口动态实现，运行时环境支持java.sql和javax.sql接口，可以使用低版本JDK编译。
无需每次使用连接时都验证连接，可以在获取或返回连接时验证，不用比设置的间隔时间更频繁。
当数据库连接建立时，一个可设置的查询将运行一次。这对保持连接建立整个时间中的会话十分有用。
可以自定义拦截器增强功能。可定义拦截器来收集查询统计，缓存会话状态，重新连接，重新查询，缓存查询结果等。
极其简单，由于非常简单的实现，源程序行数和文件数很少，相比c3p0的200多个源程序文件，Tomcat jdbc只有8个核心源文件，关于连接池的部分只有4个文件。这样更容易追溯和修改Bug。减少复杂性就是起初开发的一个焦点。
异步获取连接，可将连接请求形成队列，系统返回Future&Connection&
更好的空闲连接处理，应用更优化的算法调整连接池大小和连接的释放。
用户来决定当连接池满了在什么时刻释放连接，或者直接设置一个超时的阀值。
释放连接定时器将会在查询时重置。允许一个使用很长时间的连接不超时。这个功能由ResetAbandonedTimer完成。
在连接一定长时间后关闭连接。时间与返回连接池的时间相似。
当连接要被释放时，将得到JMX通知并且记录整个日志。这和removeAbandonedTimeout相似，但是只输出信息，不做任何操作。使用suspectTimeout属性完成设置。
可以从java.sql.Driver，javax.sql.DataSource或者javax.sql.XADataSource中取得连接，使用dataSource和dataSourceJNDI属性完成。
支持XA连接。
作为数据源在Tomcat中使用
&Resource name=&jdbc/TestDB&
& && &&&&&& auth=&Container&
&&&&&& type=&javax.sql.DataSource&
& && &&&&&& factory=&org.apache.tomcat.jdbc.pool.DataSourceFactory&
&&& &&&&&& testWhileIdle=&true&
& && &&&&&& testOnBorrow=&true&
&&& &&&&&& testOnReturn=&false&
&&& &&&&&& validationQuery=&SELECT 1&
&&&&&& validationInterval=&30000&
&&& &&&&&& timeBetweenEvictionRunsMillis=&30000&
&&&&&& maxActive=&100&
& && &&&&&& minIdle=&10&
&&& &&&&&& maxWait=&10000&
&&& &&&&&& initialSize=&10&
& && &&&&&& removeAbandonedTimeout=&60&
&&& &&&&&& removeAbandoned=&true&
&&&&&& logAbandoned=&true&
&&& &&&&&& minEvictableIdleTimeMillis=&30000&
& && &&&&&& jmxEnabled=&true&
&&& &&&&&& jdbcInterceptors=
&&org.apache.tomcat.jdbc.pool.interceptor.ConnectionSorg.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer&
& && &&&&&& username=&root&
& && &&&&&& password=&password&
&&&&&& driverClassName=&com.mysql.jdbc.Driver&
&&& &&&&&& url=&jdbc:mysql://localhost:3306/mysql&/&
&resource name="jdbc/TestDB"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
testwhileidle="true"
testonborrow="true"
testonreturn="false"
validationquery="SELECT 1"
validationinterval="30000"
timebetweenevictionrunsmillis="30000"
maxactive="100" minidle="10"
maxwait="10000"
initialsize="10"
removeabandonedtimeout="60"
removeabandoned="true"
logabandoned="true"
minevictableidletimemillis="30000"
jmxenabled="true"
jdbcinterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionS
org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
username="root"
password="password"
driverclassname="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/mysql"&
&/resource&
initialSize=10 设置连接池建立时连接的数目
&当连接池定义在GlobalNamingResources中，连接池在Tomcat启动时创键
&当连接池定义在Context中，连接池在第一次查找JNDI时创建
maxActive=100 连接数据库的最大连接数。这个属性用来限制连接池中能够打开连接的数量，可以方便数据库做连接容量规划。
minIdle=10& 连接池中存在的最小连接数目。连接池中连接数目可以变很少，如果使用了maxAge属性，有些空闲的连接会被关闭因为离它最近一次连接的时间过去太久了。但是，我们看到的打开的连接不会少于minIdle。
maxIdle属性有一点麻烦。它的不同的行为取决于是否使用了pool sweeper。pool sweeper是一个可以在连接池正在使用的时候测试空闲连接和重置连接池大小的后台线程。还负责检测连接泄露。
sweeper每timeBetweenEvictionRunsMillis milliseconds运行一次。
maxIdle定义如下
Pool sweeper关闭，如果空闲连接池大于maxIdle，返回的连接将被关闭。
Pool sweeper开启，空闲的连接数可以超过maxIdle，但如果连接空闲的时间已经超过minEvictableIdleTimeMillis，能缩小到minIdle。听起来很奇怪连接池为什么不关闭连接当空闲连接数量大于maxIdle。想想下面的情况：&&&
100个线程处理100个并发请求&&&
在一个请求中每个线程请求一个连接3次&
在这种场景下，如果我们设置maxIdle=50，那么我们会关闭和打开50*3的连接数。这样增加了数据库的负重并且减慢了应用的速度。当达到连接高峰时，我们希望能够充分利用连接池中的所有连接。因此，我们强烈希望打开 pool sweeper 。我们将在下一个部分探讨具体的事项。我们在这里额外说明maxAge这个属性。maxAge定义连接能够打开或者存在的时间，单位为毫秒。当一个连接返回到了连接池，如果这个连接已经使用过，并且距离它第一次被使用的时间大于maxAge时，这个连接会被关闭。
正如我们所看到的 isPoolSweeper算法实现，sweeper 将会被打开，当以下任一条件满足时
&timeBetweenEvictionRunsMillis&0 AND removeAbandoned=true AND removeAbandonedTimeout&0
&timeBetweenEvictionRunsMillis&0 AND suspectTimeout&0
&timeBetweenEvictionRunsMillis&0 AND testWhileIdle=true AND validationQuery!=null
&& & & & & && As of version 1.0.9 the following condition has been added
timeBetweenEvictionRunsMillis&0 AND minEvictableIdleTimeMillis&0
&& &&&&&&&&& (timer && getMinEvictableIdleTimeMillis()&0);
因此设置最理想的连接池，我们最好修改我们的配置满足这些条件
有效的连接
数据库连接池提出了一个挑战，因为连接池中的连接会过时。这是常有的事，要么数据库，或者可能是连接池和数据库中的一个设备，连接超时。唯一确定会 话连接是活跃的真正办法是使连接在服务器和数据库做一个来回访问。在Java 6中，JDBC API处理验证连接是否是有效的方法是通过提供isValid变量来调用java.sql.Connection接口。在此之前，连接池不得不采用执行一个查询的方法，比如在MySQL上执行SELECT 1.数据库分析这句查询很简单，不需要任何的磁盘访问。isValid被计划实施，但 Apache Tomcat 6的连接池，也必须保存对Java 5的兼容性。
校验查询会有一些挑战
如果它们频繁使用，会降低系统的性能
如果使用的间隔太久，会导致连接失效
如果应用调用setTransactionIsolation并设置autoCommit=false，如果应用再次调用setTransactionIsolation，会产生一个SQLException异常，因为校验查询可能在数据库中已经产生了一个新的transaction。
在这个配置中，java代码每次调用 Connection con = dataSource.getConnection()时都会执行一次SELECT 1查询。
这样保证了在连接提交给应用之前都已经测试过了。但是，对于在短时间内频繁使用连接的应用，会对性能有严重的影响。这有两个其他的配置选项：
testWhileIdle
testOnReturn
当在错误的时间对连接做测试，它们也不是真正的很有帮助。
对于很多应用来说，没有校验不是一个真正的困难。一些应用可以绕过校验通过设置minIdle=0和给minEvictableIdleTimeMillis一个很小的值，所以如果连接空闲了足够长的时间会让数据库会话超时，在此之前连接池将会移除这些空闲太久的连接。
最好的解决办法就是测试那些有一段时间没被测试过的连接。
initSQL会被存在的每一条连接执行。
连接池泄露和长时间运行的查询
连接池包含一些诊断操作。jdbc-pool和Common DBCP都能够检测和减轻没有返回连接池中的连接。这里演示是被称为抛出内存泄露的连接。
removeAbandoned-如果我们想检测内存泄露的连接，可以设置为true
removeAbandonedTimeout-调用dataSource.getConnection开始到丢弃检测到泄露连接的时间（seconds）
logAbandoned-如果想用log记录丢弃的连接，可以设置为true。当设置为true时，调用dataSource.getConnection 时会记录一个堆栈追踪，并且被打印出来当连接没有返回的时候。
但我们想要这种类型的诊断，当然有可以使用的例子。也可以运行批处理作业一次执行一个连接几分钟。我们该如何处理这些问题？
两个额外的选项已经被加入来支持这些工作
abandonWhenPercentageFull-一条连接必须满足临界值 removeAbandonedTimeout和打开连接的数量必须超过这个百分比。
&使用这个属性可能会在一次错误判断中产生在其他地方已经被认为丢弃的连接。设置这个值为100时意味着连接数除非到了maxActive限制时，是不会被考虑丢弃的。这给连接池增加了一些灵活性，但是不会让批处理作业使用单独连接5分钟。在这种情况，我们想确定当我们检测到连接仍然被使用时，我们重置超时计时器，因此，连接不会被考虑丢弃。我们通过插入一个拦截器实现。
拦截器在org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer中被指定完全限定名称，或者在org.apache.tomcat.jdbc.pool.interceptor包中使用短类名
&每次准备语句或者执行一次查询，连接池中的计时器会被重置放弃计时器。因为如此，在5分钟的批处理作业中执行多次查询和更新，都不会超时。
这是你当然想知道的情形，但你不会想去kill或者回收连接，因为你不会知道会对你的系统产生什么影响。
JDBC 拦截器
JDBC 拦截器创建是为了实现灵活性。javax.sql.PooledConnection 从底层驱动封装了java.sql.Connection/javax.sql.XAConnection或者数据源本身就是一个拦截器。拦截器以java.lang.reflect.InvocationHandler接口为基础。拦截器是一个继承自 org.apache.tomcat.pool.jdbc.JdbcInterceptor的类。
拦截器可以使用一个短小的名称，比如ConnectionState，如果这个拦截器定义在org.apache.tomcat.jdbc.pool.interceptor 包中。&
否则，必须使用一个完全限定名称。
拦截器定义在以；分割的字符串中。拦截器可以在括号内定义0个或多个参数。参数是以逗号分割的简单键值对。
java.sql.Connection接口有如下属性
&& &&& &autoCommit
&& &&& &readOnly
&& &&& &transactionIsolation
&& &&& &catalog
这些属性的默认值可以使用如下的内容为连接池配置
&&&&&&& defaultAutoCommit
&& &&& &defaultReadOnly
&& &&& &defaultTransactionIsolation
&& &&& &defaultCatalog
如果设置了这些属性，当建立连接到数据库时配置这个连接。如果没有配置 ConnectionState拦截器，在建立连接时设置这些属性会是一次性操作。如果配置了ConnectionState拦截器，每次从连接池取出的连接会将被重置为期望的状态。
其中有些方法在执行查询时会导致往返数据库。比如，调用 Connection.getTransactionIsolation()会导致驱动查询当前会话的事务隔离级别。这种往返会导致严重的性能问题并影响应用在频繁的使用连接执行非常短和快的操作的时候。 ConnectionState 拦截器可以缓存这些操作的值并调用方法查询它们从而避免往返数据库。
Statement Finalizer
java代码在使用java.sql对象后需要清除和释放使用过的资源。
Description
factory is required, and the value should be org.apache.tomcat.jdbc.pool.DataSourceFactory
Type should always be javax.sql.DataSource or javax.sql.XADataSource
Depending on the type a org.apache.tomcat.jdbc.pool.DataSource or a org.apache.tomcat.jdbc.pool.XADataSource will be created.
These attributes are shared between commons-dbcp and tomcat-jdbc-pool, in some cases default values are different.
Description
defaultAutoCommit
(boolean) The default auto-commit state of connections created by this pool. If not set, default is JDBC driver default (If not set then the setAutoCommit method will not be called.)
defaultReadOnly
(boolean) The default read-only state of connections created by this pool. If not set then the setReadOnly method will not be called. (Some drivers don't support read only mode, ex: Informix)
defaultTransactionIsolation
(String) The default TransactionIsolation state of connections created by this pool. One of the following: (see javadoc )
READ_COMMITTED
READ_UNCOMMITTED
REPEATABLE_READ
SERIALIZABLE
If not set, the method will not be called and it defaults to the JDBC driver.
defaultCatalog
(String) The default catalog of connections created by this pool.
driverClassName
(String) The fully qualified Java class name of the JDBC driver to be used. The driver has to be accessible from the same classloader as tomcat-jdbc.jar
(String) The connection username to be passed to our JDBC driver to establish a connection. Note that method DataSource.getConnection(username,password) by default will not use credentials passed into the method, but will use the ones configured here. See alternateUsernameAllowed property for more details.
(String) The connection password to be passed to our JDBC driver to establish a connection. Note that method DataSource.getConnection(username,password) by default will not use credentials passed into the method, but will use the ones configured here. See alternateUsernameAllowed property for more details.
(int) The maximum number of active connections that can be allocated from this pool at the same time. The default value is 100
(int) The maximum number of connections that should be kept in the pool at all times. Default value is maxActive:100 Idle connections are checked periodically (if enabled) and connections that been idle for longer than minEvictableIdleTimeMillis will be released. (also see testWhileIdle)
(int) The minimum number of established connections that should be kept in the pool at all times. The connection pool can shrink below this number if validation queries fail. Default value is derived from initialSize:10 (also see testWhileIdle)
initialSize
(int)The initial number of connections that are created when the pool is started. Default value is 10
(int) The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception. Default value is 30000 (30 seconds)
testOnBorrow
(boolean) The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another. NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string. In order to have a more efficient validation, see validationInterval. Default value is false
testOnReturn
(boolean) The indication of whether objects will be validated before being returned to the pool. NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string. The default value is false.
testWhileIdle
(boolean) The indication of whether objects will be validated by the idle object evictor (if any). If an object fails to validate, it will be dropped from the pool. NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string. The default value is false and this property has to be set in order for the pool cleaner/test thread is to run (also see timeBetweenEvictionRunsMillis)
validationQuery
(String) The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query does not have to return any data, it just can't throw a SQLException. The default value is null. Example values are SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server)
validationQueryTimeout
(int) The timeout in seconds before a connection validation queries fail. This works by calling java.sql.Statement.setQueryTimeout(seconds) on the statement that executes the validationQuery. The pool itself doesn't timeout the query, it is still up to the JDBC driver to enforce query timeouts. A value less than or equal to zero will disable this feature. The default value is -1.
validatorClassName
(String) The name of a class which implements the org.apache.tomcat.jdbc.pool.Validator interface and provides a no-arg constructor (may be implicit). If specified, the class will be used to create a Validator instance which is then used instead of any validation query to validate connections. The default value is null. An example value is com.mycompany.project.SimpleValidator.
timeBetweenEvictionRunsMillis
(int) The number of milliseconds to sleep between runs of the idle connection validation/cleaner thread. This value should not be set under 1 second. It dictates how often we check for idle, abandoned connections, and how often we validate idle connections. The default value is 5000 (5 seconds).
numTestsPerEvictionRun
(int) Property not used in tomcat-jdbc-pool.
minEvictableIdleTimeMillis
(int) The minimum amount of time an object may sit idle in the pool before it is eligible for eviction. The default value is 60000 (60 seconds).
accessToUnderlyingConnectionAllowed
(boolean) Property not used. Access can be achieved by calling unwrap on the pooled connection. see javax.sql.DataSource interface, or call getConnection through reflection or cast the object as javax.sql.PooledConnection
removeAbandoned
(boolean) Flag to remove abandoned connections if they exceed the removeAbandonedTimeout. If set to true a connection is considered abandoned and eligible for removal if it has been in use longer than the removeAbandonedTimeout Setting this to true can recover db connections from applications that fail to close a connection. See also logAbandoned The default value is false.
removeAbandonedTimeout
(int) Timeout in seconds before an abandoned(in use) connection can be removed. The default value is 60 (60 seconds). The value should be set to the longest running query your applications might have.
logAbandoned
(boolean) Flag to log stack traces for application code which abandoned a Connection. Logging of abandoned Connections adds overhead for every Connection borrow because a stack trace has to be generated. The default value is false.
connectionProperties
(String) The connection properties that will be sent to our JDBC driver when establishing new connections. Format of the string must be [propertyName=]* NOTE - The &user& and &password& properties will be passed explicitly, so they do not need to be included here. The default value is null.
poolPreparedStatements
(boolean) Property not used.
maxOpenPreparedStatements
(int) Property not used.
Description
(String) A custom query to be run when a connection is first created. The default value is null.
jdbcInterceptors
(String) A semicolon separated list of classnames extending org.apache.tomcat.jdbc.pool.JdbcInterceptor class. See
below for more detailed description of syntaz and examples.
These interceptors will be inserted as an interceptor into the chain of operations on a java.sql.Connection object. The default value is null.
Predefined interceptors:
org.apache.tomcat.jdbc.pool.interceptor.
ConnectionState - keeps track of auto commit, read only, catalog and transaction isolation level.
org.apache.tomcat.jdbc.pool.interceptor.
StatementFinalizer - keeps track of opened statements, and closes them when the connection is returned to the pool.
More predefined interceptors are described in detail in the .
validationInterval
(long) avoid excess validation, only run validation at most at this frequency - time in milliseconds. If a connection is due for validation, but has been validated previously within this interval, it will not be validated again. The default value is 30000 (30 seconds).
jmxEnabled
(boolean) Register the pool with JMX or not. The default value is true.
(boolean) Set to true if you wish that calls to getConnection should be treated fairly in a true FIFO fashion. This uses the org.apache.tomcat.jdbc.pool.FairBlockingQueue implementation for the list of the idle connections. The default value is true. This flag is required when you want to use asynchronous connection retrieval.
Setting this flag ensures that threads receive connections in the order they arrive.
During performance tests, there is a very large difference in how locks and lock waiting is implemented. When fairQueue=true there is a decision making process based on what operating system the system is running. If the system is running on Linux (property os.name=Linux. To disable this Linux specific behavior and still use the fair queue, simply add the property org.apache.tomcat.jdbc.pool.FairBlockingQueue.ignoreOS=true to your system properties before the connection pool classes are loaded.
abandonWhenPercentageFull
(int) Connections that have been abandoned (timed out) wont get closed and reported up unless the number of connections in use are above the percentage defined by abandonWhenPercentageFull. The value should be between 0-100. The default value is 0, which implies that connections are eligible for closure as soon as removeAbandonedTimeout has been reached.
(long) Time in milliseconds to keep this connection. When a connection is returned to the pool, the pool will check to see if the now - time-when-connected & maxAge has been reached, and if so, it closes the connection rather than returning it to the pool. The default value is 0, which implies that connections will be left open and no age check will be done upon returning the connection to the pool.
(boolean) Set to true if you wish the ProxyConnection class to use String.equals and set to false when you wish to use == when comparing method names. This property does not apply to added interceptors as those are configured individually. The default value is true.
suspectTimeout
(int) Timeout value in seconds. Default value is 0.
Similar to to the removeAbandonedTimeout value but instead of treating the connection as abandoned, and potentially closing the connection, this simply logs the warning if logAbandoned is set to true. If this value is equal or less than 0, no suspect checking will be performed. Suspect checking only takes place if the timeout value is larger than 0 and the connection was not abandoned or if abandon check is disabled. If a connection is suspect a WARN message gets logged and a JMX notification gets sent once.
rollbackOnReturn
(boolean) If autoCommit==false then the pool can terminate the transaction by calling rollback on the connection as it is returned to the pool Default value is false.
commitOnReturn
(boolean) If autoCommit==false then the pool can complete the transaction by calling commit on the connection as it is returned to the pool If rollbackOnReturn==true then this attribute is ignored. Default value is false.
alternateUsernameAllowed
(boolean) By default, the jdbc-pool will ignore the
call, and simply return a previously pooled connection under the globally configured properties username and password, for performance reasons.
The pool can however be configured to allow use of different credentials each time a connection is requested. To enable the functionality described in the
call, simply set the property alternateUsernameAllowed to true.
Should you request a connection with the credentials user1/password1 and the connection was previously connected using different user2/password2, the connection will be closed, and reopened with the requested credentials. This way, the pool size is still managed on a global level, and not on a per schema level.
The default value is false.
This property was added as an enhancement to .
dataSource
(javax.sql.DataSource) Inject a data source to the connection pool, and the pool will use the data source to retrieve connections instead of establishing them using the java.sql.Driver interface. This is useful when you wish to pool XA connections or connections established using a data source instead of a connection string. Default value is null
dataSourceJNDI
(String) The JNDI name for a data source to be looked up in JNDI and then used to establish connections to the database. See the dataSource attribute. Default value is null
useDisposableConnectionFacade
(boolean) Set this to true if you wish to put a facade on your connection so that it cannot be reused after it has been closed. This prevents a thread holding on to a reference of a connection it has already called closed on, to execute queries on it. Default value is true.
logValidationErrors
(boolean) Set this to true to log errors during the validation phase to the log file. If set to true, errors will be logged as SEVERE. Default value is false for backwards compatibility.
propagateInterruptState
(boolean) Set this to true to propagate the interrupt state for a thread that has been interrupted (not clearing the interrupt state). Default value is false for backwards compatibility.
ignoreExceptionOnPreLoad
(boolean) Flag whether ignore error of connection creation while initializing the pool. Set to true if you want to ignore error of connection creation while initializing the pool. Set to false if you want to fail the initialization of the pool by throwing exception. The default value is false.
以上只是简单的介绍，具体的使用方法还需参考：
相关热词搜索：
上一篇：下一篇：
相关阅读：
总排行程序员面试知识库
最新IT新闻
猜你喜欢：