Using Connection Pooling

Connection pooling allows your application to reuse connections. DataDirect XQuery supports connection pooling through JDBC and supports JDBC connection pool managers in the following application server environments:

For the most current information about which application server environments are supported, go to:

http://www.datadirect.com/developer/data-integration/tutorials/examples/connection-pooling/index.ssp

DataDirect XQuery has a JDBC driver that allows your application to use connection pooling with an application server’s JDBC pool manager. See Configuring Connection Pooling for information about this driver.

DataDirect XQuery provides three classes for support of connection pooling:

Configuring a Connection Through the JDBC Driver Manager

The com.ddtek.xquery.jdbc.XQueryDriver class is an implementation of the JDBC driver interface (java.sql.Driver) and is used to configure a pooled connection through the JDBC Driver Manager.

The syntax of the JDBC URL required by this class depends on if you are connecting to one relational data source or multiple relational data sources.

If you are connecting to one relational data source the syntax is: 

jdbc:datadirect:xquery://JdbcUrl={url}[;optionalProperty=value[;...]]

where:

url is a URL, as documented in Specifying Connection URIs.

optionalProperty is any of the DDXQDataSource properties, as documented in Table 6-1.

value is determined by the DDXQDataSource property you are specifying.

In the following example, the JDBC URL is defined in the first set of braces {} and the baseURI is a property of DDXQDataSource: 

jdbc:datadirect:xquery://JdbcUrl={jdbc:xquery:sqlserver://localhost:1433; 
databaseName=holdings;User=myuserID;Password=mypwd}; 
baseURI={file:///C:/xmldocuments/};

If you are connecting to multiple relational data sources the syntax is: 

jdbc:datadirect:xquery://jdbcConnections={Url={url}[;optionalProperty=value 
[;...]]},...

where:

url is a URL, as documented in Specifying Connection URIs.

optionalProperty is any of the DDXQJDBCConnection properties, as documented in Table 6-2.

value is determined by the DDXQJDBCConnection property you are specifying.

In the following example, two JDBC URLs are specified and the user ID and password are specified using the User and Password properties of DDXQJDBCConnection; they are not specified within the URL: 

jdbc:datadirect:xquery://jdbcConnections= 
{Url={jdbc:xquery:sqlserver://server1:1433;databaseName=stocks};User=myuserID; 
Password=mypwd}, {Url={jdbc:xquery:sqlserver://server2:1433;databaseName=
holdings}; 
User=myuserID2;Password=mypwd2}

Configuring a Connection Through a Data Source

The com.ddtek.xquery.jdbc.XQueryConnectionPoolDataSource class is an implementation of the JDBC interface javax.sql.ConnectionPoolDataSource and is used to configure a connection pool connection through a data source.

Some application servers, such as IBM WebSphere, require a data source to configure pooled connections and provide the infrastructure for you to create the ConnectionPoolDataSource objects you need to configure connection pooling.

The only property defined for this class is connectionURL. The value for this property is a URL, which is documented in Configuring a Connection Through the JDBC Driver Manager.

Configuring Connection Pooling

  1. The DataDirect XQuery JDBC driver must be registered in the application server and a connection pool must be created. This step makes the pooled connections available to your application code through JNDI. For example:

    2. Context evnContext = (Context)initContext.lookup("java:/comp/env");

    DataSource jdbc_ds = (DataSource)envContext.lookup("jdbc/DDXQExample");

    Connection jdbc_c = jdbc_ds.getConnection();

    Now, a JDBC connection is available.

  2. Convert the JDBC connection into an XQJ connection. For example:

    3. XQConnection xqj_c = XQueryConnection.getXQConnection(jdbc_c);

    NOTE: DataDirect XQuery provides a class, com.ddtek.xquery.jdbc.XQueryConnection, to convert the JDBC connection into an XQJ connection.

    Now, an XQJ connection is available and can be used with XQJ. For example:

    XQPreparedExpression xqj_p = xqj_c.prepareExpression("fn:doc(’foo.xml’)//abc");

  3. To make a connection available again for pooling, you must close the JDBC connection, not the XQJ connection. For example:

    4. jdbc_c.close()

For information about how to configure DataDirect XQuery to use connection pooling in the supported application server environments, go to:

http://www.datadirect.com/developer/data-integration/tutorials/examples/connection-pooling/index.ssp

Example of Servlet Using Connection Pooling

The following code is a fully functional example for a Java Servlet. 

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 javax.xml.xquery.XQConnection; 
import javax.xml.xquery.XQException; 
import javax.xml.xquery.XQExpression; 
import javax.xml.xquery.XQSequence; 
 
import com.ddtek.xquery.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( 
              "   <b>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) {} 
    } 
  } 
}