Introduction Java servlets provide a new and exciting method of developing server-side solutions. Servlets provide the features of traditional CGI scripts with the added benefits of efficiency and portability. Currently, major corporations are making the migration from CGI scripts to Java servlets. As a result, the demand for applet and servlet communication is on the rise.

This article is the third in a three-part series on Java servlets. In the Feb 98 issue of JDJ, I presented you with a 3-tier database application that used Java servlets. In this article, you will learn how to build a 3-tier database application that allows a Java applet to perform two-way communication with a Java servlet. I will focus on the concepts and techniques of applets communicating with servlets. The article will build on the 3-tier application presented in the previous article. However, if you are a newcomer and missed the previous article, don’t worry because I’ll give a review of the application.

Reviewing our Student Tracker Application

Our previous article presented a 3-tier database application that used Java servlets and the Java Database Connection (JDBC). The application allows a public speaker to keep track of students who attends her seminars. Students interact with the application by entering their contact information into an HTML form. Once the form is submitted then the Java servlet uses JDBC to store the student information in a database. Afterwards, an updated student list is generated by the servlet and returned as an HTML page to the user.

The application is partitioned into three tiers: user interface layer, the business rules layer and the data store layer. Figure 1 illustrates the three-tier design.

The first tier is a web browser, which serves as our universal client. In the first phase of the application, an HTML front-end was used for user-input and displaying the database query results. The HTML approach was taken because it lowered the requirements of the client’s web browser version. By taking this low-tech approach, the application was accessible to users who had browsers that were not Java 1.1 enabled.

The second tier of the application is implemented with a Web server capable of executing Java servlets. The Java servlet harnesses the power of JDBC to access the database to store/retrieve information as needed. A dynamic HTML page is generated by the servlet based on the database results.

The third tier is composed of our back-end database server. The database server stores the information that is used by the application. Thanks to the JDBC API, the servlet can access the database in a portable fashion by using the SQL call-level interface.

Developing an Applet Front-end

In order to enhance the student tracking system, we will develop an applet front-end. The students can now enter their contact information into a Java dialog box. Also, an updated student list is displayed in a Java list component. Figure 2 below shows the new applet front-end.


Applet-Servlet Communication with HTTP GET and POST

In the previous version, the HTML form was used to submit the student’s data to the servlet. Accessing the form data on the server side was simple and straightforward. This was accomplished by calling the method HttpRequest.getParameter( “<form field name>”) which is available in the Java Servlet API.

However, we are now using an applet front-end and we need a mechanism for the applet to communicate with the servlet. We need to capture the information a student enters and somehow pass this information to the servlet. Since servlets support the HTTP/CGI interface, we can communicate with the servlet over HTTP socket connections. The applet simply has to open a connection to the specified servlet URL. Once this connection is made, then the applet can get an output stream or input stream on the servlet.

The applet can send data to the applet by sending a GET or a POST method. If a GET method is used, then the applet must URL encode the name/value pair parameters into the actual URL string. For example, if we wanted to send the name/value pair of LastName=Jones, then our servlet URL would resemble:

http://www.foo.com/servlet/TestServlet?LastName=Jones

If you have additional name/value pairs, then they are separated by an ampersand (&). So, adding an additional name/value pair of FirstName=Joe, then our revised servlet URL would resemble:

http://www.foo.com/servlet/TestServlet?LastName=Jones&FirstName=Joe

In our application, we would have to URL encode each name/value pair for the student’s contact information. To send a GET method to a servlet, the applet can use the java.net.URLConnection class. The code fragment below shows you how.

String location = “http://www.foo.com/servlet/TestServlet?LastName=Jones”;
URL testServlet = new URL( location );
URLConnection servletConnection = testServlet.openConnection();
inputStreamFromServlet = servletConnection.getInputStream();

// Read the input from the servlet.
. . .

Once the applet has opened a connection to the URL, then the input stream from the servlet is accessed. The applet can read this input stream and process the data accordingly. The type and format of the data returned depends on the servlet. If the servlet is returning custom information, then the creation of a custom messaging protocol is needed for the applet and servlet to communicate. However, I will not get into the details of a custom protocol because I’ll present an elegant solution later in the article.

To POST data to a servlet, the java.net.URLConnection class is used again. However, this time, we must inform the URL connection that we will send data over the output stream. The POST method is powerful because you can send any form of data (plain text, binary, etc). All you have to do is set the content type in the HTTP request header. However, the servlet must be able to handle the type of data that the applet sends.

The code fragment below shows how to send a POST method to a servlet URL. The details of transmitting the data are discussed later in the article.

// connect to the servlet
String location = “http://www.foo.com/servlet/TestServlet”;
URL testServlet = new URL( servletLocation );
URLConnection servletConnection = testServlet.openConnection();

// inform the connection that we will send output and accept input
servletConnection.setDoInput(true);
servletConnection.setDoOutput(true);

// Don’t use a cached version of URL connection.
servletConnection.setUseCaches (false);
servletConnection.setDefaultUseCaches (false);

// Specify the content type that we will send binary data
servletConnection.setRequestProperty
(“Content-Type”, “<insert favorite mime type>”);

// get input and output streams on servlet
. . .

// send your data to the servlet
. . .

As you can see, applets can communicate with servlets using the GET and POST method. However, when the applet sends data using a GET method, then it must URL encode each name/value pair.

Communicating w/ Object Serialization

In our application, we would like to provide a higher level of abstraction. Instead of passing each parameter of student information (i.e. last name, first name) as name value pairs, we would like to send it as a true Java object. Our Java application already has a Student class that encapsulates all of the information about a student (see Listing 1). This information is gathered from the New Student dialog box and a Student object is created. When we register a new student, we would like to simply send the Student object to the servlet. Upon receipt of the Student object, the servlet would add the new student to the database. Also, it is our desire for the servlet to send the applet an updated student list as a vector of student objects. This will allow the applet to quickly and easily display the student list.

How can we accomplish this you ask? Easy, thanks to Java’s object serialization. Java 1.1 introduced object serialization, which allows an object to be flattened and saved as a binary file. The values of the data members are saved so in fact, the state of the object is persisted or serialized. At a later time, the object can be loaded or deserialized from the binary file with the values of its data members intact. Object serialization is fascinating in that it frees the developer from low-level details of saving and restoring the object.

You may wonder how does this relate to applet-servlet communication? Well, object serialization is not limited to binary disk files. Objects can also be serialized to any output stream. This even includes an output stream based on a socket connection. So, you can serialize an object over a socket output stream! As you’ve probably guessed by now, a Java object can also be deserialized or loaded from a socket input stream.

In order for a Java object to be serializable, its class must implement the java.io.Serializable interface. However, you will not have to actually implement any methods for this interface because the interface is empty. The java.io.Serializable interface is simply a tag for the Java Virtual Machine. We can create a custom class as follows:

class Foo implements java.io.Serializable
{
// normal declaration of data members,
// constructors and methods
}

The code fragment below shows you how to serialize an object to an output stream. In this example, we already have a socket connection to a host machine and we are simply serializing the object, myFoo.

outputToHost = new ObjectOutputStream(hostConnection.getOutputStream());

// serialize the object
Foo myFoo = new Foo();
outputToHost.writeObject(myFoo);
outputToHost.flush();
outputToHost.close();

Notice in the example that an ObjectOutputStream is created. This class is responsible for serializing an object. The object is actually serialized when the writeObject() method is called with the target object as its parameter. At this time, a binary image of the object is written to the output stream. In this case, the output stream is based on a socket connection.

However, this example would not be complete without code on the host machine to read the serialized object. The code fragment below shows you how to deserialize an object from an input stream.

inputFromClient = new ObjectInputStream(clientConnection.getInputStream());

// deserialize the object, note the cast
Foo theData = (Foo) inputFromClient.readObject();
inputFromClient.close();

An ObjectInputStream is created based on the client’s socket connection. The object is deserialized by simply calling the readObject() method. However, we must cast the object to its appropriate class, in this case, the class Foo. At this point, the object is available for normal use.

As you can see, object serialization is very straightforward and easy. Now, we’ll use this technology to pass objects back and forth between our applet and servlet.

Sending Objects from an Applet to a Servlet

With the information presented so far, we can send a Java object to a servlet. In our Student Tracking application, the applet sends a Student object to the servlet when a new student is registered. Figure 3 displays the object interaction between the servlet and the applet.


The code fragment shown in Listing 2 is used by the applet to send the Student object to the servlet. The applet is actually sending a POST method to the servlet. This client-side code fragment opens a URL connection to the servlet URL. We inform the servlet connection that we are sending output data over the connection and receiving input. Methods are also called such that the connection will not use cached versions of the URL. An important call in this code fragment is setRequestProperty(…). This method sets the content-type in the HTTP request header to the MIME-type application/octet-stream. The application/octet-stream MIME-type allows us to send binary data. In our case, the binary data is our serialized Student object. The next couple of statements creates an ObjectOutputStream and actually writes the object to the connection stream.

However, we are not yet finished. Recall that our application is in the process of registering a new student. The servlet must read this student object and update the database accordingly. Thus, we need code on the server side to receive a serialized Student object.

The code fragment in Listing 3 displays the servlet code for reading a Student object from an applet. The servlet handles POST methods by implementing the doPost() method. The servlet acquires an ObjectInputStream from the requesting applet. From there, it is simply a matter of reading the Student object from the stream. At this point, the Student object is loaded and available for registration in the database. Please make note of the small number of statements on the server-side for reading in a serialized object. You must agree that it is quite simple and straightforward.

Sending Objects from a Servlet to an Applet

In our Student Tracking application, the servlet is now capable of receiving a student object and registering them in the database. Now, the servlet must return an updated list of registered students. The updated student list is returned as a vector of student objects. This interaction is also illustrated in Figure 3.

When the servlet returns the vector of student objects, there is no need to iterate through the vector and serialize each Student object individually. The servlet can simply serialize the entire vector in one step, since the class java.util.Vector also implements the java.io.Serializable interface.

The code fragment shown in Listing 4 is used by the servlet to send a vector of Student objects to the applet. The sendStudentList() method is passed an HttpResponse parameter and a vector of Student objects. Since the applet initiated the HttpRequest, the servlet can respond to the applet by using the HttpResponse parameter. Thus, an ObjectOutputStream to the applet is created based on the HttpResponse object. The student vector is actually serialized and sent to the vector with a call to outputToApplet.writeObject(studentVector).

As we’ve seen before, code is needed by the applet to handle the data being sent from the servlet. The code fragment shown in Listing 5 is used by the applet to read in a vector the Student objects from the servlet. The applet opens a URL connection to the servlet’s location. The necessary methods are called to ensure that the applet doesn’t use cached versions of the URL connection. Next, an ObjectInputStream is created based on the servlet’s input stream socket connection. Now, all of switches have been flipped and we can easily read in our vector of Student objects. Again, remember we have to cast the object to the appropriate type. Congratulations, you have successfully read in a vector of student objects. This vector is now available for refreshing the AWT List component.

Conclusion

This article went beyond the normal method of sending name/value pairs over the HTTP/CGI protocol. The techniques presented leveraged the features of Java object serialization. As you can see, this provided an elegant way of transmitting serialized Java objects over network connections.

However, I must inform you, this article only discussed communication using the HTTP/CGI protocol. There are a number of other mechanisms for applets to communicate with server-side processes. The first one that comes to mind is Java’s Remote Method Invocation (RMI).

RMI allows a client application to call methods on a remote object as if the object was local. In fact, RMI uses object serialization to pass objects back and forth between the client application and the remote object. All of the low-level details of network connections and serialization are hidden from the developer when using RMI. If your project requires a large amount of applet-servlet communication, I’d recommend that you take a close look at RMI and the features it has to offer.

The second mechanism of communicating with server-side process is CORBA (Common Object Request Broker Architecture). Like RMI, CORBA allows you to make method calls on remote objects. If you have legacy server-side code written in a different language, then you can wrap it as a CORBA object and expose its functionality. CORBA provides a rich framework of services and facilities for distributing objects on the network.

If you’d like to get further information on distributed computing with RMI and CORBA visit the web-sites listed at the end of this article.

By now, you should understand the concepts and techniques for communication between applets and servlets. In this article, I demonstrated how an applet uses a POST method to send a serialized object to a servlet. The appropriate server-side code for the servlet was provided for reading in a serialized object. Our Student Tracking applet used this communication method to send a true Java object to a servlet. The servlet was also enhanced to return a vector of student objects to the applet. Likewise, the appropriate applet code was provided for reading in a vector of student objects.

As you can see, applet and servlet communication is straightforward with the techniques presented in this article. You can now add an applet front-end to your servlet-based application.

URL References:
//  File:  Student.java
//  Listing 1
//

import java.sql.*;
import javax.servlet.http.*;

/**
 *  The Student class has data members to describe
 *  a student.  String methods are available to
 *  display the data members to the console or web page.
 *
 *  @author Chad (shod) Darby,  [email protected]
 *  @version 0.6, 5 Jan 1998
 *
 */
public class Student implements java.io.Serializable
{
    // data members
    protected String lastName;
    protected String firstName;
    protected String company;
    protected String email;
    protected String courseTitle;
    protected String courseLocation;
    protected String expectations;
    protected java.sql.Date courseDate;

    protected final String CR = "\n";     // carriage return

    // constructors
    public Student()
    {
    }

    public Student(String aLastName, String aFirstName, String aEmail,
                   String aCompany, String aDate, String aCourseTitle,
                   String aCourseLocation, String aExpectation)
    {
        lastName = aLastName;
        firstName = aFirstName;
        email = aEmail;
        company = aCompany;

        courseDate = java.sql.Date.valueOf(aDate);
        courseTitle = aCourseTitle;
        courseLocation = aCourseLocation;
        expectations = aExpectation;
    }

    public Student(HttpServletRequest request)
    {
        lastName = request.getParameter("LastName");
        firstName = request.getParameter("FirstName");
        email = request.getParameter("Email");
        company = request.getParameter("Company");

        String dateString = request.getParameter("CourseStartDate");
        courseDate = java.sql.Date.valueOf(dateString);

        courseTitle = request.getParameter("CourseTitle");
        courseLocation = request.getParameter("CourseLocation");
        expectations = request.getParameter("Expectations");
    }

    public Student(ResultSet dataResultSet)
    {

        try {
            // assign data members
            lastName = dataResultSet.getString("LastName");
            firstName = dataResultSet.getString("FirstName");
            email = dataResultSet.getString("Email");
            company = dataResultSet.getString("Company");
            expectations = dataResultSet.getString("CourseExpectations");
            courseTitle = dataResultSet.getString("CourseTitle");
            courseLocation = dataResultSet.getString("CourseLocation");
            courseDate = dataResultSet.getDate("CourseStartDate");
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    //  accessors
    public String getLastName()
    {
        return lastName;
    }

    public String getFirstName()
    {
        return firstName;
    }

    public String getEmail()
    {
        return email;
    }

    public String getCompany()
    {
        return company;
    }

    public String getExpectations()
    {
        return expectations;
    }

    public String getCourseTitle()
    {
        return courseTitle;
    }

    public String getCourseLocation()
    {
        return courseLocation;
    }

    public Date getCourseDate()
    {
        return courseDate;
    }

    //  methods
    //  normal text string representation
    public String toString()
    {
        String replyString = "";

        replyString += "Name: " + lastName + ", " + firstName + CR;
        replyString += "E-mail: " + email + CR;
        replyString += "Company: " + company  + CR;
        replyString += "Course Expectations: " + expectations + CR;
        replyString += "Course Title: " + courseTitle + CR;
        replyString += "Course Location: " + courseLocation + CR;
        replyString += "Course Start Date: " + courseDate + CR + CR;

        return replyString;
    }

    //  returns data as HTML formatted un-ordered list
    public String toWebString()
    {
        String replyString = "<ul>";

        replyString += "<li><B>Name:</B> " + lastName + ", " + firstName + CR;
        replyString += "<li><B>E-mail:</B> " + email + CR;
        replyString += "<li><B>Company:</B> " + company  + CR;
        replyString += "<li><B>Course Expectations:</B> " + expectations + CR;
        replyString += "<li><B>Course Title:</B> " + courseTitle + CR;
        replyString += "<li><B>Course Location:</B> " + courseLocation + CR;
        replyString += "<li><B>Course Start Date:</B> " + courseDate + CR;

        replyString += "</ul>" + CR;

        return replyString;
    }

    // returns data formatted for an HTML table row
    public String toTableString(int rowNumber)
    {
        String replyString = "";
        String tdBegin = "<td>";
        String tdEnd = "</td>" + CR;

        replyString += "<tr>" + CR;
        replyString += tdBegin + rowNumber + tdEnd;
        replyString += tdBegin + lastName + ", " + firstName + tdEnd;
        replyString += tdBegin + "<a href=mailto:" + email + "> "
                               + email + "</a>" + tdEnd;

        replyString += tdBegin + company + tdEnd;
        replyString += tdBegin + expectations + tdEnd;
        replyString += "</tr>" + CR;

        return replyString;
    }
}

Listing 2

//  Listing 2
//
//  Applet client-side code to send a student object
//  to a servlet in a serialized fashion.
//
//  A POST method is sent to the servlet.
//

URL studentDBservlet = new URL( webServerStr );
URLConnection servletConnection = studentDBservlet.openConnection();  

// inform the connection that we will send output and accept input
servletConnection.setDoInput(true);
servletConnection.setDoOutput(true);

// Don't use a cached version of URL connection.
servletConnection.setUseCaches (false);
servletConnection.setDefaultUseCaches (false);

// Specify the content type that we will send binary data
servletConnection.setRequestProperty ("Content-Type", "application/octet-stream");

// send the student object to the servlet using serialization
outputToServlet = new ObjectOutputStream(servletConnection.getOutputStream());

// serialize the object
outputToServlet.writeObject(theStudent);

outputToServlet.flush();
outputToServlet.close();

Listing 3

//  Listing 3
//
//  Servlet server-side code to read a serialized
//  student object from an applet.
//
//  The servlet code handles a POST method
//
public void doPost(HttpServletRequest request,
                   HttpServletResponse response)
           throws ServletException, IOException
{
    ObjectInputStream inputFromApplet = null;
    Student aStudent = null;
    PrintWriter out = null;
    BufferedReader inTest = null;

    try
    {
        // get an input stream from the applet
	inputFromApplet = new ObjectInputStream(request.getInputStream());

        // read the serialized student data from applet
        aStudent = (Student) inputFromApplet.readObject();

        inputFromApplet.close();

	// continue the process for registering the student object

    }
    catch(Exception e)
    {
        // handle exception
    }
}
//  Listing 4
//
//  Servlet server-side code to send a serialized
//  vector of student objects to an applet.
//
//

public void sendStudentList(HttpServletResponse response, Vector studentVector)
{
     ObjectOutputStream outputToApplet;

     try
     {
         outputToApplet = new ObjectOutputStream(response.getOutputStream());

         System.out.println("Sending student vector to applet...");
         outputToApplet.writeObject(studentVector);
         outputToApplet.flush();

         outputToApplet.close();
         System.out.println("Data transmission complete.");
     }
     catch (IOException e)
     {
       e.printStackTrace();
     }
}

//  Listing 5
//
//  Applet client-side code to read a serialized
//  vector of student objects from a servlet.
//
//

// connect to the servlet
URL studentDBservlet = new URL( servletLocation );
URLConnection servletConnection = studentDBservlet.openConnection();  

// Don't used a cached version of URL connection.
servletConnection.setUseCaches (false);
servletConnection.setDefaultUseCaches(false);

// Read the input from the servlet.
//
// The servlet will return a serialized vector containing
// student entries.
//
inputFromServlet = new ObjectInputStream(servletConnection.getInputStream());
studentVector = (Vector) inputFromServlet.readObject();

download source appletservlet communication    
3 TIER
 figure1figure2figure3


Introduction

Currently, Java developers are delivering enterprise database applications. By using the Java Database Connection (JDBC) developers are able to query and update enterprise information stored in databases. However, the issue that continues to surface with database applications is performance. Application performance is critical when developing an enterprise application because typically the application sends database queries to a remote database server.

In a quest to increase performance, this article will discuss the Java classes available in the JDBC Application Programming Interface (API) for optimizing database access. There are two methods available for optimization: prepared statements and stored procedures. This article examines the implementation of prepared statements and stored procedures by analyzing a sample application.

The Consultant Job Selector Application

A technical recruiter to fill contracting positions primarily uses this application. The recruiter has the option to search for consultants with a maximum hourly rate and job category. Also, the recruiter has the capability to update the hourly rate for consultants in a given field. Figure 1 provides a snapshot of the database:


Figure 2 is a screen shot of the sample application.


The sample application is used to illustrate the implementation of prepared statements and stored procedures. Listings 1 – 5 contain the full source code for the sample application. The full source code can also be found at www.j-nine.com/pubs/javareport.

Analyzing the SQL Execution Plan

A SQL statement is used to access and update information stored in a database. When you submit a SQL statement, the database engine performs a number of steps in order to build an execution plan as shown in figure 3.


For every SQL statement submitted to the database, the execution plan is followed. The complete process is also followed if you submit the same SQL statement to the database engine.

Now let’s take a look at the “Consultant Job Selector” application. If the recruiter is searching for consultants with a maximum hourly rate of $50.00 to fill a Windows NT contract position, the application submits the following SQL statement to the database engine:

SELECT LastName, FirstName, Email, HourlyRate FROM consultantsWHERE HourlyRate <= 50.00 AND JobCategory = ‘Windows NT’; The JDBC code for this SQL statement follows: Connection connection = DriverManager.getConnection(databaseURL, userid, passwd);Statement searchStatement = connection.createStatement();ResultSet myResultSet = searchStatement.executeQuery (“SELECT LastName, FirstName, Email, HourlyRate ” +“FROM consultants ” +“WHERE HourlyRate <= 50.0 and JobCategory = ‘Windows NT’ “); If the recruiter made multiple queries using this statement, the database engine parses, compiles, plans and executes the statement each time. As you can see, this process is inefficient for multiple transactions with the same SQL statement.You can optimize the execution plan by parsing, compiling and planning the SQL statement ahead of time. Then when you submit the SQL statement again, you only have to perform the “execute” step of the SQL execution plan.Prepared StatementsA prepared statement is a pre-compiled SQL statement. The application sends the prepared statement to the database engine before the query is executed. This process allows the database engine to parse, compile and plan the query. Figure 4 illustrates the optimization of prepared statements.


When the same query is submitted again, the database engine only has to perform the “execute” step of the SQL execution plan. This minimizes the overhead of repeating the entire process for building an execution plan.

CAUTION: JDBC does not guarantee that the database engine will take advantage of prepared statements and provide this optimization. You should consult the technical documentation provided by your database vendor. Creating and Executing Prepared StatementIn order to create a prepared statement, you must call the prepareStatement(String sqlString) method of your Connection object. Sample code for preparing a statement to find all Windows NT consultants with maximum hourly rate of $50.00 follows: Connection connection = DriverManager.getConnection(dbURL, userid, passwd); searchPreparedStatement = connection.prepareStatement ( “SELECT LastName, FirstName, Email, HourlyRate ” +“FROM consultants ” +“WHERE HourlyRate <= 50.0 and JobCategory = ‘Windows NT’ “); The above statement is prepared during your application startup. This normally occurs after you have received your database connection from the driver manager. Repeat submissions of this query can be executed using the following code: ResultSet rs = searchPrepStmt.executeQuery(); Multiple submissions of the prepared SQL statement results in processing the “execute” step of the SQL execution plan only. This execution process effectively giving you the desired optimization. This optimization is useful in the “Consultant Job Selector” application because the recruiter normally makes multiple queries during an application session.Prepared Statement Limitations By now, you may have noticed the constraints imposed on the prepared statement. The prepared statement in the previous example is inflexible because the values for the hourly rate and job category are predefined. If the recruiter wanted to search for Windows NT consultants with a maximum hourly rate of $75.00, the program submits the following query SELECT LastName, FirstName, Email, HourlyRate FROM consultantsWHERE HourlyRate <= 75.00 AND JobCategory = ‘Windows NT’; Also during the same session, the recruiter may want to search for lower-paid Windows NT consultants with an hourly rate of $50.00. The program then submits an entirely different query SELECT LastName, FirstName, Email, HourlyRate FROM consultantsWHERE HourlyRate <= 50.00 AND JobCategory = ‘Windows NT’; Please note that the SQL statements differ only in the hourly rate. There may be situations in which you would like to make small modifications to the SQL statement before re-executing.Parameterized Prepared StatementsThe real power of a prepared statement is realized when used with parameters. Parameterized prepared statements gives you the ability to create a pre-compiled SQL statement and bind new parameter values prior to execution.Creating Parameterized Prepared Statements Now, let’s take the previous example a step further. The recruiter needs to submit a query based on their input of hourly rate and job category. Here is the code for creating parameterized prepared statements: searchPreparedStatement = connection.prepareStatement (“SELECT LastName, FirstName, Email, HourlyRate ” +“FROM consultants ” +“WHERE HourlyRate <= ? and JobCategory = ?”); Please make note of the question marks (“?”). The question marks serve as parameters or placeholders.Setting Prepared Statement ParametersIn order to set the parameters, the PreparedStatement class provides a collection of setXXX methods for setting strings, ints, and floats and other Java data types. public void setInt(int parameterIndex, int anInt)public void setString(int parameterIndex, String aString)public void setFloat(int parameterIndex, float aFloat)… many others available When the prepared statement, searchPreparedStatement, was created, two parameters were given. The first parameter was for the hourly rate and the second parameter was for the job category as shown below: searchPreparedStatement = connection.prepareStatement (“SELECT LastName, FirstName, Email, HourlyRate ” +“FROM consultants ” +“WHERE HourlyRate <= ? and JobCategory = ?”); Setting the parameter values is accomplished by binding a value to the parameter position. Parameter positions are numbered from left to right starting at 1.Given this information, the parameters for the prepared statement are set using the following code: searchPreparedStatement.setInt(1, theRate); // theRate is an intsearchPreparedStatement.setString(2, theJobCategory);// theJobCategory is a String To execute this SQL statement, use the following code: ResultSet myResultSet = searchPreparedStatement.executeQuery(); Now the SQL statements can be built based on the user input. The “Consultant Job Selector” prompts the recruiter for the hourly rate and job category. Given that information, the application uses the pre-compiled prepared statement and bind the user supplied data to the statement parameters. Figure 5 shows the dialog box for user input.


The recruiter can now retrieve a list of all Macintosh consultants whose hourly rate is $50.00 maximum and analyze the results of the query. If the recruiter would like to view consultants with a lower hourly rate, then he simply provides new values and the application binds these new values to the statement parameters.

Benefits of Using Parameterized Prepared Statements

By using parameterized prepared statements, the application can efficiently submit multiple queries to the database. The end user of the application, which was the recruiter in this example, received a quicker response from the database engine. As you can see, prepared statements with parameters give you the ability to create efficient pre-compiled SQL statements.

Stored Procedures

A stored procedure is pre-compiled SQL code that resides on the database server. They take input parameters and return a result.

Stored procedures are compiled only once in their lifetime. As a result, they actually execute faster than prepared statements. Recall that prepared statements need to build an execution plan each time the application is run.

In addition to the speed advantage, a stored procedure gives you the ability to centralize business logic. This centralization of the business logic directly supports reuse. By using stored procedures, you actually move complex business logic onto the database server as opposed to including it in every application you develop.

In the sample application, “Consultant Job Selector”, a stored procedure is used to update the hourly rates for a given job category. For example, the recruiter can increase the hourly rate of all Macintosh consultants to $60.00.

Creating A Stored Procedure

The task at hand is to create the stored procedure on the database server. Depending on your database, there is a different syntax for creating stored procedures.

CAUTION: Not all databases support stored procedures, namely MS Access. You can query the database metadata and see if stored procedures are supported. For example, the following is SQL code for creating the stored procedure sp_updateHourlyRate on an Oracle database: CREATE OR REPLACE PROCEDURE sp_updateHourlyRate( rate IN INTEGER,theJobCategory IN VARCHAR ) ISBEGINUPDATE consultants SET HourlyRate = rateWHERE JobCategory = theJobCategoryEND;

Once the SQL code for the stored procedure is successfully compiled, then it is callable by outside applications. The challenge now is to call the stored procedure from the “Consultant Job Selector” application.Calling Stored Procedures From JDBCIn order to call the stored procedure, you must use the CallableStatement available in the JDBC API. The following code creates a CallableStatement to access the stored procedure sp_updateHourlyRate. CallableStatement updateHourlyStatement;updateHourlyStatement = connection.prepareCall(“{call sp_updateHourlyRate [(?, ?)]}”); Passing Parameters To Stored Procedures At this point, you have a callable statement. However, you are not finished because you must bind values to the parameters. This process is very similar to setting parameters for prepared statements.So, to set the parameters for updating the hourly rate of Macintosh consultants to $60.00, you use the following code: updateHourlyStatement.setInt(1, 60); // set the rateupdateHourlyStatement.setString(2, “Macintosh”); // set the job category Once the parameters are set, you execute the statement using the following code:updateHourlyStatement.executeUpdate(); Getting Results From Stored ProceduresYou may have noticed that the stored procedure sp_updateHourlyRate only takes input parameters. You can modify the stored procedure to also return information to the calling application. This is accomplished by declaring an out parameter in the SQL code of the stored procedure. The SQL code for the revised stored procedure follows: CREATE OR REPLACE PROCEDURE sp_updateHourlyRate( rate IN INTEGER,theJobCategory IN VARCHAR,recordsAffected OUT INTEGER ) ISBEGIN… perform the update… count the changes and store result in parameter recordsAffected

END;

This revised stored procedure makes the updates and stores the number of updates in the recordsAffected out parameter. One additional step is required before calling the revised stored procedure. You must register the out parameters. Sample code follows for registering out parameters: updateHourlyStatement = connection.prepareCall (“{call sp_updateHourlyRate [(?, ?, ?)]}”); updateHourlyStatement.setInt(1, 50); // set the rate updateHourlyStatement.setString(2, “Macintosh”); // set the job category updateHourlyStatement.registerOutParameter(3, java.sql.Types.INTEGER) // set out param Now you can execute the stored procedure and get the results of the out parameter using the following code: updateHourlyStmt.executeUpdate();int theRecordsChanged = updateHourlyStmt.getInt(3); The point of this example was to illustrate the basic steps for getting results from stored procedures.Benefits Of Stored ProceduresEarlier in the section, you learned how stored procedures are used to increase execution speed and centralize business logic. Stored procedures also reduce network traffic because multiple, complex transactions are grouped into a single stored procedure. By having the SQL code pre-compiled as a stored procedure you effectively hide sensitive transaction information. This also reduces the amount of SQL code that you have to embed in your actual Java program. As you can see, stored procedures provide a number of advantages.

Conclusion

The sample application, “Consultant Job Selector”, was a good example of using prepared statements and stored procedures. The prepared statements were used in conjunction with parameters to provide dynamic SQL statements to the database engine. Also, stored procedures were used to centralize the business logic on the database server. The challenge now is to integrate these techniques in your next enterprise application to optimize database performance. Are you ready for the challenge?

Jobselect download code

Servlet Applet Communication
http://www.unix.org.ua/orelly/java-ent/servlet/ch10_01.htm

http://www.theserverside.com/discussions/thread.tss?thread_id=514

============================================================

Applet to Servlet

protected Vector getEmployeeList() throws Exception {
// create a reference to ObjectInputStream object
ObjectInputStream inputFromServlet = null;

// create a reference to a Vector,
// this will be used to keep the details of all employees
Vector employeeList = null;

// reference to Object class
Object obj = null;

// get the URLConnection to the servlet
URLConnection servletConnection = this.getURLConnectionToServlet();

// get the Object input stream
inputFromServlet = new ObjectInputStream(servletConnection.getInputStream());

// read the serialized object from the input stream, it may be a Vector
// or an exception.
obj = inputFromServlet.readObject();

// close the objectInputStream
inputFromServlet.close();

if (obj instanceof Vector) { // if it is a Vector type
// type cast it to a Vector and assign it to employeeList
employeeList = (Vector) obj;
} else if (obj instanceof Exception) {
//otherwise type cast it to Exception and throw that exception.
throw (Exception) obj;
}

// return employee list
return employeeList;
}

private URLConnection getURLConnectionToServlet(String urlParam)
throws Exception {
// Create a url request string
String location = URLString + “servlet/MainServlet” + urlParam;

// Create reference to URLConnection class
URLConnection servletConnection = null;

// Create a URL object for the given url
URL mainServlet = new URL(location);

// Open the conection to the servlet
servletConnection = mainServlet.openConnection();

// Set the connection to send output and accept input
servletConnection.setDoInput(true);
servletConnection.setDoOutput(true);

// Don’t use a cached version of URL connection.
servletConnection.setUseCaches(false);
servletConnection.setDefaultUseCaches(false);

// return the URLConnection object
return servletConnection;
}

===============================================

Servlet to Applet

protected void sendResponseToApplet(HttpServletResponse response) {
// Create a reference to ObjectOutputStream class
ObjectOutputStream outputToApplet = null;

try {
// Create ObjectOutputStream object
outputToApplet = new ObjectOutputStream(response.getOutputStream());
// Make a call to getAllEmployee function
Vector empList = this.getAllEmployee();

System.out.println(“Sending employee vector to applet…”);

// Write empList to outputToApplet object
outputToApplet.writeObject(empList);
// Flush and close the ObjectOutputStream
outputToApplet.flush();
outputToApplet.close();

System.out.println(“Data transmission complete…”);
} catch (Exception e) {
try {
outputToApplet.writeObject(new Exception(
“SQL Exception while fetching the Employee Details”, e));
// close and flush the ObjectOutputStream
outputToApplet.flush();
outputToApplet.close();
} catch (IOException ex) {
ex.printStackTrace();
}
}
}

private Vector getAllEmployee() throws SQLException {
// Create a vector object
Vector empVector = new Vector();

// Make a call to populateDataFromTable function of DbInteraction class
empVector = dbInteraction.populateDataFromTable();

// Return the Vector of all the employees
return empVector;
}

Leave a Reply