dbPool sybase connection pool
使用 dbpool，这里需要注意 ，dbpool 使用的日志库是 slf4j ，所以，在如果你项目使用的日志库不是 slf4j，那么就需要在你的项目中引入
slf4j-api-1.7.7.jar
slf4j-log4j12-1.7.7.jar
log4j-1.2.17.jar
这3个 jar包。
在讲具体如何使用 dbpool之前很有必要先了解 一下内容， 选自 http://www.snaq.net/java/DBPool/ 。
也可以在 http://www.snaq.net/java/DBPool/ 了解更多更详细的内容。
Shutdown Hook
When a pool or pool manager is no longer required, the standard procedure is to "release" it by calling the appropriate release() method, on either the ConnectionPool or ConnectionPoolManager instance. Another option is to register a "shutdown hook" to perform this step automatically when the Java VM is exiting. Bear in mind that because it occurs when the VM is exiting, you need to check if this is an appropriate time for the release for your application. To register a shutdown hook, simply call registerShutdownHook() on a ConnectionPool/ConnectionPoolManager/DBPoolDataSource instance, or use the static method ConnectionPoolManager.registerGlobalShutdownHook().
Note: if individual ConnectionPool hooks are registered, then a ConnectionPoolManager global hook registered and removed, the individual pools will have lost their individual shutdown hooks, so will need to be re-registered as required.
Note: if using a web container, you are recommended not to use the shutdown hook, but instead use the ServletContextListener as appropriate (see DataSource section), which provides a more reliable mechanism for ensuring resources are released.
Asynchronous connection destruction
Sometimes you will come across situations where the destruction of a database connection takes a disproportionate length of time. This could occur because the connections regularly get into an unstable state for some reason, or simply that the nature of the system being used means that many other resources need to be cleaned up as well. This situation can be indicative of a more serious instability (worth considering more rigorous connection validation), but there are some occasions when this is simply unavoidable. To ensure that pooling performance is not affected when this occurs you can turn on this option to perform the connection destruction asynchronously, thereby returning control immediately and not tying up the pool unnecessarily. It is recommended (and the default setting) that you leave this option disabled unless you are sure you need it. To enable this option add this line to the properties file:
<poolname>.async=true
or call setAsyncDestroy(true) on a ConnectionPool instance.
Pool listeners
It can be useful to have code able react to the current state of a pool. For this reason pools can issue events about their activity to objects which have registered interest. Listener objects must implement the snaq.db.ConnectionPoolListenerinterface, and can register/deregister their interest by using the provided methods, for example:
- addConnectionPoolListener(ConnectionPoolListener)
- removeConnectionPoolListener(ConnectionPoolListener)
Also provided for convenience is the snaq.db.ConnectionPoolEventAdapter class, which provides empty implementations of all the methods in the ConnectionPoolListener interface, so can be extended to override fewer methods. The events triggered are as follows:
| Event Identifier | Description |
|---|---|
| INIT_COMPLETED | Fired when the init() method has completed creating new pool connections. |
| CHECKOUT | Fired just before a valid connection is handed back from a checkOut(…) request. |
| CHECKIN | Fired when a connection is handed back with a checkIn(…) call. |
| MAX_POOL_LIMIT_REACHED | Fired when a check-out request causes the pooling limit (maxpool) to be reached. |
| MAX_POOL_LIMIT_EXCEEDED | Fired when a check-out request causes the pooling limit (maxpool) to be exceeded. |
| MAX_SIZE_LIMIT_REACHED | Fired when a check-out request causes the pool's maximum size limit (maxsize) to be reached. |
| MAX_SIZE_LIMIT_ERROR | Fired when a check-out request is made but the pool's maximum size limit (maxsize) has been reached. |
| VALIDATION_ERROR | Fired when a connection cannot be validated (when the isValid(…) method call fails). |
| PARAMETERS_CHANGED | Fired when the pool's parameters have been changed. |
| POOL_FLUSHED |
Fired when the pool is flushed of free/unused connections. (This generally only happens is flush() is explicitly called for a pool.) |
| POOL_RELEASED |
Fired when a pool has been released. No more events are fired from the same pool following this event, as all listeners are removed. |
PoolTracer
An example of the above pool listener feature is that of the included snaq.util.PoolTracer class. This class implements a simple pool listener to write pool activity data to a log file. This class can
either be instantiated via one of its constructors programatically, or if using the pool manager using the <poolname>.listenerN pool
property. PoolTracer requires a filename for the trace log file, which can be specified as <poolname>.listenerN.file=<filename>.
See the Javadoc API for the PoolTracer class for more information.
For example, to specify the pool tracer as the only listener on a pool pool-local, writing to a trace log file called trace.log in the current folder, place the following lines in the properties file alongside the other properties:
pool-local.listener0=snaq.util.PoolTracer pool-local.listener0.file=trace.log # Why not specify the optional date format too? pool-local.listener0.dateformat=HH:mm:ss,SSS
Good JDBC programming practices
The following two simple practices will help to ensure you get the best from DBPool:
- Always close your ResultSet/Statement/Connection instances when finished with them.
- Always close them in the reverse sequence to their creation.
The following code shows the principal, albeit very simplistically:
Connection con = pool.getConnection();
Statement st = con.createStatement();
ResultSet res = st.executeQuery("…");
// whatever
res.close();
st.close();
con.close();
Following this principal reduces the risk of resource retention problems ("memory leaks") and unexpected pooling behaviour. It seems obvious, yet many bugs/problems/headaches can be attributed to not following these two simple steps. This is made much simpler using the try-with-resources language feature, which allows you to write the above code as the following instead (for example):
try (Connection con = pool.getConnection(); Statement st = con.createStatement())
{
// The following line could also be included above,
// but more commonly would be in a separate try-block.
try (ResultSet res = st.executeQuery("…"))
{
// whatever
}
catch (SQLException sqlx)
{
// whatever
}
}
catch (SQLException ex)
{
// whatever
}
Another recommended practice is to use a Type-4 JDBC driver for your database if possible. Type-4 drivers are ones written in pure Java (with no native code), which means all driver resources are managed by the Java Virtual Machine. The choice of driver obviously depends on what is available for your database, but the choice of driver type will also depend on performance considerations, cost, etc.
下面就不废话直接代码，
// 注册链接数据的驱动
Class c = Class.forName("com.sybase.jdbc4.jdbc.SybDriver"); // Fill JDBC driver class name here.
Driver driver = (Driver) c.newInstance();
DriverManager.registerDriver(driver);
//1:pool-name,2:min,3:max,4:size,5:timeout,6:url,7:name,8:passwd
ConnectionPool tempCp = new ConnectionPool(“pool-name”, 5, 5, 6, ConnectionPoolTimeout, “连接数据库的字符串”, “用户名”, “密码”);
//设置异步修复
tempCp.setAsyncDestroy(true);
//设置不使用缓存
tempCp.setCaching(false);
//上述代码已经完成连接池的创建，
//下面是在需要用到连接时的使用方式
Connnection con = tempCp.getConnection(timeout);
Statement st = con.createStatement();
ResultSet res = st.executeQuery("query sql");
// do something
//关闭连接 ,
res.close();
st.close();
con.close();
代码看起很简单，个人建议一定要了解上述几个介绍。 详情请移架http://www.snaq.net/java/DBPool/ 或者下载 dbpool-7.0-javadoc.jar ，解压查看官方api文档。
本人将整理的jar 下载地址，包含如下 jar包，下载包含到自己项目中即可用。
dbpool-7.0.jar
dbpool-7.0-javadoc.jar
log4j-1.2.17.jar
slf4j-api-1.7.7.jar
slf4j-log4j12-1.7.7.jar
