To develop a Java application that connects to Oracle Database, you need to ensure that certain components are installed as required. This chapter covers the following topics:
To be able to develop the sample application, you need to install the following products and components:
The following subsections describe these requirements in detail.
To develop the Java application, you need a working installation of Oracle Database Server with the HR schema, which comes with the database. If you choose to install the client, then you must install the Oracle Database Server before the Oracle Database Client installation. The installation creates an instance of Oracle Database and provides additional tools for managing this database. The server installation is platform-specific. For more information, refer to the Oracle Database Installation Guide.
The HR user account, which owns the sample HR schema used for the Java application in this guide, is initially locked. You must log in as a user with administrative privileges (SYS) and unlock the account before you can log in as HR.
If the database is locally installed, use the command prompt or console window to unlock the account as follows:
Log in to SQL*Plus as a user with DBA privileges, for example:
> SQLPLUS SYS/ AS SYSDBA
Enter password: password
Run the following command:
> PASSWORD HR Changing password for HR New password: password Retype new password: password
Test the connection as follows:
> CONNECT HR
Enter password: password
You should see a message indicating that you have connected to the database.
|
Note: For information on creating and using secure passwords with Oracle Database, refer to Oracle Database Security Guide. |
In addition, some of the constraints and triggers present in the HR schema are not in line with the scope of the Java application created in this guide. You must remove these constraints and triggers as follows using the following SQL statements:
DROP TRIGGER HR.UPDATE_JOB_HISTORY; DROP TRIGGER HR.ADD_JOB_HISTORY; DROP TRIGGER HR.SECURE_EMPLOYEES; ALTER TABLE EMPLOYEES DROP CONSTRAINT JHIST_EMP_FK; DELETE FROM JOB_HISTORY;
Oracle Database Client installation is optional, but recommended. Installing Oracle Database Client on any computer allows easy access from that system to the Oracle Database. The installation also includes the following development tools:
The client installation is platform-specific. Refer to the following Oracle Database Client installation guides for more information on installing the client:
To create and compile Java applications, you need the full Java 2 Platform, Standard Edition, Software Development Kit (J2SE SDK), formerly known as the Java Development Kit (JDK). To create and compile applications that access databases, you must have the full JDBC API that comes with J2SE. This download also includes the Java Runtime Environment (JRE).
|
Note:
|
|
See Also:
|
For ease in developing the application, you can choose to develop your application in an integrated development environment (IDE). This guide uses Oracle JDeveloper to create the files for this application. For more information on installing JDeveloper, refer to Installing Oracle JDeveloper.
The sample application developed in this guide uses JavaServer Pages (JSP) technology to display information and accept input from users. To deploy these pages, you need a Web server with a servlet and JSP container, such as the Apache Tomcat application server.
This guide uses the embedded server called the Oracle WebLogic Server in JDeveloper for deploying the JSP pages. If you choose not to install Oracle JDeveloper, then any Web server that allows you to deploy JSP pages should suffice.
JDeveloper supports direct deployment to the following production application servers:
For more information about these servers, please refer to vendor-specific documentation.
Oracle Database client installation is platform-specific. You need to verify that the client installation was successful before you proceed to create the sample application. This section describes the steps for verifying an Oracle Database client installation.
Verifying a client installation involves the following tasks:
Installing Oracle Java products creates the following directories:
Check if the directories described in Table 2-1 have been created and populated in the ORACLE_HOME directory.
Table 2-1 Directories and Files in the ORACLE_HOME Directory
| Directory | Description |
|---|---|
|
|
The |
|
|
This file contains late-breaking and release-specific information about the drivers, which may not have been included in other documentation on the product. |
|
|
This directory contains the |
|
Note: These files can also be obtained from the Sun Microsystems Web site. However, it is recommended to use the files supplied by Oracle, which have been tested with the Oracle drivers. |
This section describes the environment variables that must be set for the JDBC Thin Driver. You must set the classpath for your installed JDBC Thin Driver. For JDK 5, you must set the following values for the CLASSPATH variable:
ORACLE_HOME/jdbc/lib/ojdbc5.jarORACLE_HOME/jlib/orai18n.jarEnsure that there is only one JDBC class file, such as ojdbc6.jar, and one globalization classes file, orai18n.jar, in the CLASSPATH variable.
Starting from Oracle Database 11g Release 1, you can get details about the JDBC support in the database as follows:
> java -jar ojdbc6.jar Oracle 11.1.0.0. JDBC 4.0 compiled with JDK6
In addition, you can determine the version of the JDBC driver that you installed by calling the getDriverVersion method of the OracleDatabaseMetaData class.
|
Note: The JDBC Thin Driver requires a TCP/IP listener to be running on the computer where the database is installed. |
Example 2-1 illustrates how to determine the driver version:
Example 2-1 Determining the JDBC Driver Version
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
class JDBCVersion
{
public static void main (String args[]) throws SQLException
{
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:hr/hr@localhost:1521/XE");
Connection conn = ods.getConnection();
// Create Oracle DatabaseMetaData object
DatabaseMetaData meta = conn.getMetaData();
// gets driver info:
System.out.println("JDBC driver version is " + meta.getDriverVersion());
}
}
In this guide, the integrated development environment (IDE) that is used to create the sample Java application using JDBC is Oracle JDeveloper release 11.1.1. This release of JDeveloper is supported on the Microsoft Windows Vista, Windows XP, Windows 2003, Windows 2000, Linux, and Mac OS X operating systems. Installation of JDeveloper is described in detail in Installation Guide for Oracle JDeveloper Release 11.1.1.0.0, which is available online on the Oracle Technology Network at
http://download.oracle.com/docs/cd/E12839_01/install.1111/e13666/toc.htm
This guide gives a detailed description of the JDeveloper system requirements, and all the details about installing JDeveloper on the supported platforms. You should also read JDeveloper 11g Release Notes, which is available online on the Oracle Technology Network at
http://www.oracle.com/technology/products/jdev/htdocs/11/index.html
JDeveloper 11.1.1 is available in two editions. The Studio Edition includes Oracle ADF, which is required for developing the master-detail application created in this guide.
You can install either the base installation or the full installation of the JDeveloper Studio Edition. In addition to JDeveloper, the full installation includes the required version of Java, the specialized Oracle Java Virtual Machine for JDeveloper (OJVM), and the online documentation, so the download file size is larger. For quicker downloading, you can install the JDeveloper base installation.
If you are installing the base installation, you need to have J2EE version 1.6.0_05 on your machine. If you are installing the full installation, then J2EE is included. In outline, the installation process is as follows:
Download JDeveloper version 11.1.1 Studio Edition from the Oracle Technology Network at
http://www.oracle.com/technology/software/products/jdev/htdocs/soft11.html
Download the base installation (jdevjavabase11110.zip), or the full installation (jdevstudio11110install.exe ). It is recommended that you download the Studio Edition to avail all features.
To launch the installer for the base installation, enter the following command at the command line:
java -jar jdevstudio11110install.jar
To launch the installer for the full installation, double click jdevstudio11110install.exe and follow the instructions.
|
Note: When choosing the Middleware Home directory, ensure that you choose a directory that does not contain spaces. For example, do not useC:\Program Files as the Middleware Home. |
To change a JDK location that you have previously specified, you have to modify the jdev.conf file. Set the variable SetJavaHome in the file <install_dir>/jdeveloper/jdev/bin/jdev.conf to the location of your Java installation. Here, Middleware Home directory has been represented by <install_dir>.For example, in a UNIX environment, if the location of your JDK is in a directory called /usr/local/java, your entry in jdev.conf would be as follows:
SetJavaHome /usr/local/java
Other tasks that you must perform include setting the permissions for all JDeveloper files to read, and giving all users write and execute permissions to files in a range of JDeveloper directories.
If you are using the base installation, there are some additional setup tasks, such as setting the location of your Java installation in the JDeveloper configuration file, optionally installing OJVM, and downloading the online documentation so that it is locally available.
|
See Also:
|
To start JDeveloper on Windows, click on Start, select All Programs, then select Oracle Fusion Middleware and select JDeveloper Studio 11.1.1.0.0. You can also run the <install_dir>\jdeveloper\jdev\bin\jdevw.exe file. To use a console window for displaying internal diagnostic information, run the jdev.exe file in the same directory instead of jdevw.exe.
To start JDeveloper on other platforms, run the <install_dir>/jdeveloper/jdev/bin/jdev file.
While unconnecting from the database in JDeveloper is a simple task, it is not a process by itself in a Java application. In the application, you must explicitly close all ResultSet, Statement, and Connection objects after you are through using them. When you close the Connection object, you are unconnected from the database. The close methods clean up memory and release database cursors. Therefore, if you do not explicitly close ResultSet and Statement objects, serious memory leaks may occur, and you may run out of cursors in the database. You must then close the connection.
This chapter includes the following sections:
The following steps add a closeAll method to the DataHandler class:
Open DataHandler.java in the Java Source Editor by double-clicking it in the Application Navigator.
Declare the closeAll method at the end of the DataHandler class as follows:
public void closeAll() {
}
Within the method body, check whether the ResultSet object is open as follows:
if ( rset != null ) {
If it is open, close it and handle any exceptions as follows:
try { rset.close(); } catch ( Exception ex ) {}
rset = null;
}
Repeat the same actions with the Statement object.
if ( stmt != null ) {
try { stmt.close(); } catch ( Exception ex ) {}
stmt = null;
}
Finally, close the Connection object.
if ( conn != null ) {
try { conn.close(); } catch ( Exception ex ) {}
conn = null;
}
You must close the ResultSet, Statement, and Connection objects only after you have finished using them. In the DataHandler class, the insert, update, and delete methods must close these objects before returning. Note that the query methods cannot close these objects until the employees.jsp page has finished processing the rows returned by the query.
In the following steps, you add the appropriate calls to the closeAll method in the DataHandler.java file:
Open DataHandler.java in the Java Source Editor.
At the end of the addEmployee method, after the closing brace of the catch block, add the following call to the closeAll method in a finally block:
finally {
closeAll();
}
Add the same call to the addEmployeeSP, deleteEmployeeById, findEmployeeById, updateEmployee, and authenticateUser methods.
Open the employees.jsp file in the Visual Editor. Find the scriptlet inside the Employees table, and double-click to open the Insert Scriptlet dialog box.
Add the following statement after the while loop:
empsbean.closeAll();
Save your work, and compile and run the application to ensure that everything still works correctly.
2 Day + Java Developer's Guide
11g Release 2
E12137-02
February 2012
Oracle Database 2 Day + Java Developer's Guide, 11g Release 2
E12137-02
Copyright © 2007, 2012, Oracle and/or its affiliates. All rights reserved.
Primary Authors: Deepa Aswani, Rosslynne Hefferan, Maitreyee Chaliha
Contributing Authors: Kathleen Heap, Simon Law
Contributors: Kuassi Mensah, Chris Schalk, Christian Bauwens, Mark Townsend, Paul Lo, Venkatasubramaniam Iyer
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
This chapter adds functions and code to the DataHandler.java file for querying the database. This chapter has the following sections:
In outline, to query Oracle Database from a Java class to retrieve data, you must do the following:
Create a connection by using the OracleDataSource.getConnection method. This is covered in Chapter 3, "Connecting to Oracle Database".
Define your SQL statements with the methods available for the connection object. The createStatement method is used to define a SQL query statement.
Using the methods available for the statement, run your queries. You use the executeQuery method to run queries on the database and produce a set of rows that match the query conditions. These results are contained in a ResultSet object.
You use a ResultSet object to display the data in the application pages.
The following sections describe important Java Database Connectivity (JDBC) concepts related to querying the database from a Java application:
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.
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.
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.
Table 4-1 Key Query Execution Methods for java.sql.Statement
| Method Name | Return Type | Description |
|---|---|---|
|
|
Runs the given SQL statement, which returns a Boolean response: true if the query runs successfully and false if it does not. | |
|
|
|
Adds a set of parameters to a |
|
|
Submits a batch of commands to the database for running, and returns an array of update counts if all commands run successfully. | |
|
|
Runs the given SQL statement, which returns a single | |
|
|
Runs the given SQL statement, which may be an |
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.
|
See Also:
|
Scrollability 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.
Scrollability and sensitivity are independent of updatability, and the three result set types and two concurrency types combine for the following six result set categories:
Forward-only/read-only
Forward-only/updatable
Scroll-sensitive/read-only
Scroll-sensitive/updatable
Scroll-insensitive/read-only
Scroll-insensitive/updatable
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: A forward-only updatable result set has no provision for positioning at a particular row within theResultSet object. You can update rows only as you iterate through them using the next method. |
This section discusses how you can use JDeveloper to create a Java class that queries data in Oracle Database in the following sections:
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.
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;
After the connection declaration, declare variables for Statement, ResultSet, and String objects as follows:
Statement stmt; ResultSet rset; String query; String sqlString;
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{
Press Enter to include a closing brace for this method, and a new line in which to start entering the method code.
Call the getDBConnection method created earlier:
getDBConnection();
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.
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);
Run the query and retrieve the results in the ResultSet instance as follows:
rset = stmt.executeQuery(query);
Return the ResultSet object:
return rset;
Save your work. From the File menu, select Save All.
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;
}
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).
Open the DataHandler.java class in the Java Visual Editor from the Application Navigator.
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
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.
Import the ResultSet package:
import java.sql.ResultSet;
In the main method declaration, add exception handling as follows:
public static void main(String[] args) throws Exception{
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();
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));
}
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
Run the JavaClient.java file. To do this, right-click in the Java Source Editor window and select Run from the shortcut menu.
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
When you finish testing the application, set the jdbcUrl, userid and password variables in DataHandler.java back to null.
The 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:
HTML tags to design and format the dynamically generated Web page
Standard JSP tags or Java-based scriptlets to call other components that generate the dynamic content on the page
JSP tags from custom tag libraries that generate the dynamic content on the page
In this section, you will see how you can create JSP pages for the application in this guide in the following sections:
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 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.
Scriptlets 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.
HTML 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.
HTML 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.
The following steps describe how to create a simple JSP page:
In the Application Navigator, right-click the View project and choose New from the shortcut menu.
In the New Gallery, select the All Technologies tab.
Expand the Web Tier node from the Categories list and select JSP.
In the Items list, select JSP and click OK. The Create JSP Dialog box is displayed.
On the JSP File screen, enter a name for the JSP page and select JSP Page.
On the Create JSP screen, enter a name for the JSP page and click OK. 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.
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
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.
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.
With the cursor still on the heading you added, from the Design menu select Align, and then Center.
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.
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:
With employees.jsp open in the Visual Editor, click the list arrow at the top right of the Component Palette, and select CSS.
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
|
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. |
This section includes the following subsections:
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:
Open employees.jsp in the Visual Editor.
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.
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.
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
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.
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.
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
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.
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
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.
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
In the import list, select the Hierarchy tab, expand the java node, then the sql node, and then select ResultSet. Click OK.
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.
The following steps describe how you can add a table to the JSP page to display the results of the getAllEmployees query:
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.
In the Insert Table dialog box, specify 1 row and 6 columns. Leave all Layout properties as defaults. Click OK.
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.
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.)
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>");
}
Click OK.
The JSP page created is shown in Figure 4-8.
You 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:
Determining what filtered set is required
Users can specify the set of employee records that they want to view by entering a filter criterion in a query field, in this case, a part of the name that they want to search for. The employees.jsp page accepts this input through form controls, and processes it.
Creating a method to return a query ResultSet
The user input string is used to create the SQL query statement. This statement selects all employees whose names include the sequence of characters that the user enters. The query searches for this string in both the first and the last names.
Displaying the results of the query
This is done by adding code to the employees.jsp page to use the method that runs the filtered query.
This section describes filtering query data in the following sections:
The following steps describe how you can create the getEmployeesByName method. This method allows users to filter employees by their first or last name.
From the Application Navigator, open the DataHandler.java class in the Java Visual Editor.
After the getAllEmployees method, declare the getEmployeesByName method as follows:
public ResultSet getEmployeesByName(String name) throws SQLException {
}
Within the body of the method, add the following code to convert the name to uppercase to enable more search hits:
name = name.toUpperCase();
Call the method to connect to the database:
getDBConnection();
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";
Print a trace message:
System.out.println("\nExecuting query: " + query);
Run the query and return a result set as before:
rset = stmt.executeQuery(query); return rset;
Save the file and compile it to ensure there are no compilation errors.
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:
Open the JavaClient.java class in the Java Source Editor.
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));
}
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 ofuserid, password, and jdbcUrl back to null after testing. For more information, refer to Declaring Connection-Related Variables. |
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.
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:
With the employees.jsp page displayed in the Visual Editor, position the cursor between the useBean tag and the scriptlet.
In the HTML Forms page of the Component Palette, select Form.
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.
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.
Position the cursor to the left of the Text Field and add the following text:
Filter by Employee name:
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.
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
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.
Open the employees.jsp file in the Visual Editor.
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
Click OK.
Save the file.
The 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:
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.
Open the DataHandler.java class in the Source Editor.
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 {
}
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.
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;
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 usingtry and catch blocks, refer to Exception Handling in Chapter 5. |
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.
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;
}
}
The following steps create a login.jsp page, on which users enter the login details for the schema they are going to work on:
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.
Apply the JDeveloper style sheet to the page.
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.
On the next line, enter Application Login, with the Heading 3 style applied. Align this heading to the left-hand side of the page.
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.
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.
In the Insert Scriptlet dialog box, enter the following code:
String loginerrormsg = null;
loginerrormsg = (String) session.getAttribute("loginerrormsg");
if (loginerrormsg != null) {
Add another scriptlet in exactly the same way, and this time enter only a single closing brace (}) in the Insert Scriptlet dialog box.
Place the cursor between the two scriptlets and press Enter to create a new line. Apply the Heading 4 style to the new line.
With the cursor still on the new line, in the JSP page of the Component Palette, click Expression.
In the Insert Expression dialog box, enter loginerrormsg.
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.
In these steps, you add fields to the login.jsp page on which users enter their login details.
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.
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.
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.
Add a Table to the page. Position it inside the Form. Specify a 3-row and 2-column layout, and accept other layout defaults.
In the first column of the three rows, enter the following as the text to display for users:
User ID:
Password:
Host:
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.
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.
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.
In the following steps, you create the login_action.jsp page, which is a nonviewable page that processes the login operation.
Create a JSP page and call it login_action.jsp. Accept all default settings for the JSP page.
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.
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.
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 + ":1521:ORCL";
userIsValid = empsbean.authenticateUser(jdbcUrl, userid, password, session);
Add another Scriptlet, and add the following code to it:
if (userIsValid){
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.
Add another scriptlet, and enter the following code:
} else {
Add another jsp:forward tag, and this time move forward to login.jsp.
Add a final Scriptlet, and enter a closing brace (}).
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>
To test the login page and the filtering of employees, do the following:
In the Application Navigator, right-click the view project, and select Run.
You might be prompted to specify a Default Run Target for the project. For now, set this to login.jsp. You can later change the project properties for the default run target page to be any page of your choice.
The login page is displayed in your browser, as shown in Figure 4-12.
Figure 4-12 Login Page for Sample Application in the Browser
Enter the following login details for your database, and then click Submit.
User ID: hr
Password: hr
Host: Host name of the machine with Oracle Database
The Employee.java file is displayed in your browser as shown in Figure 4-13.
Figure 4-13 Unfiltered Employee Data in employee.jsp
Enter a string of letters by which you want to filter employee data. For example, enter ing in the Filter by Employee Name field, and click Filter. A filtered list is displayed, which is shown in:
Figure 4-14 Filtered Employee Data in employee.jsp
In this chapter, you will see how you can modify the sample application and add functionality that allows users to edit, update, and delete data in Oracle Database. This chapter includes the following sections:
In outline, a bean is a Java class that has properties, events and methods. For each of its properties, the bean also includes accessors, that is get and set methods. Any object that conforms to certain basic rules can be a bean. There is no special class that has to be extended to create a bean.
In the steps for creating a sample application in this chapter, a JavaBean is used to hold a single employee record. When a user wants to edit an existing record or add a new one, it is used as a container to hold the changed or new values for a single row of a table to prepare the row for using to update the database.
The bean contains properties for each field in an employee record, and then JDeveloper creates the accessors (get and set methods) for each of those properties. You will see how to create a JavaBean for the sample application in the following subsections:
Employee.java is the JavaBean that is used in the sample application to hold a single employee record and modify its contents. To create a JavaBean, do the following:
Right-click the View project, and from the shortcut menu, click New.
In the New Gallery dialog box, select the All Technologies tab.
Expand the General category and select JavaBeans in the General category. From the Items list, select Bean. Click OK.
In the Create Bean dialog box, enter Employee as the name, hr as the package, and ensure that the Extends: field is set to java.lang.Object. Click OK to create the bean.
Save the file. The Employee.java file should now contain the following code:
package hr;
public class Employee {
public Employee(){
}
}
In the JavaBean, you must create one field for each column in the Employees table, and accessor methods (get and set methods) for each field.
Add an import statement for java.sql.Date, which is the field type for one of the fields:
import java.sql.Date;
Add a field to the Employee class for each of the columns in the Employees table. Each field is private, and the field types are as follows:
private Integer employeeId; private String firstName; private String lastName; private String email; private String phoneNumber; private Date hireDate; private String jobId; private Double salary; private Double commissionPct; private Integer departmentId;
Right-click on the Source Editor page and select Generate Accessors from the shortcut menu. In the Generate Accessors dialog box, select the top-level Employee node. A check mark is displayed for that node and for all the fields. Click OK. Figure 5-1 shows the Generate Accessors dialog box with all the fields selected.
Save the file. The Employee.java file should now contain the following code:
Example 5-1 Skeleton Code for a Basic Java Bean with Accessor Methods
package hr;
import java.sql.Date;
public class Employee {
public Employee() {
}
private Integer employeeId;
private String firstName;
private String lastName;
private String email;
private String phoneNumber;
private Date hireDate;
private String jobId;
private Double salary;
private Double commissionPct;
private Integer departmentId;
public void setEmployeeId(Integer employeeId) {
this.employeeId = employeeId;
}
public Integer getEmployeeId() {
return employeeId;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getFirstName() {
return firstName;
}
...
...
...
...
// This list has been shortened and is not comprehensive. The actual code contains // accessor methods for all the fields declared in the bean.
public void setDepartmentId(Integer departmentId) {
this.departmentId = departmentId;
}
public Integer getDepartmentId() {
return departmentId;
}
}
Updating a row in a database table from a Java application requires you to do the following tasks:
Create a method that finds a particular employee row. This is used to display the values for a particular employee on an edit page.
Create a method that takes the updated employee data from the bean and updates the database.
On the main application page, in every row of employee data, include a link that allows a user to edit the data for that employee. The links take the user to the edit.jsp file with the data for that employee displayed, ready for editing.
Create a JSP page called edit.jsp, that includes a form and a table to display all the data of a single employee and allows a user to change the values.
Create a JSP page that processes the form on the edit.jsp page, writes the updated values to the Employee.java bean and calls the updateEmployee method.
You will see how to do this in the following sections:
The method you create in these steps is used to find the record for a particular employee. It is used when a user wants to edit or delete a particular employee record, and selects a link for that employee on the Employee.java page.
If the DataHandler class is not already open in the Java Source Editor, double-click it in the Application Navigator to open it.
In the DataHandler class, declare a new method that identifies the employee record to be updated:
public Employee findEmployeeById(int id) throws SQLException {
}
Within the body of this method, create a new instance of the Employee bean called selectedEmp.
Employee selectedEmp = new Employee();
Connect to the database.
getDBConnection();
Create a Statement object, define a ResultSet type, and formulate the query. Add a trace message to assist with debugging.
stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
query = "SELECT * FROM Employees WHERE employee_id = " + id;
System.out.println("\nExecuting: " + query);
Run the query and use a ResultSet object to contain the result.
rset = stmt.executeQuery(query);
Use the result set returned in rset to populate the fields of the employee bean using the set methods of the bean.
while (rset.next()) {
selectedEmp.setEmployeeId(new Integer(rset.getInt("employee_id")));
selectedEmp.setFirstName(rset.getString("first_name"));
selectedEmp.setLastName(rset.getString("last_name"));
selectedEmp.setEmail(rset.getString("email"));
selectedEmp.setPhoneNumber(rset.getString("phone_number"));
selectedEmp.setHireDate(rset.getDate("hire_date"));
selectedEmp.setSalary(new Double(rset.getDouble("salary")));
selectedEmp.setJobId(rset.getString("job_id"));
}
Return the populated object.
return selectedEmp;
In the following steps, you will see how to create a method to update employee data in the database:
Open the DataHandler class.
Declare an updateEmployee method as follows:
public String updateEmployee(int employee_id, String first_name,
String last_name, String email,
String phone_number, String salary,
String job_id) throws SQLException {
}
Within the body of this method, create an instance of the Employee bean, containing details for the selected employee:
Employee oldEmployee = findEmployeeById(employee_id);
Connect to the database.
getDBConnection();
Create a Statement object and specify the ResultSet type as before.
stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
Create a StringBuffer to accumulate details of the SQL UPDATE statement that needs to be built:
StringBuffer columns = new StringBuffer( 255 );
For each field in an employee record, check whether the user has changed the value and if so, add relevant code to the StringBuffer. For each item added after the first one, add a comma to separate the items. The following code checks if the first_name variable changed, and if so, adds details to the SQL in the StringBuffer that will be used to update the database:
if ( first_name != null &&
!first_name.equals(oldEmployee.getFirstName() ) )
{
columns.append( "first_name = '" + first_name + "'" );
}
For the last_name, before appending the new last name, check to see whether there are already some changes in the StringBuffer and if so, append a comma to separate the new change from the previous one. Use the following code:
if ( last_name != null &&
!last_name.equals(oldEmployee.getLastName() ) ) {
if ( columns.length() > 0 ) {
columns.append( ", " );
}
columns.append( "last_name = '" + last_name + "'" );
}
Use the samecode logic to check for changes made to email, and phone_number.
|
Note: Only significant parts of the code are included within this procedure. Example 5-2 contains the complete code for this method. |
For the salary field, obtain a String value to add to the StringBuffer as follows:
if ( salary != null &&
!salary.equals( oldEmployee.getSalary().toString() ) ) {
if ( columns.length() > 0 ) {
columns.append( ", " );
}
columns.append( "salary = '" + salary + "'" );
When the whole set of changes has been assembled, check to see whether there are in fact any changes, that is, whether the StringBuffer contains anything. If so, construct a SQL UPDATE statement using the information in the StringBuffer and execute it. If the StringBuffer does not contain any changes, output a message saying so:
if ( columns.length() > 0 )
{
sqlString = "update Employees SET " + columns.toString() +
" WHERE employee_id = " + employee_id;
System.out.println("\nExecuting: " + sqlString);
stmt.execute(sqlString);
}
else
{
System.out.println( "Nothing to do to update Employee Id: " +
employee_id);
}
Return the word "success".
return "success";
Save your work and make the file to check there are no syntax errors.
Example 5-2 contains the complete code for this method.
Example 5-2 Method for Updating a Database Record
public String updateEmployee(int employee_id, String first_name,
String last_name, String email,
String phone_number, String salary,
String job_id) throws SQLException {
Employee oldEmployee = findEmployeeById(employee_id);
getDBConnection();
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
StringBuffer columns = new StringBuffer( 255 );
if ( first_name != null &&
!first_name.equals( oldEmployee.getFirstName() ) )
{
columns.append( "first_name = '" + first_name + "'" );
}
if ( last_name != null &&
!last_name.equals( oldEmployee.getLastName() ) ) {
if ( columns.length() > 0 ) {
columns.append( ", " );
}
columns.append( "last_name = '" + last_name + "'" );
}
if ( email != null &&
!email.equals( oldEmployee.getEmail() ) ) {
if ( columns.length() > 0 ) {
columns.append( ", " );
}
columns.append( "email = '" + email + "'" );
}
if ( phone_number != null &&
!phone_number.equals( oldEmployee.getPhoneNumber() ) ) {
if ( columns.length() > 0 ) {
columns.append( ", " );
}
columns.append( "phone_number = '" + phone_number + "'" );
}
if ( salary != null &&
!salary.equals( oldEmployee.getSalary().toString() ) ) {
if ( columns.length() > 0 ) {
columns.append( ", " );
}
columns.append( "salary = '" + salary + "'" );
}
if ( job_id != null &&
!job_id.equals( oldEmployee.getJobId() ) ) {
if ( columns.length() > 0 ) {
columns.append( ", " );
}
columns.append( "job_id = '" + job_id + "'" );
}
if ( columns.length() > 0 )
{
sqlString =
"UPDATE Employees SET " + columns.toString() +
" WHERE employee_id = " + employee_id;
System.out.println("\nExecuting: " + sqlString);
stmt.execute(sqlString);
}
else
{
System.out.println( "Nothing to do to update Employee Id: " +
employee_id);
}
return "success";
}
In the following steps, you add a link to each row of the employees table on the employees.jsp page, that users will click to edit that row.
Open employees.jsp in the Visual Editor.
Add an extra column to the table that displays employee details. To do this, position the cursor in the last column of the table, right-click and select Table from the shortcut menu, then select Insert Rows Or Columns. In the Insert Rows or Columns dialog box, select Columns and After Selection and click OK.
This extra column will contain the link that reads Edit for each row. Each of these links leads to a separate page where the selected employee record can be edited. To do this, double-click the scriptlet that is inside the Employees table, to display the Scriptlet Properties dialog box.
Modify the scriptlet to include a link to the edit.jsp page. The modified scriptlet should contain the following 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><td> <a href=\"edit.jsp?empid=" + rset.getInt(1) +
"\">Edit</a></td>");
out.println("<tr>");
}
When the edit link is clicked for any employee, this code passes the employee ID to the edit.jsp page, which will handle the employee record updates. The edit.jsp page will use this to search for the record of that particular employee in the database.
Save employees.jsp. Figure 5-2 shows employees.jsp when it is run and displayed in a browser, illustrating the link users can click to edit employee data.
Figure 5-2 Link to Edit Employees in employees.jsp
In this section, you will create the edit.jsp file that allows users to update an employee record.
Create a new JSP page and name it edit.jsp. Accept all other defaults.
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.
On the next line, type Edit Employee Record, with the Heading 3 style applied. Align this heading to the left of the page.
Add the JDeveloper style sheet to the page.
Add a jsp:usebean tag. Enter empsbean as the ID, and hr.DataHandler as the Class. Set the Scope to session, and click OK.
Position the cursor after the useBean tag and add another jsp:usebean tag. This time enter employee as the ID, browse to select hr.Employee as the class, and leave the Scope as page. Click OK.
Add a Scriptlet to the page. The scriptlet code passes the employee ID to the findEmployeeById method and retrieves the data inside the Employee bean. Enter the following code in the Insert Scriptlet dialog box:
Integer employee_id = new Integer(request.getParameter("empid"));
employee = empsbean.findEmployeeById(employee_id.intValue());
Add a Form to the page. In the Insert Form dialog, enter update_action.jsp for the Action field. You cannot select this page from the drop down list as you have not yet created it.
Add a Table to the page. Position it inside the Form. Specify a 6-row and 2-column layout, and accept other layout defaults.
In the first column, enter the following headings, each on a separate row: First Name, Last Name, Email, Phone, Job, Monthly Salary.
Drag a Hidden Field component from the HTML Forms page of the Component Palette. Drop it in the second column, adjacent to the First Name heading. In the Insert Hidden Field dialog, enter employee_id as the Name property and enter <%= employee.getEmployeeId() %> as the Value property.
Drag a Text Field component to this column, adjacent to the First Name heading. In the Insert Text Field dialog, enter first_name in the Name field, and <%= employee.getFirstName() %> in the Value field. Click OK.
Drag a second Text Field component to this column, adjacent to the Last Name heading. In the Insert Text Field dialog, enter last_name in the Name field, and <%= employee.getLastName() %> in the Value field. Click OK.
In a similar way, add text fields adjacent to each of the remaining column headings, using email, phone_number, job_id, and salary as the field names and the corresponding getter method for each field. These are specified in the following table.
Add a Submit button in the form, below the table. Enter Update as its Value.
Save the application.
The resultant edit.jsp page should look similar to the page shown in Figure 5-3.
Figure 5-3 Creating a JSP Page to Edit Employee Details
In this section, you will see how to create the update_action.jsp file. This page processes the form on the edit.jsp page that allows users to update an employee record. There are no visual elements on this page, this page is used only to process the edit.jsp form and returns control to the employees.jsp file.
Create a new JSP page and call it update_action.jsp. Accept all other defaults for the page in the JSP Creation Wizard.
Drag a Page Directive component from the JSP page of the Component Palette onto the page. In the Insert Page Directive dialog box, browse to import java.sql.ResultSet. Click OK.
Add a jsp:usebean tag. Enter empsbean as the ID, and hr.DataHandler as the Class. Set the Scope to session, and click OK.
Add a Scriptlet to the page. Enter the following code into the Insert Scriptlet dialog box:
Integer employee_id = new Integer(request.getParameter("employee_id"));
String first_name = request.getParameter("first_name");
String last_name = request.getParameter("last_name");
String email = request.getParameter("email");
String phone_number = request.getParameter("phone_number");
String salary = request.getParameter("salary");
String job_id = request.getParameter("job_id");
empsbean.updateEmployee(employee_id.intValue(), first_name, last_name, email, phone_number, salary, job_id );
Drag a jsp:forward tag onto the page. In the Insert Forward dialog box, enter employees.jsp for the Page property.
Save your work.
Run the project and test whether you can edit an employee record. Click Edit for any employee on the employees.jsp page, and you should be directed to the page shown in Figure 5-4. Modify any of the employee details and check whether the change reflects in the employees.jsp page.
The steps for inserting a new employee record to the Employees table are similar to the process for updating an employee record:
Create a method to insert a new employee row into the Employees table.
Add a link to the main application page, allowing a user to click to insert a new employee. The link takes the user to an insert.jsp with an empty form ready for the user to enter details for the new row.
Create a JSP page to process the form on the insert.jsp page.
Create a JSP page with form controls for users to enter the values for the new employee.
This section covers the creation of Java application code for inserting new employee data in the following subsections:
In the following steps, you will create a method for inserting a new employee record.
Open DataHandler.java in the Java Source Editor.
Declare a method to add a new employee record.
public String addEmployee(String first_name,
String last_name, String email,
String phone_number, String job_id, int salary) throws SQLException {
}
Add a line to connect to the database.
getDBConnection();
Create a Statement object, define a ResultSet type as before, and formulate the SQL statement.
stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
sqlStriy ng =
"INSERT INTO Employees VALUES (EMPLOYEES_SEQ.nextval, '" +
first_name + "','" +
last_name + "','" +
email + "','" +
phone_number + "'," +
"SYSDATE, '" +
job_id + "', " +
salary + ",.30,100,80)";
|
Note: The last three columns (Commission, ManagerId, and DepartmentId) contain hard-coded values for the sample application. |
Add a trace message, and then run the SQL statement.
Return a message that says "success" if the insertion was successful.
Make the file to check for syntax errors.
Example 5-3 shows the code for the addEmployee() method.
Example 5-3 Method for Adding a New Employee Record
public String addEmployee(String first_name,
String last_name, String email,
String phone_number, String job_id, int salary) throws SQLException {
getDBConnection();
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
sqlString =
"INSERT INTO Employees VALUES (EMPLOYEES_SEQ.nextval, '" +
first_name + "','" +
last_name + "','" +
email + "','" +
phone_number + "'," +
"SYSDATE, '" +
job_id + "', " +
salary + ",.30,100,80)";
System.out.println("\nInserting: " + sqlString);
stmt.execute(sqlString);
return "success";
}
In these steps, you add a link to the header row of the employees table that users can click to add a new employee.
Open employees.jsp in the Visual Editor.
Drag a Hyper Link component from the HTML Common page of the Component Palette into the empty column header cell at the end of the header row. In the Insert HyperLink dialog box, enter insert.jsp in the HyperLink field, and Insert Employee in the Text field. You cannot browse to find insert.jsp as you have not yet created it. Click OK.
Save employees.jsp.
In these steps, you create the insert.jsp page, which allows users to enter details of a new employee record.
Create a new JSP page and call it insert.jsp.
Give the page the same heading as before, AnyCo Corporation: HR Application, and format it as Heading 2, and center it.
On the next line enter Insert Employee Record, and apply the Heading 3 format. Align this heading to the left of the page.
Add the JDeveloper stylesheet to the page.
Add a Form. In the Insert Form dialog box, enter insert_action.jsp for the Action property, and click OK.
Add a Table inside the Form. Specify that you want 6 rows and 2 columns and accept all other layout defaults.
In the first column, enter the following headings, each on a separate row: First Name, Last Name, Email, Phone, Job, Monthly Salary.
Drag and drop a Text Field into the column to the right of the First Name header. In the Insert Field dialog box, type first_name in the Name property.
Drag a Text Field next to each of the Last Name, Email, Phone, and Monthly Salary headers. Specify the values for each of these text fields for the Name property in the Insert Field dialog box. The values are indicated in the following table:
| Text Field For | Set the Name Property To |
|---|---|
| Last Name | last_name |
email | |
| Phone | phone_number |
| Monthly Salary | salary |
This procedure is different for the Job row.
Drag a Combo Box component from the HTML Forms page of the Component Palette to the column next to the Job heading.
In the Insert Select dialog box, enter job_id as the name, and 1 as the size. Click on the add (+) icon and enter SA_REP in the Value field, and in the Caption field, enter Sales Representative. Click on the add(+) sign to add each of the following job titles, then click OK.
| Value | Caption |
|---|---|
HR_REP | HR Representative |
PR_REP | PR Representative |
MK_MAN | Marketing Manager |
SA_MAN | Sales Manager |
FI_MAN | Finance Manager |
IT_PROG | Software Developer |
AD_VIP | Vice President |
Drag a Submit button to the Form below the table. In the Insert Submit Button dialog box, enter Add Employee for the Value property.
Save your work.
Figure 5-5 shows the insert.jsp page in the Visual Editor.
In these steps, you create the insert_action.jsp page. This is a page that processes the form input from insert.jsp, which is the page on which users enter a new employee record. There are no visual elements on this page, and it is only used to process the insert.jsp form and return control to the employees.jsp file.
Add a jsp:usebean tag. As before, enter empsbean as the ID, and hr.DataHandler as the Class. Set the Scope to session, and click OK.
Position the cursor after the useBean tag and add a Scriptlet to the page. Enter the following code into the Insert Scriptlet dialog box:
String first_name = request.getParameter("first_name");
String last_name = request.getParameter("last_name");
String email = request.getParameter("email");
String phone_number = request.getParameter("phone_number");
String job_id = request.getParameter("job_id");
Integer salary = new Integer(request.getParameter("salary"));
empsbean.addEmployee(first_name, last_name, email, phone_number, job_id, salary.intValue());
Drag a jsp:forward tag onto the page. In the Insert Forward dialog box, enter employees.jsp.
Save your work.
Run the View project to test whether you can insert a new employee record.
To insert an employee, click Insert Employee on the employees.jsp page shown in Figure 5-6.
Figure 5-7 shows the page where you can insert new employee data with some data filled in, and the list of jobs being used to select a job.
The steps for deleting a record are similar to those for editing and inserting a record:
Use the method created in Creating a Method to Identify an Employee Record to identify a particular employee row. This is used to identify the row to be deleted.
Create a method that deletes an employee record from the database.
Add a link to the main application page for each row, allowing a user to click to delete the employee in that row. The link takes the user to a delete_action.jsp, with the ID of the employee whose record is to be deleted.
To delete the employee from the database, create a JSP page to call the delete method created in Step 2.
This section discusses the following tasks related to deleting employee data:
The method created in the following steps is used to delete employee records by ID:
Declare a new method that identifies the employee record to be deleted:
public String deleteEmployeeById(int id) throws SQLException {
}
Connect to the database as before.
getDBConnection();
Create a Statement object, define a ResultSet type as before, and formulate the SQL statement. Add a trace message to assist with debugging.
stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
sqlString = "DELETE FROM Employees WHERE employee_id = " + id;
System.out.println("\nExecuting: " + sqlString);
Run the SQL statement.
stmt.execute(sqlString);
If the SQL statement runs without any errors, return the word, Success.
return "success";
Example 5-4 shows the code for the deleteEmployeeById() method.
Example 5-4 Method for Deleting an Employee Record
public String deleteEmployeeById(int id) throws SQLException {
getDBConnection();
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
sqlString = "DELETE FROM Employees WHERE employee_id = " + id;
System.out.println("\nExecuting: " + sqlString);
stmt.execute(sqlString);
return "success";
}
In the following instructions, you add a link to each row of the employees table on the employees.jsp page. Clicking on that link will delete all employee data for that row.
Open employees.jsp in the Visual Editor.
In the column you created to contain the Edit link, add another link for deleting the row. To do this, double-click the scriptlet that is inside the Employees table, to display the Scriptlet Properties dialog box.
Modify the scriptlet to include a link to a delete_action.jsp page. The modified scriptlet should contain the following 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><td> <a href=\"edit.jsp?empid=" + rset.getInt(1) +
"\">Edit</a> <a href=\"delete_action.jsp?empid=" +
rset.getInt(1) + "\">Delete</a></td>");
out.println("<tr>");
}
Save employees.jsp.
In the following steps, you create the delete_action.jsp page, which is a page that only processes the delete operation. There are no visual elements on this page.
Add a jsp:usebean tag. As before, enter empsbean as the ID, and hr.DataHandler as the Class. Set the Scope to session, and click OK.
Add a Scriptlet to the page. Enter the following code into the Insert Scriptlet dialog box:
Integer employee_id =
new Integer(request.getParameter("empid"));
empsbean.deleteEmployeeById(employee_id.intValue());
Drag Forward from the Component Palette to add a jsp:forward tag to the page. In the Insert Forward dialog box, enter employees.jsp.
Save your work.
Run the project and try deleting an employee. Figure 5-8 shows the links for deleting employee records from the employees.jsp.
Figure 5-8 Link for Deleting an Employee from employees.jsp
If you click Delete for any of the employee records, then that employee record will be deleted.
A SQLException object instance provides information on a database access error or other errors. Each SQLException instance provides many types of information, including a string describing the error, which is used as the Java Exception message, available via the getMessage method.
The sample application uses try and catch blocks, which are the Java mechanism for handling exceptions. With Java, if a method throws an exception, there needs to be a mechanism to handle it. Generally, a catch block catches the exception and specifies the course of action in the event of an exception, which could simply be to display the message.
Each JDBC method throws a SQLException if a database access error occurs. For this reason, any method in an application that executes such a method must handle the exception.
All the methods in the sample application include code for handling exceptions. For example, the getDBConnection, which is used to get a connection to the database, throws SQLException, as does the getAllEmployees method as follows:
public ResultSet getAllEmployees() throws SQLException {
}
For an example of code used to catch and handle SQLExceptions, refer to the code in the authenticateUser method in the DataHandler.java class. In this example, a try block contains the code for the work to be done to authenticate a user, and a catch block handles the case where the authentication fails.The following sections describe how to add code to the sample application to catch and handle SQLExceptions.
To handle SQL exceptions in the methods in the sample application, do the following:
Ensure that the method throws SQLException. For example, the method:
public ResultSet getAllEmployees() throws SQLException
Use try and catch blocks to catch any SQLExceptions. For example, in the getAllEmployees method, enclose your existing code in a try block, and add a catch block as follows:
public ResultSet getAllEmployees() throws SQLException {
try {
getDBConnection();
stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
sqlString = "SELECT * FROM Employees order by employee_id";
System.out.println("\nExecuting: " + sqlString);
rset = stmt.executeQuery(sqlString);
}
catch (SQLException e) {
e.printStackTrace();
}
return rset;
}
As another example, the deleteEmployee method rewritten to use try and catch blocks would return "success" only if the method was successful, that is, the return statement is enclosed in the try block. The code could be as follows:
public String deleteEmployeeById(int id) throws SQLException {
try {
getDBConnection();
stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
sqlString = "delete FROM Employees where employee_id = " + id;
System.out.println("\nExecuting: " + sqlString);
stmt.execute(sqlString);
return "success";
}
catch (SQLException e) {
e.printStackTrace();
}
}
As a refinement to the code for the sample application, you can create a method that can be used in any method that might throw a SQLException, to handle the exception. As an example, the following method could be called in the catch block of any of the methods in the sample application. This method cycles through all the exceptions that have accumulated, printing a stack trace for each.
Example 5-5 Adding a Method to Handle Any SQLException in the Application
public void logException( SQLException ex )
{
while ( ex != null ) {
ex.printStackTrace();
ex = ex.getNextException();
}
}
In addition, in the catch block, you can return text that explains why the method has failed. The catch block of a method could therefore be written as follows:
catch ( SQLException ex ) {
logException( ex );
return "failure";
}
To add this feature to your application:
Edit each of the methods to include try and catch blocks.
In the catch block of each method, run the logException method.
For methods that have a return value of String, include a return statement to return a message indicating that the method has failed such as:
return "failure";
The web.xml file is the deployment descriptor file for a web application. One section of the web.xml file can be used for defining a start page for the application, for example:
<web-app>
...
<welcome-file>
myWelcomeFile.jsp
</welcome-file>
...
</web-app>
If you do not define a welcome page in your web.xml file, generally a file with the name index, with extension .html, .htm, or .jsp if there is one, is used as the starting page. With JDeveloper, you can define which page is to be the default run target for the application, that is, the page of the application that is displayed first, by defining it in the properties of the project.
Once the application has started, and the start page has been displayed, navigation through the application is achieved using the following scheme:
Links, in the form of HTML anchor tags, define a target for the link, usually identifying another JSP page to which to navigate, and some text for the link.
HTML submit buttons, are used to submit forms on the pages, such as forms for entering new or changed data.
jsp:forward tags, which are executed on JSP pages that handle queries and forms, to forward to either the same JSP page again, or another JSP page.
In the following steps, you create the index.jsp page, which will be the default starting page for the application. The page does not include any display elements, and simply forwards the user to the application login page, login.jsp. To do this you use the jsp:forward tag. A jsp:forward tag runs on JSP pages that handle queries and forms, to forward to either the same JSP page again, or another JSP page.
Create a new JSP page and call it index.jsp.
For the sample application, we will not add any text to this page. From the JSP page of the Component Palette, drag Forward to include a jsp:forward tag in the page.
In the Insert Forward dialog box for the forward tag, enter login.jsp as the Page.
You can now specify this new page as the default target for the application as follows:
In the Application Navigator, right-click the View project and choose Project Properties.
In the displayed tree, select Run/Debug/Profile. In the Run/Debug/Profile area, ensure that Use Project Settings is selected, and in the Run Configurations area, ensure that Default Configurations is selected. Click Edit.
In the Edit Launch Settings dialog box, select Launch Settings. In the Launch Settings area on the right, click Browse next to the Default Run Target field and navigate to find the new index.jsp page you just created and click OK. Then click OK again to close the dialog box.
You can now run your application by right-clicking in the View project and select Run from the shortcut menu. The application runs and runs index.jsp, which has been set as the default launch target for the application. The index.jsp forwards you directly to the login page, login.jsp, which is displayed in your browser.
This Preface introduces you to Oracle Database 2 Day + Java Developer's Guide, by discussing the intended audience and conventions of this document. It also includes a list of related Oracle documents that you can refer to for more information.
This guide is intended for application developers using Java to access and modify data in Oracle Database. This guide illustrates how to perform these tasks using a simple Java Database Connectivity (JDBC) application. This guide uses the Oracle JDeveloper integrated development environment (IDE) to create the application. This guide can be read by anyone with an interest in Java programming, but it assumes at least some prior knowledge of the following:
Java
Oracle PL/SQL
Oracle databases
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.
Accessibility of Links to External Web Sites in Documentation
This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.
Deaf/Hard of Hearing Access to Oracle Support Services
To reach Oracle Support Services, use a telecommunications relay service (TRS) to call Oracle Support at 1.800.223.1711. An Oracle Support Services engineer will handle technical issues and provide customer support according to the Oracle service request process. Information about TRS is available at http://www.fcc.gov/cgb/consumerfacts/trs.html, and a list of phone numbers is available at http://www.fcc.gov/cgb/dro/trsphonebk.html.
For more information, see the following documents in the Oracle Database documentation set:
Oracle® Fusion Middleware Installation Guide for Oracle JDeveloper, 11g Release 1 (11.1.1) and JDeveloper Online Documentation on Oracle Technology Network at
http://www.oracle.com/technetwork/developer-tools/jdev/documentation/index.html
Oracle Database JDBC Developer's Guide and Reference, 11g Release 2 (11.2)
Oracle Database Java Developer's Guide, 11g Release 2 (11.2)
The following text conventions are used in this document:
| Convention | Meaning |
|---|---|
| boldface | Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary. |
| italic | Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values. |
monospace | Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter. |
tcoGN%Q
ǪƗО 0C`jd5 5/Bcv%n+@:R +P 6~a,x? j C.D~FEHJPRT U>VXWZ\b df>g^hl
Ij-+paYj bp
;6M6Thr窈THD[s5[01hii ^^~ꨞꪾ >^~n : .r0^ =A=j>Bɕnr @Ю>PȮn~~ { tٛ) \I}^ex~YٞO\IO
?X<_ "?$oip|U!0. ) _q+ ]v{,KdY^ @ NҞAn lP@enX,lnp ri%g0~ ?_Oi,0{9!p%C.T4bfl? 1tcp'Th &r}Nh0T8s_ȟ_젨2p_؟ڿ ݿap6'<pPloE6 ,6},69hh6C,C%9h6 56}j 3sC3h~i~i3joo%,!t!!a
(
L3:1P
cA
5 ڀ!4@G?ghpQ64t` D
3 2Vph 48!j1)]ʴӧPJ͕ǴjMs' 4hzKٳhӪ]˶۷pe#>} %@6^h(Ј?kæFKhц"I͉c 5!-5\0c6phT&I: o*o]Z !XKNv/ 7<
f 5l
AMHqԤH@4b~ j`r!
6 ]p@eX݆ vᇺGq$X-Xń.,e]Θ#t2H#0phQ]襀3DQfAu a"@DB/Fjh,I Hm4 HTt FP&B sH␈&ĤU3r]Y (Rla x p@rG'ǥ h . r!Dp!+BA j"x\Vk[Ff -(F&('
`y.9t0 RmP `GG"FG3 b ZPKf lG -jG `. $ï{@ jP! z!m $P *ƞ;hT p\D yW0}C-u{ru~GYr03s:V !j_NG#r'|ȣS#9g N8ֶ߹BgMsU'һИ_i̽g|;, Ȭ/; Rox
h o4v' z`
k `'1|C!W=N g V ޥ,{:7QWS@hQ 51+@6)hPձ"zq6܄ Ie]{ت&eRZҀ}"j80rO@X37,O9+! IZ$)+]&=xE.X"&f2بɴm|=s dr['=Nj,[ #V:kbh院qD^Lbj6͏.e 8HϘ svȚ5S[\,G QÚt6
:IP
SdYO)ER@&БM
>PO8'0:IOԏ0>EzN)| 64hX M) zHJ|ab &L9PTJժZXͪVծz`
UߠRr
ₒ<ĩ5p\J5LF%PSⲔ`KMb 'AZ >Ab(iQ7oa P\ʉLd $]jWֺ='-tU>
] @ ;PK42{3 3 PK .A OEBPS/img/usebeanrep.gif GIF89aXh =6UVr lۥآ^1hК 4MVtJם@@ (P ̡l ʉ xsHI5yB0N}0Aͮ6EY Ƞv eF4J E(
p^ wl,(7pHEZ@avܮ 7 dT@r p>cTp'' ./!h0.&c)Y?0~U %_ɈuPqPxQS D r}5h0@ J 5]7dz0z0 9k%pj?fVI`dPd_&r~ h
ipTLT0phh~h?
#N
4NN4
? &h~
hqhw"t i"2
wW]723
s@`//2B:@ m&iÇФ @!ҭ[ 0C$* ?tD02`@ i
;_ \x4oLt" LJ 0(:0Ca pT`$B0Z@pm*C!i&#TÄ0a u 7R܂3nX#"dPd) "R \\#0.7\D9!"D ~h'S 0H1aD YHq>}daF 5ë "Q`]`)dWBz 4^6~8:<0(Bn?̸G>HCP.?~Xv};6n;S-e?ıDk I )P gp>cV|VlP)lW :TA@o/Ŏ +^DŖ难PU )h U b G `/m XQ}0XQ='#7?%9;rar >^~P)eZ)
I`h `@6pF$
p PRd2 2M`` 3 Tl@SR ފا 2?4_68.J&`V Z .cI `@9$ g
w-(q/PSCw->fi8cvj1"?x0tV- ٮxz|~ov; :s qoc>m&& 5 y}R}oNk ;C#0!u,50 ?_>?W0F S"lUoXOt5t8X }RbAS9$ ! 0s ̿r ~h~&" np% ksI `9\IIT ~2//'sB=%h
B &
2/B2
ijjj~mq`0!B !xhaME4!$9-jH(c9TӺRDhê4;`J5 @ ON8@AC4 95hF(4 Bj
Kׯ`ÊUG
ӡC(TC 8p$:us ;#Gq yb ۹Xa[< (^=gAA|c9lA1 §l~#gCj؝<|-0aկ d6QoŇ ܚ4߱g}ZPAu'*KDG[0E)
)l`pH)|8ֈ$h:iTQ ,bT@E
B} f
ZepQgs wM6[mp1V_b=cjF 6[d fxjDesǣ 5QrTnjWa doЦ Am
x::RR!J
2 P"S
TቴjlU p 0 `zi%'cs6C6eRy^V']qDmI)JțvfX!Z]m [uGU mJ(藜n-Ʀl xmV!QG$j*n*E<48+0,;ilai utЍ } p5 P`4`;k_ `gT /EDIeZUpZ([@ #fyBQqcJCe'& ~٣?۳4+
";Y%q X 1#3YS E`2` m+`%z
d[ ǩkVA@{˸MpVи+{ @J;빉K,@K{A > ڶ@|0f4=G E zG l`
dRp&{wy0pHA'P4B=(al;[ۿ *@gY$@8BB 0 E x oY*X9~`o D 3,
`678m0, gьG« ZhCR!D5G<1 &1)S!^ >!qRh
0d %>
1
C/' PUl@n%9:k3DQRh
h+pp|f hxZ A0=хCqpnnәlVщtm V$i0_gn^B&^D?<40ʥe[+Yr`g04dgahBA\iq j% h~
h~
}hAii
him ~ /jh
lp} p
%lmh%l pil% i}ph9 9} q}ntp}ƨhim9 2
9
li Xw¯iJh#
J52Eŋ3jȱǏ C< YR ":i!4{ΜF#PM ؈pXJsӒ4Y&1CbN*Ȓ 5 J d[p T`A P SfOrAZl2Xq<%PC'' p 2ێB,| |a"" 6./S7\"
xP@ܽ 4kwG r&pN;S#X x !e*]5)К̇|]0p ;iaAF?6'*. [
.`x ,N"(Ԟ 2u< nЅK^ M4 vEB<]@ O piIM+08.4%8 |ᴄW> 4nwx
z P% @hV4B-톊$Ҋ<DiM2dG Uʯ 0C5I`8m=I.r VLE8 se00#`dΔ-ua/G.il;'Tv1s0fbH/ a)|CxrnK |)sRh,臒D'JQ_r;s%?1҅1dGMIblt%H!Qs M'9idf m wғ|`K :c CJɣ37!zœ2RG'<2ypi9ZѲhFN|p\ַtu+^*Ws]:Xկ+b[5b#Kټ6l[ٿf
hGKҚMjWK^lgKͭnw
pKMr:Ѝt; p#vz
xKMz|Kͯ~j UOZLN!)X@
@as ܧA' 23!P1oV4c6M /V\?fi=<BYB ;$bWI0 k\Nw LP;<OMZ<<+
p |ƀ/1]W>w#0-/M` h:X0l}nk hƟ?k~"ib"3 a#~#R | (Ұ!4 ܠ' P+ H# &ù٤#`mxڂ b!. ZLe^
Z=6Yq "E E0̲ ,
& =#J0 ZpwD33A3^2x2 ,kA`rLHI8DTp q= Gek A_0oHȺ Yڟ w3S= P!}wv56!""3Z |3((P0
NZ1 _x-| p0 ص-2P(RUf]4 3*H
>)BNt0 'a# < DZ=S)o/'~SG
'D7 44> , Y o
\ , q{, 17,Y#|V _| ,
, s'QZ
T< w^
},_^03h4
.SGq>:H 4@#>X2ʐvи07QZA7.ȗ4q3A Š xE K 0 GD64$lY
ΪH! 4Xb&+nB\:@ٳ.QEC
X nHA#8` ,88
Wr9fcFt1(0d /4d ,L` X.pd"(pȁ dsqvT(c ||P8 5A@
@E 0 Ila?@
C /m`P h\ lNj00"8^'@G Y@
( P@
HGl p@"C a ]@]yD\ #9B p VPuYIF*i/ d{v)!2*{#0" @xA(["sLXLG |@ \tj- 94A Lp_D1S6 6pq jaIw :4 AH*6/|| _|G #d c 50B=aVQ I1GQ 00
m |~ q\ !apC^D x9gT0gX#@ XA#mZv4aGCxgxQAr +|X@
|
}X̝|QPQ_IOk4
dk P} O۱ tKY@"`=90c=qiW@D%H 74j 8p`0 "$& SJ6 JZ, A^ /, P-` f 1PZHP * ) vk;Pny{ p)sGoo{@ap?3^O ?& !<'A H$ +Xtph |!:5 xiLu+ T#/~qq7 Vd |MU ,
0!@"VSg=$N;\ Z!eĞ qjuP(aCTj"1 p;,05 !x Y@ { X7,0";3
Y D/ >F+ъ~aJ[ҘtYb0*ܠOŞ" HWT Wg Wccָ5o[ M"9o)rFR)u$%+l#,> 6̩djn ."d t"-_ \
pR٠98g6z mB G~2ªe@ `.: lC 6bx5@χ~Lx@,?0^P P R( )p*ƪyNt%->H}0@p1+BW@el*6PXh@\ ^ _j>0`6Z̽D%B `pXN Z `8 ,x@ >@O |
Ypt40 BpZRQRm ʚZ)j&y0C|RWAqGL(N0"Fs$^ v @.C .5䊗&;P{4?1d c 8#hNs1'6p*Uz F.{2i]F;
QڋvMj+XJAAl6WV8eE A ƛ W9@tYw]Nm]Z"e!\L:WM I&阢 'fqlf~W eYlkJ ?[x-L%.ܴ0rLNVla 2h+\D
X`D(?q: 6hZ "hI [ k( 3 KҗRtо Fx gIx5
%N}FB)}x Vu? {" 1O2v ve# O/= W? ILa3P{ #ch90VW77X? px7W F[>R: y<yHi% pk O?Ia nDY 6 1o M&qtt~lg L(4#*u Zir}` Hg$`8`kg-SY #nz
z@@
! pM)[X
k R! P =rHC!8lW e,8FuN
` C3 vX4BwQ:A Ai"c "G^G" c4)_6_7ĐP![]8VR f 0&4zr8uxs a
.3\WQ1f FQ.}_Achk(u0:HxH98hNf>) 7+(bqE TZx V(EԐc(13E w p8oTp43<C u> S YeB$ݢG7q!SM??PS !1us
P ZSC^oh#Ǡ*K =.#1 B Q* Q X -1&6 VxgkVL;6ah8iJAp0[`4 %j! 9S
{ v7^D IIJ~:8y Hl7z ,&u+qap3TO뱎XZ[Z]àtBҔ4pxRm/P
lrR
^ $VS@~TD: !-v*4 0Y8rӖ )wi!2Fh)Wa>;)O]|/ ~oBEt8eLЬ9e7 бP8?C鑩N['V
WF@ iKC"_ahFѰ G [{Kx}k T_a++Gf8%'\5zz
0zz Lyzz4yyyyz .y!!9-
)z zy z-=ķ
HB
y#pzX^ZƐ)L 6 L#x
J-1czBØA<@\ՠG]U'۶n)+9Z8@,H7]
9|mZ ܘk I ߿~ `G] Zx{=9`QHOL S\A}6?mjBY \' hl,=@4<,<Iן4 B<
Z@C 㢧 3`<hp`Y/EۀT\p 1X3@_'f%LĞmT P\3Q,\A@sssIW@(lp j] khU.E7|נ[n%]v! ^|f` V\v`Qƙef\&%b`S!+N& $B ,
P}@ x@aJxx Eh%XP:q$rbP,V$(P't͠)S˂ jk6+v@UV,߄ +5P΅ ۛ
MmE`"s)8MJ
lRZk[8>yC]wW_V5A uA$ܥ _B 5/w84\c3bsBذIE5HZ%
VPXͯVA$
8T>
ZzUBP
ּN- |Fغ
W ҂ !7
ؐG ?)@
l.gt= B =xVP]9%8 B
)Pe. y@iu^[6Lrv|&~Oy6(ERTL 39s] E <1&7o H99P @>8tCн?} Л(L
WPhA?7Ę*xA 3p%`8b&:nX*ZX̢ =nO d2|}`xxAe? " юSJ fpxp :Ї*{{ 2 `8v;/ 0@MIR-H+PЌfdtɽt/|DR`2NvR ,bLyp $@,,'pE= 6>20#Lj %%L:7te03X MJ}f8(7S ;Іm#0IQ` [ 9N ZEr=@>-{O E/AQb\e\LO8j6Pz:F ]\q^&=!9a,j l!D;@6` >pF$[ >Ȑz0 Ĩ>h jq5=uR Q\:g8"w &@4OZ nsMjW-pOS@&P%bـ7Ah+DX+Hz 8 T{|kj KFoFoG^
/
s !PUj*&cRμQ%5CBP 86
df7Kxؼ`(^ gXK 4 53 G&Rs F7P:n Fqmn1ʢc ! ,T$5{'ްO#<[a L8-H,\ y "b_;"
rǰ
w TfXùpgY wA $
P
δ7N{Ә6gبt EcR:ƂF "7HJu V-P `
0会Z@q z@y'
X 9OoD^duj[0m4px4GY/jЀp"; NB X Ja
Fl ů^ xQ#ú
WdS 1Ko .{ ^ax4!*O`{$!k6HB}.)VY@b aȼ W$-HxB6 S ")hgQp Y(GX0W4n.)f z&擇$u y ?U\bL H
( c@ p- R|!FŬA9҃!/S 3,x WhQ1B/->Ao@"4/F:o'E8oXψZA.G5e?SLgDj\ l` jok?~|_ qNO?L |? pL _?/ /__¿? /oӿ֟ܿd
l^ d4@'@8>p/Hó螈Azѭ"+ǫG
l ̹Ln`'I4DyF<5@&':Kg\,kM @\ DoM ՚I Aw2 Pש픲-6?M
阢f0@$? Pb
%WL ݹ / zz kq)2,"HӰݙ)Y q|xzݹ4YJp )=
hi
0?8G ` 'g[/`7F9 {mѲWSTx''RƜg IJu 47 6 +*(ꀰ/m9LJs{,ց 6p(?B-O R့@)xD
jݞ ?
} ]{ƺ@ X?P@{#,%[6bc `Zg 6Sn\T <\H9wos gpۛW-; ǯGN(OW{y9
ްEȹw>ЇNHO% o6 Jҙ.[XΩ{`NK :x~
SDp@.uw]y'x ~!?=G|3?zw^IWֻ{e=w;`7O'C?|X @ֵO߁?W?緿o'g~(GWǀX:U}pq eUq@f%U V_eUa0Hm1 e)HłB1w@*17e%!H$h>8 PE8H!WHY?8ZhUUaxd Xg _GX h0K0sH_
0 C3 h J.a TsTP
Ex(
p*,+`
SdASħv *sAFSS
g0z5
Bɔ6&:Z{ Mf&[h^J:ڪJ FnAJ':Zz?e:z::>0g
ySb]E 4|Yq+yѧB"
6HU5 u \^5Bb<\U +?H?rr;a6
?Q
yg
*="pkNl/{PMw((WJ- 6xy8єY&B'6khg8J{O Kq Vx x٢N&^'!沭Bt&
-0O<~v| 21z}
!;mHЇ1gzIFSSE^B`pqٴ 5.3A{ۺ뺲;[[UT1',I:[ƫ,)ۼ/ [{؛ڛ}
~
M["0[{蛾껾۾;[{ۿ ;|
<\|
"<$\& &,.0 2<ZG |LB< *LHELNP |0
@ `\_a|b|hlnS<˻܄6z|~ǀ Ȃ<Ȅl@/z,6ȌȎ]ƒ<ɔƑUHȑ\mL(Ơ ~èʮʰ\Gp
\ ~ = cY \, Y P p|Y7P ` C<\ͼ ä8p ^<|?,|| 8ܬZ