Using Java Server Pages with Oracle 9iA Simple Application by ExampleBy Patrick WesleyKennesaw State UniversityForCS 8630 - Dr. GuimaraesSummer 2005Date: July 2, 20041Purpose The purpose of this paper is to explore the technology behind Java Server Page (JSP), discussed in Chapter 28 of Database Systems by Thomas Connolly and Carolyn Begg andhow to use them to utilize an Oracle 9i RDBMS and the Apache Tomcat Server for developing a dynamic web based application.Paper'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 Apache Tomcat is included.Java Server PagesWhat 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 2compatibility. JSPs utilize the Apache Tomcat Server to produce content rich Web pages to users through their Web 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 converts 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 and 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 enables Java programs to utilize 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.3How does Java Database Connectivity (JDBC with Oracle 9i) work? Java Database Connectivity (JDBC) works with databases that are SQL compliant andthat 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 can 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 TomcatWhat is Apache Tomcat? Apache Tomcat is an HTML Web server with the addition of the Tomcat software 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 Tomcat, 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 the Apache server. The normal ports of communication are ports 80 4(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 sends 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 Movie 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 application 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 negligible 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 objects 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 creating the tables. 5Once this is done, you can create the 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 demo_pk PRIMARY KEY (Demo_Id) );Create the tables below using the example above as a guideline.Table Name: MoviesColumns: Movie_ID NUMBER Primary Key Movie_Name VARCHAR2(60) Movie_Length NUMBER Movie_Genre_ID NUMBER Movie_Checked_Out_Ind VARCHAR2(1) Table Name: ActorsColumns: Actor_ID NUMBER Primary Key Actor_Fname VARCHAR2(60) Actor_Lname VARCHAR2(1)Table Name: ContactsColumns: Contacts_ID NUMBER Primary Key Contacts_Fname VARCHAR2(60) Contacts_Lname VARCHAR2(1) Contacts_Address VARCHAR2(40) Contacts_City VARCHAR2(20)