Specifying a Database Connection

If your queries access relational data, you need to register the database connection with the Java servlet container. This section describes two different ways to do this:

• Specifying a Single Connection
• Database Connection Pooling

Specifying a Single Connection

You can use the Web Application configuration file (web.xml) to specify the database connection, as shown in this example, which 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.

Connections specified this way are created and then discarded with each request. 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 the pool when it is no longer neeed, 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 exmaple. 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, and JBoss, for example).

Creating a Connection Pool

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

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

  <?xml version="1.0" encoding="ISO-8859-1"?>
  <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 you 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, and IBM WebSphere Application Server. You can learn more about setting up connection pools for these servers here: http://www.datadirect.com/developer/xquery/xquery-examples/
connection-pooling/index.ssp.

Next Steps

Once you've 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. We'll discuss two popular technologies — SOAP and REST — in the following section.