Defining the Database Connection Pools

Each master index application requires two database connection pools; one for the master controller and one for the sequence manager. To set up the connection pools, you need to create the connection pools and then define a JDBC resource for each. This section provides general instructions for setting up the connection pools. For more information about the procedures in this section, see the online help provided with the GlassFish Admin Console.

Perform the following steps to define database connectivity through the application server:

• Step 1: Add the MySQL or Oracle Driver to the Application Server

• Step 2: Create two JDBC Connection Pools

• Step 3: Create the JDBC Resources

Step 1: Add the MySQL or Oracle Driver to the Application Server

If you are using a MySQL or Oracle database, you need to manually install or copy the database driver to the application server environment. If you are using a SQL Server database, you can skip this step.

For Oracle, you can either install the driver on the application server or copy the ojdbc14.jar file from your Oracle client installation (Oracle_client\jdbc\lib) to app_server_home\lib. To install the driver, see the documentation for the GlassFish server.

For MySQL, download and extract the latest MySQL Connector/J — for connecting to MySQL from Java. You can access the driver at the MySQL downloads page. Copy mysql-connector-java-5.1.6-bin.jar to app_server_home\lib.

Once the driver is installed or copied, continue to Step 2: Create two JDBC Connection Pools.

Step 2: Create two JDBC Connection Pools

The JDBC connection pools provide connections to the master index database. You need to create two connection pools that are configured in the same way.

Before proceeding, make sure you have the relevant information about the master index database (such as the database name, URL, and administrator login credentials).

To Create the JDBC Connection Pools

Before You Begin

If you are using an Oracle or MySQL database, add the database driver to the application server environment, as described in Step 1: Add the MySQL or Oracle Driver to the Application Server.

1. Log in to the GlassFish server Admin Console.

   You can access the console from the Services window in NetBeans.

2. In the left portion of the Admin Console, expand Resources, expand JDBC, and then select Connection Pools.
3. On the Create Connection Pool page, click New.
4. In the Name field, enter a name for the connection pool.
5. In the Resource Type field, select the Java class for the type of transactions the master index application processes.

   • javax.sql.DataSource – Use this class if the master index application is using local transactions only.

   • javax.sql.XADataSource – Use this class if the master index application transactions are distributed, either within the application or across applications.

   • javax.sql.ConnectionPoolDataSource – Use this class if the master index application is using local transactions only. This class provides possible performance improvements.

6. In the Database Vendor field, select the database platform used by the master index database.
7. Click Next.
8. In the DataSource Classname field, accept the default class or enter a new one to use.
9. Modify the Pool Settings properties according to your business practices.
10. Modify the Connection Validation properties according to your business practices.
11. Modify the Transaction properties according to whether the application supports transactional processing.

    ---

    Note - Make sure you configure Transaction properties to match the transaction mode you specified for the master index application and the connection pool Resource Type you selected.

    ---

12. In the additional properties section, enter the values for the master index database. Be sure to enter the following information at a minimum (you might need to create some of these properties).
    • For Oracle:

      • URL – The URL that points to the database. The syntax of the URL is jdbc:oracle:thin:@host:port:database_name.

        ---

        Note - If you are using a third-party JDBC driver, refer to the documentation for that driver for information about the URL.

        ---

      • user – The login ID for the user you created in Step 2: Create a Master Index Database and User.

      • password – The password for the above user.

      • ImplicitCachingEnabled – An indicator of whether implicit statement caching is enabled. Set this property to true.

      • MaxStatements – The maximum number of statements in the cache. Set this property to 1000.

    • For MySQL:

      • URL – The URL that points to the database. The syntax of the URL is jdbc:mysql://server:port/database_name.

        ---

        Note - If you are using a third-party JDBC driver, refer to the documentation for that driver for information about the URL.

        ---

      • user – The login ID for the user you created in Step 2: Create a Master Index Database and User.

      • password – The password for the above user.

      • DatabaseName – The name of the database.

    • For Microsoft SQL Server:

      • databaseName – The name of the database.

      • portNumber – The port number for the database.

      • serverName – The name of the server that is hosting the database.

      • user – The login ID for the user you created in Step 2: Create a Master Index Database and User.

      • password – The password for the above user.

      • SendStringParametersAsUnicdoe – An indicator of whether string parameters are sent to the database in Unicode or in the default character encoding of the database. Set this property to false.

      • MaxPooledStatements – The maximum number of prepared statements in the cache. Set this property to 1000.

13. Follow the previous steps again to create another connection pool for the sequence manager.
14. Continue to Step 3: Create the JDBC Resources.

Step 3: Create the JDBC Resources

A JDBC resource (also known as a data source) gives the master index application the ability to connect to the database. Two JDBC resources are required.

To Create the JDBC Resources

Before You Begin

Create the JDBC connection pools, as described in Step 2: Create two JDBC Connection Pools.

1. In the left portion of the Admin Console, expand Resources, expand JDBC, and then select JDBC Resources.
2. On the Create JDBC Resource page, click New.
3. In the JNDI Name field, enter a unique name for the JDBC resource.

   The name must be in the form jdbc/application_nameDataSource, where application_name is the name of the master index application. For example, jdbc/PersonDataSource.

4. In the Pool Name field, enter the name of the first JDBC connection pool you created in Step 2: Create two JDBC Connection Pools.
5. (Optional) In the Description field, enter a brief description of the resource.
6. In the Status field, select the Enabled check box.
7. Click OK.
8. Repeat the previous steps to create a JDBC resource for the sequence manager with these guidelines:

   • In the Pool Name field, enter the name of the second JDBC connection pool you created.

   • The name of the JDBC resource must be in the form jdbc/application_nameSequenceDataSource, where application_name is the name of the master index application. For example, jdbc/PersonSequenceDataSource.