Chapter 2 JDBC Resources

A JDBC resource (data source) provides applications with a means of connecting to a database. Typically, the administrator creates a JDBC resource for each database accessed by the applications deployed in a domain. (However, more than one JDBC resource can be created for a database.)

This chapter contains the following sections:

• About JDBC Resources

• About JDBC Connection Pools

• How JDBC Resources and Connection Pools Work Together

• Configurations for Specific JDBC Drivers

About JDBC Resources

To store, organize, and retrieve data, most applications use relational databases. Java EE applications access relational databases through the JDBC API.

Creating a JDBC Resource

To create a JDBC resource, specify a unique JNDI name that identifies the resource. Expect to find the JNDI name of a JDBC resource in java:comp/env/jdbc subcontext. For example, the JNDI name for the resource of a payroll database could be java:comp/env/jdbc/payrolldb. Because all resource JNDI names are in the java:comp/env subcontext, when specifying the JNDI name of a JDBC resource in the Admin Console, enter only jdbc/name. For example, for a payroll database specify jdbc/payrolldb.

To create a JDBC resource using the Admin Console, select Resources > JDBC Resources. Specify the resources settings as follows:

• JNDI Name: Specify a unique name. The JNDI name organizes and locates components within a distributed computing environment similarly to the way that card catalogs organize and represent locations of books in a library. Consequently, the JNDI name becomes an important method of accessing the JDBC resource. By convention, the name begins with the jdbc/ string. For example: jdbc/payrolldb. Don’t forget the forward slash.

• Pool Name: Choose the connection pool to be associated with the new JDBC resource.

• Description: Type a short description of the resource.

• Status: If you want the resource to be unavailable, deselect the Enabled checkbox. By default, the resource is available (enabled) as soon as it is created.

About JDBC Connection Pools

To create a JDBC resource, specify the connection pool with which it is associated. Multiple JDBC resources can specify a single connection pool.

A JDBC connection pool is a group of reusable connections for a particular database. Because creating each new physical connection is time consuming, the server maintains a pool of available connections to increase performance. When an application requests a connection, it obtains one from the pool. When an application closes a connection, the connection is returned to the pool.

Creating a JDBC Connection Pool

When creating a connection pool , you are actually defining the aspects of a connection to a specific database. Before creating the pool, you must first install and integrate the JDBC driver. The properties of connection pools can vary with different database vendors. Some common properties are the database’s name (URL), user name, and password.

Certain data specific to the JDBC driver and the database vendor must be entered. Before proceeding, gather the following information:

• Database vendor name

• Resource type, such as javax.sql.DataSource (local transactions only) javax.sql.XADataSource (global transactions)

• Data source class name: If the JDBC driver has a Datasource class for the resource type and database, then the value of the Datasource Classname field is required.

• Required properties, such as the database name (URL), user name, and password

How JDBC Resources and Connection Pools Work Together

To store, organize, and retrieve data, most applications use relational databases. Java EE applications access relational databases through the JDBC API. Before an application can access a database, it must get a connection.

At runtime, here’s what happens when an application connects to a database:

1. The application gets the JDBC resource (data source) associated with the database by making a call through the JNDI API.

   Given the resource’s JNDI name, the naming and directory service locates the JDBC resource. Each JDBC resource specifies a connection pool.

2. Via the JDBC resource, the application gets a database connection.

   Behind the scenes, the application server retrieves a physical connection from the connection pool that corresponds to the database. The pool defines connection attributes such as the database name (URL), user name, and password.

3. Now that it’s connected to the database, the application can read, modify, and add data to the database.

   The applications access the database by making calls to the JDBC API. The JDBC driver translates the application’s JDBC calls into the protocol of the database server.

4. When it’s finished accessing the database, the application closes the connection.

   The application server returns the connection to the connection pool. Once it’s back in the pool, the connection is available for the next application.

Configurations for Specific JDBC Drivers

Application Server is designed to support connectivity to any database management system with a corresponding JDBC driver. The following JDBC driver and database combinations are supported. These combinations have been tested with Application Server and are found to be Java EE compatible. They are also supported for CMP.

• Java DB Type 4 Driver

• Sun Java System JDBC Driver for DB2 Databases

• Sun Java System JDBC Driver for Oracle 9i and 10g Databases

• Sun Java System JDBC Driver for Microsoft SQL Server Databases

• Sun Java System JDBC Driver for Sybase Databases

• IBM DB2 8.1 and 8.2 Type 2 Driver

• Oracle Thin Type 4 Driver for Oracle 9i and 10g Databases

• Microsoft SQL Server JDBC Driver

• PostgreSQL JDBC Driver

• MM MySQL Type 4 Driver (Non-XA)

For an up to date list of currently supported JDBC drivers, see the Sun Java System Application Server Platform Edition 9 Release Notes.

Other JDBC drivers can be used with Application Server , but Java EE compliance tests have not been completed with these drivers. Although Sun offers no product support for these drivers, Sun offers limited support of the use of these drivers with Application Server .

• MM MySQL Type 4 Driver (XA Only)

• JConnect Type 4 Driver for Sybase ASE 12.5 and 15 Databases

• Inet Oraxo JDBC Driver for Oracle 9i and 10g Databases

• Inet Merlia JDBC Driver for Microsoft SQL Server Databases

• Inet Sybelux JDBC Driver for Sybase Databases

• OCI Oracle Type 2 Driver for Oracle 9i and 10g Databases

• IBM Informix Type 4 Driver

---

Note –

An Oracle database user running the capture-schema command needs ANALYZE ANY TABLE privileges if that user does not own the schema. These privileges are granted to the user by the database administrator. For information about capture-schema, see Sun Java System Application Server Platform Edition 9 Reference Manual.

---

Java DB Type 4 Driver

The Java DB database is based on the Derby database from Apache. The Java DB JDBC driver is included with the Application Server by default.

The JAR file for the Java DB driver is derbyclient.jar.

Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: JavaDB

• DataSource Classname: Specify one of the following:

  org.apache.derby.jdbc.ClientDataSource
  org.apache.derby.jdbc.ClientXADataSource

• Properties:

  • user - Specify the database user.

    This is only necessary if Java DB is configured to use authentication. Java DB does not use authentication by default. When the user is provided, it is the name of the schema where the tables reside.

  • password - Specify the database password.

    This is only necessary if Java DB is configured to use authentication.

  • databaseName - Specify the name of the database.

  • serverName - Specify the host name or IP address of the database server.

  • portNumber - Specify the port number of the database server if it is different from the default.

• URL: jdbc:derby://serverName:portNumber/databaseName;create=true

  Include the ;create=true part only if you want the database to be created if it does not exist.

Sun Java System JDBC Driver for DB2 Databases

The JAR files for this driver are smbase.jar, smdb2.jar, and smutil.jar. Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: DB2

• DataSource Classname: com.sun.sql.jdbcx.db2.DB2DataSource

• Properties:

  • serverName - Specify the host name or IP address of the database server.

  • portNumber - Specify the port number of the database server.

  • databaseName - Set as appropriate.

  • user - Set as appropriate.

  • password - Set as appropriate.

• URL: jdbc:sun:db2://serverName:portNumber;databaseName=databaseName

Sun Java System JDBC Driver for Oracle 9i and 10g Databases

The JAR files for this driver are smbase.jar, smoracle.jar, and smutil.jar. Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: Oracle

• DataSource Classname: com.sun.sql.jdbcx.oracle.OracleDataSource

• Properties:

  • serverName - Specify the host name or IP address of the database server.

  • portNumber - Specify the port number of the database server.

  • SID - Set as appropriate.

  • user - Set as appropriate.

  • password - Set as appropriate.

• URL: jdbc:sun:oracle://serverName[:portNumber][;SID=databaseName]

Sun Java System JDBC Driver for Microsoft SQL Server Databases

The JAR files for this driver are smbase.jar, smsqlserver.jar, and smutil.jar. Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: mssql

• DataSource Classname: com.sun.sql.jdbcx.sqlserver.SQLServerDataSource

• Properties:

  • serverName - Specify the host name or IP address and the port of the database server.

  • portNumber - Specify the port number of the database server.

  • user - Set as appropriate.

  • password - Set as appropriate.

  • selectMethod - Set to cursor.

• URL: jdbc:sun:sqlserver://serverName[:portNumber]

Sun Java System JDBC Driver for Sybase Databases

The JAR files for this driver are smbase.jar, smsybase.jar, and smutil.jar. Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: Sybase

• DataSource Classname: com.sun.sql.jdbcx.sybase.SybaseDataSource

• Properties:

  • serverName - Specify the host name or IP address of the database server.

  • portNumber - Specify the port number of the database server.

  • databaseName - Set as appropriate. This is optional.

  • user - Set as appropriate.

  • password - Set as appropriate.

• URL: jdbc:sun:sybase://serverName[:portNumber]

IBM DB2 8.1 and 8.2 Type 2 Driver

The JAR files for the DB2 driver are db2jcc.jar, db2jcc_license_cu.jar, and db2java.zip. Set environment variables as follows:

LD_LIBRARY_PATH=/usr/db2user/sqllib/lib:${j2ee.home}/lib
DB2DIR=/opt/IBM/db2/V8.1
DB2INSTANCE=db2user
INSTHOME=/usr/db2user
VWSPATH=/usr/db2user/sqllib
THREADS_FLAG=native

Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: DB2

• DataSource Classname: com.ibm.db2.jcc.DB2SimpleDataSource

• Properties:

  • user - Set as appropriate.

  • password - Set as appropriate.

  • databaseName - Set as appropriate.

  • driverType - Set to 2.

  • deferPrepares - Set to false.

Oracle Thin Type 4 Driver for Oracle 9i and 10g Databases

The JAR file for the Oracle driver is ojdbc14.jar. Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: Oracle

• DataSource Classname:Specify one of the following:

  oracle.jdbc.pool.OracleDataSource
  oracle.jdbc.xa.client.OracleXADataSource

• Properties:

  • user - Set as appropriate.

  • password - Set as appropriate.

  • URL - Specify the complete database URL using the following syntax:

    jdbc:oracle:thin:[user/password]@host[:port]/service
    

    For example:

    jdbc:oracle:thin:@localhost:1521:customer_db

  • xa-driver-does-not-support-non-tx-operations - Set to the value true. Optional: only needed if both non-XA and XA connections are retrieved from the same connection pool. Might degrade performance.

    As an alternative to setting this property, you can create two connection pools, one for non-XA connections and one for XA connections.

  ---

  Note –

  You must set the oracle-xa-recovery-workaround property in the Transaction Service for recovery of global transactions to work correctly. For details, see Workarounds for Specific Databases.

  When using this driver, it is not possible to insert more than 2000 bytes of data into a column. To circumvent this problem, use the OCI driver (JDBC type 2).

  ---

Microsoft SQL Server JDBC Driver

The JAR file for this driver is sqljdbc.jar. Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: mssql

• DataSource Classname: Specify one of the following:

  com.microsoft.sqlserver.jdbc.SQLServerDataSource
  com.microsoft.sqlserver.jdbc.SQLServerXADataSource

• Properties:

  • serverName - Specify the host name or IP address and the port of the database server. This is optional. This could be a DNS or IP address, or it could be localhost or 127.0.0.1 for the local computer.

  • instanceName - Specify the instance to connect to on serverName. This is optional. If not specified, a connection to the default instance is made.

  • portNumber - Specify the port number of the database server. This is optional. The default is 1433. For optimal connection performance, set the portNumber when connecting to a named instance. This avoids a round trip to the server to determine the port number.

  • user - Set as appropriate.

  • password - Set as appropriate.

  • selectMethod - Set to cursor.

  • Additional properties - For additional optional properties you can set, see Setting the Connection Properties.

• URL: jdbc:sqlserver://[serverName][\instanceName][:portNumber][;property=value]

PostgreSQL JDBC Driver

The JAR file for the PostgreSQL driver is postgresql-version.jdbc3.jar, for example, postgresql-8.2dev-500.jdbc3.jar. For more information, see http://jdbc.postgresql.org. Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: PostgreSQL

• DataSource Classname: org.postgresql.ds.PGSimpleDataSource

• Properties:

  • serverName - Specify the host name or IP address of the database server.

  • portNumber - Specify the port number of the database server.

  • databaseName - Set as appropriate.

  • user - Set as appropriate.

  • password - Set as appropriate.

MM MySQL Type 4 Driver (Non-XA)

The JAR file for the MySQL driver is mysql-connector-java-version-bin-g.jar, for example, mysql-connector-java-3.1.12-bin-g.jar. Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: mysql

• DataSource Classname: Specify one of the following:

  com.mysql.jdbc.jdbc2.optional.MysqlDataSource

• Properties:

  • serverName - Specify the host name or IP address of the database server.

  • port - Specify the port number of the database server.

  • user - Set as appropriate.

  • password - Set as appropriate.

  • databaseName - Set as appropriate.

  • URL - If you are using global transactions, you can set this property instead of serverName, port, and databaseName.

    The MM MySQL Type 4 driver doesn’t provide a method to set the required relaxAutoCommit property, so you must set it indirectly by setting the URL property:

    jdbc:mysql://host:port/database?relaxAutoCommit="true"

MM MySQL Type 4 Driver (XA Only)

The JAR file for the MySQL driver is mysql-connector-java-version-bin-g.jar, for example, mysql-connector-java-3.1.12-bin-g.jar. Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: mysql

• DataSource Classname: Specify one of the following:

  com.mysql.jdbc.jdbc2.optional.MysqlXADataSource

• Properties:

  • serverName - Specify the host name or IP address of the database server.

  • port - Specify the port number of the database server.

  • user - Set as appropriate.

  • password - Set as appropriate.

  • databaseName - Set as appropriate.

  • URL - If you are using global transactions, you can set this property instead of serverName, port, and databaseName.

    The MM MySQL Type 4 driver doesn’t provide a method to set the required relaxAutoCommit property, so you must set it indirectly by setting the URL property:

    jdbc:mysql://host:port/database?relaxAutoCommit="true"

JConnect Type 4 Driver for Sybase ASE 12.5 and 15 Databases

The JAR file for the Sybase driver is jconn2.jar. Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: Sybase

• DataSource Classname:Specify one of the following:

  com.sybase.jdbc2.jdbc.SybDataSource
  com.sybase.jdbc2.jdbc.SybXADataSource

• Properties:

  • serverName - Specify the host name or IP address of the database server.

  • portNumber - Specify the port number of the database server.

  • user - Set as appropriate.

  • password - Set as appropriate.

  • databaseName - Set as appropriate. Do not specify the complete URL, only the database name.

  • BE_AS_JDBC_COMPLIANT_AS_POSSIBLE - Set to true.

  • FAKE_METADATA - Set to true.

Inet Oraxo JDBC Driver for Oracle 9i and 10g Databases

The JAR file for the Inet Oracle driver is Oranxo.jar. Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: Oracle

• DataSource Classname: com.inet.ora.OraDataSource

• Properties:

  • user - Specify the database user.

  • password - Specify the database password.

  • serviceName - Specify the URL of the database. The syntax is as follows:

    jdbc:inetora:server:port:dbname
    

    For example:

    jdbc:inetora:localhost:1521:payrolldb

    In this example,localhost is the host name of the machine running the Oracle server, 1521 is the Oracle server’s port number, and payrolldb is the SID of the database. For more information about the syntax of the database URL, see the Oracle documentation.

  • serverName - Specify the host name or IP address of the database server.

  • port - Specify the port number of the database server.

  • streamstolob - If the size of BLOB or CLOB data types exceeds 4 KB and this driver is used for CMP, this property must be set to true.

  • xa-driver-does-not-support-non-tx-operations - Set to the value true. Optional: only needed if both non-XA and XA connections are retrieved from the same connection pool. Might degrade performance.

    As an alternative to setting this property, you can create two connection pools, one for non-XA connections and one for XA connections.

Inet Merlia JDBC Driver for Microsoft SQL Server Databases

The JAR file for the Inet Microsoft SQL Server driver is Merlia.jar. Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: mssql

• DataSource Classname: com.inet.tds.TdsDataSource

• Properties:

  • serverName - Specify the host name or IP address and the port of the database server.

  • port - Specify the port number of the database server.

  • user - Set as appropriate.

  • password - Set as appropriate.

Inet Sybelux JDBC Driver for Sybase Databases

The JAR file for the Inet Sybase driver is Sybelux.jar. Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: Sybase

• DataSource Classname: com.inet.syb.SybDataSource

• Properties:

  • serverName - Specify the host name or IP address of the database server.

  • portNumber - Specify the port number of the database server.

  • user - Set as appropriate.

  • password - Set as appropriate.

  • databaseName - Set as appropriate. Do not specify the complete URL, only the database name.

OCI Oracle Type 2 Driver for Oracle 9i and 10g Databases

The JAR file for the OCI Oracle driver is ojdbc14.jar. Make sure that the shared library is available through LD_LIBRARY_PATH and that the ORACLE_HOME property is set. Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: Oracle

• DataSource Classname:Specify one of the following:

  oracle.jdbc.pool.OracleDataSource
  oracle.jdbc.xa.client.OracleXADataSource

• Properties:

  • user - Set as appropriate.

  • password - Set as appropriate.

  • URL - Specify the complete database URL using the following syntax:

    jdbc:oracle:oci:[user/password]@host[:port]/service
    

    For example:

    jdbc:oracle:oci:@localhost:1521:customer_db

  • xa-driver-does-not-support-non-tx-operations - Set to the value true. Optional: only needed if both non-XA and XA connections are retrieved from the same connection pool. Might degrade performance.

    As an alternative to setting this property, you can create two connection pools, one for non-XA connections and one for XA connections.

IBM Informix Type 4 Driver

Configure the connection pool using the following settings:

• Name: Use this name when you configure the JDBC resource later.

• Resource Type: Specify the appropriate value.

• Database Vendor: Informix

• DataSource Classname:Specify one of the following:

  com.informix.jdbcx.IfxDataSource
  com.informix.jdbcx.IfxXADataSource

• Properties:

  • serverName - Specify the Informix database server name.

  • portNumber - Specify the port number of the database server.

  • user - Set as appropriate.

  • password - Set as appropriate.

  • databaseName - Set as appropriate. This is optional.

  • IfxIFXHost - Specify the host name or IP address of the database server.