Using OpenCms's Database connection pool
m (syntax) |
(→Configuration of the database connection pool) |
||
| Line 10: | Line 10: | ||
Your changes will look something like this: | Your changes will look something like this: | ||
| − | <code lang=" | + | <code lang="ini"> |
# | # | ||
# Declaration of database pools | # Declaration of database pools | ||
Revision as of 19:13, 22 December 2008
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.
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(); %>
