6.5. Using a Database Pool

6.5. Using a Database Pool
	Chapter 6. Database Configuration [DRAFT (1.1)]

There are two steps necessary in order for an application module to use a database connection pool. First, the deployment information for the module must declare a resource reference and map it to a specific connection pool available to that module. Second, the module's code must look up the connection pool in JNDI.

	Note
	The procedure described here uses J2EE 1.4 deployment descriptor syntax. The same mapping could be done with earlier versions, but the deployment descriptor file format would be somewhat different.

6.5.1. Updating the Module's Deployment Information

Once the database pool has been deployed, application modules can refer to it using the name specified in the configuration information. To do this, the application module first declares a resource-ref with a type of javax.sql.DataSource in its standard J2EE deployment descriptor. For example, a web application could do it like this:

WEB-INF/web.xml

<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
         http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
         version="2.4">
  ...
  <resource-ref>
    <res-ref-name>jdbc/DataSource</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
  </resource-ref>
</web-app>

The key elements here are:

resource-ref: This block holds all the configuration information for a single database pool reference.
res-ref-name: A JNDI name fragment, which also acts as a unique identifier for this resource reference. When an application component looks in JNDI, it will find the resource under jdbc:comp/env/ followed by the value specified here (in this case, jdbc:comp/env/jdbc/DataSource).
res-type: Should always be javax.sql.DataSource for a database pool.
res-auth: If set to Container, the server will handle logging in to the database with the username and password configured for the connection pool. If set to Application, the application must specify a username and password every time it gets a connection from the pool.
res-sharing-scope: If multiple components in the same transaction attempt to use database connections from the same pool, this value controls whether they will all get the same connection (Shareable) or they will each get a different connection (Unshareable). The default value of Shareable is usually preferable, though it may not work if one component will change connection settings such as the isolation level or localization (and you don't want other components to be affected by that).

Now in the Geronimo deployment plan for that module, you'll specify which connection pool in the server will be used to satisfy the data source required by the web module. A deployment plan that maps it to the database pool configured in Example 6.1, “Database Pool Deployment Plan” would look like this:

WEB-INF/geronimo-web.xml

<web-app xmlns="http://geronimo.apache.org/xml/ns/j2ee/web-1.1">
    ...
    <resource-ref
     xmlns:naming="http://geronimo.apache.org/xml/ns/naming-1.1">
        <ref-name>jdbc/DataSource</ref-name>
        <resource-link>MySQLDataSource</resource-link>
    </resource-ref>
</web-app>

The important elements here are:

resource-ref: Holds the Geronimo information that maps a resource reference to a specific database pool.
ref-name: Matches the res-ref-name in the J2EE deployment descriptor, and identifies which resource reference this configuration block applies to.
resource-link: Identifies the database connection pool that will be used to fulfill requests to the resource named by the ref-name element. To identify the pool, the value specified here must match the name specified in the database pool deployment information.

With the resource-ref in the web.xml deployment descriptor and the resource-ref in the geronimo-web.xml deployment plan, if a servlet or other component of the web application looks in JNDI under java:comp/env/jdbc/DataSource, then it will find a javax.sql.DataSource that uses connections to the PostgreSQL database configured in Example 6.1, “Database Pool Deployment Plan”.

An EJB or Application Client could configure a database pool using the same resource reference elements shown above, though of course the rest of the deployment descriptor would look different.

	Tip
	This section did not cover all the options available in Geronimo deployment plans for the different application modules. For full details on customizing application modules, see Chapter 11, Web Applications (WARs) [DRAFT (1.1)], Chapter 12, Enterprise Java Beans (EJB JARs) [DRAFT (1.0)], and Chapter 14, Client Applications (Client JARs) [IN PROGRESS 1.0].

6.5.2. Application Code

The application code necessary to access a database connection pool looks like this:

InitialContext ctx = new InitialContext();
DataSource ds = ctx.lookup("java:comp/env/jdbc/DataSource");
Connection con = ds.getConnection();

The JNDI location used to look up the pool is "java:comp/env/" followed by the res-ref-name specified for the resource-ref in the J2EE deployment descriptor. In Section 6.5.1, “Updating the Module's Deployment Information”, the res-ref-name was jdbc/DataSource so here we use the full JNDI location "java:comp/env/jdbc/DataSource".

Since the res-auth in the web.xml deployment descriptor was set to Container, the application code does not specify a username or password to use to connect to the database. If the res-auth had been set to Application, you'd pass a username and password to getConnection(), but be aware that using a variety of usernames for the database connections makes the pooling less efficient.

	Note
	Geronimo does not support a "global JNDI space" where all resource are listed. The only portable way for an application to access a database pool is to use a resource reference and access the pool through the component's local `java:comp/env/` namespace as is described here.


6.4. Manual Configuration and Deployment		6.6. Reconfiguring a Previously Deployed Pool