XQuery TUTORIAL

XQuery JDBC Connection Pooling Tutorial

Updated: 05 Oct 2021

Overview

Connection pooling allows your application to reuse connections. DataDirect XQuery supports connection pooling through JDBC, and it supports JDBC connection pool managers in several popular application server environments.

Supported Deployment Environments

The JDBC pooling mechanism in DataDirect XQuery® provides support for the following application servers:

Deployment guidelines and procedures for each of these databases are provided in the sections that follow.

Example

See the Example: JDBC Connection Pooling Servlet for an example of implementing a JDBC connection pool in DataDirect XQuery®.


Tomcat

This section describes the guidelines for deploying DataDirect XQuery® on an application server running Tomcat 5.x or 6.x.

  • Install DataDirect XQuery® by copying the DataDirect XQuery® jar files to CATALINA_home/common/lib, whereCATALINA_home is your Tomcat installation directory.
  • Define your JDBC DataSource resource in CATALINA_home/conf/server.xml

    For example:

<context path="/DDXQServlet" docBase="DDXQServlet.war">
<Resource name="jdbc/DDXQExample"
auth="Container"
type="javax.sql.DataSource"
username="john"
password="topsecret"
driverClassName="com.ddtek.xquery3.jdbc.XQueryDriver"
url="jdbc:datadirect:xquery3://jdbcUrl=
{jdbc:xquery:sqlserver://localhost:1433;databaseName=ddxq_example}"
initialSize="1"
accessToUnderlyingConnectionAllowed="true"
validationQuery="SELECT * FROM FOO" />
</context>

JDBC DataSource resource configuration options are described later in this section. Note, however, that the accessToUnderlyingConnectionAllowed configuration parameter must be set to "true".

  • Deploy the servlet DDXQServlet.war by copying it into CATALINA_home/webaps.
  • Start Tomcat.
  • Open your Web browser and point it to:

    http://myserver:8080/DDXQServlet/
Tomcat JDBC DataSource Resource Configuration Options

A JDBC DataSource resource can be configured with a number of options. The following table describes settings that are specific to DataDirect XQuery®. Refer to your Tomcat documentation for details.

Parameter

Description

type

Must be javax.sql.DataSource.

username

User name to log on the database.

password

Password to log on the database.

url

Connection URL in the format jdbc:datadirect:xquery3://JdbcUrl={url}[;optionalProperty=value[;...]] See Configuring a Connection Through the JDBC Driver Manager in the DataDirect XQuery User's Guide and Reference for more information.

driverClassName

Must be com.ddtek.xquery3.jdbc.XQueryDriver.

defaultReadOnly

Not supported; do not use.

defaultTransactionIsolation

Cannot be set. In order to change the default transaction isolation, use the appropriate DataDirect XQuery connection property.

defaultCatalog

Cannot be set. In order to change the default catalog, use the appropriate DataDirect XQuery connection property.

validationQuery

See Tomcat documentation. Note that this query can be anything. DataDirect XQuery will ping the database connection to check the connection validity.

poolPreparedStatements

Should not be used in the context of DataDirect XQuery®. Use DataDirect XQuery’s query pooling to maximize performance.

maxOpenPreparedStatements

Should not be used in the context of DataDirect XQuery®. Use DataDirect XQuery's query pooling to maximize performance.

accessToUnderlyingConnectionAllowed

MUST be set to true.

Example

See the Example: JDBC Connection Pooling Servlet for an example of implementing a JDBC connection pool in DataDirect XQuery®.

JBoss

This section describes the guidelines for deploying DataDirect XQuery® on an application server running JBoss 4.x or 5.x.

  • Install DataDirect XQuery® by copying the DataDirect XQuery® jar files to JBoss_home/server/server_config/lib, where JBoss_home is your JBoss installation directory, and server_config is your server configuration directory.
  • In the JBoss_home/server/server_config/deploy directory, create a DataSource resource file named ddxqexample-ds.xml. The file name must end with the characters -ds.xml so that the JBoss server can recognize it as a DataSource resources file; however, the file name can be prefixed with any set of characters.

    For example:

<?xml version="1.0" encoding="UTF-8" ?>
<datasources>
<local-tx-datasource>
<jndi-name>DDXQExample</jndi-name>
<use-java-context>false</use-java-context>
<connection-url>jdbc:datadirect:xquery3://jdbcUrl=
{jdbc:xquery:sqlserver://localhost:1433;databaseName=ddxq_example}
</connection-url>
<driver-class>com.ddtek.xquery3.jdbc.XQueryDriver</driver-class>
<user-name>john</user-name>
<password>topsecret</password>
<check-valid-connection-sql>SELECT * FROM FOO</check-valid-connection-sql>
</local-tx-datasource>
</datasources>

JDBC DataSource resource configuration options are described later in this section. Note, however, that the accessToUnderlyingConnectionAllowed configuration parameter must be set to "true".
  • Deploy the servlet DDXQServlet.ear by copying it into JBoss_home/server/server_config/deploy.
  • Start JBoss.
  • Open your Web browser and point it to:

    http://myserver:8080/DDXQServlet/
JBoss JDBC DataSource Resource Configuration Options

JBoss supports different type of JDBC DataSource resources. With DataDirect XQuery, you must use either no-tx-datasource or local-tx-datasource. The following table describes settings that are specific to DataDirect XQuery®. Refer to your JBoss documentation for details.

Parameter

Description

user-name

User name to log on the database.

password

Password to log on the database.

connection-url

Connection URL.

driver-class

Must be com.ddtek.xquery3.jdbc.XQueryDriver.

new-connection-sql

Cannot be set; if set it is ignored. In order to set an initialization string, use the appropriate DataDirect XQuery connection property.

track-statements

No statements are tracked.

prepared-statement-cache-size

Should not be used in the context of DataDirect XQuery. Use DataDirect XQuery’s query pooling to maximize performance.

transaction-isolation

Cannot be set. In order to change the default transaction isolation use the appropriate DataDirect XQuery connection property.

Example

See the Example: JDBC Connection Pooling Servlet for an example of implementing a JDBC connection pool in DataDirect XQuery®.

BEA WebLogic

This section describes the guidelines for deploying DataDirect XQuery® on an application server running BEA WebLogic Platform 9.x or 10.x.

  • Install DataDirect XQuery® by copying the DataDirect XQuery® jar files to your WebLogic domain library directory. This directory is usually located at $DOMAIN_DIR/lib.
  • Restart your WebLogic application server.
  • Start your WebLogic Server Administration Console.
  • Create a JDBC DataSource resource.
    • Browse to Services < JDBC < Data Sources, and choose Create a New JDBC Data Source.

    The JDBC Properties window appears.

    • Specify the following properties with the values shown here:
      • Data source name — "DDXQExample"
      • JNDI name — "jdbc/DDXQExample"
      • Database Type — "other"
    • Click Next.

    The Transaction Options window appears.

    • Choose to enable or disable the support for Global Transactions. If support is enabled, you must choose either “Emulate Two-Phase Commit” or “One-Phase Commit".
    • Click Next.

    The Connection Properties window appears.

    • You use this window to specify standard connection properties. Properties on this window are not used, with the exception of the password.
    • Click Next.

    The Create a New JDBC Data Source window refreshes. (This screen shot has been truncated for space considerations.)

    • In the Driver Class Name field, type “com.ddtek.xquery3.jdbc.XQueryDriver” and specify the proper JDBC URL. For example:

      “jdbc:datadirect:xquery3://jdbcUrl={jdbc:xquery:sqlserver://
      localhost:1433;databaseName=ddxq_example}”

      Make sure that the URL specifies all connection attributes, with the exception of the password. For the Test Table Name field, specify any dummy table name.

    • Click Next.

    You can now deploy this new JDBC Data Source on one or more of your servers.

    • Select the servers on which you wish to deploy the JDBC Data Source and click Finish.
    • Next, you need to alter the created JDBC Data Source. Choose to edit the “DDXQExample” data source, and subsequently select “Connection Pool” in the “Configuration” tab. (This screen shot has been truncated for space considerations.)

    • Browse to the advanced options and make sure “Remove Infected Connections Enabled” is unchecked. (This screen shot has been truncated for space considerations.)

    • Save all changes and your “DDXQExample” data source is ready for deployment.
  • Deploy the servlet by first locating Deployments on the main page of the administration console; next, choose Install and follow the instructions to deploy your DDXQServlet.ear.
  • Open your Web browser and point to http://myserver:7001/DDXQservlet/6.
Example

See the Example: JDBC Connection Pooling Servlet for an example of implementing a JDBC connection pool in DataDirect XQuery®.

IBM Websphere

This section describes the guidelines for deploying DataDirect XQuery® on an application server running IBM WebSphere Application Server V6.1.

  • Start the WebSphere Administrative Console.
  • Install DataDirect XQuery® in WebSphere.
    • Browse to Resources < JDBC < JDBC Providers, and choose Create a New JDBC Provider.
    • Specify the following properties with the values shown here:
      • Database type — "User-defined"
      • Implementation class name — "com.ddtek.xquery3.jdbc.XQueryConnectionPoolDataSource"
      • Name — "DataDirect XQuery JDBC Provider"
    • Click Next.
    • Specify all the DataDirect XQuery jar files; these files are in the \lib directory of your DataDirect XQuery installation.
    • Click Next.
    • Approve the information entered in the previous step.
    • Click Next to complete the JDBC provider definition.
    • Next, create a JDBC DataSource resource.
    • Browse to Resources < JDBC < Data Sources, and choose Create a New JDBC Data Source.
    • Specify the following properties with the values shown here:
      • Data source name — "DDXQExample"
      • JNDI name — "jdbc/DDXQExample"
    • Click Next.
    • Select the Select an existing JDBC provider radio button, and choose "DataDirect XQuery JDBC Driver".
    • Click Next and confirm the information you have just entered.
    • Next, set the properties for the DDXQExample on the Data sources configuration window.
    • Specify "DDXQExample" in the Name field on the Configuration tab, and then choose Custom properties.
    • Add the required data source properties.
  • Deploy the servlet by browsing to Applications < Install New Application, and choose to deploy DDXQServlet.ear .
  • Open your Web browser and point it to http://myserver:9080/DDXQServlet/.
Example

See the Example: JDBC Connection Pooling Servlet for an example of implementing a JDBC connection pool in DataDirect XQuery®.

Oracle

This section describes the guidelines for deploying DataDirect XQuery® on an application server running Oracle Application Server 10g.

Before You Begin

Before you begin, make sure you have installed DataDirect XQuery® by copying the DataDirect XQuery® jar files toOracle_Application_Server_home/j2ee/home/applib, where Oracle_Application_Server_home is your Oracle Application Server installation directory.

Deploying on Oracle Containers for J2EE

To deploy DataDirect XQuery® on an application server running Oracle Containers for J2EE 10g, you need to:

  • Create and configure a JDBC DataSource resource
  • Create the JNDI binding for the resource
  • Deploy the servlet

These steps are described in the following sections.

Creating and Configuring a JDBC DataSource Resource

To create and configure a JDBC DataSource resource:

  • Start the Oracle Enterprise Manager.
  • Browse to Administration > Services > JDBC Resources.

  • Next, create a JDBC DataSource resource:
    • Click the Create button under Connection Pools.

    The Create Connection Pool – Application window appears.

    • Select New Connection Pool under Connection Pool Type and click the Continue button.

    The Create Connection Pool window refreshes, as shown here:

    • Specify the following properties with the values shown here:
      • Name —DDXQExamplePool
      • Connection Factory Class — com.ddtek.xquery3.jdbc.XQueryConnectionPoolDataSource
      • JDBC URL — dummy-url
    • Scroll to the bottom of the page and click the “Add Another Row” button.
    • In the Name field, type connectionUrl. In the Value field, use the URL described in “Configuring a Connection Through the JDBC Driver Manager” in the DataDirect XQuery User’s Guide and Reference.
  • Next, click the Attributes tab to set some of the properties of the pool.

Because Oracle Application Server supports advanced pooling properties only with Oracle data sources, you need to set timeout values.

Note the following before setting the Inactivity Timeout (seconds) field:

Oracle Application Server is not able to detect stale connections in the pool; nor is it able to ping unused connections to keep them active. Because of this, it is possible that your database or firewall will close idle connections.

To prevent this from occurring, set the Inactivity Timeout (seconds) field to a value lower than the database session idle timeout. If this value is not set appropriately, your connection in the pool can become corrupt and subsequent requests for a connection from the pool will result in your application receiving a stale connection.

  • Once you have set the timeout values, click the Apply button.
Creating the JNDI Binding

Once you have created the JDBC DataSource resource, you need to create the JNDI binding:

  • From the JDBC Resources window, click the Create button under DataSources.

The Create Data Source – Application & Type window appears.

  • Make sure the Application field is set to default and that the Data Source Type is set to Managed Data Source, and then click the Continue button.

The Create Data Source – Managed Data Source window appears:

  • Specify the following properties with the values shown here:
    • Name — DDXQExample
    • JNDI Location — jndi/DDXQExample
    • Transaction Level — Global & Local Transactions Only
    • Connection Pool — DDXQExamplePool
  • Click the Finish button.

You are returned to the JDBC Resources window:

Deploying the Servlet

To deploy the servlet:

  • Return to the Oracle Enterprise Manager Home and select the Applications tab:

  • Click the Deploy button, and choose to deploy DDXQServlet.ear.
  • Open your Web browser and test the application.
Example

See the Example: JDBC Connection Pooling Servlet for an example of implementing a JDBC connection pool in DataDirect XQuery®.

Example

The following code sample shows a servlet using JDBC connection pooling in DataDirect XQuery®.

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import com.ddtek.xquery3.XQConnection;
import com.ddtek.xquery3.XQException;
import com.ddtek.xquery3.XQExpression;
import com.ddtek.xquery3.XQSequence;
import com.ddtek.xquery3.jdbc.XQueryConnection;
/**
* DataDirect Servlet example demonstrating the integration
with JDBC Connection Pooling
*/
public class DDXQServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
Connection jdbc_c = null;
XQExpression xqj_e = null;
try {
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource jdbc_ds = (DataSource)envContext.lookup("jdbc/DDXQExample");
jdbc_c = jdbc_ds.getConnection();
PrintWriter out = response.getWriter();
XQConnection xqj_c = XQueryConnection.getXQConnection(jdbc_c);
xqj_e = xqj_c.createExpression();
XQSequence xqj_s = xqj_e.executeQuery(
"<>Current date: </b>,current-date(),<br/>," +
"<b>Current time: </b>,current-time(), " +
" <table border='1'> "+
"   <tr> "+
"   <th>User</th> "+
"   <th>Stock</th> "+
"   <th>Shares</th> "+
"      </tr> "+ " 
"      { "+
"      for $item in collection('holdings')/holdings "+
"      return "+
"   <tr> "+
"   <td>{$item/userid/data(.)}</td> "+
"   <td>{$item/stockticker/data(.)}</td> "+
"   <td>{$item/shares/data(.)}</td> "+
"   </tr> "+
"   } "+
"  </table> ");
    
xqj_s.writeSequence(out, new Properties());
out.close();
}
catch(Exception e){
throw new ServletException(e);
}
finally {
if (xqj_e != null) try{xqj_e.close();}
catch (XQException e) {}
    
if (jdbc_c != null) try{jdbc_c.close();}
catch (SQLException e) {}
}
}
}

 

Contents

Connect any application to any data source anywhere

Explore all DataDirect Connectors

Need additional help with your product?

Get Customer Support