Using OpenCms's Database connection pool
(Added subsection "Configuration of the attributes of the connection with a Properties object") |
(OpenCms 9.5.1 supports configuration with properties object method. Correct link to Oracle's doc) |
||
| (One intermediate revision by one user not shown) | |||
| Line 84: | Line 84: | ||
=== Configuration of the attributes of the connection with a <tt>Properties</tt> object === | === 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/ | + | 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: | For the above example: | ||
| Line 105: | Line 105: | ||
# optional parameters for the URL of the JDBC driver | # optional parameters for the URL of the JDBC driver | ||
# db.pool.yourdb.jdbcUrl.params=?characterEncoding\=UTF-8 | # db.pool.yourdb.jdbcUrl.params=?characterEncoding\=UTF-8 | ||
| − | </code | + | </code> |
== Access the new connection == | == Access the new connection == | ||
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(); %>
