Tomcat-Oracle How-To for Java Server Pages Book

by Mike Braden
mikeb@mwbinc.com

Companion Book:

JavaServer Pages
O'Reilly
by Hans Bergsten

The JSP Book examples were written to run on the Windows platform using MS Access as the database. This document was produced to list the changes necessary to run the examples on a Unix system connecting to an Oracle database.

The prerequisites are that Oracle should be installed, either on the system that is running Tomcat, or on another system that is accessible from the network on which the Tomcat server is located. In this document, Tomcat and Oracle were installed on the same system. The system utilizes the following:

Solaris 8
Oracle 8i (8.1.5)
Tomcat 3.3m1

Oracle was installed according to Oracle's installation instructions for Solaris and the sample database was created. In Solaris, the user oracle was created as the owner of the Oracle Installation. For this document, we log into Solaris as the user oracle and execute all of the SQLPlus commands that require sysdba level access.

Planning the Database

The database used for the examples is very simple. It consists of two tables, which are non-relational. One user is created and given the rights to create the tables. For more detailed information, see the references section at the end.

The Oracle User for our Examples:

 Table Employee

Table EmployeeProjects

Table InputTest

Note: We are not creating a primary key for the EmployeeProjects table. This table includes multiple UserName entries that are identical. In MS Access, each table must have a primary key and it is possible to make the pair of columns a single paired key. For these examples, it is easier to not have a primary key. In a real-world production database, you would do this differently.

Creating the ora User

Enter the following in SQLPlus as sys. DBA privileges are required to create the new user.

$ sqlplus

SQL*Plus: Release 8.1.5.0.0 - Production on Mon Feb 26 18:36:51 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter user-name: sys
Enter password:

Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

SQL> create user ora9 identified by jspbook;

SQL> grant CONNECT,RESOURCE,UNLIMITED TABLESPACE TO ora9

SQL> exit

Creating the Example Tables

Now we login as the new user (ora9) and create the tables required by the examples.

$ sqlplus ora9/jspbook

SQL*Plus: Release 8.1.5.0.0 - Production on Mon Feb 26 18:48:16 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

SQL> create table Employee
( UserName varchar2(50) constraint user_pk primary key,
Password varchar2(50),
FirstName varchar2(50),
LastName varchar2(50),
Dept varchar2(50),
EmpDate date,
EmailAddr varchar2(50),
ModDate date );

Table created.

SQL> create table EmployeeProjects
( UserName varchar2(50),
ProjectName varchar2(50));

Table created.

SQL> create table InputTest
( MyDate date,
MyNumber number);

Table created.

Now, to verify that the table was created correctly, you can enter the following:

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
EMPLOYEE
EMPLOYEEPROJECTS
INPUTTEST

SQL> describe employee
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(50)
 PASSWORD                                           VARCHAR2(50)
 FIRSTNAME                                          VARCHAR2(50)
 LASTNAME                                           VARCHAR2(50)
 DEPT                                               VARCHAR2(50)
 EMPDATE                                            DATE
 EMAILADDR                                          VARCHAR2(50)
 MODDATE                                            DATE

SQL>

That completes our work in Oracle.

Installing the Oracle JDBC Libraries

Before we can use the new tables that we have setup, the Oracle JDBC drivers need to be made available to the example web applications. This is accomplished by copying the drivers into the WEB-INF/lib directory for the web application. We must also change the extension of the files from .zip to .jar. This will allow Tomcat to automatically load the library files. The file format for .zip and .jar files is identical, so we are able to change the file name to make the change.

Copy the oracle drivers to $TOMCAT_HOME/webapps/ora/WEB-INF/lib directory

# cp $ORACLE_HOME/jdbc/lib/classes111.zip \
$TOMCAT_HOME/webapps/ora/WEB-INF/lib/classes111.jar
# cp $ORACLE_HOME /jdbc/lib/nls_charset11.zip \
$TOMCAT_HOME/webapps/ora/WEB-INF/lib/nls_charset11.jar

$ORACLE_HOME is typically /u01/app/oracle/product/8.1.5 This depends on the version of Oracle as well as the installation specifics of the DBA that installed Oracle.

Note: Normally it is possible to place the .jar files in the $TOMCAT_HOME/lib directory for Tomcat to make them available to all web applications. However, when using this type of installation with the Oracle JDBC drivers, an error occurs when starting Tomcat.

Oracle Connection Statements

The connection statements contain the information for the database in a URL format string. The URL format is as follows:

URL = jdbc:oracle:<drivertype>:<user>/<password>@<database >

The driver we are using is the Oracle thin client driver. The thin driver database must include the hostname of the database server, the TCP/IP port for the server's listener, and the database identifier (SID). The SID is the same SID that was entered during the installation of Oracle for the sample database. Port 1521 is the default TCP port that is created by the Oracle listener configuration.

The following shows how the URL looks using a SID of SAMP:

<ora:useDataSource id="example"
className="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:ora9/jspbook@localhost:1521:SAMP"  
/>

For more information, refer to the Oracle documentation on JDBC.  Links are provided in the reference section at the end of this document.

If you are unable to connect to the database, verify that it is available using the Oracle tnsping utility. You should be able to "ping" the database listener by using:

# $ORACLE_HOME/bin/tnsping localhost

See the Oracle database administration documentation for more information on listeners, tns names and tnsping.

Updating the Example JSP Files

The final step in making the changes for Oracle JDBC is updating the connection statements in the JSP files to use the new drivers and point to the correct database. There are several files within chapters 9, 10 and 11 that contain the connection statements.

In order to determine which files to change, one must look at the source for each file to locate the connection statements. Fortunately, Unix contains some powerful utilities to assist in this task. As shown below, grep allows us to look into all of the files within a given directory to locate the files that contain the connection statements.

# cd $TOMCAT_HOME/webapps/ora/ch9
# grep jdbc *
delete.jsp: className="sun.jdbc.odbc.JdbcOdbcDriver"
delete.jsp: url="jdbc:odbc:example" />
find.jsp: className="sun.jdbc.odbc.JdbcOdbcDriver"
find.jsp: url="jdbc:odbc:example" />
store.jsp: className="sun.jdbc.odbc.JdbcOdbcDriver"
store.jsp: url="jdbc:odbc:example" />

The technique above was used to locate the files that require changes. The following sections detail the changes to each file. Since these files were created on a Windows system, they contain DOS style line endings. To make the source easier to read and edit, we use the dos2unix utility to replace the DOS line endings with Unix style line endings.

Chapter 9 Examples

Files to edit:

ch9/store.jsp
ch9/find.jsp
ch9/delete.jsp

# mv store.jsp store.jsp.orig
# dos2unix store.jsp.orig store.jsp

Edit store.jsp and change

<ora:useDataSource id="example"
className="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:example" />

to

<ora:useDataSource id="example"
className="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:ora9/jspbook@localhost:1521:SAMP" />

# mv find.jsp find.jsp.orig
# dos2unix find.jsp.orig find.jsp

Edit find.jsp and change

<ora:useDataSource id="example"
className="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:example" />

to

<ora:useDataSource id="example"
className="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:ora9/jspbook@localhost:1521:SAMP" />

# mv delete.jsp delete.jsp.orig
# dos2unix delete.jsp.orig delete.jsp

Edit delete.jsp and change

<ora:useDataSource id="example"
className="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:example" />

to

<ora:useDataSource id="example"
className="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:ora9/jspbook@localhost:1521:SAMP" />

Chapter 10 Examples

Files to edit:

ch10/authenticate.jsp
ch10/updateprofile.jsp
ch10/admin/store.jsp
ch10/search/find.jsp
ch10/search/delete.jsp

# grep jdbc *
authenticate.jsp: className="sun.jdbc.odbc.JdbcOdbcDriver"
authenticate.jsp: url="jdbc:odbc:example" />
updateprofile.jsp: className="sun.jdbc.odbc.JdbcOdbcDriver"
updateprofile.jsp: url="jdbc:odbc:example" />

# mv authenticate.jsp authenticate.jsp.orig
# dos2unix authenticate.jsp.orig authenticate.jsp

# mv updateprofile.jsp updateprofile.jsp.orig
# dos2unix updateprofile.jsp.orig updateprofile.jsp

Edit both authenticate.jsp and updateprofile.jsp and change

<ora:useDataSource id="example"
className="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:example" />

to

<ora:useDataSource id="example"
className="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:ora9/jspbook@localhost:1521:SAMP" />

ch10/admin

# grep jdbc *
store.jsp: className="sun.jdbc.odbc.JdbcOdbcDriver"
store.jsp: url="jdbc:odbc:example" />

# mv store.jsp store.jsp.orig
# dos2unix store.jsp.orig store.jsp

Edit store.jsp and change

<ora:useDataSource id="example"
className="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:example" />

to

<ora:useDataSource id="example"
className="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:ora9/jspbook@localhost:1521:SAMP" />

ch10/search

# grep jdbc *
delete.jsp: className="sun.jdbc.odbc.JdbcOdbcDriver"
delete.jsp: url="jdbc:odbc:example" />
find.jsp: className="sun.jdbc.odbc.JdbcOdbcDriver"
find.jsp: url="jdbc:odbc:example" />

Edit delete.jsp and find.jsp and change

<ora:useDataSource id="example"
className="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:example" />

to

<ora:useDataSource id="example"
className="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:ora9/jspbook@localhost:1521:SAMP" />

Chapter 11 Examples

Files to change:

ch11/store.jsp

# grep jdbc *
store.jsp: className="sun.jdbc.odbc.JdbcOdbcDriver"
store.jsp: url="jdbc:odbc:example" />

# mv store.jsp store.jsp.orig
# dos2unix store.jsp.orig store.jsp

Edit store.jsp and change

<ora:useDataSource id="example"
className="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:example" />

to

<ora:useDataSource id="example"
className="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:ora9/jspbook@localhost:1521:SAMP" />

Additional Changes for Tomcat 3.3

The conf location changed for passwords as well as the file structure. The new file should be tomcat-users.xml and it is located in $TOMCAT_HOME/conf/users directory.

Resources

 Note:  Some of the above links require an account on Oracle Technical Network.  I highly recommend that you create an account and review some of the material on this site.  This site contains a wealth of Oracle information and documentation as well as downloadable versions of most of Oracle's products.