4 Querying for and Displaying DataThis chapter adds functions and code to the DataHandler.java file for querying the database. This chapter has the following sections: Show
4.1 Overview of Querying for Data in Oracle Database XEIn outline, to query Oracle Database XE from a Java class to retrieve data, you must do the following:
The following sections describe important Java Database Connectivity (JDBC) concepts related to querying the database from a Java application:
4.1.1 SQL StatementsOnce 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. Example 4-1 Creating 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 XE, you use the OracleCallableStatement object. See Also:
4.1.2 Query Methods for the Statement ObjectTo 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. Table 4-1 Key Query Execution Methods for java.sql.Statement Method NameReturn TypeDescriptionexecute(String sql) Boolean Runs the given SQL statement, which returns a Boolean response: true if the query runs successfully and false if it does not. addBatch() void Adds a set of parameters to a PreparedStatement object batch of commands. executeBatch() int[] Submits a batch of commands to the database for running, and returns an array of update counts if all commands run successfully. executeQuery(String sql) ResultSet Runs the given SQL statement, which returns a single ResultSet object. executeUpdate(String sql) int Runs the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or a SQL statement that returns nothing, such as a SQL DDL statement. See Also: http://www.oracle.com/technetwork/java/javase/documentation/api-jsp-136079.html4.1.3 Result SetsA 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. 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. See Also:
4.1.3.1 Features of ResultSet ObjectsScrollability refers to the ability to move backward as well as forward through a result set. You can also move to any particular position in the result set, through either relative positioning or absolute positioning. Relative positioning lets you move a specified number of rows forward or backward from the current row. Absolute positioning lets you move to a specified row number, counting from either the beginning or the end of the result set. When creating a scrollable or positionable result set, you must also specify sensitivity. This refers to the ability of a result set to detect and reveal changes made to the underlying database from outside the result set. A sensitive result set can see changes made to the database while the result set is open, providing a dynamic view of the underlying data. Changes made to the underlying column values of rows in the result set are visible. Updatability refers to the ability to update data in a result set and then copy the changes to the database. This includes inserting new rows into the result set or deleting existing rows. A result set may be updatable or read-only. 4.1.3.2 Summary of Result Set Object TypesScrollability and sensitivity are independent of updatability, and the three result set types and two concurrency types combine for the following six result set categories:
Example 4-2 demonstrates how to declare a scroll-sensitive and read-only ResultSet object. Example 4-2 Declaring a Scroll-Sensitive, Read-Only ResultSet Object stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);Note: 4.2 Querying Data from a Java ApplicationThis section discusses how you can use JDeveloper to create a Java class that queries data in Oracle Database XE in the following sections:
4.2.1 Creating a Method in JDeveloper to Query DataThe 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 the DataHandler.java file in the Application Navigator to display it in the Java Source Editor.
The code for the getAllEmployees method should be as shown in Example 4-3. Example 4-3 Using the Connection, Statement, Query, and ResultSet Objects 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; }4.2.2 Testing the Connection and the Query MethodsIn 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.
See Also: Declaring Connection-Related Variables4.3 Creating JSP PagesThe HRApp application uses JavaServer Pages (JSP) technology to display data. JSP technology provides a simple, fast way to create server-independent and platform-independent dynamic Web content. A JSP page has the .jsp extension. This extension notifies the Web server that the page should be processed by a JSP container. The JSP container interprets the JSP tags and scriptlets, generates the content required, and sends the results back to the client as an HTML or XML page. To develop JSP pages, you use some or all of the following:
See Also: http://www.oracle.com/technetwork/java/javaee/jsp/index.html In this section, you will see how you can create JSP pages for the application in this guide in the following sections:
4.3.1 Overview of Page PresentationIn the application created in this guide, JSP pages are used to do the following tasks:
JSP pages are presented to users as HTML or XML. So, you can control the presentation of data in the same way as you do it 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:
4.3.1.1 JSP TagsJSP tags are used in the sample application in this guide for the following tasks: to initialize Java classes that hold the application methods and the JavaBean used to hold a single employee record, and to forward the user to either the same or another page in the application. The jsp:useBean tag is used in pages to initialize the class that contains all the methods needed by the application, and the jsp:forward tag is used to forward the user to a specified page. You can drag the tags you need from the Component Palette of JSP tags, and enter the properties for the tag in the corresponding dialog box that is displayed. 4.3.1.2 ScriptletsScriptlets are used to run the Java methods that operate on the database and to perform other processing in JSP pages. You can drag a scriptlet tag component from the Component Palette and drop it onto your page, ready to enter the scriptlet code. In JDeveloper, the code for scriptlets is entered in the Scriptlet Source Editor dialog box. In this application, you use scriplets for a variety of tasks. As an example, one scriptlet calls the DataHandler method that returns a ResultSet object containing all the employees in the Employees table, which you can use to display that data in your JSP page. As another example, a scriplet is used to iterate through the same ResultSet object to display each item in a row of a table. 4.3.1.3 HTML TagsHTML tags are typically used for layout and presentation of the nondynamic portions of the user interface, for example headings and tables. In JDeveloper, you can drag and drop a Table component from the Component Palette onto your page. You must specify the number of rows and columns for the table, and all the table tags are automatically created. 4.3.1.4 HTML FormsHTML forms are used to interact with or gather information from the users on Web pages. The FORM element acts as a container for the controls on a page, and specifies the method to be used to process the form input. For the filter control to select which employees to display, the employees.jsp page itself processes the form. For login, insert, edit, and delete operations, additional JSP pages are created to process these forms. To understand how the JSP pages in this application are interrelated, refer to Figure 1-2. You can add a form in a JSP page by selecting it from the Component Palette of HTML tags. If you attempt to add a control on a page outside of the form component or in a page that does not contain a form, then JDeveloper prompts you to add a form component to contain it. 4.3.2 Creating a Simple JSP PageThe following steps describe how to create a simple JSP page:
4.3.3 Adding Static Content to a JSP PageJDeveloper 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 enables you to add components to the page and the Property Inspector enables you to set the properties of the components. A blank page in the Visual Editor is shown in Figure 4-3. Figure 4-3 Adding Content to JSP Pages in the JDeveloper Visual Source Editor Description of "Figure 4-3 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.
4.3.4 Adding a Style Sheet to a JSP PageYou 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:
4.4 Adding Dynamic Content to the JSP Page: Database Query ResultsThis section includes the following subsections:
4.4.1 Adding a JSP useBean Tag to Initialize the DataHandler ClassA 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:
Figure 4-7 shows the representation of the useBean tag in the employees.jsp page. Figure 4-7 useBean Representation in the employees.jsp File Description of "Figure 4-7 useBean Representation in the employees.jsp File" If you do not see the UseBean tag on the design view of the JSP, then go to Preferences from the Tools menu, and select the Show Invisible JSP Elements option, which is shown Figure 4-8. Figure 4-8 Preferences Window to Select JSP and HTML Visual Editor Options Description of "Figure 4-8 Preferences Window to Select JSP and HTML Visual Editor Options" 4.4.2 Creating a Result SetThe 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.
4.4.3 Adding a Table to the JSP Page to Display the Result SetThe following steps describe how you can add a table to the JSP page to display the results of the getAllEmployees query:
The JSP page created is shown in Figure 4-13. Figure 4-13 Table in a JSP Page Description of "Figure 4-13 Table in a JSP Page" 4.5 Filtering a Query Result SetYou can filter the results of a query by certain parameters or conditions. You can also allow users of the application to customize the data filter. In the sample application created in this guide, the procedure of filtering the query result consists of the following tasks:
This section describes filtering query data in the following sections:
4.5.1 Creating a Java Method for Filtering ResultsThe following steps describe how you can create the getEmployeesByName method. This method enables users to filter employees by their first or last name.
4.5.2 Testing the Query Filter MethodYou 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:
4.5.3 Adding Filter Controls to the JSP PageTo 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:
4.5.4 Displaying Filtered Data in the JSP PageIn the previous section, you created a text field component on the JSP page that accepts user input. 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.
4.6 Adding Login Functionality to the ApplicationThe login functionality used in the sample application is a simple example of application-managed security. It is not a full Java EE security implementation, but simply used as an example in the sample application. To implement this simple login functionality, you must perform the following tasks:
4.6.1 Creating a Method to Authenticate UsersIn 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.
The complete code is shown in Example 4-4. Example 4-4 Implementing User Validation public boolean authenticateUser(String jdbcUrl, 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; } }4.6.2 Creating a Login PageThe following steps create a login.jsp page, on which users enter the login details for the schema they are going to work on:
4.6.3 Preparing Error Reports for Failed LoginsThe 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.
Before continuing with the following sections, return to the design view of the page by selecting the Design tab. 4.6.4 Creating the Login InterfaceIn these steps, you add fields to the login.jsp page on which users enter their login details.
Your login.jsp page should now appear as shown in Figure 4-17. Figure 4-17 Login Page Description of "Figure 4-17 Login Page" 4.6.5 Creating a JSP Page to Handle Login ActionIn the following steps, you create the login_action.jsp page, which is a nonviewable page that processes the login operation.
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:XE"; userIsValid = empsbean.authenticateUser(jdbcUrl, userid, password, session);%> <%if(userIsValid){%><jsp:forward page="employees.jsp"/> <%if (userIsValid){%><jsp:forward page="login.jsp"/><%}%> </body>4.7 Testing the JSP PageTo test the login page and the filtering of employees, do the following:
|