Previous Contents Index Next |
Sun Java System Application Server Standard and Enterprise Edition 7 2004Q2 Developer's Guide to J2EE Services and APIs

> ## Chapter 2
> Using the JDBC API for Database Access

> This chapter describes how to use the Java™ Database Connectivity (JDBC™) API for database access with the Sun Java™ System Application Server. This chapter also provides high level JDBC implementation instructions for servlets and EJB™ components using the Sun Java System Application Server. The Sun Java System Application Server supports the core JDBC 3.0 API and the JDBC 2.0 extensions.

> This chapter contains the following sections:

> - Introducing the JDBC API
> - General Steps for Creating a JDBC Resource
> - Configurations for Specific JDBC Drivers
> - Creating Applications That Use the JDBC API
> - Using the JDBC API in Application Layers
> - Sample Applications

> ##

> Introducing the JDBC API

> From a programming perspective, the JDBC API is a set of Java classes and methods that allow embedding of database calls in server applications. More specifically, the JDBC Specification is a set of interfaces that every JDBC driver vendor must implement. A driver processes the JDBC statements in your application and routes the SQL arguments they contain to your database engines. The Sun Java System Application Server supports a variety of JDBC drivers, which support a variety of Enterprise Information Systems (EIS) databases.

> The following figure illustrates how application components use the JDBC API to interact with databases.

> The JDBC API lets you write high-level, easy-to-use programs that operate seamlessly with and across many different databases without requiring you to know most of the low-level database implementation details.

> For explanations of two-tier and three-tier database access models, see the Sun Java System Application Server Administrator’s Guide.

> The JDBC specifications are available here:

> http://java.sun.com/products/jdbc/download.html

> A useful JDBC tutorial is located here:

> http://java.sun.com/docs/books/tutorial/jdbc/index.html

> The rest of this section includes these topics:

> - Supported Functionality
> - Understanding Database Limitations

> ### Supported Functionality

> The JDBC specification is a broad, database-vendor-independent set of guidelines. The guidelines encompass the broadest database functionality range possible in a simple framework. At a minimum, the JDBC API assumes the database supports the SQL-3 database access language. Sun Java System Application Server supports these parts of the JDBC specification:

> - The JDBC 3.0 core database access and functionality that a server vendor must implement to be JDBC compliant is supported. The Sun Java System Application Server fully meets the compliance standard. From a database vendor’s perspective, the JDBC 3.0 API describes a database access model that permits full access to the standard SQL-3 language, the standard language portions each vendor supports, and the language extensions each vendor implements.
> - The JDBC 2.0 Standard Extension API, which describes advanced features, many of which offer improved database performance, is supported.

> Note | Sun Java System Application Server does not support connection pooling or transactions for an application’s database access if it does not use standard J2EE™ DataSource objects.

> ### Understanding Database Limitations

> When using the JDBC API in your server applications, you may encounter situations where the results are not what you desire or expect. You may think the problem lies in the JDBC API or driver implementation. However, the vast majority of these problems are limitations in your database engine.

> Because the JDBC API covers the broadest possible database support, it enables you to attempt operations not every database supports. For example, most database vendors support most of the SQL-3 language, but no vendor provides fully unqualified support for all of the SQL-3 standard. Most vendors built SQL-3 support on top of their existing proprietary relational database management systems, and either those proprietary systems offer features not in SQL-3 or SQL-3 offers features not available in those systems. Most vendors have added non standard SQL-3 extensions to their SQL implementation to support their proprietary features. The JDBC API provides ways to access vendor-specific features, but these features may not be available for all databases you use.

> Some JDBC access problems can result if you attempt to access JDBC features that are either partially supported or not supported by the JDBC driver. Check the JDBC driver documentation for details about which JDBC features are supported.

> ##

> General Steps for Creating a JDBC Resource

> To prepare a JDBC resource for use in J2EE applications deployed to the Sun Java System Application Server, perform the following tasks:

> - Integrating the JDBC Driver
> - Creating a Connection Pool
> - Creating a JDBC Resource
> - Creating a JDBC Connection Pool and Resource for a Cluster (Enterprise Edition)

> For information about how to configure some specific JDBC drivers, see Configurations for Specific JDBC Drivers.

> ### Integrating the JDBC Driver

> To use JDBC features, you must choose a JDBC driver to work with the Sun Java System Application Server, then you must set up the driver. This section covers these topics:

> - Supported Database Drivers
> - Making the JDBC Driver JAR Files Accessible

> #### Supported Database Drivers

> Supported JDBC drivers are those that have been fully tested by Sun. For a list of the JDBC drivers currently supported by the Sun Java System Application Server, see the Sun Java System Application Server 7 Release Notes.

> For configurations of supported and other drivers, see Configurations for Specific JDBC Drivers.

> Note | Because the drivers and databases supported by the Sun Java System 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.

> #### Making the JDBC Driver JAR Files Accessible

> To integrate the JDBC driver into a Sun Java System Application Server instance, you can do either of the following:

> - Make the driver’s class files accessible to the Common Classloader. Copy the JAR and ZIP files into the instance_dir/lib directory or copy the .class files into the instance_dir/lib/classes directory, then restart the server.
> - Make the driver’s class files accessible to the System Classloader. Go to the server instance page in the Administration interface, click the JVM Settings tab (JVM™ is an abbreviation of Java™ Virtual Machine), click the Path Settings option, edit the Classpath Suffix field, click Save, then restart the server.

> Using either classloader makes classes accessible to any application or module across the server instance. For more information about Sun Java System Application Server classloaders, see the Sun Java System Application Server Developer’s Guide.

> ### Creating a Connection Pool

> When you create a JDBC connection pool in the Sun Java System Application Server, you can define many of the characteristics of your database connections.

> You can create a connection pool in one of these ways:

> - Using the Administration Interface
> - Using The Command Line Interface

> The “Using The Administration Interface” section describes each connection pool setting. The “Using The Command Line Interface” section merely lists syntax and default values.

> For additional information about connection pools, including connection pool monitoring, see the Sun Java System Application Server Administrator’s Guide.

> #### Using the Administration Interface

> To create a JDBC connection pool using the Administration interface, perform the following tasks:

> 1. Open the JDBC component under your server instance.
> 2. Click Connection Pools.
> 3. Click the New button.
> 4. Enter the following information:
> - Name (required) - Enter a name (or ID) for the connection pool.
> - Database Vendor (required) - Select the database driver vendor from the list. You must select a JDBC driver that you have integrated as described in Integrating the JDBC Driver. You can select Other if your database driver is not listed.
> 5. If you want to enable global transactions, check the Global Transaction Support box.

> If you check this box, the Datasource Classname value you enter later must implement the java.sql.XADataSource interface.

> 6. Click the Next button. (You can click the Back button to return to this page.)

> Your Database Vendor selection determines what is displayed when you click the Next button.

> 7. Enter or edit the Datasource Classname value. This is the vendor-supplied DataSource class name.
> 8. Specify values for any properties your JDBC driver requires. If a property you need is not listed, use the Add button to add it. The following table lists some standard and commonly used properties.

> Table 2-1 Common Connection Pool Properties

> Property | Description
> --- | ---
> User | Specifies the user name for this connection pool.
> Password | Specifies the password for this connection pool.
> databaseName | Specifies the database for this connection pool.
> serverName | Specifies the database server for this connection pool.
> port | Specifies the port on which the database server listens for requests.
> networkProtocol | Specifies the communication protocol.
> roleName | Specifies the initial SQL role name.
> datasourceName | Specifies an underlying XADataSource, or a ConnectionPoolDataSource if connection pooling is done.
> description | Specifies a text description.
> url | Specifies the URL for this connection pool. Although this is not a standard property, it is commonly used.

> 9. You can change the Pool Settings listed in the following table.

> Table 2-2 Pool Settings

> Setting | Default | Description
> --- | --- | ---
> Steady Pool Size | 8 | Specifies the initial and minimum number of connections maintained in the pool.
> Max Pool Size | 32 | Specifies the maximum number of connections that can be created to satisfy client requests.
> Pool Resize Quantity | 2 | Specifies the number of connections to be destroyed if the existing number of connections is above the Steady Pool Size (subject to the Max Pool Size limit). This is enforced periodically at the Idle Timeout interval. An idle connection is one that has not been used for a period specified by Idle Timeout.
> Idle Timeout (secs) | 300 | Specifies the minimum time that a connection can remain idle in the free pool. After this amount of time, the pool can close this connection.
> Max Wait Time | 60000 | Specifies the amount of time, in milliseconds, that the caller is willing to wait to acquire a connection. If 0, the caller is blocked indefinitely until a resource is available or an error occurs.

> 10. You can change the Connection Validation settings listed in the following table. All of these settings are optional.

> Table 2-3 Connection Validation Settings

> Setting | Default | Description
> --- | --- | ---
> Connection Validation Required | Unchecked | Specifies whether connections have to be validated before being given to the application. If a resource’s validation fails, it is destroyed, and a new resource is created and returned.
> Validation Method | auto-commit | Legal values are as follows: auto-commit (default), which uses Connection.setAutoCommit() meta-data, which uses Connection.getMetaData() table, which performs a query on the table specified in the Table Name setting
> Table Name | none | Specifies the table name to be used to perform a query to validate a connection. This setting is mandatory if and only if the Validation Method is set to table.
> Fail All Connections | Unchecked | If checked, closes all connections in the pool if a single validation check fails. Recovery of a minimum number of connections (specified by the Steady Pool Size setting) is attempted. This setting is mandatory if and only if Connection Validation Required is checked. If Connection Validation Required is unchecked, this setting is ignored.

> 11. You can change the Transaction Isolation settings listed in the following table. Both of these settings are optional.

> Table 2-4 Transaction Isolation Settings

> Setting | Default | Description
> --- | --- | ---
> Transaction Isolation | default JDBC driver isolation level | Specifies the transaction isolation level on the pooled database connections. Allowed values are read-uncommitted, read-committed, repeatable-read, or serializable. Not all databases support all these values. For more information about these values, see Using JDBC Transaction Isolation Levels. Applications that change the isolation level on a pooled connection programmatically risk polluting the pool, which can lead to errors. See Guarantee Isolation Level for more details.
> Guarantee Isolation Level | Checked | Applicable only when the Transaction Isolation level is explicitly set. If checked, every connection obtained from the pool is guaranteed to have the desired isolation level. This may impact performance on some JDBC drivers. You can uncheck this setting if you are certain that the hosted applications do not return connections with altered isolation levels.

> 12. Click the Finish button.

> #### Using The Command Line Interface

> To create a JDBC connection pool using the command line, use the asadmin create-jdbc-connection-pool command. The syntax is as follows, with defaults shown for optional parameters that have them:

> asadmin create-jdbc-connection-pool --user admin_user [--password admin_password] [--passwordfile password_file] [--host localhost] [--port 4848] [--secure | -s] [--instance instance_name] --datasourceclassname class_name [--restype javax.sql.DataSource] [--steadypoolsize=8] [--maxpoolsize=32] [--maxwait=60000] [--poolresize=2] [--idletimeout=300] [--isolationlevel isolation_level] [--isisolationguaranteed=true] [--isconnectvalidatereq=false] [--validationmethod=auto-commit] [--validationtable table_name] [--failconnection=false] [--description text] [--property (name=value)[:name=value]*] connection_pool_id

> For more information about the parameters specific to asadmin create-jdbc-connection-pool, see Using the Administration Interface. For more information about the general asadmin parameters (--user, --password, --passwordfile, --host, --port, and --secure), see the Sun Java System Application Server Administrator’s Guide.

> For example:

> asadmin create-jdbc-connection-pool --user joeuser --password secret --datasourceclassname oracle.jdbc.pool.OracleDataSource --failconnection=true --isconnectvalidatereq=true --property url=jdbc\\:oracle\\:thin\\:@myhost\\:1521\\:V8i:user=staging_lookup_app:pa ssword=staging_lookup_app OraclePoollookup

> Note that the colon characters (:) within property values must be escaped with double backslashes (\\) on Solaris™ platforms as shown, because otherwise they are interpreted as property delimiters. For details about using escape characters, see the Sun Java System Application Server Administrator’s Guide.

> To delete a JDBC connection pool, use the following command:

> asadmin delete-jdbc-connection-pool --user admin_user [--password admin_password] [--passwordfile password_file] [--host localhost] [--port 4848] [--secure | -s] [--instance instance_name] connection_pool_id

> For example:

> asadmin delete-jdbc-connection-pool --user joeuser --password secret OraclePoollookup

> To list JDBC connection pools, use the following command:

> asadmin list-jdbc-connection-pools --user admin_user [--password admin_password] [--passwordfile password_file] [--host localhost] [--port 4848] [--secure | -s] [--instance instance_name]

> For example:

> asadmin list-jdbc-connection-pools --user joeuser --password secret --instance server1

> ### Creating a JDBC Resource

> 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:

> - Using The Administration Interface
> - Using The Command Line Interface

> The “Using The Administration Interface” section describes each connection pool setting. The “Using The Command Line Interface” section merely lists syntax and default values.

> For general information about JDBC resources, see the Sun Java System Application Server Administrator’s Guide.

> #### Using The Administration Interface

> To create a JDBC resource using the Administration interface, perform these tasks:

> 1. Open the JDBC component under your server instance.
> 2. Click JDBC Resources.
> 3. Click the New button.
> 4. Enter the following information:
> - JNDI Name (required) - Enter the JNDI name that application components must use to access the JDBC resource. For more information, see Looking Up a JDBC Resource.
> - Pool Name (required) - Select from the list the name (or ID) of the connection pool used by this JDBC resource. For more information, see Creating a Connection Pool.
> - Description (optional) - You can enter a text description of the JDBC resource.
> 5. Check the Data Source Enabled box to enable the JDBC resource.

> If a JDBC resource is disabled, no application component can connect to it, but its configuration remains in the server instance.

> 6. Click the OK button.
> 7. Go to the server instance page.
> 8. Click the General tab.
> 9. Click the Apply Changes button.

> #### Using The Command Line Interface

> To create a JDBC resource using the command line, use the asadmin create-jdbc-resource command. The syntax is as follows, with defaults shown for optional parameters that have them:

> asadmin create-jdbc-resource --user admin_user [--password admin_password] [--passwordfile password_file] [--host localhost] [--port 4848] [--secure | -s] [--instance instance_name] --connectionpoolid connection_pool_id [--enabled=true] [--description text] [--property (name=value)[:name=value]*] jndi_name

> For more information about the parameters specific to asadmin create-jdbc-resource, see Using The Administration Interface. For more information about the general asadmin parameters (--user, --password, --passwordfile, --host, --port, and --secure), see the Sun Java System Application Server Administrator’s Guide.

> For example:

> asadmin create-jdbc-resource --user joeuser --password secret --connectionpoolid OraclePoollookup OracleDSlookup

> To delete a JDBC resource, use the following command:

> asadmin delete-jdbc-resource --user admin_user [--password admin_password] [--passwordfile password_file] [--host localhost] [--port 4848] [--secure | -s] [--instance instance_name] jndi_name

> For example:

> asadmin delete-jdbc-resource --user joeuser --password secret OracleDSlookup

> To list JDBC resources, use the following command:

> asadmin list-jdbc-resources --user admin_user [--password admin_password] [--passwordfile password_file] [--host localhost] [--port 4848] [--secure | -s] [--instance instance_name]

> For example:

> asadmin list-jdbc-resources --user joeuser --password secret --instance server1

> After you create the JDBC resource, you must reconfigure the server instance using the following command:

> asadmin reconfig --user user [--password password] [--passwordfile password_file] [--host localhost] [--port 4848] [--secure | -s][--discardmanualchanges=false | --keepmanualchanges=false] instance_name

> For example:

> asadmin reconfig --user joeuser --password secret server1

> ### Creating a JDBC Connection Pool and Resource for a Cluster (Enterprise Edition)

> If a server instance is part of a cluster, you can create the same JDBC connection pool and resource on each instance in the cluster using the cladmin command. For details, see the Sun Java System Application Server Administrator’s Guide.

> ##

> Configurations for Specific JDBC Drivers

> The following JDBC driver and database combinations are supported for Sun Java System Application Server 7, Enterprise Edition. The combinations listed here have been tested with the Sun Java System Application Server 7, Enterprise Edition and are found to be J2EE compatible.

> - PointBase Type4 Driver
> - IBM DB2 8.1 Type2 Driver
> - Data Direct Connect JDBC3.0/ Type4 Driver for Oracle 9.x Databases
> - Oracle Thin/Type4 Driver for Oracle 9.x Databases
> - OCI Oracle Type2 Driver for Oracle 9.x Databases
> - Data Direct Connect JDBC3.0/ Type4 Driver for Microsoft SQL Server Databases
> - Sybase JConnect/Type4 Driver

> For an up to date list of the JDBC drivers currently supported by the Sun Java System Application Server 7, Enterprise Edition, see the Sun Java System Application Server 7, Enterprise Edition Release Notes.

> Other JDBC drivers have been used with Sun Java System Application Server 7, Enterprise Edition, but J2EE compliance tests have not been completed with these drivers.

> - Data Direct Connect JDBC3.0/ Type4 Driver for IBM DB2 Databases
> - Data Direct Connect JDBC3.0/ Type4 Driver for Informix Databases
> - IBM Informix Type4 Driver
> - Data Direct Connect JDBC3.0/ Type4 Driver for Sybase Databases
> - MM MySQL Type4 Driver
> - CloudScape Type4 Driver

> For details about how to integrate a JDBC driver and how to use the Administration interface or the command line interface to implement the configuration, see General Steps for Creating a JDBC Resource.

> ### PointBase Type4 Driver

> The PointBase JDBC driver is included with the Sun Java System Application Server by default. Therefore, you do not need to integrate this JDBC driver with the Sun Java System Application Server. This driver is verified J2EE compatible and supported.

> The JAR file for the PointBase driver is pbclient.jar.

> Configure the connection pool using the following settings:

> - Name: You will use this name when you configure the JDBC resource later.
> - Resource Type: Specify the appropriate value.
> - Database Vendor: PointBase
> - Global Transaction Support, Datasource Classname: See the following table.

> Table 2-5 Datasource Classname Values for the PointBase Driver

> Global Transaction Support | Datasource Classname Value
> --- | ---
> No (unchecked) | com.pointbase.jdbc.jdbcDataSource
> Yes (checked) | com.pointbase.xa.xaDataSource

> - Properties:
> - user - Specify the database user.
> - password - Specify the database password.
> - databaseName - Specify the URL of the database. The syntax is as follows:

> jdbc:pointbase:server://server:port/dbname,new

> ### IBM DB2 8.1 Type2 Driver

> This driver is verified J2EE compatible and supported. 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: You will use this name when you configure the JDBC resource later.
> - Resource Type: Specify the appropriate value.
> - Database Vendor: DB2
> - Global Transaction Support, Datasource Classname: See the following table.

> Table 2-6 Datasource Classname Values for the IBM DB2 Driver

> Global Transaction Support | Datasource Classname Value
> --- | ---
> No (unchecked) | com.ibm.db2.jcc.DB2SimpleDataSource
> Yes (checked) | 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.

> ### Data Direct Connect JDBC3.0/ Type4 Driver for Oracle 9.x Databases

> This driver is verified J2EE compatible and supported. You must set compatible=9.0.0.0.0 in the init.ora file and define the following JVM property in the Sun Java System Application Server:

> -Doracle.jdbc.J2EE13Compliant=true

> The JAR files for the Oracle driver are base.jar, oracle.jar, and util.jar. Configure the connection pool using the following settings:

> - Name: You will use this name when you configure the JDBC resource later.
> - Database Vendor: Data Direct 3
> - Global Transaction Support, Datasource Classname: See the following table.

> Table 2-7 Datasource Classname Values for the Data Direct Driver for Oracle 9.x Databases

> Global Transaction Support | Datasource Classname Value
> --- | ---
> No (unchecked) | com.ddtek.jdbcx.oracle.OracleDataSource
> Yes (checked) | com.ddtek.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.
> - User - Set as appropriate.
> - Password - Set as appropriate.
> - SID - Set as appropriate.
> - xa-driver-does-not-support-non-tx-operations - Set to the value true. Optional: only needed if Global Transaction Support is checked.

> ### Oracle Thin/Type4 Driver for Oracle 9.x Databases

> This driver is verified J2EE compatible and supported. You must set compatible=9.0.0.0.0 in the init.ora file and define the following JVM property in the Sun Java System Application Server:

> -Doracle.jdbc.J2EE13Compliant=true

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

> - Name: You will use this name when you configure the JDBC resource later.
> - Resource Type: Specify the appropriate value.
> - Database Vendor: Oracle
> - Global Transaction Support, Datasource Classname: See the following table.

> Table 2-8 Datasource Classname Values for the Oracle Thin Driver

> Global Transaction Support | Datasource Classname Value
> --- | ---
> No (unchecked) | oracle.jdbc.pool.OracleDataSource
> Yes (checked) | 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. May 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 “Transaction Recovery” on page 53 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).

> ### OCI Oracle Type2 Driver for Oracle 9.x Databases

> This driver is verified J2EE compatible and supported. Make sure that the shared library is available through LD_LIBRARY_PATH and that the ORACLE_HOME property is set. You must also set compatible=9.0.0.0.0 in the init.ora file and define the following JVM property in the Sun Java System Application Server:

> -Doracle.jdbc.J2EE13Compliant=true

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

> - Name: You will use this name when you configure the JDBC resource later.
> - Resource Type: Specify the appropriate value.
> - Database Vendor: Oracle
> - Global Transaction Support, Datasource Classname: See the following table.

> Table 2-9 Datasource Classname Values for the OCI Oracle Driver

> Global Transaction Support | Datasource Classname Value
> --- | ---
> No (unchecked) | oracle.jdbc.pool.OracleDataSource
> Yes (checked) | 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. May 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.

> ### Data Direct Connect JDBC3.0/ Type4 Driver for Microsoft SQL Server Databases

> This driver is verified J2EE compatible and supported. The JAR files for the Microsoft SQL Server driver are util.jar, sqlserver.jar, and base.jar. Configure the connection pool using the following settings:

> - Name: You will use this name when you configure the JDBC resource later.
> - Database Vendor: Data Direct 3
> - Global Transaction Support, Datasource Classname: See the following table.

> Table 2-10 Datasource Classname Values for the Data Direct Driver for MSSQL/SQL Databases

> Global Transaction Support | Datasource Classname Value
> --- | ---
> No (unchecked) | com.ddtek.jdbcx.sqlserver.SQLServerDataSource
> Yes (checked) | com.ddtek.jdbcx.sqlserver.SQLServerDataSource

> - 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. This is optional.
> - SelectMethod - Set as appropriate. Example values are direct, cursor, and so on.

> ### Sybase JConnect/Type4 Driver

> This driver is verified J2EE compatible and supported. The JAR file for the Sybase driver is jconn2.jar. Configure the connection pool using the following settings:

> - Name: You will use this name when you configure the JDBC resource later.
> - Database Vendor: Sybase
> - Global Transaction Support, Datasource Classname: See the following table.

> Table 2-11 Datasource Classname Values for the Sybase JConnect/Type4 Driver

> Global Transaction Support | Datasource Classname Value
> --- | ---
> No (unchecked) | com.sybase.jdbc2.jdbc.SybDataSource
> Yes (checked) | 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.

> Note | If you are using JConnect version 5.2, you must set the sybase-xa-recovery-workaround property in the Transaction Service for recovery of global transactions to work correctly. For details, see “Transaction Recovery” on page 53.

> ### Data Direct Connect JDBC3.0/ Type4 Driver for IBM DB2 Databases

> This driver is not verified J2EE compatible and not fully supported. Configure the connection pool using the following settings:

> - Name: You will use this name when you configure the JDBC resource later.
> - Database Vendor: Data Direct 3
> - Global Transaction Support, Datasource Classname: See the following table.

> Table 2-12 Datasource Classname Values for the Data Direct Driver for IBM DB2 Databases

> Global Transaction Support | Datasource Classname Value
> --- | ---
> No (unchecked) | com.ddtek.jdbcx.db2.DB2DataSource
> Yes (checked) | Not available

> - 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.

> ### Data Direct Connect JDBC3.0/ Type4 Driver for Informix Databases

> This driver is not verified J2EE compatible and not fully supported. Configure the connection pool using the following settings:

> - Name: You will use this name when you configure the JDBC resource later.
> - Database Vendor: Data Direct 3
> - Global Transaction Support, Datasource Classname: See the following table.

> Table 2-13 Datasource Classname Values for the Data Direct Driver for Informix Databases

> Global Transaction Support | Datasource Classname Value
> --- | ---
> No (unchecked) | com.ddtek.jdbcx.informix.InformixDataSource
> Yes (checked) | com.ddtek.jdbcx.informix.InformixDataSource

> - 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.
> - InformixServer - Set as appropriate.

> ### IBM Informix Type4 Driver

> This driver is not verified J2EE compatible and not fully supported. Configure the connection pool using the following settings:

> - Name: You will use this name when you configure the JDBC resource later.
> - Database Vendor: Informix 9.x
> - Global Transaction Support, Datasource Classname: See the following table.

> Table 2-14 Datasource Classname Values for the IBM Informix Type4 Driver

> Global Transaction Support | Datasource Classname Value
> --- | ---
> No (unchecked) | com.informix.jdbcx.IfxDataSource
> Yes (checked) | 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.

> ### Data Direct Connect JDBC3.0/ Type4 Driver for Sybase Databases

> This driver is not verified J2EE compatible and not fully supported. Configure the connection pool using the following settings:

> - Name: You will use this name when you configure the JDBC resource later.
> - Database Vendor: Data Direct 3
> - Global Transaction Support, Datasource Classname: See the following table.

> Table 2-15 Datasource Classname Values for the Data Direct Driver for Sybase Databases

> Global Transaction Support | Datasource Classname Value
> --- | ---
> No (unchecked) | com.ddtek.jdbcx.sybase.SybaseDataSource
> Yes (checked) | com.ddtek.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.
> - User - Set as appropriate.
> - Password - Set as appropriate.
> - databaseName - Set as appropriate. This is optional.
> - SelectMethod - Set as appropriate. Example values are direct, cursor, and so on.

> ### MM MySQL Type4 Driver

> This driver is not verified J2EE compatible and not fully supported. Configure the connection pool using the following settings:

> - Name: You will use this name when you configure the JDBC resource later.
> - Database Vendor: Other
> - Global Transaction Support, Datasource Classname: See the following table.

> Table 2-16 Datasource Classname Values for the MM MySQL Type4 Driver

> Global Transaction Support | Datasource Classname Value
> --- | ---
> No (unchecked) | com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
> Yes (checked) | com.mysql.jdbc.jdbc2.optional.MysqlXaConnectionPoolDataSource

> - 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.
> - URL - If Global Transaction Support is checked, set this property instead of serverName, portNumber, and databaseName. The MM MySQL Type4 driver does not provide a method to set the required relaxAutoCommit property, so you must set it indirectly by setting the URL property as in:

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

> ### CloudScape Type4 Driver

> This driver is not verified J2EE compatible and not fully supported. The JAR files for the CloudScape driver are db2j.jar, db2jtools.jar, db2jcview.jar, jh.jar, db2jcc.jar, and db2jnet.jar. Configure the connection pool using the following settings:

> - Name: You will use this name when you configure the JDBC resource later.
> - Database Vendor: Other
> - Global Transaction Support, Datasource Classname: See the following table.

> Table 2-17 Datasource Classname Values for the CloudScape Type4 Driver

> Global Transaction Support | Datasource Classname Value
> --- | ---
> No (unchecked) | COM.cloudscape.core.BasicDataSource
> Yes (checked) | COM.cloudscape.core.XADataSource

> - Properties:
> - User - Set as appropriate.
> - Password - Set as appropriate.
> - databaseName - Set as appropriate.
> - RemoteDataSourceProtocol - Set as appropriate, for example: rmi.
> - CloudscapeProperties - Set as appropriate, for example: create=true.

> ##

> Creating Applications That Use the JDBC API

> 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:

> - Using Connections
> - Using JDBC Transaction Isolation Levels

> ### Using Connections

> To use connections, you should be familiar with these topics:

> - Looking Up a JDBC Resource
> - Pooling Connections
> - Sharing Connections
> - Opening and Closing Connections

> For general information about connections and JDBC URLs, see the Sun Java System Application Server Administrator’s Guide.

> #### Looking Up a JDBC Resource

> The standard Java Naming and Directory Interface™ (JNDI) subcontext for JDBC resources is java:comp/env/jdbc.

> The JDBC 3.0 API specifies that all JDBC resources are registered in the jdbc naming subcontext of a JNDI namespace, or in one of its child subcontexts. The JNDI namespace is hierarchical, like a file system’s directory structure, so it is easy to find and nest references. A JDBC resource is bound to a logical JNDI name. The name identifies a subcontext, jdbc, of the root context, and a logical name. In order to change the JDBC resource, you can just change its entry in the JNDI namespace without having to modify the application.

> For more information about the JNDI API, see the JDBC 2.0 Standard Extension API and Chapter 4, “Using the Java Naming and Directory Interface.”

> The following code example demonstrates how a JDBC resource is looked up and a connection created from it. As illustrated, the string that is looked up is the same as specified in the res-ref-name element in the deployment descriptor file.

> InitialContext ctx = null;
> String dsName1 = "java:comp/env/jdbc/HelloDbDs";
> DataSource ds1 = null;
> Connection conn1 = null;
> try {
> ctx = new InitialContext();
> ds1 = (DataSource)ctx.lookup(dsName1);
> UserTransaction tx = ejbContext.getUserTransaction();
> tx.begin();
> Connection conn1 = ds1.getConnection();
> // use conn1 to do some database work -- note that
> // conn1.commit(), conn1.rollback(), and conn1.setAutoCommit()
> // cannot be used here
> tx.commit();
> conn1.close();
> conn1 = null;
> } catch(Exception e) {
> e.printStackTrace(System.out);
> }
> finally {
> if (conn1 != null) {
> try {
> conn1.close();
> } catch (Exception e) {
> // ignore
> }
> }
> }

> #### Pooling Connections

> Creating and destroying database connections are expensive operations. Connection pooling allows reuse of persistent connections. When an application closes a connection, the connection is returned to the pool.

> For details about connection pool settings (maximum number of connections, connection timeout, and so on), see Creating a Connection Pool.

> Note | Connection pooling is an automatic feature of the Sun Java System Application Server. The API is not exposed.

> #### Sharing Connections

> 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 are shared only if res-sharing-scope is set to Shareable in the J2EE deployment descriptor. To turn off connection sharing, set res-sharing-scope to Unshareable.

> #### Opening and Closing Connections

> When you open a JDBC connection using DataSource.getConnection(), the Sun Java System Application Server allocates connection resources. You can use the default user name and password defined for your connection pool or you can pass in other values. For details about setting the default user name and password, see Creating a Connection Pool.

> When a connection is no longer needed, call Connection.close() to free the connection resources. Always reestablish connections before continuing database operations after you call Connection.close().

> Tip | Using Connection.close() in a finally block is good programming practice. Depending on your database vendor, you may have to close statements as well. Connections are not automatically closed; an application must close its connections.

> Use Connection.isClosed() to test whether the connection is closed. This method returns false if the connection is open, and returns true only after Connection.close() is explicitly called.

> Some connection behavior depends on whether it is a local or global connection:

> - You can manage the transaction context for local connections using the setAutoCommit(), commit(), and rollback() methods.
> - Transaction management methods such as setAutoCommit(), commit(), and rollback() are not allowed for global connections.

> ### Using JDBC Transaction Isolation Levels

> For general information about transactions, see Chapter 3, “Using the Transaction Service,” and the Sun Java System Application Server Administrator’s Guide.

> Not all database vendors support all transaction isolation levels available in the JDBC API. The Sun Java System Application Server permits specifying any isolation level your database supports, but throws an exception against values your database does not support. The following table defines transaction isolation levels.

> Table 2-18 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.

> Specify or examine the transaction isolation level for a connection using the Connection.setTransactionIsolation() and Connection.getTransactionIsolation() methods, respectively. 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.

> Note | Applications that change the isolation level on a pooled connection programmatically risk polluting the pool, which can lead to errors.

> ##

> Using the JDBC API in Application Layers

> The JDBC API is part of the Sun Java System Application Server runtime environment. This means the JDBC API is always available any time you use Java to program an application. In a typical multi-tiered server application, you use the JDBC API to access an EIS database from a client, from the presentation layer, in servlets, and in EJB components.

> However, in practice it makes sense—for security and portability reasons—to restrict database accesses to the middle layers of a multi-tiered server application. In the Sun Java System Application Server programming model, this means placing all JDBC calls in servlets or preferably EJB components.

> ### Using the JDBC API in EJB Components

> There are two reasons to place JDBC calls in EJB components:

> - Placing all JDBC calls inside EJB components makes your application more modular and more portable. Because you use EJB components as building blocks for many applications with little or no changes, you can use an EJB component to maintain a common interface to your EIS database.
> - EJB components provide built-in mechanisms for transaction control. Placing JDBC calls in well-designed EJB components frees you from programming explicit transaction control using the JDBC API or java.transaction.UserTransaction, which provides low-level transaction support under the JDBC API.

> Note | For container-managed transactions, use a globally available JDBC resource to create a global connection so that the EJB transaction manager controls the transaction.

> For more information about transactions in EJB components, see “Transaction Management” on page 52 and the Sun Java System Application Server Developer’s Guide to Enterprise JavaBeans Technology.

> ### Using the JDBC API in Servlets

> Servlets are at the heart of a Sun Java System Application Server application. They stand between a client interface, such as an HTML or JSP™ page (a page created with the JavaServer Pages™ technology), and the EJB components that do the bulk of an application’s work.

> The Sun Java System Application Server applications use JDBC calls embedded in EJB components for most database accesses. This is the preferred method for database accesses using the Sun Java System Application Server because it enables you to take advantage of the transaction control built into EJB components and their containers. Servlets, however, can also provide database access through the JDBC API.

> In some situations, accessing a database directly from a servlet can offer a speed advantage over accessing a database from EJB components. There is less call overhead, if an application is spread across servers so that EJB components are accessible only through the Java Remote Method Interface (RMI).

> If access to a database is from a servlet, use the JDBC 2.0 RowSet interface to interact with the database. A row set is a Java object that encapsulates a set of rows that have been retrieved from a database or other tabular data source, such as a spreadsheet. The RowSet interface provides JavaBean properties that allow a RowSet instance to be configured to connect to a database and retrieve a set of rows.

> ##

> Sample Applications

> JDBC sample applications are in the following directory:

> install_dir/samples/jdbc

> Previous Contents Index Next

> Copyright 2004 Sun Microsystems, Inc. All rights reserved.