The code just after the IF statement displayed in the previous section handles all of the tasks necessary to display the account information to the user. The code begins by creating a new type of statement called a PreparedStatement. As you might have guessed, when we access the MySQL database for the account information, we are going to limit the data returned using the account number entered by the user. We need a WHERE clause like this:
WHERE acc_id = 1034055 and ts = 0
This WHERE clause will cause the database server to return information for records only in which the acc_id is 1034055 and the ts field is 0. In the previous chapter, we built this WHERE clause using code like this:
"WHERE acc_id = " + <somevariable> + " and ts = 0"
While this works, there will be cases later in this chapter where we want to insert binary data into the query for updating. SQL, Connector/J, and MySQL all support a Statement object called PreparedStatement. This statement gives us the ability to use placeholders within the query and replace them with actual values using statements that place the data into the query in the proper format. For example, our WHERE clause could be written as follows:
"WHERE acc_id = ? and ts = 0"
The ? character is the placeholder and is counted as placeholder number 1. Before showing you how to use the PreparedStatement, let’s create an object of its type first. The code in our servlet is
statement = connection.prepareStatement( "SELECT *
FROM acc_acc LEFT JOIN acc_add
on acc_acc.acc_id = acc_add.acc_id
WHERE acc_acc.acc_id = ? AND acc_acc.ts = 0");
Notice that there is a join in this code; we ignore that fact until the “Joins” sec- tion later in this chapter. A PreparedStatement is created using the Connection object and a call to the method prepareStatement(String). Unlike with the
Statement object, we place our query into the call to prepareStatement using the ? placeholder in all of the places we need to fill with data at a later point. The query can contain any number of placeholders, and they are counted with the leftmost placeholder having a value of 1.
Once the PreparedStatement has been allocated, it’s time to fill in the accounts number. We accomplish this with the following code:
statement.setInt(1,
Integer.parseInt(inRequest.getParameter("account")));
The JDBC specification defines a large set of set<type> methods against a Pre- paredStatement object to fill all of the placeholders. In the statement above, the method fills the first placeholder with the integer value associated with the account parameter returned from the <form> HTML found in Listing 6.2. Using the placeholders means that we needn’t concern ourselves with creating a large query string using smaller strings. In addition, we don’t need to worry about for- matting the actual value being passed to the database server.
Once all of the parameters have been filled, the query is executed with the code rs = statement.executeQuery();
Once the query returns the ResultSet, we need to build the HTML that will be passed back to the client browser as a result of its initial request. Earlier in the servlet code, a call was made to obtain a PrintWriter object:
out = outResponse.getWriter();
The PrintWriter object is directly associated with the Response object passed back to the client browser. Anything that we write in the PrintWriter object will be passed back to the browser. Since we have already told the system that the response will be HTML, we need to put HTML tags into the object.
The first code we encounter after the ResultSet is obtained from our query is a check to ensure that there are results from the query. The code looks like this:
if (!rs.next()) {
out.println("<HTML>No Account Found for # " + inRequest.getParameter("account") + "</HTML>"); } else {
Because we need to move to the next row in the ResultSet, we check the return value of a call to rs.next(). If the command is successful, then we know there was at least one result in the set (we don’t handle multiple rows in this example code). Otherwise, the account wasn’t in the database, so we return a small HTML page to the client to let them know the account wasn’t found.
If the account was found, we start the process of building the HTML page found in Figure 6.3. The HTML page consists of a little text and a <FORM> with the
account information. Refer to Listing 6.3 for all of the HTML passed to the client to build the page. All of the values returned from the ResultSet are obtained with the familiar rs.getString() method and used to build the various tags nec- essary for the client. Once the HTML is created, the page is automatically returned to the client’s browser by the servlet. Once the page is displayed, the user can review the information and possibly change it.