Using Java Server Pages with Oracle 9i A Simple Application by Example By Patrick Wesley

mobdescriptiveSoftware and s/w Development

Oct 28, 2013 (3 years and 7 months ago)

64 views


1





Using Java Server Pages with Oracle 9i

A Simple Application by Example


By Patrick Wesley


Kennesaw State University


For


CS 8630
-

Dr. Guimaraes


Summer 2005


Date: July 2, 2004

















2

Purpose



The purpose of this paper is to explore th
e technology behind Java Server Page

(JSP)
,
discussed in Chapter 28 of Database Systems by Thomas Connolly and Carolyn Begg

and how to use them to utilize an Oracle 9i RDBMS and the Apache Tomcat Server for
developing a
dynamic
web based application.


Pape
r's Organization


This paper is organized in the following manner. First, I will briefly discuss what Java
Server Pages,
Java Database Connectivity (with Oracle 9i)
, and Apache Tomcat are and
how each work
.

Then I will discuss how to implement the use
of these technologies
together to create a dynamic Web based application.

The actual application itself is
generalized in some areas due to the time limitations, however instructions on how to
implement a simple Java Server Page using Oracle, JDBC, and Apa
che Tomcat is
included.


Java Server Pages

What
is

a
Java Server Page

(JSP)
?


Java Server Page (JSP) is Sun Microsystems' API extension that enables the
production of dynamic Web pages

through server
-
side scripting with Java language
compatibility
. JSP

allows for rapid development of web content and
can be used
with
Java Database Connectivity
(JDBC) drivers to access database systems with JDBC

3

compatibility.

JSPs utilize the Apache Tomcat Server to produce content rich Web pages
to users through their W
eb browser.


How Java Server Pages work
?


Java Server Page (JSP) works
when a JSP is accessed through the Web by a user
through their Web browser. When this occurs, the JSP is transferred from the Web server
(Apache) to the Tomcat software which conver
ts the Web page being viewed into a Java
program and then compiles it into binary form if there is no current copy of the binary
cached. Once the compilation process is complete, the HTML in the requested page is
placed in a Java wrapper / output method an
d sent to the user's Web browser.


.

Java Database Connectivity (with Oracle 9i)

What is
Java Database Connectivity

(JDBC

with Oracle 9i)
?


Java Database Connectivity (JDBC) is
a Java API created by Sun Microsystems that

e
nables Java programs to utili
ze embedded SQL statements to query and manipulate
databases that are SQL compliant and have JDBC drivers. For example, you could write
an application using Java Server Pages, JDBC and any database that is SQL compliant
and has JDBC drivers.






4

How does
J
ava Database Connectivity

(JDBC

with Oracle 9i) work
?


Java Database Connectivity (JDBC) works with databases that are SQL compliant and
that have JDBC drivers. A JDBC driver is created for each individual database
, such as
Oracle 8i or Oracle 9i
. So,
if there is no JDBC driver for the database that you are using,
you may have to look at other options, such as ODBC. A JDBC driver allows
communication to occur
between
the application
and
the database through a TCP port

or
socket
.
The Oracle Thin driver c
an be used to
utilize

JDBC with Oracle 9i. It uses
SQL*Net on top of Java sockets to connect and communicate with the Oracle 9i
database.


Apache Tomcat

What is Apache Tomcat
?


Apache Tomcat is a
n

HTML Web server with the addition of the Tomcat softwa
re or
servlet container that is used to generate Web pages from JSPs and Java Servlets. The
combination of Apache and Tomcat allow the use of JSP and presentation of dynamic
Web pages through an Internet browser, such as Mozilla. Apache can work without
To
mcat, but it would not provide JSP functionality.


How does Apache Tomcat work
?


In a nutshell Apache Tomcat listens for requests on specified ports and then returns
the results of valid requests or error messages depending on how the administrator of
the
machine hosting th
e Apache server. The normal ports of communication are ports 80

5

(HTTP)
and 443

(HTTPS
-

Encrypted). Tomcat is the Java container that takes JSP code,
compiles it and then returns the HTML wrapped with the Java binary and sen
ds

to the
requesting Web browser through a Java
output
component.

Tomcat works seamlessly
with Apache to present JSPs as if it were any other HTML document.


Overview of
the Implementation


The application being implemented for the purpose of this paper is a Mov
ie Inventory
application
or Movie Manager
that tracks a personal movie collection. The application
will contain forms for movies, actors, contacts (borrow the movies), querying the
database and a cross
-
reference table for tying actors to movies. The appli
cation will be
developed using notepad to create the JSPs, Oracle 9i to house the data, and the Apache
Tomcat server to serve the application over the Web. Since this application will not be
made to the general public, security for this application is negl
igible and will not be
discussed.


Step 1: Creating the Oracle 9i
Objects


Assuming Oracle 9i is installed on the
database server being used for this
implementation and the E
-
R diagrams have been created, I will begin with the physical
design of the ob
jects in an existing database.


The first step to create the
objects

is to login to
Oracle's
SQL*PLUS as the owner of
the future

objects with an appropriate default tablespace.
Another consideration is what
constraints

and triggers

to include when crea
ting the tables.


6


Once this is done, you can create
th
e following objects or tables using the example
below
.

Not the Primary Key constraint.

Example:

Create table Demo

(


Demo_Id NUMBER
,


Demo_Name VARCHAR2(30)
,


CONSTRAINT d
emo_pk PRIMARY KEY (Demo_Id)


)
;



Create the tables below using the example above as a guideline.


Table Name: Movies

Columns: Movie_ID

NUMBER Primary Key


Movie_Name VARCHAR2(60)



Movie_Length NUMBER


Movie_Genre_ID NUMBER


Movie_Checked_Out_Ind VARCHAR2(1)



Table Name: Actors

Columns: Actor_ID

NUMBER Primary Key


Actor_Fname VARCHAR2(60)


Actor_Lname VARCHAR2(1)



Table Name: Contacts

Columns: Contacts_ID NUMBER Primary Key



Contacts_Fname VARCHAR2(60)


Contacts_Lname VARCHAR2(1)


Contacts_Address VARCHAR2(40)


Contacts_City VARC
HAR2(20)


Contacts_State VARCHAR2(2)


Contacts_Zip NUMBER


Contacts_Phone_Area NUMBER


Contacts_Phone

NUMBER


Table Name: Actor_Movie_CrossRef

Columns: Movie_ID NUMBER Primary Key


Actor_ID NUMBER Primary Key


7


Table Name: Genre

Columns: Genre_ID

NUMBER Primary Key


Genre_Name VARCHAR2(30)




Once these tables are created, the associated Triggers need to be created to maintain
data integrity. For example, we need a Trigger that will delete
all the rows or tuples in the
Actor_Movie_CrossRef if the associated Movie is deleted. This will prevent orphan
records from existing. To create the trigger, you need to login to Oracle SQL*PLUS as
the owner of the objects that the Trigger will manipulate
. An example of such a Trigger is
below:

Example:

Create or replace trigger trg_delete_movies

Before

delete on movies

Begin


Delete from Actor_Movie_CrossRef


Where Actor_Movie_CrossRef.Actor_ID =
new.
Actor_ID.


End;

/



Once all of the tables, trigg
ers and constraints have been created, the next step can
begin, which is getting the Apache Tomcat server installed and running.






8

Step 2: Install and Configure Apache Tomcat

Install Apache Tomcat


To install Apache Tomcat, go to
http://jakarta.apache.org/site/binindex.cgi

and
download the binary installation package for the most current version of Apache Tomcat

specific to your operating system
. Accept the defaults in the installation for t
he purpose
of this sample.
If you

decide to use this install as a production server, you will need to
change the default listening port from 8080 to 80. TCP port 80 is the standard port for
HTTP request.
If you have any problems with the installation you
can access the Apache
Tomcat documentation at
http://jakarta.apache.org/tomcat/tomcat
-
5.0
-
doc/index.html
.


Configure Apache Tomcat


Once Apache Tomcat is installed, you can acc
ess the Tomcat settings by going to the
Apache Tomcat Properties form to change any settings needed. On the Windows XP
operating system, you can to Start
-
> Programs
-
> Apache Tomcat
-

>Configure Apache
Tomcat. This will bring up the form and you can modif
y the setting there. The thing that
most likely needs to be changed is the Java Class Path. Just add the Path to the files you
need, such as Oracle's classes12.zip.


Now that Apache is installed and the Java Class Path is set in Apache Tomcat, you can
start the server by using the General tab on the Tomcat Configure form. Once the server
has been started you can access

the Apache Tomcat Server Documentation on your server
by going to:
http://localhost:80

or
http://localhost:8080

(default setting)
. This will bring
up the documentation for Apache Tomcat. Some other things to consider at this point
are


9

where to deploy your application. In order to deploy an application with Apache T
omcat,
you must place you application's files (JSP, HTML, etc) inside Tomcat's working
directory. The default path is: install_path
\

Apache Software Foundation
\
Tomcat
5.0
\
webapps
\
ROOT
. For example on my personal server the path is:

C:
\
Program
Files
\
Apache
Software Foundation
\
Tomcat 5.0
\
webapps
\
ROOT
. You can add you
application under the webapps directory, but you will have to place that path in the URL,
which may raise security questions if this were
an

Internet available application, which it
is not and wi
ll not be discussed.


Step 3: Create a Java Server Page Application using JDBC

Create the Application


For the sake of keeping things simple and straight forward, I will go through the
general points for creating a JSP application using JDBC and Oracle

9i. I will however
present working code that can be used to test Apache Tomcat, Oracle 9i, JDBC, and a
Java Server Page. This is useful once you have installed Apache Tomcat, Oracle 9i and
the JSP and you want to test it to make sure that everything is w
orking correctly.


General Application Creation


To create the Movie Manage Application,
we will

need to create
the

JSPs for each
form that
we
wish to create. This can be done in one JSP, but to keep things standardized,
we
will want to create one JSP
per form. So, we will need Movies.jsp, Authors.jsp,
Contacts.jsp,

Query.jsp, and A_M_Crossref.jsp. The Movies.jsp will contain information
about movies and allow users to create new records,
update records,
delete records and

10

view records. The other forms

will have the same functionality
ex
cept for the Query.jsp
which will allow queries only.


The forms that are used to insert, update, view
, and delete
records will have code sections that do each function. In order to achieve this
functionality, a variable

will be used to track what state the JSP is in at execution.
This
variable will be set when a button is pressed on the JSP page.
For example, if the Form
load
s

by default
in view mode and a user
want

to delete a record, the
user simply presses
the
delete
button
and the

state is set to

delete, which executes code in the delete state
section. The same holds true for the update, insert, and view states of each form. The
query form will allow users to query all aspects of the Movie Manager database and will
pr
oduce links to the various forms. For example, if a user wants to know who has a
certain movie checked out they can query by movie, then have the option of going to the
Movies form with the queried data loaded in order to make changes or view other
informa
tion. The JSPs will
all
be very similar in nature and functionality.



Once the JSPs are created, it
'
s time to create some test data using the JSP forms and

develop various scenarios to test the triggers and constraints to ensure that they are
working

properly. After the testing is done, it will be time to deploy the application in a
folder that will house the application for real world use.

Due to time limitations, this is
where the discussion on the Movie Manager application ends and where I begin
di
scussing how to implement the creation, deployment, and testing of a simple JSP to test
all of the technologies used in the simple application example discussed.





11

Creating a Simple JSP with Code Explanations


The first step in creating a JSP is to cr
eate the JSP document.

In the text below, the
text in black is

my comments, the text in blue is the sample working code, and the
magenta
text is

other comments.

Creating a JSP can be

done

by using an editor of your
choice and typing the following

code
high
lighted in blue
:

(Note: All code placed in JSP file is highlighted for Clearer Reading)

<%@page contentType="text/html"



import = "java.io.*"


import = "java.lang.*"


import = "java.sql.*" %>



These statements
above
tell the server to treat this docu
ment as
a

text / html document.


You will want to place your import statements here for you Java libraries.


Next, you can add
the first
HTML tags

(below
) to

the JSP

-

This allows it to display in
the Browser

(your java code will be wrapped in the HTML)
.


<html>


<head><title>JSP Page</title></head>


<body>



Test JSP using Oracle 9i and JDBC


The code below specifies libraries to be used to
c
ompile the Java
c
ode


<%
--

<jsp:useBean id="beanInstanceName" scope="session" class="package.class" />
--
%>


<%
--

<
jsp:getProperty name="beanInstanceName" property="propertyName" />
--
%>


The code below sets the variables needed to establish a JDBC connection and SQL query.


<%


Connection dbconn;

/* Database Connection Variable */


ResultSet res
ults;

/* Result Set Variable
-

Holds Results of SQL Query */


PreparedStatement sql;

/* SQL Statement Variable
-

Holds SQL Statement */


12

The try statement is needed
to
catch error
s

if the Oracle driver does not load correctly.


try


{


Class.forName("oracle.jdbc.driver.OracleDriver");


try


{


String
tablename
;

/* hold table name from query results*/


boolean doneheading = false;


/* loop control variable */



This code

below

establishes the connection to the Oracle 9i database using
DriverManager.getConnection method. The information in the method call is called the
URL. Its form is ("driver@hostname:port:Oracle_SI
D
"
).


The following URL variables and their meanings are below:


Jdbc:oracle:thin = Oracle's JDBC Driver

Myserver = The name of the Server housing the Oracle 9i Database

1521 = The TCP port that
the
Oracle

database

is listening on

MYDB = The Oracle Instanc
e or Oracle SID (Database Name)

System = user id querying the database

Test = the password used to access the database with the specified user id
.


(Note: you will need to change the URL below to match that of your Oracle and Tomcat
servers)



dbconn = Dri
verManager.getConnection("
J
dbc:oracle:thin:@
Myserver
:1521:
MYDB
",
"
S
ystem", "
T
est
");


The sql variable is set with the SQL statement desired. The dbconn.prepareStatement is
used to create the value in sql.


sql = dbconn.prepareStatement("SELECT table_name,
owner FROM all_tables");


Now,
results are

set to the results of the execution of the sql.executeQuery method
.
This
places the values or result of the query in the "results" variable.



results = sql.executeQuery();


Now that the query has be
en completed, we can loop through the results and display them
in the user's browser.


Results.next = gets the next value in the results cursor or result set.



while(results.next())

/* loop through the results cursor or set */



{


if(! doneheading)

/* if not finished create HTML table */


13


{


out.println("<table border=2>");

/* create HTML table */



doneheading = true;

/* set donerhading to true */


}


Put the column table_name into the tablename variable for printing



tablename

= results.getString("t
able_name");

/* get table_name */


Print the tablename variable, which holds the table_name column from all_tables.



out.println("<tr><td>" +
tablename

+ "</td></tr>");

/* print */



}




if(doneheading)

/* close table when doneheading */


{


out.println("</table>");

/* close the HTML table */


}


else


{


out.println("No
tables in All_TABLES"
);

/* if no records */


}


}


catch (SQLException s)


{


out.println("SQL Error
-

<br>" + s.getErrorC
ode());


}


}


catch (ClassNotFoundException err)


{


out.println("Class loading error");


}

%>

Close the HTML document

</body>

</html>



With this simple JSP we can test Apache Tomcat to ensure

that the CLASSPATH is
set correctly in Tomcat.


14


First, name the file we just created test.jsp and place it into the

default directory or
folder. My default folder is:

C:
\
Program Files
\
Apache Software Foundation
\
Tomcat
5.0
\
webapps
\
ROOT
.


Next, type
:
http://localhost:80/test.jsp

(Note: if you did not change the default port
during

the Tomcat install, then you need to type 8080
(default port)
for the port.). If you
see a long list of Oracle 9i tables, t
hen Apache and your Oracle 9i database are working
using JDBC. If you do not see the list of tables, you may want to check the following:

1) Check
t
he Tomcat
Class path

(Include all the classes that you need in the path, such as
Oracle's classes12.zip, wh
ich can be downloaded on Oracle's web site.)

2) Make sure that your URL is correct. (If you are using an IP address in your URL and
you are using DHCP, you may want to check the tnsnames.ora and listener.ora file in
your oracle d
irectory or folder (c:
\
orac
lepath
\
network
\
admin)

3)
Make sure that Tomcat is running. (I know that it's obvious, but it happens)

4) If Oracle and Tomcat are working

correctly,
your tnsnames.ora and listener.ora files
are correct, and your URL is correct, you may want to double check

your JSP code to
make sure that everything is correct.


If none of these problems above are causing your JSP not to work, you may want to
check out the Jakarta site discussed above and review the Tomcat documentation

and
double check your JSP code
.



Summary


The technologies discussed in this document are very powerful when combined. The
power of Java and Oracle combined is very important and it's all made possible by

15

Apache Tomcat in this example. Although I would have liked to discuss the sampl
e
application in further detail, time constraints would not allow it. Hopefully the sample
JSP
code

and the installation and configuration instructions help to convey how a simple
application can become rather complicated. Also, the study and discussion on

how
Tomcat converts a JSP to output to a Web browser was rather informative and important
when thinking about developing in JSP.



















16

References



Laurie B, Laurie P, (February 1999).
Apache
:
The Definitive Guide

Second Edition
,

O
'
R
eilly & As
sociates:

Sebastopol, CA.

Retrieved July 2, 2004 from the World Wide
Web:

http://www.hk8.org/old_web/linux/apache/ch01_01.htm


Reilly D
.

(2000). Getting Started with JDBC. Retrieved July 2, 2004 from the World
Wide Web:
http://www.javacoffeebreak.com/articles/jdbc/


Burden P. (2004). Introduction to JSP. Retrieved July 2, 2004 from the World Wide Web:
http://www.scit.wlv.ac.uk/~j
phb/sst/jsp/intro.html


Feuerstein
, S, Odewahn A, (May 2000).
Oracle PL/SQL Developer's Workbook
,

O'Rielly & Associates: Sebastopol, CA. Retrieved July 2, 2004 from the World Wide
Web:
http://www.oreilly.com/catalog/ordevworkbook/chapter/ch16s.html


www.developer.novell.com

(2004). How does JDBC work in AnyInfo? Retrieved July 2,
2004 from the World Wide Web:

http://developer.novell.com/research

/devnotes/
1998/december/05/03.htm



The Apache Software Foundation, (2004). The Jakarta Site
-

Apache Tomcat. Retrieved
July 2, 2004 from the World Wide Web:
http://jakarta.apache.org/tomcat/
.


www.webopedia.com

(2004). What is JDBC? Retrieved July 2, 2004 from the World
Wide Web:
http://www.webopedia.com/TERM/J/JDBC.html