Sun Java System Web Server 7.0 Update 5 Administrator's Guide

Managing JDBC Connection Pools

Configuring JDBC Connection Pool

In Web Server 7.0, JDBC Connection Pools are configured through JDBC resource elements. The simplest connection pool can be configured by following the steps listed below. In this example, the connection pool will use the Oracle JDBC driver.

ProcedureTo Create a JDBC Connection Pool

  1. Start wadm.

  2. Create a JDBC Resource with the basic configuration.

    Other attributes are available to fine tune the connection pool. Refer to the Manual Pages for more attributes and examples.


    wadm> create-jdbc-resource --config=test 
    --datasourceclass=oracle.jdbc.pool.OracleDataSource jdbc/MyPool
  3. Configure Vendor Specific Properties.

    Properties are used to configure the driver's vendor specific properties. In the example below the properties url, user and password are added to the JDBC resource.


    wadm> add-jdbc-resource-userprop --config=test --jndi-name=jdbc/MyPool 
    url=jdbc:oracle:thin:@hostname:1521:MYSID user=myuser password=mypassword
  4. Enable Connection Validation.

    Connection validation can be enabled for the pool. If this option is used, connections will be validated before they are passed to the application. This enables the web server to automatically re-establish database connections in the case of the database becoming unavailable due to network failure or database server crash. Validation of connections will incur additional overhead and slightly reduce performance.


    wadm> set-jdbc-resource-prop --config=test --jndi-name=jdbc/MyPool 
    connection-validation-table-name=test connection-validation=table
  5. Change Default Pool Settings.

    In this example, change the maximum number of connections.


    wadm> set-jdbc-resource-prop --config=test --jndi-name=jdbc/MyPool 
    max-connections=100
  6. Deploy the Configuration.


    wadm> deploy-config test
  7. Provide the Jar Files Containing the JDBC driver.

    The server needs to be provided with the classes that implement the driver. This can be done in two ways:

    • Copy the driver's jar file into the server instance lib directory. This is the simplest way, as the jar files included in the instance lib directory will be automatically loaded and available to the server.

    • Modify the JVM's class-path-suffix to include the JDBC driver's jar file.


      wadm> set-jvm-prop  --config=test class-path-suffix=/export/home/lib/classes12.jar
  8. Usage in Web Applications.

    • Modifying WEB-INF/web.xml.


      <web-app>
      ...
        <resource-ref>
          <description>JDBC Connection Pool</description>
          <res-ref-name>jdbc/myJdbc</res-ref-name>
          <res-type>javax.sql.DataSource</res-type>
          <res-auth>Container</res-auth>
        </resource-ref>
      ...
      </web-app>
    • Modifying WEB-INF/sun-web.xml.


      <sun-web-app>
      ...
        <resource-ref>
          <res-ref-name>jdbc/myJdbc</res-ref-name>
          <jndi-name>jdbc/MyPool</jndi-name>
        </resource-ref>
      ...
      </sun-web-app>
    • Using the Connection Pool.


      Context initContext = new InitialContext();
          Context webContext = (Context)context.lookup("java:/comp/env");
      
          DataSource ds = (DataSource) webContext.lookup("jdbc/myJdbc");
          Connection dbCon = ds.getConnection();