Working with the Database Connections Pool

An entity bean using CMP connects to databases via a JDBC connection pool. To prepare for database access, this pool must first be created and deployed to Geronimo.

Geronimo uses TranQL to provide database connection pooling. You must specify the following via a deployment plan:

• The database driver to use when accessing the database

• The database name, username, login password

• Other connections pool configuration parameters

The deployment plan is a JCA resource adapter deployment plan, typically called geronimo-ra.xml (the name you should use if you include the plan in an EAR under the META-INF directory). You can either handcraft this plan, or use the Web Console Wizard to create one.

The following section shows how to use the Web console to create a plan for deploying a database pool.

To create the AuthorConnectionsPool, first select Services→Database Pools from the menu. Then, click the Using the Geronimo Database Wizard link to create a database pool. The first screen of this wizard is shown in Figure 12-8.

Figure 12-8: Using the wizard to create the AuthorConnectionsPool database pool

Enter the name of the database pool as shown in Figure 12-8, and select Derby embedded as the database type. Click Next to select the driver to use and supply additional configuration information. You will be greeted with the driver selection screen, as shown in Figure 12-9.

Figure 12-9: Setting driver and parameters for the AuthorConnectionsPool

Select the driver as shown in Figure 12-9. The displayed drivers are the ones known to this wizard, including many that already have code built into the default Geronimo repository. You can also download a driver into the repository by clicking the Download a Driver button.

In this case, you are using the embedded Derby driver already in the repository. Use APP for both the DB user and password. This user is one already configured for the Derby database.

Enter wroxauthors into the Database: field. Click Next. On the final screen, accept everything by default and click the Test Connection button. This will test the connection pool you’ve just created. After testing, you can click the Show Plan button. This will display the resource adapter deployment plan that you can use to deploy this database connection pool, as shown in Figure 12-10.

Figure 12-10: Displaying the deployment plan for the deployment of AuthorConnectionsPool

The generated deployment plan is shown in the following listing:

 <?xml version="1.0" encoding="UTF-8"?> <connector xmlns="http://geronimo.apache.org/xml/ns/j2ee/connector-1.1">     <dep:environment xmlns:dep="http://geronimo.apache.org/xml/ns/deployment-1.1">         <dep:moduleId>             <dep:groupId>console.dbpool</dep:groupId>             <dep:artifactId>AuthorConnectionsPool</dep:artifactId>             <dep:version>1.0</dep:version>             <dep:type>rar</dep:type>         </dep:moduleId>         <dep:dependencies>             <dep:dependency>                 <dep:groupId>org.apache.derby</dep:groupId>                 <dep:artifactId>derby</dep:artifactId>                 <dep:version>10.1.1.0</dep:version>                 <dep:type>jar</dep:type>             </dep:dependency>         </dep:dependencies>     </dep:environment>      <resourceadapter>         <outbound-resourceadapter>             <connection-definition>                 <connectionfactory- interface>javax.sql.DataSource</connectionfactory-interface>                 <connectiondefinition-instance>                     <name>AuthorConnectionsPool</name>                     <config-property-setting name="Password">APP</config-property- setting>                     <config-property-setting name="Driver">org.apache.derby.jdbc.EmbeddedDriver</config-property-setting>                     <config-property-setting name="UserName">APP</config-property- setting>                     <config-property-setting name="ConnectionURL">jdbc:derby:wroxauthors</config-property-setting>                     <connectionmanager>                         <local-transaction/>                         <single-pool>                             <max-size>10</max-size>                             <min-size>0</min-size>                             <match-one/>                         </single-pool>                     </connectionmanager>                 </connectiondefinition-instance>             </connection-definition>         </outbound-resourceadapter>     </resourceadapter> </connector>

At this point, you can either save the deployment plan in a file and include it as geronimo-ra.xml under the META-INF directory in the EAR file or you can click the Deploy Pool button now. If you include the plan in the EAR, the database pool will be deployed with the application - and undeployed when you undeploy the application’s configuration.

If you click the Deploy Pool button, the database pool will be deployed immediately and ready for use by any application running on the server instance.

You will see more examples of resource adapter deployment plans (and explanations of the elements used in this plan) in Chapter 13.

After deploying the database pool, you will see it listed in the Database Pools portlet, as shown in Figure 12-11.

Figure 12-11: Successful deployment of the AuthorConnectionsPool

Trying Out the Example

Deploy the authorscmp2.ear to Geronimo, and try out the URL:

http://localhost:8080/progeron/authors.cgi

You should see the list of authors displayed as in Chapter 2. However, this time, the data is retrieved via the session bean querying the CMP entity bean - fetching data from the Derby database. Figure 12-12 shows the list.

Figure 12-12: The example running with data from Derby RDBMS

If you change any deployment descriptor, remember to rebuild the EAR file before redeploying the application. This can be accomplished using the following Ant command:

 ant ear

The ear target in the build.xml file will take care of compiling and assembling the EAR file after your modifications.