Using OpenCms's Database connection pool

OpenCms delivers a mechanism to include additional databases, that can be accessed from within OpenCms via the Opencms-SqlManager, the database connection pool. The pool contains all the information to access the database and an identifier, that is used in your jsp's to access the the database.

Configuration of the database connection pool

Instead of setting up and configuring your own database connection pool, you can use OpenCms's version of the database connection pool management. After succesfully setting up OpenCms, the pool already contains the connection, it uses to store and retrieve itself in the configured database.

First, you must add your database pool to the config file, located: %jsp-container folder%\webapps\opencms\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 default database pool
 #################################################################################
 ...
 
 
 #
 # 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.testOnBorrow=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

To make changes of the opencms.properties-file take effect restart your jsp-container, i.e. Apache Tomcat.

Configuration of the attributes of the connection with a Properties object

According to Java DB Developer's Guide - Specifying attributes in a properties object, instead of specifying attributes on the connection URL, it's also possible to specify attributes as properties in a Properties object passed as a second argument to the DriverManager.getConnection method.

The current version of OpenCms 9_5 doesn't support this additional "configuration with a Properties object" method. But if you're using your own compiled opencms.jar, the alkacon/opencms-core pull-request #299 extends the configurations options. This could be necessary if the jdbc driver supports different configuration parameters depending on the configuration method.

For the above example:

 #
 # 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
 
 # When opening the connection, set the property socketKeepAlive=true
 db.pool.yourdb.connectionProperties.socketKeepAlive=true
 
 # 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
</code lang="ini">
 
== Access the new connection ==
 
You can access the database from within your JSP pages via java.sql.* (be  sure to import java.sql.*, org.opencms.db.*, and org.opencms.main.* for it to work).  
 
Be sure to release the connection when you are done with it, in any case within the jsp, where you have opened it. 
 
=== Example jsp ===
Create a test.jsp in the opencms' vfs, i.e. /yoursite/test.jsp. Supposing there exists a connection ''yourdb'', which refers to a database that contains a table ''people'' with columns ''id'' and ''name'', a test code could look like this:
<code lang="Java">
 <%@page import="java.sql.*,org.opencms.db.*,org.opencms.main.*"%>
 <% java.sql.Connection con = OpenCms.getSqlManager().getConnection("yourdb");
    Statement stmt = con.createStatement();
    ResultSet rs;
    rs = stmt.executeQuery("SELECT id, name FROM people");
 
    while (rs.next()) {
 %> <%= rs.getInt(1) %> <%= rs.getString(2) %><br><%
    }
 
    rs.close();
    stmt.close();
 
    con.close();
 %>