O'Reilly Media | Java Programming with Oracle JDBC

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle JDBC is where the write-once-run-anywhere database meets Java, the write-once-run-anywhere programming language. JDBC acts as the bridge between Oracle and Java. But what is JDBC? JDBC is a Java API for executing dynamic SQL statements. Oracle JDBC is for executing dynamic SQL statements in a standard way and for leveraging Oracle's extended functionality. Consequently, when you use Oracle as your persistent storage, you have to make a decision early on as to whether to program for portability or for additional performance and functionality.

Before we start our discussion on how you can use JDBC, I think it's appropriate to cover some required background information. In this chapter, we'll start by looking at the architecture of the JDBC API. Then we'll continue by defining client/server and four different types of Oracle clients. Finally, we'll finish with my soapbox speech about how it's important to use the set capabilities of SQL.

In this section, I will try to give you the big picture of the JDBC API. Given this overview, you'll have a contextual foundation on which to lay your knowledge as you build it chapter by chapter while reading this book.

The JDBC API is based mainly on a set of interfaces, not classes. It's up to the manufacturer of the driver to implement the interfaces with their own set of classes. Figure 1-1 is a class diagram that shows the basic JDBC classes and interfaces; these make up the core API. Notice that the only concrete class is DriverManager. The rest of the core API is a set of interfaces.

Figure 1-1: The interfaces of the core JDBC API

I'll take a second to explain some of the relationships in the diagram. DriverManager is used to load a JDBC Driver. A Driver is a software vendor's implementation of the JDBC API. After a driver is loaded, DriverManager is used to get a

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this section, I will try to give you the big picture of the JDBC API. Given this overview, you'll have a contextual foundation on which to lay your knowledge as you build it chapter by chapter while reading this book.

The JDBC API is based mainly on a set of interfaces, not classes. It's up to the manufacturer of the driver to implement the interfaces with their own set of classes. Figure 1-1 is a class diagram that shows the basic JDBC classes and interfaces; these make up the core API. Notice that the only concrete class is DriverManager. The rest of the core API is a set of interfaces.

Figure 1-1: The interfaces of the core JDBC API

I'll take a second to explain some of the relationships in the diagram. DriverManager is used to load a JDBC Driver. A Driver is a software vendor's implementation of the JDBC API. After a driver is loaded, DriverManager is used to get a Connection. In turn, a Connection is used to create a Statement, or to create and prepare a PreparedStatement or CallableStatement. Statement and PreparedStatement objects are used to execute SQL statements. CallableStatement objects are used to execute stored procedures. A Connection can also be used to get a DatabaseMetaData object describing a database's functionality.

The results of executing a SQL statement using a Statement or PreparedStatement are returned as a ResultSet. A ResultSet can be used to get the actual returned data or a ResultSetMetaData object that can be queried to identify the types of data returned in the ResultSet.

The six interfaces at the bottom of Figure 1-1 are used with object-relational technology. A Struct is a weakly typed object that represents a database object as a record. A Ref is a reference to an object in a database. It can be used to get to a database object. An Array is a weakly typed object that represents a database collection object as an array. The

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In Part II, we'll examine how to establish JDBC connections from four types of Oracle clients: an application, an applet, a servlet, and an internal client. But first, I need to define what I mean by client. Let's begin that discussion by clarifying the term client/server.

Over the years, I've heard countless, sometimes convoluted, definitions for the term client/server. This has led to a great deal of confusion when discussing application architecture or platforms. So you have a consistent definition of the term client/server, I propose we use Oracle's early definition for client/server and then define the four different types of clients we'll encounter in this book.

It's my opinion that Oracle is in large part responsible for the definition and success of the so-called client/server platform. From its beginnings, Oracle has been a client/server database. Here's my definition of client/server:

Any time two different programs run in two separate operating-system processes in which one program requests services from the other, you have a client/server relationship.

In the early days, before the use of networks, Oracle applications consisted of the Oracle RDBMS running on one operating-system process as the server and one or more end users running their application programs in other operating-system processes. Even though this all took place on one physical computer, it's still considered client/server. The Oracle RDBMS represents the server, and the end-user application programs represent the clients.

With the use of networks, the communication between the client and server changed, but the client/server relationship remained the same. The key difference was that client and server programs were moved to different computers. Examples of this are the use of C, C++, VisualBasic, PowerBuilder, and Developer 2000 to develop applications that run on personal computers and in turn communicate with an Oracle database on a host computer using TCP/IP via Net8. This type of scenario is what most people think of when they hear the term client/server. I call this type of client/server architecture

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

OK. Get ready. Here's my soapbox speech. A final word before you start. Don't make the mistake of becoming dependent on a procedural language and forgetting how to use the set-oriented nature of SQL to solve your programming problems. In other words, make sure you use the full power of SQL. A common example of this phenomenon is the batch updating of data in a table. Often, programmers will create a program using a procedural language such as PL/SQL or Java, open a cursor on a table for a given set of criteria, then walk through the result set row by row, selecting data from another table or tables, and finally updating the original row in the table with the data. However, all this work can be done more quickly and easily using a simple SQL UPDATE statement with a single- or multicolumn subquery.

I can't emphasize enough how important it is for you to know the SQL language in order to get the most from using JDBC. If you don't have a lot of experience using SQL, I suggest you read SQL in a Nutshell, by Kevin Kline with Daniel Kline (O'Reilly)or Oracle: The Complete Reference, by George Koch and Kevin Loney (Osborne McGraw-Hill).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In Chapter 1, I defined four client types. In this chapter, I'll discuss how to make a database connection from the first type of client, an application. Establishing a database connection may sound like an easy task, but it's often not, because you lack the necessary information. In this chapter, I'll not only explain the ins and outs of making a connection but also talk about the different types of connections you can make and point out the advantages of each.

In order to connect a Java application to a database using JDBC, you need to use a JDBC driver. This driver acts as an intermediary between your application and the database. There are actually several types of JDBC drivers available, so you need to choose the one that best suits your particular circumstances. You also need to be aware that not all driver types are supported by Oracle, and even when a driver type is supported by Oracle, it may not be supported by all versions of Oracle.

Sun has defined four categories of JDBC drivers. The categories delineate the differences in architecture for the drivers. One difference between architectures lies in whether a given driver is implemented in native code or in Java code. By native code, I mean whatever machine code is supported by a particular hardware configuration. For example, a driver may be written in C and then compiled to run on a specific hardware platform. Another difference lies in how the driver makes the actual connection to the database. The four driver types are as follows:

Type 1: JDBC bridge driver: This type uses bridge technology to connect a Java client to a third-party API such as Oracle DataBase Connectivity (ODBC). Sun's JDBC-ODBC bridge is an example of a Type 1 driver. These drivers are implemented using native code.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In order to connect a Java application to a database using JDBC, you need to use a JDBC driver. This driver acts as an intermediary between your application and the database. There are actually several types of JDBC drivers available, so you need to choose the one that best suits your particular circumstances. You also need to be aware that not all driver types are supported by Oracle, and even when a driver type is supported by Oracle, it may not be supported by all versions of Oracle.

Sun has defined four categories of JDBC drivers. The categories delineate the differences in architecture for the drivers. One difference between architectures lies in whether a given driver is implemented in native code or in Java code. By native code, I mean whatever machine code is supported by a particular hardware configuration. For example, a driver may be written in C and then compiled to run on a specific hardware platform. Another difference lies in how the driver makes the actual connection to the database. The four driver types are as follows:

Type 1: JDBC bridge driver: This type uses bridge technology to connect a Java client to a third-party API such as Oracle DataBase Connectivity (ODBC). Sun's JDBC-ODBC bridge is an example of a Type 1 driver. These drivers are implemented using native code.
Type 2: Native API (part Java driver): This type of driver wraps a native API with Java classes. The Oracle Call Interface (OCI) driver is an example of a Type 2 driver. Because a Type 2 driver is implemented using local native code, it is expected to have better performance than a pure Java driver.
Type 3: Network protocol (pure Java driver)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Installing the JDBC drivers varies depending on whether you use the OCI driver or the Thin driver. Let's start with the OCI driver installation.

To install the OCI driver software, follow these steps:

Install the Oracle client software from its distribution CD.
Add the appropriate classesXXX.zip file to your CLASSPATH environment variable.
If you are using Java 2 Enterprise Edition (J2EE), add the appropriate classesXXX.zip file to your J2EE_CLASSPATH environment variable.
Add the client binaries to your PATH environment variable.
On Unix or Linux, add the client binaries to the LD_LIBRARY_PATH environment variable.

Section 2.2.1.1: Install the Oracle Client

If you are going to use the OCI driver, you'll need the Oracle8i Oracle Client distribution media or the Oracle Enterprise Edition distribution media (typically, these are on CD-ROM) to install the client software. Follow your operating-system-specific instructions to execute the Oracle Universal Installer. Then simply follow the installation instructions from the Oracle Universal Installer's screen.

The Oracle Universal Installer creates several directories during the installation of the client software on your computer. The directories of interest to you are all under ORACLE_HOME\jdbc. ORACLE_HOME refers to the directory where the Oracle client software was installed. Typically, these directories are:

demo/samples

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

After you've installed the appropriate driver, it's time to get down to some programming and learn how to establish a database connection using JDBC. The programming involved to establish a JDBC connection is fairly simple. Here are the steps to follow:

Add import statements to your Java program so the compiler will know where to find the classes you'll be using in your Java code.
Register your JDBC driver. This step causes the JVM to load the desired driver implementation into memory so it can fulfill your JDBC requests.
Formulate a database URL. That is, create a properly formatted address that points to the database to which you wish to connect.
Code a call to the DriverManager object's getConnection( ) method to establish a database connection.

Import statements tell the Java compiler where to find the classes you reference in your code and are placed at the very beginning of your source code. To use the standard JDBC package, which allows you to select, insert, update, and delete data in SQL tables, add the following imports to your source code:

import java.sql.* ;          // for standard JDBC programs
import java.math.* ;         // for BigDecimal and BigInteger support

If you need to use JDK 1.1.x, you can still get most of Oracle's JDBC 2.0 features by including the following import statement in your program:

import oracle.jdbc2.*        // for Oracle interfaces equivalent to
                             // JDBC 2.0 standard package for JDK 1.1.x

Keep in mind, however, that when you do start using JDK 1.2.x or higher you'll have to modify your code and remove this import statement.

Without the imports shown here you'll have to explicitly identify each class file with its full package path and name. For example, with

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If you're a PL/SQL programmer, then the concept of exceptions will not be all that new to you. If you're new to Java and have not previously used a programming language that uses exception handling, then this material may get confusing. Hang in there! By the time we're done, you should have a fairly good idea of what exceptions are and how to deal with them in your JDBC programs.

In Java, exception handling allows you to handle exceptional conditions such as program-defined errors in a controlled fashion. When an exception condition occurs, an exception is thrown. The term thrown means that current program execution stops, and control is redirected to the nearest applicable catch clause. If no applicable catch clause exists, then the program's execution ends.

Section 2.4.1.1: Try blocks

Both the JVM and you -- explicitly in your own code -- can throw an exception. Java uses a try-catch-finally control block similar to PL/SQL's BEGIN-EXCEPTION-END block. The try statement encloses a block of code that is "risky" -- in other words, which can throw an exception -- and that you wish to handle in such a way as to maintain control of the program in the event that an exception is thrown. Exceptions thrown in a try block are handled by a catch clause coded to catch an exception of its type or one of its ancestors. For example, when using JDBC, the exception type thrown is usually a SQLException.

A try statement can have any number of catch clauses necessary to handle the different types of exceptions that can occur within the try block. A try block can also have a finally clause. The finally clause is always executed before control leaves the try block but after the first applicable catch clause. Here is the general form of a try block:

try {
 // Your risky code goes between these curly braces!!!
}
catch(Exception e) {
 // Your exception handling code goes between these curly braces, 
 // similar to the exception clause in a PL/SQL block.
}
finally {
 // Your must-always-be-executed code goes between these curly braces.
}

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this chapter, we'll explore issues that are specific to using JDBC with applets. We'll begin by asking the question: "What type of JDBC driver supports an applet, and for which versions of the JDK?" Then we'll talk about other things you need to know, such as the life cycle of an applet, when to open and close a database connection, how to package an applet that uses Oracle JDBC classes, how to deal with the restrictions placed on JDBC connections by the secure environment of your browser's JVM, and how to connect through a firewall.

For applets, you have only one driver choice: the client-side Thin driver. Since it's a 100% pure Java driver, you can package it with your applet's archive so it's downloaded by the browser along with your applet. I'll discuss how to package the Thin driver with your applet later in this chapter. For now, just keep in mind as we go along that you'll need to package the appropriate classesXXX.zip file with your applet, and you'll be using the Thin database URL syntax discussed in Chapter 2.

As of Oracle8i Version 8.1.6, JDK 1.0.x is no longer supported by Oracle. Instead, Oracle8i now supports only JDK Versions 1.1.x and 1.2.x. Table 3-1 lists the support files you need to package with your applet to support each of these versions.

Table 3-1: JDBC support files
JDK version	JDBC classes	National Language Support classes
JDK 1.1.x	`classes111.zip`	`nls_charset11.zip`

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

For applets, you have only one driver choice: the client-side Thin driver. Since it's a 100% pure Java driver, you can package it with your applet's archive so it's downloaded by the browser along with your applet. I'll discuss how to package the Thin driver with your applet later in this chapter. For now, just keep in mind as we go along that you'll need to package the appropriate classesXXX.zip file with your applet, and you'll be using the Thin database URL syntax discussed in Chapter 2.

As of Oracle8i Version 8.1.6, JDK 1.0.x is no longer supported by Oracle. Instead, Oracle8i now supports only JDK Versions 1.1.x and 1.2.x. Table 3-1 lists the support files you need to package with your applet to support each of these versions.

Table 3-1: JDBC support files
JDK version	JDBC classes	National Language Support classes
JDK 1.1.x	`classes111.zip`	`nls_charset11.zip`
JDK 1.2.x	`classes12.zip`	`nls_charset12.zip`

In addition to matching up your applet with the correct support files for the JDK version with which you are developing, you must also make sure that the browser you're targeting (i.e., on which you intend to run your applet) supports the same JDK that you are using to develop the applet. Currently, you either need to use JDK 1.1.x or need to depend on your end users having the Java 2 browser plug-in installed in their browsers. Without that plug-in, the currently predominant versions of both Internet Explorer and Netscape Navigator support only JDK 1.1.x. The newest versions of these browsers, such as Netscape Navigator 6 and other browsers programmed using Java, support JDK 1.2.x or later.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

From a programmer's perspective, an applet has four stages to its life cycle. They are defined by the following four methods that are called by the browser as the applet is loaded and run:

init( ): This is called just after an applet is created and before the applet is displayed in the browser. It is normally used to perform any initialization that should take place only once in the life cycle of the applet. This includes the creation of a thread to run the applet.
start( ): This is called when the applet becomes visible in your browser and is used to start the thread that runs the applet.
stop( ): This is called when the applet is no longer visible. When this method is called, a well behaved applet will put its thread to sleep, or stop the thread entirely, in order to conserve computer resources.
destroy( ): This is called when the applet is purged from your browser's memory cache. It is used to stop the applet's thread and to release any other computer resources the applet may be using.

The choice of which of these methods you use to open and close a database connection is not straightforward. You must consider how you will use the connection within your applet. If your applet will open a database connection, retrieve some data, then close the connection, and do this only once, you may wish to perform these functions in

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

After you have written your applet, you'll want to combine its class files with those from the appropriate Oracle classesXXX.zip file into a single zip or jar file as you did for Example 3-1. This step is necessary because an applet using JDBC is naturally quite complex and contains many classes. Getting to just one file makes things easier to manage. It is also simpler and more efficient to specify just one file in the HTML APPLET tag rather than specify multiple archive files.

For simplicity's sake, this discussion on packaging focuses on the use of JDK 1.2. If you are using JDK 1.1, the syntax for using the jar tool to create the jar file will be slightly different. If you use WinZip, the procedure will be the same as it is for JDK 1.2.

During the development stage for an applet, you can begin your packaging effort by simply making a copy of the Oracle classes12.zip file. Give it the name of your archive file but retain the .zip extension. Then add your applet's class files, uncompressed, to the zip file that you just copied and renamed. Why uncompressed? I actually don't know. This is an Oracle recommendation. I have used them as compressed class files when I have created a jar file, but I have never done so using a zip file. For example, if you're going to create a zip file for an applet named TestAppletPolicy, you should follow these steps:

Copy the file classes12.zip to TestAppletPolicy.zip. On a Windows system, you can do this by executing a command such as:
```
copy c:\windows\ora81\jdbc\lib\classes12.zip TestAppletPolicy.zip
```
Add your applet's class files to TestAppletPolicy.zip using your favorite zip utility. With WinZip, you can right-click on the TestAppletPolicy.class file and select Add to Zip. Then just select TestAppletPolicy.zip as your destination zip file.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Applets run in a JVM in your browser. For security reasons, applets, by default, run with restricted access to your computer's local resources. This restricted access to your computer's local resources, or " sandbox" as it is affectionately (sometimes not-so-affectionately) called, limits an applet's ability to contact other computers over the network. The rule is that applets are limited to opening sockets , or network connections, only to the host from which they are downloaded. In effect, this limits any applet to connecting to a database only on the same host from which it was downloaded. If your database is installed on the same host as your web server, then this does not pose a problem, but often, databases reside on a host of their own. When the latter is the case, there are two ways you can work around this limitation using JDBC. The first is to use Oracle's Connection Manager. The second is to get socket permissions for your applet.

If you try to connect to a database on a host other than the source of the applet, you'll get a security exception. For example, the following is a security exception received from Internet Explorer while running the applet named TestApplet:

init(  ): loading OracleDriver for applet created at 2000-09-30 19:20:21.606
init(  ): getting connection
com.ms.security.SecurityExceptionEx[TestAppletInitDestroy.init]: cannot connect to 
"dssnt01"

Here is the same exception obtained from Netscape Navigator:

init(  ): loading OracleDriver for applet created at 2000-09-30 19:22:33.576
init(  ): getting connection
netscape.security.AppletSecurityException: security.Couldn't connect to 'dssnt01' 
with origin from 'dssw2k01'.

Let's continue our discussion by looking at how to get around this restriction by using Oracle's Connection Manager.

Connection Manager is a lightweight, highly scalable, middle-tier program that receives and forwards Net8 packets from one source to another. When Connection Manager resides on the same host as a web server, an applet can get around the network connection restriction of the sandbox by making a connection to Connection Manager, which will in turn forward any Net8 requests on to the appropriate database listener. As I stated in Chapter 2, you can classify the combined use of Oracle's Thin driver together with Connection Manager as a Type 3 driver. To use Connection Manager, you must install it on the same host as your applet's web server. Then you must use a special form of database URL. And you thought we had covered every possible type didn't you? First, let's cover Connection Manager's installation.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Another constraint that you may have to deal with when accessing a remote database is the use of firewalls. Firewalls allow only desirable connections between networks. This means that under normal circumstances, a firewall will prevent your applet from connecting to a database located on the other side of the firewall. The solution to this problem is to use a firewall that supports Net8. Additionally, you need to use yet another special form of the Net8 connection string.

Firewalls use a set of rules to determine which clients can connect through them. These rules are based on a client's hostname, DNS alias, or IP address. A firewall goes through several steps to determine whether to allow an applet to connect and compare a client's hostname against its set of rules. If a match is not found, the firewall extracts the IP address of a client and compares it with the rules. Since an applet has restricted access to the local system, the JDBC Thin driver cannot get the name of its host to pass in its connection request. You must, therefore, configure a firewall to allow connections from the applet's IP address.

You must also never allow the hostname _ _jdbc_ _ to be used in a firewall's set of rules. This literal has been coded into Net8-compatible firewalls to force the lookup of the IP address. If you inadvertently add this hostname to a firewall's set of rules, any Oracle JDBC Thin driver will be able to pass through the firewall.

You must also take into consideration that your applet may have to use a security policy to access a remote firewall just as it needed a security policy to enable access to a remote database. The only difference is the port you specify when you set up your socket permissions. If the firewall resides on the same host as your web server, you'll have no problem making a connection. If it does not, you'll have to use a security policy to give it socket permissions to access the port on the firewall's server.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Now that you understand the connection restrictions that JDBC applets face, let's discuss the best time to use each solution.

For an intranet-based application, Connection Manager is your easiest solution. If an applet will be used solely on your internal network, common sense dictates that there is probably no need to go through the additional work of signing your applets to establish trust, for you know who has created them, and you implicitly trust the individuals that work for your organization. In addition, and for the same reason, there is no need to set up a security policy to restrict the applet's access to a specified resource. By using Connection Manager, you do not need to go through either of these steps to establish a remote connection, thereby saving you the costs of signing your applets and administering local policy on each user's desktop.

On the other hand, for an Internet-based application, you will want the signed applet to verify a trust chain and to force the use of a security policy to restrict the applet's access to local resources. As an end user of an Internet-based applet, you'll want to verify that the applet is from the source you trust and prevent the applet from accessing any restricted resources. In addition, you may be required to pass through a firewall to access a remote database, in which case the applet's signer will need to use the firewall URL syntax to establish a remote database connection through your firewall and the signer's firewall.

Now that you are aware of the special considerations of establishing a connection in an applet, let's move on to those for servlets in Chapter 4.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this chapter, we'll explore issues that are specific to using JDBC with servlets. Unlike applets, servlets can use the OCI driver as well as the Thin driver. Like applets, servlets have a distinct life cycle that will impact your selection of a connection strategy. Let's begin our exploration by examining your driver choices when developing servlets.

With servlets, you can use either the OCI driver or the Thin driver. As is the case when developing applications, I recommend you use the Thin driver unless one of the following considerations applies to your work:

You make heavy use of stored procedures.
You have the ability to make a Bequeath connection to the database.

For most practical purposes, the Thin driver is just as fast as the OCI driver. One exception is when you execute stored procedures. When stored procedures are invoked, the Thin driver can take up to twice as long as the OCI driver to execute a call. What does this mean in terms of response time? If it typically takes half a second for the OCI driver to make a stored-procedure call, then it will take the Thin driver one second. That's not much of a problem if you make only one stored-procedure call for each call you make to your servlet. The situation changes, however, if you make multiple stored-procedure calls for each call to your servlet. In such a case, your response time can deteriorate quickly. In our scenario, three stored-procedure calls will lead to a three-second delay. So if your servlets typically make several calls to stored procedures, you should consider using the OCI driver.

The other reason to use the OCI driver is to allow your servlet to make a Bequeath connection to the database. Using the Bequeath protocol results in a direct connection to a dedicated server process that allows your servlet to communicate directly with the Oracle8i database. You bypass the Net8 listener process and eliminate the layer of software associated with TCP/IP. Consequently, a Bequeath connection can result in a significant gain in response time as opposed to a TCP/IP connection. Bequeath connections, however, can be made only in one situation -- your servlet container and your database must reside on the same host.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

With servlets, you can use either the OCI driver or the Thin driver. As is the case when developing applications, I recommend you use the Thin driver unless one of the following considerations applies to your work:

You make heavy use of stored procedures.
You have the ability to make a Bequeath connection to the database.

For most practical purposes, the Thin driver is just as fast as the OCI driver. One exception is when you execute stored procedures. When stored procedures are invoked, the Thin driver can take up to twice as long as the OCI driver to execute a call. What does this mean in terms of response time? If it typically takes half a second for the OCI driver to make a stored-procedure call, then it will take the Thin driver one second. That's not much of a problem if you make only one stored-procedure call for each call you make to your servlet. The situation changes, however, if you make multiple stored-procedure calls for each call to your servlet. In such a case, your response time can deteriorate quickly. In our scenario, three stored-procedure calls will lead to a three-second delay. So if your servlets typically make several calls to stored procedures, you should consider using the OCI driver.

The other reason to use the OCI driver is to allow your servlet to make a Bequeath connection to the database. Using the Bequeath protocol results in a direct connection to a dedicated server process that allows your servlet to communicate directly with the Oracle8i database. You bypass the Net8 listener process and eliminate the layer of software associated with TCP/IP. Consequently, a Bequeath connection can result in a significant gain in response time as opposed to a TCP/IP connection. Bequeath connections, however, can be made only in one situation -- your servlet container and your database must reside on the same host.

Now that you understand your options for selecting an Oracle driver for servlet development, let's examine the life cycle of a servlet to see how it will affect your strategy for making a connection.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

From a programmer's perspective, a servlet has three stages to its life cycle. They are defined by the following three methods, or types of methods:

init( ): This method is normally used to perform any initialization that should take place only once in the lifetime of the servlet. The init( ) method is invoked automatically before any of the servlet's doXXX( ) methods can be called.
doXXX( ): The various do methods -- doGet( ), doDelete( ), doPost( ), and doPut( ) -- are called as needed by web users to satisfy their dynamic content and form processing needs.
destroy( ): This method is called just before the servlet container removes the servlet from memory, which typically happens when the servlet container itself is shutting down.

Given the life cycle described here, you have four strategies for making a database connection. The differences between these strategies hinge on when the connection is made and on whether connections are shared between servlets. The four strategies are:

Per-transaction connection: You load the Oracle JDBC driver in the servlet's init( ) method, open a connection at the beginning of each doXXX( ) method, and close that connection at the end of each doXXX( ) method.
Dedicated connection: You use a combination of the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Of the four strategies outlined in this chapter, a cached connection strategy is best suited for dynamic content that does not need to be kept secure -- for example, a public web site that produces its content by retrieving information from a database. If you intend to use authentication to limit your connection audience to a select group of users but don't need to keep track of who's making changes to data, the cached connection strategy is still the most efficient. However, you'll need to add a layer of code to your servlets to prompt the user for authentication and to verify their credentials against an application-maintained list of valid users. You'll also need to store the resulting authorization in a cookie or session object in order to maintain it from one servlet call to the next. You can even use a cached connection for a form processing application, but you will need to use an even more elaborate authenticate-and-store methodology. By the time you're done adding all the extra code to your servlets to manage the authentication process, it may just be easier to use a session connection.

If you have an application that requires a high level of security, then a session-based connection is a better fit. One example of such an application is a medical application in which each transaction needs to be logged to an audit trail showing who added or modified data. With a session-based connection, you can have each application user log in using a distinct database username and password. This facilitates audit logging, because you can use the auditing features that come as part of the database itself, rather than writing your own. Using the database's auditing facility also helps prevent any malicious tampering with the audit trail data.

For more information on writing servlets, I suggest you read Java Servlet Programming by Jason Hunter and William Crawford (O'Reilly). You can also browse information about the reference implementation servlet container, Tomcat, which I used to test the examples in this chapter, at

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As you probably already know, the Oracle8i database engine includes an embedded JVM known as the JServer. In this chapter, we'll explore the issues that are specific to using JDBC to connect objects that reside in Oracle8i's internal JVM to a database. I say a database rather than the database, because JDBC can be used to connect internally to the local database or externally to another database. As in the other connection chapters, we'll cover the types of Oracle drivers available. We'll also go over lots of examples to show each type of driver in use and talk about the types of Java objects that the internal JVM supports. Let's begin our discussion by looking at the Oracle drivers that are available for an internal client.

To support the use of JDBC by Java code running within JServer, Oracle supplies the following two server-side JDBC drivers:

Server-side internal driver: The server-side internal driver is used by stored procedures, EJB, or any other type of object that resides in Oracle8i's internal JVM to establish a direct connection internally to the local database. The server-side internal driver runs in the same memory space as the database kernel, the SQL engine, and the JServer JVM. Any Java object that uses this driver to connect to the database has the same default session as any PL/SQL stored procedure or SQL object. This driver has all the same APIs as the client-side drivers.
Server-side Thin driver: The server-side Thin driver can be used by stored procedures, EJB, and other objects to access databases other than the one in which they are running. The server-side Thin driver is, for all practical purposes, exactly the same as the client-side Thin driver, except that it is an internal driver.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

To support the use of JDBC by Java code running within JServer, Oracle supplies the following two server-side JDBC drivers:

Server-side internal driver: The server-side internal driver is used by stored procedures, EJB, or any other type of object that resides in Oracle8i's internal JVM to establish a direct connection internally to the local database. The server-side internal driver runs in the same memory space as the database kernel, the SQL engine, and the JServer JVM. Any Java object that uses this driver to connect to the database has the same default session as any PL/SQL stored procedure or SQL object. This driver has all the same APIs as the client-side drivers.
Server-side Thin driver: The server-side Thin driver can be used by stored procedures, EJB, and other objects to access databases other than the one in which they are running. The server-side Thin driver is, for all practical purposes, exactly the same as the client-side Thin driver, except that it is an internal driver.

Now that you have an overview of what drivers are available, let's take a closer look at the server-side internal driver.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As with the client-side drivers, when using the server-side internal driver you need to formulate an appropriate database URL for use with the

DriverManager.getConnection(
)

method. With the server-side internal driver you have two choices for a URL:

jdbc:oracle:kprb:
jdbc:default:connection:

The last colon characters on these URLs are necessary only if you want them to work. I say this because I spent several nights unsuccessfully trying to make either of these URLs work. The documentation I was reading showed them used without and with the colon. My preference was to leave off the colon, hence my troubles. When I finally broke down and used the colon on the end, the URLs worked. So, as I say: the last colons on these URLs are necessary only if you want them to work.

I recommend you use jdbc:oracle:kprb: as the database URL when connecting through the server-side internal driver. It has the same basic format as the rest of the URLs we've used so far, and you can use it with any form of the getConnection( ) method.

When you invoke getConnection( ) to connect through the server-side internal driver, any unneeded parameters will be ignored. For example, if you pass a username and password, they are simply ignored, because you are using a default connection. This default connection was created when you connected to the database to invoke your stored Java program. This means you can take a Java program you've written to load data into Oracle, change the driver type to kprb, load it into the database, add an appropriate Java security policy to the database for file access permissions, and execute the program without any major modifications. Using getConnection( ) in this way is a good programming practice. It means you'll consistently use the same methodology to connect to the database for both internal and external programs. This will make it easier for you, and especially for the next guy or gal, to maintain your code.

The URL

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

With the server-side Thin driver you now have two ways to connect to another Oracle database from a Java program in an Oracle database. You can create a database link or use the server-side Thin driver. In my opinion, it's a much better solution to use database links than to use the server-side Thin driver. With database links you get the following advantages:

Transparent distributed transaction management
Centralized administration of the database connection
Centralized database security

To access another database with the Thin driver, you need to use:

An XAConnection for distributed transaction management
An appropriate database URL in each Java object

However, you also open the database to security compromises. For example, to access an Oracle database outside of the current database, you need to set up a SocketPermission security policy to allow your Java program to open a socket to the external database. Once that policy is created, any program can use it to open external connections. This also means that external programs can access the current database without going through its authentication system. That said, there may be times when an external connection using the Thin driver is the right solution to a problem. So let's examine the use of the Thin driver by working through an example.

Example 5-4 contains a stored procedure that makes a connection to an external database using the Thin driver. This stored procedure, TestExternalConnection, uses the same database URL syntax that is used with the client-side Thin driver.

Example 5-4. A stored procedure to test an external connection

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle states that you can run any Java program in JServer. This is not a false statement, but since JServer does not support servlets in Version 8.1.6, you have to consider the usefulness of what can be run. You are really limited to two types of objects:

Stored procedures: These refer to any Java object with a static method that can be wrapped with a SQL function, procedure, or package.
Enterprise JavaBeans (EJB): These use JDBC but are not executed using JDBC. Instead, they are executed using the IIOP protocol.

Since the use of JDBC by stored procedures and EJB is the same, I see no point in covering EJB here. One thing worth noting about internal Java programs is what happens when you make calls to System.out.println( ) and System.err.println( ). Where does this output go?

By default, any calls to the methods

System.out.println(
)

and System.err.println( ) goes into a trace file. That's not very useful, but you can make the output go to the SQL*Plus screen buffer. The SQL*Plus screen buffer is the buffer that the SYS.DBMS_OUTPUT.PUT_LINE stored procedure uses. To send Java output to the SQL*Plus screen buffer, call the SYS.DBMS_JAVA.SET_OUTPUT( ) stored procedure from within your Java stored procedure. The syntax for doing that is:

SYS.DBMS_JAVA.SET_OUTPUT(BUFFER_SIZE IN NUMBER)

BUFFER_SIZE is an optional parameter. The default buffer size is 2,000 bytes, and the maximum value is 1,000,000 bytes. Of course, the SQL*Plus buffer is useful only while using SQL*Plus. But the buffer output when using SQL*Plus can be an invaluable troubleshooting tool during development.

Example 5-8 is a stored procedure to test the use of SET_OUTPUT. TestDbmsJavaSetOutput

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Table of Contents

Section 2.2.1.1: Install the Oracle Client

Section 2.4.1.1: Try blocks