Using OpenCms's Database connection pool

Revision as of 22:24, 30 July 2007

Instead of setting up and configuring your own database connection pool, you can use OpenCms's version of the database connection pool management.

First, you must add your database pool to the config file, located: %tomcat folder%\webapps\ROOT\WEB-INF\config\opencms.properties.

Copy over the default pool, and modify it for your database pool, paying special attention to the sections: "Declaration of database pools", "Configuration of the {poolname} database pool", and "Configuration for statement pooling".

Your changes will look something like this:

#
# Declaration of database pools
#################################################################################
db.pools=default,yourdb

#
# Configuration of the yourdb database pool
#################################################################################
# name of the JDBC driver - this one is for MsSQL
db.pool.yourdb.jdbcDriver=net.sourceforge.jtds.jdbc.Driver

# URL of the JDBC driver
db.pool.yourdb.jdbcUrl=jdbc:jtds:sqlserver://yourSQLserver.com/yourDB

# optional parameters for the URL of the JDBC driver
# db.pool.yourdb.jdbcUrl.params=?characterEncoding\=UTF-8

# user name to connect to the database
db.pool.yourdb.user=youruser

# password to connect to the database
db.pool.yourdb.password=yourpassword

# the URL to make the JDBC DriverManager return connections from the DBCP pool
db.pool.yourdb.poolUrl=opencms:yourdb

# the maximum number of objects that can be borrowed from the pool
db.pool.yourdb.maxActive=25

# the maximum amount of time before throwing an exception when the pool is exhausted
db.pool.yourdb.maxWait=2000

# the minimum number of objects that will kept connected
db.pool.yourdb.minIdle=3

# the maximum number of objects that can sit idled in the pool
db.pool.yourdb.maxIdle=10

# action to take when the pool is exhausted {grow|block|fail}
db.pool.yourdb.whenExhaustedAction=block

# connections will be validated before they are borrowed from the pool
db.pool.yourdb.testOn Borrow=true

# connections will be validated by evictor thread
db.pool.yourdb.testWhileIdle=false

# number of milliseconds to sleep between runs of the evictor thread
# -1 means no idle connection evictor thread will be run
db.pool.yourdb.timeBetweenEvictionRuns=-1

# number of connections tested in a run of the evictor thread
db.pool.yourdb.numTestsPerEvictionRun=3

# minimum amount of time in milliseconds a connection may be idle in the pool before it is eligable for eviction
db.pool.yourdb.minEvictableIdleTime=1800000

# the query to validate connections
# MSQL version (also MS SQL): SELECT 1
# Oracle version            : SELECT 'validationQuery' FROM DUAL
db.pool.yourdb.testQuery=SELECT 1

You can then use code like this to access the database from within your JSP pages (you'll need to import java.sql.*, org.opencms.db.*, and org.opencms.main.* for it to work):

java.sql.Connection conn = 
               OpenCms.getSqlManager().getConnection("yourdb");

Be sure to release the connection when you're done with it.