4 Querying for and Displaying Data

SQL Statements

Once you connect to the database and, in the process, create a Connection object, the next step is to create a Statement object. The createStatement method of the JDBC Connection object returns an object of the JDBC Statement type. Example 4-1 shows how to create a Statement object.

Statement stmt = conn.createStatement();

The Statement object is used to run static SQL queries that can be coded into the application.

In addition, for scenarios where many similar queries with differing update values must be run on the database, you use the OraclePreparedStatement object, which extends the Statement object. To access stored procedures on Oracle Database, you use the OracleCallableStatement object.

Query Methods for the Statement Object

To run a query embedded in a Statement object, you use variants of the execute method. Important variants of this method are listed in Table 4-1.

Result Sets

A ResultSet object contains a table of data representing a database result set, which is generated by executing a statement that queries the database.

A cursor points to the current row of data in a ResultSet object. Initially, it is positioned before the first row. You use the next method of the ResultSet object to move the cursor to the next row in the result set. It returns false when there are no more rows in the ResultSet object. Typically, the contents of a ResultSet object are read by using the next method within a loop until it returns false.

The ResultSet interface provides accessor methods (getBoolean, getLong, getInt, and so on) for retrieving column values from the current row. Values can be retrieved by using either the index number of the column or the name of the column.

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, to read data from multiple ResultSet objects, you must use multiple Statement objects. A ResultSet object is automatically closed when the Statement object that generated it is closed, rerun, or used to retrieve the next result from a sequence of multiple results.

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

Creating a Method in JDeveloper to Query Data

The following steps show you how to add a simple query method to your DataHandler.java class. If DataHandler.java is not open in the JDeveloper integrated development environment (IDE), double-click it in the Application Navigator to display it in the Java Source Editor.

1. In the DataHandler class, add the following import statements after the existing import statements to use the Statement and ResultSet JDBC classes:

   import java.sql.Statement;
   import java.sql.ResultSet;
   
   

2. After the connection declaration, declare variables for Statement, ResultSet, and String objects as follows:

   Statement stmt;
   ResultSet rset;
   String query;
   String sqlString;
   
   

3. Create a method called getAllEmployees, which will be used to retrieve employee information from the database. Enter the signature for the method:

   public ResultSet getAllEmployees() throws SQLException{ 
   
   

4. Press Enter to include a closing brace for this method, and a new line in which to start entering the method code.

5. Call the getDBConnection method created earlier:

   getDBConnection(); 
   
   

6. Use the createStatement method of the Connection instance to provide context for executing the SQL statement and define the ResultSet type. Specify a read-only, scroll-sensitive ResultSet type:

   stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
   
   

   The Java Code Insight feature can help you ensure that the statement syntax is correct.

7. Define the query and print a trace message. The following code uses a simple query: it returns all the rows and columns in the Employees table and the data is ordered by the Employee ID:

   query = "SELECT * FROM Employees ORDER BY employee_id";
   System.out.println("\nExecuting query: " + query);
   
   

8. Run the query and retrieve the results in the ResultSet instance as follows:

   rset = stmt.executeQuery(query); 
   
   

9. Return the ResultSet object:

   return rset;
   
   

10. Save your work. From the File menu, select Save All.

The code for the getAllEmployees method should be as shown in Example 4-3.

public ResultSet getAllEmployees() throws SQLException{
        getDBConnection(); 
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
               ResultSet.CONCUR_READ_ONLY);
        query = "SELECT * FROM Employees ORDER BY employee_id";
        System.out.println("\nExecuting query: " + query);
        rset = stmt.executeQuery(query); 
        return rset;
    }

Testing the Connection and the Query Methods

In the following steps, you create a simple Java class to test the methods in the DataHandler.java class. To test your application at this stage, you can temporarily set the value of the jdbcUrl variable to the connection string for your database and set the values of the userid and password variables to the values required to access the HR schema ("hr" in each case).

1. Open the DataHandler.java class in the Java Visual Editor from the Application Navigator.

2. Change the jdbcUrl, userid and password variables to contain the values required for the HR schema as follows:

   String jdbcUrl = "connect-string"
   String userid = "hr";
   String password = "hr";
   
   

   where connect-string is, for example:

   jdbc:oracle:thin:@dbhost.companyname.com:1521:ORCL
   

   See Also:

   Declaring Connection-Related Variables in Chapter 3

3. Create a new Java class in the hr package. Name it JavaClient, make it a public class, and generate a default constructor and a main method. The skeleton JavaClient.java class is created and displayed in the Java Source Editor.

   See Also:

   Chapter 3 for information on creating a Java class file

4. Import the ResultSet package:

   import java.sql.ResultSet;
   
   

5. In the main method declaration, add exception handling as follows:

   public static void main(String[] args) throws Exception{
   
   

6. Replace the JavaClient object created by default with a DataHandler object. Locate the following line:

   JavaClient javaClient = new JavaClient();
   
   

   Replace this with:

   DataHandler datahandler = new DataHandler();
   
   

7. Define a ResultSet object to hold the results of the getAllEmployees query, and iterate through the rows of the result set, displaying the first four columns, Employee Id, First Name, Last Name, and Email. To do this, add the following code to the main method:

   ResultSet rset = datahandler.getAllEmployees();
   
   while (rset.next()) {
   System.out.println(rset.getInt(1) + " " +
     rset.getString(2) + " " + 
     rset.getString(3) + " " + 
     rset.getString(4));
   }
   
   

8. Compile the JavaClient.java file to check for compilation errors. To do this, right-click in the Java Source Editor, and select Make from the shortcut menu.

   If there are no errors in compilation, you should see the following message in the Log window:

   Successful compilation: 0 errors, 0 warnings

9. Run the JavaClient.java file. To do this, right-click in the Java Source Editor window and select Run from the shortcut menu.

10. Examine the output in the Log window. Notice the trace message, followed by the four columns from the Employees table as shown in Figure 4-1.

    Figure 4-1 Test Output for Query Method in Log Window

    
    Description of "Figure 4-1 Test Output for Query Method in Log Window"
    
    

11. When you finish testing the application, set the jdbcUrl, userid and password variables in DataHandler.java back to null.

Overview of Page Presentation

In the application created in this guide, JSP pages are used to do the following tasks:

• Display data.

• Hold input data entered by users adding employees and editing employee data.

• Hold the code needed to process the actions of validating user credentials and adding, updating, and deleting employee records in the database.

Because JSP pages are presented to users as HTML or XML, you can control the presentation of data in the same way as you would for static HTML and XML pages. You can use standard HTML tags to format your page, including the title tag in the header to specify the title to be displayed for the page.

You use HTML tags for headings, tables, lists and other items on your pages. Style sheets can also be used to define the presentation of items. If you use JDeveloper to develop your application, you can select styles from a list.

The following sections describe the main elements used in the JSP pages of the sample application:

• JSP Tags

• Scriptlets

• HTML Tags

• HTML Forms

Creating a Simple JSP Page

The following steps describe how to create a simple JSP page:

1. In the Application Navigator, right-click the View project and choose New from the shortcut menu.

2. In the New Gallery, from the Filter By: list, select All Technologies.

3. Expand the Web Tier node and select JSP.

4. In the Items list, select JSP and click OK. The Create JSP Wizard is displayed.

5. On the JSP File screen, enter a name for the JSP page and select JSP Page.

6. On the Error Page Options screen, select Do Not Use an Error Page to Handle Uncaught Exceptions in this File.

7. Note:

   You create an error page to make Java exceptions thrown by an application more readable for the user. When you create an error page, you specify a user-friendly interpretation of possible application errors. However, functionally, this is not a required feature in the application and has not been covered in this guide.

8. Do not add any JSP tag libraries at this stage. Click Next on the Tag Libraries screen.

9. Retain the default settings on the HTML Options screen and click Finish. The new page opens in the JSP/HTML Visual Editor and is ready for you to start adding text and components to your web page.

Adding Static Content to a JSP Page

JDeveloper provides the Component Palette and the Property Inspector on the right hand side of the JSP/HTML Visual Editor. You can also use the JSP Source Editor by clicking the Source Editor tab next to the Design tab at the bottom of the page. The Component Palette allows you to add components to the page and the Property Inspector allows you to set the properties of the components. A blank page in the Visual Editor is shown in Figure 4-2.

Figure 4-2 Adding Content to JSP Pages in the JDeveloper Visual Source Editor

Description of "Figure 4-2 Adding Content to JSP Pages in the JDeveloper Visual Source Editor"

The following steps show how you can add text to the employees.jsp page. They use the Visual Editor to modify the JSP. The Visual Editor is like a WYSIWYG editor and you can use it to modify content.

1. With employees.jsp open in the Visual Editor, in the top line of your page, enter AnyCo Corporation: HR Application. From the list of styles at the top of the page, on the left-hand side, select Heading 2.

2. With the cursor still on the heading you added, from the Design menu select Align, and then Center.

3. In a similar way, on a new line, enter Employee Data, and format it with the Heading 3 style. Position it on the left-hand side of the page.

Adding a Style Sheet to a JSP Page

You can add a style sheet reference to your page, so that your headings, text, and other elements are formatted in a consistent way with the presentation features, such as the fonts and colors used in the Web pages. You can add a style sheet to the page as follows:

1. With employees.jsp open in the Visual Editor, click the list arrow at the top right of the Component Palette, and select CSS.

2. From the CSS list, drag JDeveloper onto your page. As soon as you select the style sheet it is added to your page and formats the page with the JDeveloper styles. Figure 4-3 shows the JSP Page with the content added to it in the previous section and the JDeveloper stylesheet applied to it.

   Figure 4-3 Adding Static Content to the JSP Page

   
   Description of "Figure 4-3 Adding Static Content to the JSP Page"
   
   

   Note:

   In JDeveloper version 10.1.3, you can associate a stylesheet with the JSP page while creating it in the JSP Creation Wizard. The only difference is that you need to browse and locate the stylesheet to be applied to the JSP page, instead of just dragging and dropping it onto the page.

Adding a JSP useBean Tag to Initialize the DataHandler Class

A jsp:useBean tag identifies and initializes the class that holds the methods that run in the page. To add a jsp:useBean tag, follow these steps:

1. Open employees.jsp in the Visual Editor.

2. In the Component Palette, select the JSP set of components. Scroll through the list to select UseBean. Then, drag and drop it onto your JSP page, below the headings.

3. In the Insert UseBean dialog box, enter empsbean as the ID, and for the Class, browse and select the hr.DataHandler class. Set the Scope to session, and leave the Type and BeanName fields blank.

4. Click OK to create the tag in the page.

Figure 4-4 shows the representation of the useBean tag in the employees.jsp page.

Figure 4-4 useBean Representation in the employees.jsp File

Description of "Figure 4-4 useBean Representation in the employees.jsp File"

Creating a Result Set

The following steps describe how you can add a scripting element to your page to call the getAllEmployees method and hold the result set data that is returned. This query is defined in the DataHandler class, and initialized in the page by using the jsp:useBean tag.

1. Open the employees.jsp page in the Visual Editor. In the JSP part of the Component Palette, select Scriptlet and drag and drop it onto the JSP page next to the representation of the UseBean.

2. In the Insert Scriptlet dialog box, enter the following lines of code, which will call the getAllEmployees method and produce a ResultSet object:

   ResultSet rset;
   rset = empsbean.getAllEmployees();
   
   

   Click OK. A representation of the scriptlet is displayed on the page as shown in Figure 4-5.

   Figure 4-5 Scriptlet Representation in a JSP Page

   
   Description of "Figure 4-5 Scriptlet Representation in a JSP Page"
   
   

3. Select the Source tab at the bottom of the Visual Editor to see the code that has been created for the page so far. A wavy line under ResultSet indicates that there are errors in the code.

4. The Structure window on the left-hand side also indicates any errors in the page. Scroll to the top of the window and expand the JSP Errors node. Figure 4-6 shows how the error in the code is shown in the Structure window.

   Figure 4-6 Viewing Errors in the Structure Window

   
   Description of "Figure 4-6 Viewing Errors in the Structure Window"
   
   

5. You must import the ResultSet package. To do this, click the page node in the Structure window to display the page properties in the Property Inspector.

6. Click in the empty box to the right of the import property. Click the ellipsis symbol (...). The import dialog box is displayed, which is shown in Figure 4-7.

   Figure 4-7 Importing Packages in JDeveloper

   
   Description of "Figure 4-7 Importing Packages in JDeveloper"
   
   

7. In the import list, select the Hierarchy tab, expand the java node, then the sql node, and then select ResultSet. Click OK.

8. On the Source tab, examine the code to see if the import statement has been added to the code for your page. The error should disappear from the list in the Structure window. Before continuing with the following sections, return to the design view of the page by selecting the Design tab.

Adding a Table to the JSP Page to Display the Result Set

The following steps describe how you can add a table to the JSP page to display the results of the getAllEmployees query:

1. If the employees.jsp page is not open in the Visual Editor, double-click it in the Application Navigator to open it, and work in the Design tab. With the employees.jsp file open in the Visual Editor, position the cursor after the scriptlet and from the HTML Common page of the Component Palette, select the Table component.

2. In the Insert Table dialog box, specify 1 row and 6 columns. Leave all Layout properties as defaults. Click OK.

3. In the table row displayed on the page, enter text as follows for the headings for each of the columns: First Name, Last Name, Email, Job, Phone, Salary. Use Heading 4 to format the column names.

4. Add a scripting element for output, this time to display the values returned for each of the columns in the table. To do this, select the table as follows. Position the cursor on the top border of the table, and click when the cursor image changes to a table image. From the JSP Component Palette, select Scriptlet. (You need not drag the scriptlet into your table; it is inserted automatically.)

5. In the Insert Scriptlet dialog box, enter the following lines of code:

   while (rset.next ())
       {
       out.println("<tr>");
       out.println("<td>" + 
         rset.getString("first_name") + "</td><td> " + 
         rset.getString("last_name") + "</td><td> " + 
         rset.getString("email") + "</td><td> " + 
         rset.getString("job_id") + "</td><td>" + 
         rset.getString("phone_number") + "</td><td>" + 
         rset.getDouble("salary") + "</td>");
       out.println("</tr>");
       } 
   

6. Click OK.

The JSP page created is shown in Figure 4-8.

Figure 4-8 Table in a JSP Page

Description of "Figure 4-8 Table in a JSP Page"

Creating a Java Method for Filtering Results

The following steps describe how you can create the getEmployeesByName method. This method allows users to filter employees by their first or last name.

1. From the Application Navigator, open the DataHandler.java class in the Java Visual Editor.

2. After the getAllEmployees method, declare the getEmployeesByName method as follows:

   public ResultSet getEmployeesByName(String name) throws SQLException {
   
   }
   
   

3. Within the body of the method, add the following code to convert the name to uppercase to enable more search hits:

   name = name.toUpperCase();
   
   

4. Call the method to connect to the database:

   getDBConnection();
   
   

5. Specify the ResultSet type and create the query:

   stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
                               ResultSet.CONCUR_READ_ONLY);
   query =
   "SELECT * FROM Employees WHERE UPPER(first_name) LIKE \'%" + name + "%\'" +
   " OR UPPER(last_name) LIKE \'%" + name + "%\' ORDER BY employee_id";
   
   

6. Print a trace message:

   System.out.println("\nExecuting query: " + query);
   
   

7. Run the query and return a result set as before:

   rset = stmt.executeQuery(query);
   return rset; 
   
   

8. Save the file and compile it to ensure there are no compilation errors.

Testing the Query Filter Method

You can use the JavaClient.java class created in Testing the Connection and the Query Methods to test the getEmployeesByName method. You must add the getEmployeesByName method to display the query results as described in the following steps:

1. Open the JavaClient.java class in the Java Source Editor.

2. After the result set displaying the results from the getAllEmployees query, define a result set for the conditional query as follows:

   rset = datahandler.getEmployeesByName("King");
    
   System.out.println("\nResults from query: ");
    
   while (rset.next()) {
     System.out.println(rset.getInt(1) + " " + 
     rset.getString(2) + " " + 
     rset.getString(3) + " " + 
     rset.getString(4));
   }
   
   

3. To test your application at this stage, you can temporarily adjust the values of the jdbcUrl, userid and password variables in the DataHandler class to provide the values required for the HR schema. Save the file, and compile it to check for syntax errors.

   Note:

   Make sure you change the values of userid, password, and jdbcUrl back to null after testing. For more information, refer to Declaring Connection-Related Variables.

4. To test-run the code, right-click in the Java Source Editor and select Run from the shortcut menu. In the Log window, you will first see the results of the getAllEmployees method, then the results from the getEmployeesByName("xxx") query. Here, xxx is set to "King" to test the filtering functionality. In actual operation, this parameter will be set to the value provided by the user of the application to filter the search.

Adding Filter Controls to the JSP Page

To accept the filter criterion and to display the filter results, you must modify the employees.jsp page. In the following steps, you add a form element and controls to the employees.jsp page that accepts input from users to filter employees by name:

1. With the employees.jsp page displayed in the Visual Editor, position the cursor between the useBean tag and the scriptlet.

2. In the HTML Forms page of the Component Palette, select Form.

3. In the Insert Form dialog box, use the down arrow for the Action field and select employees.jsp. Leave the other fields empty and click OK.

   The form is displayed on the page in the Visual Editor, represented by a dotted-line rectangle.

4. In the HTML Forms page of the Component Palette, scroll to Text Field. Select it and drag and drop it inside the Form component. In the Insert Text Field dialog, enter query as the value of the Name field and click OK. The text field box is displayed within the form. This field allows users to enter filter criteria.

5. Position the cursor to the left of the Text Field and add the following text:

   Filter by Employee name:

6. In the HTML Forms page of the Component Palette, scroll to Submit Button. Select it and drop it inside the Form component to the right of the Text Field.

7. In the Insert Submit Button dialog box, leave the Name field empty and enter Filter as the value of the Value field, and click OK.

   Figure 4-9 shows these HTML Form components in the employees.jsp file.

   Figure 4-9 HTML Form Components in the JSP Page

   
   Description of "Figure 4-9 HTML Form Components in the JSP Page"
   
   

Displaying Filtered Data in the JSP Page

In the previous section, you created a text field component on the JSP page that accepts user inputs. In this text field, users can specify a string with which to filter employee names. You also added a submit button.

In the following steps, you add code to the scriptlet in the employees.java file to enable it to use the getEmployeesByName method. This method is used only if a user submits a value for filtering the results. If this filter criterion is not specified, the getAllEmployees method is used.

1. Open the employees.jsp file in the Visual Editor.

2. Double-click the Scriptlet tag on the page (not the one inside the table) to open the Properties dialog box. Modify the code as follows:

   ResultSet rset;
   String query = request.getParameter("query");
   if (query != null && query != null)
     rset = empsbean.getEmployeesByName(query);
   else
     rset = empsbean.getAllEmployees();
   
   

   Figure 4-10 shows how you can use the Scriptlet Properties dialog box to modify the code.

   Figure 4-10 Using the Scriptlet Properties Dialog Box

   
   Description of "Figure 4-10 Using the Scriptlet Properties Dialog Box"
   
   

3. Click OK.

4. Save the file.

Creating a Method to Authenticate Users

In the following steps, you create a method in the DataHandler.java class that authenticates users by checking that the values they supply for the userid and password match those required by the database schema.

1. Open the DataHandler.java class in the Source Editor.

2. Create a method called authenticateUser that checks if the userid, password, and host values supplied by a user are valid:

   public boolean authenticateUser(String jdbcUrl, String userid, String password,
     HttpSession session) throws SQLException {
    
   }
   
   

3. JDeveloper prompts you with a wavy underline and a message that you need to import a class for HttpSession. Press the Alt+Enter keys to import the javax.servlet.http.HttpSession class.

4. Within the body of the method, assign the jdbcUrl, userid, and password values from the call to the attributes of the current object as follows:

   this.jdbcUrl= jdbcUrl;
   this.userid = userid;
   this.password = password;
   
   

5. Attempt to connect to the database using the values supplied, and if successful, return a value of true. Enclose this in a try block as follows:

   try {
     OracleDataSource ds;
     ds = new OracleDataSource();
     ds.setURL(jdbcUrl);
     conn = ds.getConnection(userid, password);
     return true;
   }
   

   See Also:

   For information about using try and catch blocks, refer to Exception Handling in Chapter 5.

6. To handle the case where the login credentials do not match, after the try block, add a catch block. The code in this block prints out a log message and sets up an error message. This error message can be displayed to the user if a login attempt fails. The jdbcUrl, userid and password variables are set back to null, and the method returns the value false. To do this, enter the following code:

   catch ( SQLException ex )  {
     System.out.println("Invalid user credentials");
     session.setAttribute("loginerrormsg", "Invalid Login. Try Again...");
     this.jdbcUrl = null;
     this.userid = null;
     this.password = null;
     return false;
   } 
   
   

The complete code is shown in Example 4-4.

public boolean authenticateUser(String userid, String password,
  HttpSession session) throws SQLException {
 
  this.jdbcUrl = jdbcUrl;
  this.userid = userid;
  this.password = password;
  try {
    OracleDataSource ds;
    ds = new OracleDataSource();
    ds.setURL(jdbcUrl);
    conn = ds.getConnection(userid, password);
    return true;
  } catch ( SQLException ex )  {
  System.out.println("Invalid user credentials");
  session.setAttribute("loginerrormsg", "Invalid Login. Try Again...");
  this.jdbcUrl = null;
  this.userid = null;
  this.password = null;
  return false;
  } 
}

Creating a Login Page

The following steps create a login.jsp page, on which users enter the login details for the schema they are going to work on:

1. In the View project, create a new JSP page. Change the Name to login.jsp and accept all other defaults. The new page opens in the JSP/HTML Visual Editor and is ready for you to start adding text and components to your Web page.

2. Apply the JDeveloper style sheet to the page.

3. Give the page the same heading as earlier, AnyCo Corporation: HR Application, apply the Heading 2 style to it, and align it to the center of the page.

4. On the next line, enter Application Login, with the Heading 3 style applied. Align this heading to the left-hand side of the page.

Preparing Error Reports for Failed Logins

The following steps add functions to the login.jsp page for displaying error messages when a user login fails. The scriptlets and expression used in the login.jsp page set up a variable to hold any error message. If the user login fails, the connection method sets a message for the session. This page checks to see if there is such a message, and if present, it displays the message.

1. With the login.jsp page open in the Visual Editor, position the cursor after the text on this page. Then, from the JSP page of the Component Palette, drag and drop the Scriptlet element from the palette onto the page.

2. In the Insert Scriptlet dialog box, enter the following code:

   String loginerrormsg = null;
       loginerrormsg = (String) session.getAttribute("loginerrormsg");
       if (loginerrormsg != null) {
   
   

3. Add another scriptlet in exactly the same way, and this time enter only a single closing brace (}) in the Insert Scriptlet dialog box.

4. Place the cursor between the two scriptlets and press Enter to create a new line. Apply the Heading 4 style to the new line.

5. With the cursor still on the new line, in the JSP page of the Component Palette, click Expression.

6. In the Insert Expression dialog box, enter loginerrormsg.

7. To see the code that has been added to your login.jsp page, below the Visual Editor, select the Source tab. The code should appear as follows:

   <% 
       String loginerrormsg = null;
       loginerrormsg = (String) session.getAttribute("loginerrormsg");
       if (loginerrormsg != null) {
   %>
   <h4>
     <%=  loginerrormsg %>
   </h4>
   <% 
   }
   %>
   
   

Before continuing with the following sections, return to the design view of the page by selecting the Design tab.

Creating the Login Interface

In these steps, you add fields to the login.jsp page on which users enter their login details.

1. If the login.jsp page is not open in the Visual Editor, double-click it in the Application Navigator to open it, and check that the Design tab is selected.

2. Position the cursor after the second scriptlet and, in the HTML Forms page of the Component Palette, select Form. The Form is displayed on the page in the Visual Editor, represented by a dotted-line rectangle.

3. In the HTML Forms page of the Component Palette, select Form. In the Insert Form dialog box, enter login_action.jsp as the value for the Action field. This file will be used to process the user input in the login.jsp file. (You cannot select this page from a list as it is not created yet.) Leave the other fields empty and click OK.

   The Form is displayed on the page in the Visual Editor, represented by a dotted rectangle.

4. Add a Table to the page. Position it inside the Form. Specify a 3-row and 2-column layout, and accept other layout defaults.

5. In the first column of the three rows, enter the following as the text to display for users:

   User ID:

   Password:

   Host:

6. From the HTML page of the Component Palette, drag a Text Field into the table cell to the right of the User ID: cell. In the Insert Text Field dialog box, enter userid as the value of the Name property. Leave the other fields empty and click OK.

7. In the same way, add a Text Field to the table cell to the right of the Password: cell and enter password as the value of the Name property. Similarly, add a Text Field to the table cell to the right of the Host: cell and enter host as the value of the Name property.

8. Drag a Submit button to the Form below the table. Enter Submit for the Value property of the button.

Your login.jsp page should now appear as shown in Figure 4-11.

Figure 4-11 Login Page

Description of "Figure 4-11 Login Page"

Creating a JSP Page to Handle Login Action

In the following steps, you create the login_action.jsp page, which is a nonviewable page that processes the login operation.

1. Create a JSP page and call it login_action.jsp. Accept all default settings for the JSP page.

2. With login_action.jsp open in the Visual Editor, from the JSP page of the Component Palette, drag a Page Directive component to the page. In the Insert Page Directive dialog box, for the Import field, browse to import java.sql.ResultSet. Click OK.

3. Drag a jsp:usebean tag onto the page. Enter empsbean as the ID and browse to select hr.DataHandler as the Class. Set the Scope to session, and click OK.

4. Position the cursor after the useBean tag and add a Scriptlet to the page. Enter the following code into the Insert Scriptlet dialog box and click OK.

   boolean userIsValid = false;
   String host = request.getParameter("host");
   String userid = request.getParameter("userid");
   String password = request.getParameter("password");
   String jdbcUrl = "jdbc:oracle:thin:@" + host;
   userIsValid = empsbean.authenticateUser(jdbcUrl, userid, password, session);
   
   

5. Add another Scriptlet, and add the following code to it:

   if (userIsValid){
   
   

6. In the JSP page of the Component Palette, find Forward and drag it onto the page to add a jsp:forward tag onto the page. In the Insert Forward dialog box, enter employees.jsp.

7. Add another scriptlet, and enter the following code:

   } else {
   
   

8. Add another jsp:forward tag, and this time move forward to login.jsp.

9. Add a final Scriptlet, and enter a closing brace (}).

10. Save your work.

To see the code that has been added to login_action.jsp, select the Source tab. The code displayed is similar to the following:

<body>
<%@ page import="java.sql.ResultSet"%><jsp:useBean id="empsbean"
                                                   class="hr.DataHandler"
                                                   scope="session"/>
<%boolean userIsValid = false;
String host = request.getParameter("host");
String userid = request.getParameter("userid");
String password = request.getParameter("password");
String jdbcUrl = "jdbc:oracle:thin:@" + host + ":1521:ORCL";
userIsValid = empsbean.authenticateUser(jdbcUrl, userid, password, session);%><%if (userIsValid){%><jsp:forward page="employees.jsp"/><%if (userIsValid){%><jsp:forward page="login.jsp"/><%}%>
</body>