Specifying a Database Connection

---

If your queries access relational data, you need to register the database connection with the Java servlet container. The XQueryWebService supports

Single database connections
Database connection pooling

Specifying a Single Connection

You can use the Web Application configuration file (web.xml) to specify the database connection. This example specifies a connection to Microsoft SQL Server:

<init-param> 
  <param-name>DDXQJDBCConnection1</param-name> 
  <param-value>jdbc:xquery:sqlserver://localhost;user=sa;DatabaseName=pubs 
               </param-value> 
</init-param>  

The name of the <param-name> element can be any string you like.

Single database connections are created and then discarded with each request, which can add to an application’s processing overhead. A more efficient technique is database connection pooling, which is discussed next.

Database Connection Pooling

Connection pooling is a technique for specifying database connections that allows a Web application to create a database connection on demand, and then return it to a pool when it is no longer need, rather than discarding it. Connection pooling can improve response time and help preserve database resources. Web server requests are locked if no connection is available in the pool.

Another benefit of using connection pooling is that it allows for connection recovery in the event that the connection is lost – if the server times out, for example. Dropped or disrupted connections are automatically replaced once the server is returned to service.

All popular Java servlet containers offer a connection pooling framework, and DataDirect XQuery can be plugged into most of them (Apache Tomcat, BEA WebLogic, IBM WebSphere, JBoss, and Oracle for example). See Using Connection Pooling for more information.

Creating a Connection Pool – Example

Here's how to create a connection pool in Apache Tomcat:

Create a META-INF directory under the \employee-lookup directory (<Tomcat_dir>\webapps\employee-lookup
\META-INF, for example).
Place the following configuration file, context.xml, in that directory:

<Context path="/employee-lookup" docBase="employee-lookup" 
         crossContext="false" reloadable="true" debug="0"> 
 
<Resource name="jdbc/employee-lookup" 
          auth="Container" 
          type="javax.sql.DataSource" 
          username="root" 
          password="sa" 
          driverClassName="com.ddtek.xquery3.jdbc.XQueryDriver" 
          url="jdbc:datadirect:xquery3://JdbcUrl= 
               {jdbc:mysql://localhost:3306/pubs_dbo?}" 
          initialSize="1" 
          accessToUnderlyingConnectionAllowed="true" 
          validationQuery="SELECT * FROM users"/> 
</Context> 
 

Note that the name= attribute of the <Resource> element has to match the <res-ref-name> element (here it is "jdbc/employee-lookup") in the web.xml configuration file we described previously. This is the name that the Java servlet uses to perform the Java Naming and Directory Interface (JNDI) lookup required to retrieve the connection pool.

Creating a Connection Pool for Other Servers

As mentioned previously, DataDirect XQuery supports several other servers in addition to Tomcat, including JBoss, BEA WebLogic, IBM WebSphere Application Server, and Oracle Application Server. You can learn more about support for connection pools for these servers here: Using Connection Pooling.

Next Steps

Once you have specified your relational database connection, you can start to think about the technology you want to use to access the Web services based on your XQuery. Two popular technologies – SOAP and REST – are described in the following section.