This chapter describes how to use the JavaTM Database Connectivity (JDBCTM) API for database access with the Sun Java System Application Server. This chapter also provides high level JDBC implementation instructions for servlets and EJBTM components using the Application Server. The Application Server supports the JDBC 3.0 API, which encompasses the JDBC 2.0 Optional Package API.
The JDBC specifications are available at http://java.sun.com/products/jdbc/download.html.
A useful JDBC tutorial is located at http://java.sun.com/docs/books/tutorial/jdbc/index.html.
For explanations of two-tier and three-tier database access models, see the Sun Java System Application Server Enterprise Edition 8.2 Administration Guide.
The Application Server does not support connection pooling or transactions for an application’s database access if it does not use standard J2EETM DataSource objects.
This chapter discusses the following topics:
To prepare a JDBC resource for use in J2EE applications deployed to the Application Server, perform the following tasks:
For information about how to configure some specific JDBC drivers, see the Configurations for Specific JDBC Drivers.
To use JDBC features, you must choose a JDBC driver to work with the Application Server, then you must set up the driver. This section covers these topics:
Supported JDBC drivers are those that have been fully tested by Sun. For a list of the JDBC drivers currently supported by the Application Server, see the Sun Java System Application Server Enterprise Edition 8.2 Release Notes. For configurations of supported and other drivers, see Configurations for Specific JDBC Drivers.
Because the drivers and databases supported by the Application Server are constantly being updated, and because database vendors continue to upgrade their products, always check with Sun technical support for the latest database support information.
To integrate the JDBC driver into a Application Server domain, copy the JAR files into the domain-dir/lib directory, then restart the server. This makes classes accessible to any application or module across the domain. For more information , see Using the Common Classloader.
When you create a connection pool that uses JDBC technology (a JDBC connection pool) in the Application Server, you can define many of the characteristics of your database connections.
You can create a JDBC connection pool in one of these ways:
In the Administration Console, open the Resources component, open the JDBC component, and select Connection Pools. For details, see the Sun Java System Application Server Enterprise Edition 8.2 Administration Guide.
Use the asadmin create-jdbc-connection-pool command. For details, see the Sun Java System Application Server Enterprise Edition 8.2 Reference Manual.
You can test a JDBC connection pool for usability in one of these ways:
In the Administration Console, open the Resources component, open the JDBC component, select Connection Pools, and select the connection pool you want to test. Then select the Ping button in the top right corner of the page. For details, see the Sun Java System Application Server Enterprise Edition 8.2 Administration Guide.
Use the asadmin ping-connection-pool command. For details, see the Sun Java System Application Server Enterprise Edition 8.2 Reference Manual
Both these commands fail and display an error message unless they successfully connect to the connection pool.
For information about how to tune a connection pool, see the Sun Java System Application Server Enterprise Edition 8.2 Performance Tuning Guide.
A JDBC resource, also called a data source, lets you make connections to a database using getConnection(). Create a JDBC resource in one of these ways:
In the Administration Console, open the Resources component, open the JDBC component, and select JDBC Resources. For details, see the Sun Java System Application Server Enterprise Edition 8.2 Administration Guide.
Use the asadmin create-jdbc-resource command. For details, see the Sun Java System Application Server Enterprise Edition 8.2 Reference Manual.
An application that uses the JDBC API is an application that looks up and connects to one or more databases. This section covers these topics:
When multiple connections acquired by an application use the same JDBC resource, the connection pool provides connection sharing within the same transaction scope. For example, suppose Bean A starts a transaction and obtains a connection, then calls a method in Bean B. If Bean B acquires a connection to the same JDBC resource with the same sign-on information, and if Bean A completes the transaction, the connection can be shared.
Connections obtained through a resource are shared only if the resource reference declared by the J2EE component allows it to be shareable. This is specified in a component’s deployment descriptor by setting the res-sharing-scope element to Shareable for the particular resource reference. To turn off connection sharing, set res-sharing-scope to Unshareable.
For general information about connections and JDBC URLs, see the Sun Java System Application Server Enterprise Edition 8.2 Administration Guide.
The DataSource implementation in the Application Server provides a getConnection method that retrieves the JDBC driver’s SQLConnection from the Application Server’s Connection wrapper. The method signature is as follows:
public java.sql.Connection getConnection(java.sql.Connection con) throws java.sql.SQLException
For example:
InitialContext ctx = new InitialContext(); com.sun.appserv.jdbc.DataSource ds = (com.sun.appserv.jdbc.DataSource) ctx.lookup("jdbc/MyBase"); Connection con = ds.getConnection(); Connection drivercon = ds.getConnection(con); // Do db operations. con.close();
If you get a Connection from an Application Server JDBC connection pool, create a Statement object, and then use the Statement.getConnection method, the statement returns the physical connection instead of the wrapped connection. When you close this physical connection, you break the connection pool logic. To avoid this problem, use the following asadmin create-jvm-options command, then restart the server:
asadmin create-jvm-options -Dcom.sun.appserv.jdbc.wrapJdbcObjects=true |
For more information about the asadmin create-jvm-options command, see the Sun Java System Application Server Enterprise Edition 8.2 Reference Manual.
The DataSource implementation in the Application Server provides a getNonTxConnection method, which retrieves a JDBC connection that is not in the scope of any transaction. There are two variants, as follows:
public java.sql.Connection getNonTxConnection() throws java.sql.SQLException
public java.sql.Connection getNonTxConnection(String user, String password) throws java.sql.SQLException
Another way to get a non-transactional connection is to create a resource with the JNDI name ending in __nontx. This forces all connections looked up using this resource to be non transactional.
Typically, a connection is enlisted in the context of the transaction in which a getConnection call is invoked. However, a non-transactional connection is not enlisted in a transaction context even if a transaction is in progress.
The main advantage of using non-transactional connections is that the overhead incurred in enlisting and delisting connections in transaction contexts is avoided. However, use such connections carefully. For example, if a non-transactional connection is used to query the database while a transaction is in progress that modifies the database, the query retrieves the unmodified data in the database. This is because the in-progress transaction hasn’t committed. For another example, if a non-transactional connection modifies the database and a transaction that is running simultaneously rolls back, the changes made by the non-transactional connection are not rolled back.
Here is a typical use case for a non-transactional connection: a component that is updating a database in a transaction context spanning over several iterations of a loop can refresh cached data by using a non-transactional connection to read data before the transaction commits.
For general information about transactions, see Chapter 12, Using the Transaction Serviceand the Sun Java System Application Server Enterprise Edition 8.2 Administration Guide. For information about last agent optimization, which can improve performance, see Transaction Scope.
Not all database vendors support all transaction isolation levels available in the JDBC API. The Application Server permits specifying any isolation level your database supports. The following table defines transaction isolation levels.
Table 11–1 Transaction Isolation Levels
Transaction Isolation Level |
Description |
---|---|
TRANSACTION_READ_UNCOMMITTED |
Dirty reads, non-repeatable reads and phantom reads can occur. |
TRANSACTION_READ_COMMITTED |
Dirty reads are prevented; non-repeatable reads and phantom reads can occur. |
TRANSACTION_REPEATABLE_READ |
Dirty reads and non-repeatable reads are prevented; phantom reads can occur. |
TRANSACTION_SERIALIZABLE |
Dirty reads, non-repeatable reads and phantom reads are prevented. |
Note that you cannot call setTransactionIsolation() during a transaction.
You can set the default transaction isolation level for a JDBC connection pool. For details, see Creating a Connection Pool.
To verify that a level is supported by your database management system, test your database programmatically using the supportsTransactionIsolationLevel() method in java.sql.DatabaseMetaData, as shown in the following example:
java.sql.DatabaseMetaData db; if (db.supportsTransactionIsolationLevel(TRANSACTION_SERIALIZABLE) { Connection.setTransactionIsolation(TRANSACTION_SERIALIZABLE); }
For more information about these isolation levels and what they mean, see the JDBC 3.0 API specification.
Applications that change the isolation level on a pooled connection programmatically risk polluting the pool, which can lead to errors.
Application Server 8.2 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 8.2 and are found to be J2EE compatible. They are also supported for CMP.
Sun Java System JDBC Driver for Oracle 8i, 9i, and 10g Databases
Sun Java System JDBC Driver for Microsoft SQL Server Databases
For an up to date list of currently supported JDBC drivers, see the Sun Java System Application Server Enterprise Edition 8.2 Release Notes.
Other JDBC drivers can be used with Application Server 8.2, but J2EE 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 8.2.
Oracle Thin Type 4 Driver for Oracle 8i, 9i, and 10g Databases
OCI Oracle Type 2 Driver for Oracle 8i, 9i, and 10g Databases
For details about how to integrate a JDBC driver and how to use the Administration Console or the command line interface to implement the configuration, see the Sun Java System Application Server Enterprise Edition 8.2 Administration Guide.
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 Using the capture-schema Utility.
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. This configuration applies to CloudScape databases as well.
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.
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
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]
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]
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]
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.
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.
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"
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"
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.
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.
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.
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.
You must set the oracle-xa-recovery-workaround property in the Transaction Service for recovery of global transactions to work correctly. For details, see Transaction Scope.
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).
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.
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.