balld 00/07/10 21:10:32
Modified: xdocs sqltaglib.xml
Added: xdocs connection-pool.xml
Log:
added brian millett's connection pool docs
Revision Changes Path
1.7 +68 -5 xml-cocoon/xdocs/sqltaglib.xml
Index: sqltaglib.xml
===================================================================
RCS file: /home/cvs/xml-cocoon/xdocs/sqltaglib.xml,v
retrieving revision 1.6
retrieving revision 1.7
diff -u -r1.6 -r1.7
--- sqltaglib.xml 2000/05/01 19:43:43 1.6
+++ sqltaglib.xml 2000/07/11 04:10:31 1.7
@@ -13,6 +13,8 @@
<source>processor.xsp.logicsheet.sql.java =
resource://org/apache/cocoon/processor/xsp/library/java/sql.xsl</source>
<p>Note the line break is for formatting purposes, it should
not appear in your cocoon.properties file.</p>
+ <p>If you are going to use connection pools, then make sure that you
have followed the
+ <a href="connection-pool.html">installation</a> steps for the
connection pools.</p>
</s1>
<s1 title="Configuration">
@@ -37,15 +39,17 @@
<p>The <code>execute-query</code> element will contain child
elements in the sql taglib namespace which specify parameters for the query. It
is an error if it contains children (not necessarily descendents) in another
namespace. No other element in the sql taglib namespace may contain children in
the sql taglib namespace.</p>
<p>The list of valid sql taglib configuration elements is:</p>
<dl>
- <dt><code>driver</code> <strong>(mandatory)</strong></dt>
+ <dt><code>driver</code> <strong>(mandatory*)</strong></dt>
<dd>The fully qualified class name of the JDBC driver</dd>
- <dt><code>dburl</code> <strong>(mandatory)</strong></dt>
+ <dt><code>dburl</code> <strong>(mandatory*)</strong></dt>
<dd>The JDBC URL to the data source</dd>
+ <dt><code>use-connection</code>
<strong>(mandatory**)</strong></dt>
+ <dd>The name of the connection in the JDBC connection pool
configured in cocoon.properties.</dd>
<dt><code>query</code> <strong>(mandatory)</strong></dt>
<dd>The SQL query to execute.</dd>
- <dt><code>username</code></dt>
+ <dt><code>username</code><strong>*</strong></dt>
<dd>The username for database authentication</dd>
- <dt><code>password</code></dt>
+ <dt><code>password</code><strong>*</strong></dt>
<dd>The password for database authentication</dd>
<dt><code>doc-element</code></dt>
<dd>The name of the element which will wrap the entire
resultset, if there are any results. If this is not set, there will be no
top-level wrapper.</dd>
@@ -77,9 +81,68 @@
<dd>The namespace prefix to use when creating result elements.
FIXME - we should ask for a namespace URI and prefix, right?</dd>
<dt><code>column-format</code></dt>
<dd>Indicates that a column needs special formatting. See the
column formatting section.</dd>
+ <dt><code><strong>*</strong></code></dt>
+ <dd>Indicates that this is manditory ONLY if you are NOT using
the connection pool</dd>
+ <dt><code><strong>**</strong></code></dt>
+ <dd>Indicates that this is manditory ONLY if you ARE using the
connection pool</dd>
</dl>
- <p>Note that the query element may contain elements from other
XSP-enabled namespaces, like request or session. The others may not right now,
though I'm certainly willing to consider changing that if desired. I'm still
new at this XSP thing too.</p>
+ <p>Note that the query element may contain elements from other
XSP-enabled namespaces, like request or session. The others may not right now,
though I'm certainly willing to consider changing that if desired. I'm still
new at this XSP thing too.
+ </p>
</s1>
+
+ <s1 title="Examples">
+ <p>Let's step through an example of using a connection defined to be
"helpdesk"
+ that uses oracle. In the cocoon.properties have something simular
to this:</p>
+ <source>
+ #### ORACLE
+
processor.xsp.pool.database.helpdesk.driver=oracle.jdbc.driver.OracleDriver
+
processor.xsp.pool.database.helpdesk.url=jdbc:oracle:thin:@localhost:1521:dldf
+ processor.xsp.pool.database.helpdesk.username=dnUser
+ processor.xsp.pool.database.helpdesk.password=dbPass
+ # The number of database connections to cache in the
ConnectionPool.
+ processor.xsp.pool.database.helpdesk.maxConnections=3
+ </source>
+ <p>then in my xml file, I would use the connection pool name
"helpdesk" as:</p>
+ <source><![CDATA[
+ <sql:execute-query>
+ <sql:use-connection>helpdesk</sql:use-connection>
+ <sql:skip-rows>0</sql:skip-rows>
+ <sql:max-rows>50</sql:max-rows>
+ <sql:null-indicator>y</sql:null-indicator>
+ <sql:count-attribute>BOB</sql:count-attribute>
+ <sql:doc-element>ROWSET</sql:doc-element>
+ <sql:row-element>ROW</sql:row-element>
+ <sql:tag-case>preserve</sql:tag-case>
+ <sql:id-attribute>ID</sql:id-attribute>
+ <sql:query>
+ SELECT last_name, first_name, initials
+ FROM EMPLOYEE
+ </sql:query>
+ </sql:execute-query>
+ ]]></source>
+ <p>Notice that for the connection pool, you do <strong>not</strong> use
the <strong>driver</strong>,
+ <strong>dburl</strong>, <strong>username</strong>,
+ nor <strong>password</strong> tags.
+ Lets look at an example that does not use the connection pool:</p>
+ <source><![CDATA[
+ <sql:execute-query>
+ <sql:driver>com.informix.jdbc.IfxDriver</sql:driver>
+
<sql:dburl>jdbc:informix-sqli://localhost:1526/database:informixserver=server</sql:dburl>
+ <sql:username>dbUser</sql:username>
+ <sql:password>dbPass</sql:password>
+ <sql:skip-rows>0</sql:skip-rows>
+ <sql:max-rows>50</sql:max-rows>
+ <sql:tag-case>preserve</sql:tag-case>
+ <sql:count-attribute>count</sql:count-attribute>
+ <sql:doc-element>ROWSET</sql:doc-element>
+ <sql:row-element>ROW</sql:row-element>
+ <sql:null-indicator>omit</sql:null-indicator>
+ <sql:id-attribute>ID</sql:id-attribute>
+ <sql:query>SELECT * FROM CUSTOMER;</sql:query>
+ </sql:execute-query>
+ ]]></source>
+ </s1>
+
<s1 title="Column Formatting">
<p>Generally, column values are formatted naively - the taglib
requests an Object from the ResultSet and invoked the toString() method. Some
columns return byte or character arrays - in that case, we construct a new
String using the array as a construction argument.</p>
<p>These defaults may be overridden by supplying one or more
sql:column-format elements. Each sql:column-format element may contain these
child elements:</p>
1.1 xml-cocoon/xdocs/connection-pool.xml
Index: connection-pool.xml
===================================================================
<?xml version="1.0"?>
<!DOCTYPE document SYSTEM "./dtd/document-v10.dtd">
<document>
<header>
<title>Installing Cocoon Pool</title>
<authors>
<person name="Brian Millett" email="[EMAIL PROTECTED]"/>
</authors>
</header>
<body>
<s1 title="Required Components">
<p>
In the <code>/lib</code> directory you'll find the
<code>turbine-pool.jar</code>
jar package that contains the Turbine Connection Pool binary files.
While this package may not be the most up-to-date version, it is
guaranteed and tested to work properly with Cocoon so, we suggest that you
use it. Note, however, that it was redistributed untouched from
its original distribution.
</p>
</s1>
<s1 title="Installing Turbine Connection Pool">
<s2 title="Installing Cocoon on Apache JServ">
<p>
First thing to do is to make sure that Cocoon and all the needed
components
(as explained in the previous section) are visible. This implies adding
this to the servlet engine classpath by adding a line
to your <code>jserv.properties</code> file for the connection pool jar
package.
</p>
<source>wrapper.classpath=[path-to-jar]/[jar-name].jar</source>
<p>
Here is an example:
</p>
<source>
wrapper.classpath=/usr/local/java/cocoon/lib/turbine-pool.jar
</source>
<p>To use the pool, you need to have the appropriate properties defined
in your <i>cocoon.properties</i> file. The important ones are listed
below.
This is an example of creating a default pool for use with Oracle.
Currently
Turbine supports a wide number of different databases. If your
database
is not already supported, please subscribe to the mailing list and ask
for help or try on your own. It is quite easy to add support for your
favorite
database.</p>
<source>
# These are your database settings, look in the
# org.apache.turbine.util.db.pool.* package for more information.
processor.xsp.pool.database.default.driver=oracle.jdbc.driver.OracleDriver
processor.xsp.pool.database.default.url=jdbc:oracle:thin:@localhost:1521:ORCL
processor.xsp.pool.database.default.username=dbUser
processor.xsp.pool.database.default.password=dbPass
processor.xsp.pool.database.default.maxConnections=3
processor.xsp.pool.database.default.expiryTime=3600000
# These are the supported jdbc-drivers and their adaptors.
# These properties are used by the DBFactory.
processor.xsp.pool.database.adaptor=DBWeblogic,DBOracle,DBInstantDB,DBPostgres,DBSybase,DBInformix
processor.xsp.pool.database.adaptor.DBWeblogic=weblogic.jdbc.pool.Driver
processor.xsp.pool.database.adaptor.DBOracle=oracle.jdbc.driver.OracleDriver
processor.xsp.pool.database.adaptor.DBInstantDB=org.enhydra.instantdb.jdbc.idbDriver
processor.xsp.pool.database.adaptor.DBPostgres=postgresql.Driver
processor.xsp.pool.database.adaptor.DBInformix=com.informix.jdbc.IfxDriver
processor.xsp.pool.database.adaptor.DBSybase=com.sybase.jdbc.SybDriver
# The full path name to a pool log file
# if not given, commands to log events using
org.apache.turbine.util.Log will be ignored.
# This file must already exist and be writable.
# Default: none
#
processor.xsp.pool.logfile=/opt/apache/var/log/dbPool.log
</source>
<p> The <strong>default</strong> is by convention the default connection
used
by the Turbine Connection Pool. To define a Named connection, add
new entries
with the word "default" replaced with the name you want. Here is an
example
for defining a connection to a MySQL database called "helpdesk":</p>
<source>
processor.xsp.pool.database.helpdesk.driver=oracle.jdbc.driver.OracleDriver
processor.xsp.pool.database.helpdesk.url=jdbc:oracle:thin:@localhost:1521:ORCL
processor.xsp.pool.database.helpdesk.username=dbUser
processor.xsp.pool.database.helpdesk.password=dbPass
processor.xsp.pool.database.helpdesk.maxConnections=3
processor.xsp.pool.database.helpdesk.expiryTime=3600000
</source>
<p>The maxConnections setting is the maximum number
of connections to cache. The expiryTime setting is for automaticially
timing
out cached connections to the database. This is to prevent connections
from becoming stale. The username and password settings should be
obvious.
If your database does not support users, then simply leave these items
blank. The rest of the settings are database specific and you should
read
the various connection interfaces javadoc and drivers to find out the
appropriate
strings to use.</p>
<p>
</p>
</s2>
</s1>
<s1 title="Using Turbine Connection Pool in your code">
<p>This section is to describe how you can use the Turbine connection pool
in your own code. To demonstrate this, I'll show an example how I modified
Ricardo Rocha's excellent example XSQL. this is some of the code that he
had before:</p>
<source><![CDATA[
import java.sql.*;
import java.util.*;
import org.w3c.dom.*;
import DBConnectionManager;
public class XSQL {
static DBConnectionManager manager = DBConnectionManager.getInstance();
public static Element executeQuery(
String connectionName,
String statementId,
String statementText,
Document factory
)
throws SQLException
{
Connection connection = null;
try {
connection = manager.getConnection(connectionName);
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(statementText);
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
Element rowSet = factory.createElement("xsql:rowset");
rowSet.setAttribute("id", statementId);
for (int i = 0; resultSet.next(); i++) {
Element row = factory.createElement("xsql:row");
row.setAttribute("sequence", String.valueOf(i + 1));
rowSet.appendChild(row);
for (int j = 0; j < columnCount; j++) {
String value = resultSet.getString(j + 1);
Element element =
factory.createElement(metaData.getColumnLabel(j + 1).toLowerCase());
row.appendChild(element);
if (value != null) {
element.appendChild(
factory.createTextNode(value)
);
}
}
}
resultSet.close();
statement.close();
return rowSet;
} catch (SQLException e) {
throw(new SQLException(statementText));
} finally {
if (connection != null) {
manager.freeConnection(connectionName, connection);
}
}
}
}
]]></source>
<p>Now this is the code modified (bold text) to use the Turbine
conncection pool:</p>
<source><![CDATA[
import java.sql.*;
import java.util.*;
import org.w3c.dom.*;
import DBConnectionManager;
public class XSQL {
<strong>static DBBroker pool = DBBroker.getInstance();</strong>
public static Element executeQuery(
String connectionName,
String statementId,
String statementText,
Document factory
)
throws SQLException
{
Connection connection = null;
<strong>DBConnection db = null;</strong>
try {
<strong>db = pool.getConnection(connectionName);</strong>
<strong>connection = db.getConnection();</strong>
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(statementText);
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
Element rowSet = factory.createElement("xsql:rowset");
rowSet.setAttribute("id", statementId);
for (int i = 0; resultSet.next(); i++) {
Element row = factory.createElement("xsql:row");
row.setAttribute("sequence", String.valueOf(i + 1));
rowSet.appendChild(row);
for (int j = 0; j < columnCount; j++) {
String value = resultSet.getString(j + 1);
Element element =
factory.createElement(metaData.getColumnLabel(j + 1).toLowerCase());
row.appendChild(element);
if (value != null) {
element.appendChild(
factory.createTextNode(value)
);
}
}
}
resultSet.close();
statement.close();
return rowSet;
} catch (SQLException e) {
throw(new SQLException(statementText));
} finally {
if (connection != null) {
<strong>pool.releaseConnection(db);</strong>
}
}
}
}
]]></source>
<p>Please see the <a
href="http://java.apache.org/turbine/getting_started.html";>getting
started</a> documentation at the <a
href="http://java.apache.org/turbine";>Turbine</a>
homepage for more information about Turbine.
</p>
</s1>
</body>
</document>
