Using OpenCms's Database connection pool

From OpenCms Wiki
(Difference between revisions)
Jump to: navigation, search
m
(OpenCms 9.5.1 supports configuration with properties object method. Correct link to Oracle's doc)
 
(5 intermediate revisions by 4 users not shown)
Line 1: Line 1:
−
Instead of setting up and configuring your own database connection pool, you can use OpenCms's version of the database connection pool management.
+
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:
 
First, you must add your database pool to the config file, located:
−
%tomcat folder%\webapps\ROOT\WEB-INF\config\opencms.properties.
+
%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".
 
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:
 
Your changes will look something like this:
 +
<code lang="ini">
 
  #
 
  #
 
  # Declaration of database pools
 
  # Declaration of database pools
 
  #################################################################################
 
  #################################################################################
 
  db.pools=default,yourdb
 
  db.pools=default,yourdb
 +
 +
#
 +
# Configuration of the default database pool
 +
#################################################################################
 +
...
 +
  
 
  #
 
  #
Line 49: Line 59:
  
 
  # connections will be validated before they are borrowed from the pool
 
  # connections will be validated before they are borrowed from the pool
−
  db.pool.yourdb.testOn Borrow=true
+
  db.pool.yourdb.testOnBorrow=true
  
 
  # connections will be validated by evictor thread
 
  # connections will be validated by evictor thread
Line 68: Line 78:
 
  # Oracle version            : SELECT 'validationQuery' FROM DUAL
 
  # Oracle version            : SELECT 'validationQuery' FROM DUAL
 
  db.pool.yourdb.testQuery=SELECT 1
 
  db.pool.yourdb.testQuery=SELECT 1
 +
</code>
 +
 +
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 <tt>Properties</tt> object ===
 +
 +
According to [http://docs.oracle.com/javadb/10.10.1.2/devguide/tdevdvlp36289.html Oracle's 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 <tt>Properties</tt> object passed as a second argument to the [http://docs.oracle.com/javase/7/docs/api/java/sql/DriverManager.html#getConnection(java.lang.String,%20java.util.Properties)|<tt>DriverManager.getConnection</tt>] method. There are jdbc drivers that supports different configuration parameters depending on the configuration method used. If you are using such a driver, you could have to resort to this alternative configuration method.
 +
 +
Since its [https://github.com/alkacon/opencms-core/releases/tag/build_9_5_1 version 9.5.1], OpenCms supports this additional "configuration with a <tt>Properties</tt> object" method.
 +
 +
For the above example:
 +
 +
<code lang="ini">
 +
#
 +
# 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>
 +
 +
== 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.
  
−
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):
+
=== 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");
  
−
  java.sql.Connection conn =  
+
    while (rs.next()) {
−
                OpenCms.getSqlManager().getConnection("yourdb");
+
  %> <%= rs.getInt(1) %> <%= rs.getString(2) %><br><%
 +
    }
 +
   
 +
    rs.close();
 +
    stmt.close();
  
−
Be sure to release the connection when you're done with it.
+
    con.close();
 +
%>
 +
</code>

Latest revision as of 13:02, 7 July 2015

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.

Contents

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 Oracle's 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. There are jdbc drivers that supports different configuration parameters depending on the configuration method used. If you are using such a driver, you could have to resort to this alternative configuration method.

Since its version 9.5.1, OpenCms supports this additional "configuration with a Properties object" 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

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: 

 <%@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();
 %>
Retrieved from "http://www.opencms-wiki.org/index.php?title=Using_OpenCms%27s_Database_connection_pool&oldid=5917"
Personal tools
Namespaces
Variants
Actions
Navigation
Toolbox