C.2 SQLJ in Servlets
A servlet is a Java program that may be deployed and run by an application server, and invoked using a browser. This section shows a simple servlet, named ServletExample.sqlj (Example C-2), that contains SQLJ statements. This section also describes how to compile the servlet and deploy it to the Oracle Internet Application Server (iAS) Version 1.0.2. For an introduction to servlets, I recommend Java Servlet Programming by Jason Hunter and William Crawford (O'Reilly).
ServletExample.sqlj retrieves the same customer details as AppletExample.sqlj, but displays them in an HTML table. When this servlet is invoked using a browser, the servlet's doGet( ) method is called. The doGet( ) method then retrieves and displays the details for all customers in the customers table.
Example C-2. ServletExample.sqlj
/* ServletExample.sqlj illustrates how to include SQLJ statements in a servlet. */ // import the servlet, io, and util classes import javax.servlet.*; import javax.servlet.http.*; import java.io.*; import java.util.*; // import the SQLJ classes import java.sql.*; import oracle.sqlj.runtime.Oracle; import sqlj.runtime.ref.DefaultContext; public class ServletExample extends HttpServlet { // declare an iterator class #sql private static iterator CustIteratorClass( int id, String first_name, String last_name, java.sql.Date dob, String phone ); // handle get request public void doGet( HttpServletRequest req, HttpServletResponse res ) throws ServletException, IOException { // output HTML res.setContentType("text/html"); PrintWriter out = res.getWriter( ); out.println("<html><head><title>Customers</title></head>"); out.println("<body>"); // retrieve and display the customer details try { DefaultContext conn_context = Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); CustIteratorClass cust_iterator; #sql [conn_context] cust_iterator = { SELECT id, first_name, last_name, dob, phone FROM customers }; // write the customer details out to an HTML table out.println("<table width=100% border=1>"); out.println("<tr>"); out.println("<th>Id</th>"); out.println("<th>First Name</th>"); out.println("<th>Last Name</th>"); out.println("<th>DOB</th>"); out.println("<th>Phone</th>"); out.println("</tr>"); while (cust_iterator.next( )) { out.println("<tr>"); out.println("<td>" + cust_iterator.id( ) + "</td>"); out.println("<td>" + cust_iterator.first_name( ) + "</td>"); out.println("<td>" + cust_iterator.last_name( ) + "</td>"); out.println("<td>" + cust_iterator.dob( ) + "</td>"); out.println("<td>" + cust_iterator.phone( ) + "</td>"); out.println("</tr>"); } // end of while cust_iterator.close( ); conn_context.close( ); out.println("</table>"); out.println("</body></html>"); } catch ( SQLException e ) { out.println("SQLException " + e); } } } You can use the sqlj command-line utility to compile ServletExample.sqlj, but prior to this you must install the Sun Microsystems Servlet Developer's Kit. Alternately, you can install a version of Java 2 Enterprise Edition, which also has the capability to compile servlets. You can download these software components from Sun's Java web site at http://java.sun.com. You should also use the SQLJ utility's -ser2class option to convert the .ser files to .class files prior to deploying the servlet (the -ser2class option was covered in Chapter 6). The following example shows how to compile ServletExample.sqlj from the command line:
sqlj -ser2class ServletExample.sqlj
As with any other type of Java program unit, you can also use JDeveloper to compile and run ServletExample.sqlj.
Once you've compiled the servlet, you can deploy it to the iAS by copying the following class files to the directory ORACLE_HOME\Apache\Jserv\servlets (ORACLE_HOME is the directory under which iAS is installed):
ServletExample.class ServletExample$CustIteratorClass.class ServletExample_SJProfileKeys.class ServletExample_SJProfile0.class
You can then run the servlet by pointing your browser to the following URL:
- http://localhost/servlet/ServletExample
The output from ServletExample.sqlj, as displayed in Internet Explorer, is shown in Figure C-2.
