Access Oracle database from JSP

Installing Tomcat

In your zone, login as root. Next, run the following command:

/opt/soc/bin/tomcat-setup

The above installs the necessary files for your own instance of Tomcat in 2 setups

Tomcat as a Web Server (www:tomcat)
Tomcat as a JSP Server (jsp:tomcat)

Running Tomcat as a web server only supports servlets & JSPs. In this example, we will run Tomcat as a JSP server, and let Apache be the front end web server. The benefit is that CGI, PHP, servlets and JSP will all be supported. (CGI and PHP are handled by Apache, while servlets and JSP are handled by Tomcat which is "stacked" behind Apache.)

Start services

Ensure that Apache has already been started, you may do so by issuing:

svcs apache22

The state should reflect as 'online'. If not, you may start the service by issuing:

svcadm enable www:apache22

For extra information, you may stop or restart a service by using the 'disable' or 'restart' command option, such as:

svcadm disable www:apache22
svcadm restart www:apache22

Likewise, ensure that Tomcat (as a JSP Server) has been started too, by issuing:

svcs tomcat

The state should be 'disabled' for www:tomcat, and 'online' for jsp:tomcat.

You may now safely exit from root account.

URL for accessing the JSP files

The base URL of resources served by Apache is:

http://<your_zone>/

As mentioned previously, servlets and JSPs are handled by Tomcat, which is stacked behind Apache (the web server front end). So, how does Apache know when to direct servlets and JSP traffic to Tomcat? The answer is in the file httpd-proxy-ajp.conf, which can be found in directory /usr/local/apache/conf/extra/. Search for the lines that have syntax:

ProxyPass <path> ajp://localhost:8009/
ProxyPassReverse <path> ajp://localhost:8009/

This means that addresses which are prefixed by

http://<your_zone>/<path>/

will be directed to Tomcat. The default value for <path> is '/WEB-INF'. You may change it to any meaningful name you prefer (e.g. '/jsp'). If a change is made, do remember to restart the Apache service by issuing 'svcadm restart www:apache22' from your root account, or the change will not take effect.

Try accessing the default Tomcat page by accessing http://<your_zone>/<path>/ from your browser:

Install Oracle JDBC Driver

Locate the file classes12.jar from within the zone at directory /opt/oracle/product/9.2.0/jdbc/lib
Copy classes12.jar to directory /usr/local/apache-tomcat/shared/lib
Restart Tomcat service by issuing 'svcadm restart jsp:tomcat' from your root account. This is necessary for the driver to be loaded in the system environment.

Physical location of JSP files

Servlets and JSPs served by Tomcat are to be placed in /usr/local/apache-tomcat/webapps/. Each directory in /usr/local/apache-tomcat/webapps/ contains the source codes of a web application.

JSP Sample Code

Before creating the sample JSP file, make sure you have created the table 'employee' in your Oracle account.

create table employee (
  id varchar2(5),
  name varchar2(30),
primary key (id))

Now, let's move on to the JSP part. Create a directory 'test' (under /usr/local/apache-tomcat/webapps/). This is where we will store our sample JSP file.

Use your favorite editor to create a file named 'oracle_test.jsp', under directory 'test', with the following contents:

<%@ page import="java.sql.*" %>

<%
  
  //initialize driver class
  try {    
    Class.forName("oracle.jdbc.driver.OracleDriver");
  } catch (Exception e) {
    out.println("Fail to initialize Oracle JDBC driver: " + e.toString() + "<P>");
  }
  
  String dbUser = "yourUserID";
  String dbPasswd = "yourPassword";
  String dbURL = "jdbc:oracle:thin:@sid3.comp.nus.edu.sg:1521:sid3";

  //connect
  Connection conn = null;
  try {
    conn = DriverManager.getConnection(dbURL,dbUser,dbPasswd);
    out.println(" Connection status: " + conn + "<P>");
  } catch(Exception e) {
    out.println("Connection failed: " + e.toString() + "<P>");      
  }

  String sql;
  int numRowsAffected;
  Statement stmt = conn.createStatement();
  ResultSet rs;
  
  // insert
  try {
    
    sql = "insert into employee values ('10', 'John')";
    numRowsAffected = stmt.executeUpdate(sql);
    out.println(numRowsAffected + " employee(s) inserted. <BR>");
  
  } catch (SQLException e) {
    
    out.println("Error encountered during row insertion for employee: " + e.toString() + "<BR>");
  
  }

  // select
  sql = "select id, name from employee";
  rs = stmt.executeQuery(sql);
  while (rs.next()) {
    out.println("Id = " + rs.getString("ID") + ", Name = " + rs.getString("NAME") + "<BR>");  
  }

  out.println("<P>");
 
  // delete
  try {
    sql = "delete from employee";
    numRowsAffected = stmt.executeUpdate(sql);
    out.println(numRowsAffected + " employee(s) deleted. <BR>");
  } catch (SQLException e) {

    out.println("Error encountered during deletion of employee: " + e.toString() + "<BR>");
  
  }  

  out.println("<P>");
  
  rs.close();
  stmt.close();

  //commit
  conn.commit();
  
  //disconnect
  conn.close();
  
%>  

<HTML>
<BODY>
Bye bye!  The system time is now <%= new java.util.Date() %>
</BODY>
</HTML>

Test out your sample JSP by accessing 'http://<your_zone>/<path>/test/oracle_test.jsp' with a browser.