| Sun ONE Application Server 7 Developer's Guide to J2EE Features and Services |
Chapter 2
Using the JDBC™ API for Database AccessThis chapter describes how to use the Java™ Database Connectivity (JDBC™) API for database access with the Sun™ ONE Application Server. This chapter also provides high level JDBC implementation instructions for servlets and EJB™ components using the Sun ONE Application Server. The Sun ONE Application Server supports the core JDBC 3.0 API and the JDBC 2.0 extensions.
This chapter contains the following sections:
Introducing the JDBC APIFrom 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 ONE 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 ONE 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
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 ONE 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 ONE 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.
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 ResourceTo prepare a JDBC resource for use in J2EE applications deployed to the Sun ONE Application Server, perform the following tasks:
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 ONE Application Server, then you must set up the driver. This section covers these topics:
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 ONE Application Server, see the Sun ONE Application Server 7 Platform Summary.
For configurations of certified drivers, see "Configurations for Specific JDBC Drivers".
Making the JDBC Driver JAR Files Accessible
To integrate the JDBC driver into a Sun ONE 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, 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 ONE Application Server classloaders, see the Sun ONE Application Server Developer’s Guide.
???How do you add the JDBC driver JAR files to an app client’s classpath???
Creating a Connection Pool
When you create a JDBC connection pool in the Sun ONE Application Server, you can define many of the characteristics of your database connections.
You can create a connection pool in one of these ways:
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 ONE Application Server Administrator’s Guide.
Using the Administration Interface
To create a JDBC connection pool using the Administration interface, perform the following tasks:
- Open the JDBC component under your server instance.
- Click Connection Pools.
- Click the New button.
- 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.
- 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.
- 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.
- Enter or edit the Datasource Classname value. This is the vendor-supplied DataSource class name.
- 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.
- You can change the Pool Settings listed in the following table.
- You can change the Connection Validation settings listed in the following table. All of these settings are optional.
- You can change the Transaction Isolation settings listed in the following table. Both of these settings are optional.
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.
- 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 ONE 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:password=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. On Windows platforms, colon characters (:) must be escaped with single backslashes (\). For details about using escape characters, see the Sun ONE 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:
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 ONE Application Server Administrator’s Guide.
Using The Administration Interface
To create a JDBC resource using the Administration interface, perform these tasks:
- Open the JDBC component under your server instance.
- Click JDBC Resources.
- Click the New button.
- 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.
- 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.
- Click the OK button.
- Go to the server instance page.
- Click the General tab.
- 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 ONE 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
Configurations for Specific JDBC DriversThe following certified JDBC 2.0 drivers have passed the J2EE Compatibility Test Suite (CTS) when tested with Sun ONE Application Server:
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 4.2 JDBC driver is included with the Sun ONE Application Server by default, except for the Solaris bundled installation, which does not include PointBase. Therefore, unless you have the Solaris bundled installation, you do not need to integrate this JDBC driver with the Sun ONE Application Server.
Configure the connection pool using the following settings:
- Name: You will use this name when you configure the JDBC resource later.
- Database Vendor: PointBase 4.2
- Global Transaction Support, Datasource Classname: See the following table.
- Properties:
Configure the JDBC resource using the following settings:
Data Direct Connect JDBC3.0/ Type4 Driver for Oracle 9.x Databases
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.
- 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.
Configure the JDBC resource using the following settings:
Creating Applications That Use the JDBC APIAn 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
To use connections, you should be familiar with these topics:
For general information about connections and JDBC URLs, see the Sun ONE Application Server Administrator’s Guide.
Looking Up a JDBC Resource
The recommended 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 ONE 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 ONE 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().
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:
Using JDBC Transaction Isolation Levels
For general information about transactions, see Chapter 3, "Using the Transaction Service," and the Sun ONE Application Server Administrator’s Guide.
Not all database vendors support all transaction isolation levels available in the JDBC API. The Sun ONE 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.
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 LayersThe JDBC API is part of the Sun ONE 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 ONE 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.
For more information about transactions in EJB components, see "Transaction Management" and the Sun ONE Application Server Developer’s Guide to Enterprise JavaBeans Technology.
Using the JDBC API in Servlets
Servlets are at the heart of a Sun ONE 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 ONE 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 ONE 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 ApplicationsJDBC sample applications are in the following directory:
install_dir/samples/jdbc
