6 Using Additional Functionality in Oracle Database XE

Using OraclePreparedStatement

To run static SQL queries on the database, you use the Statement object. However, to run multiple similar queries or perform multiple updates that affect many columns in the database, it is not feasible to hard-code each query in your application.

You can use OraclePreparedStatement when you need to run the same SQL statement multiple times. Consider a query like the following:

SELECT * FROM Employees WHERE ID=xyz;

Every time the value of xyz in this query changes, the SQL statement needs to be compiled again.

If you use OraclePreparedStatement functionality, the SQL statement you need to run is precompiled and stored in a PreparedStatement object, and you can run it as many times as required without the need to compile it every time it is run. If the data in the statement changes, you can use bind variables as placeholders for the data and then provide literal values at run time.

Consider the following example of using OraclePreparedStatement:

OraclePreparedStatement pstmt = conn.prepareStatement("UPDATE Employees                                     SET salary = ? WHERE ID = ?");
   pstmt.setBigDecimal(1, 153833.00)
   pstmt.setInt(2, 110592)   

The advantages of using the OraclePreparedStatement interface include:

• You can batch updates by using the same PreparedStatement object

• You can improve performance because the SQL statement that is run many times is compiled only the first time it is run.

• You can use bind variables to make the code simpler and reusable.

Using OracleCallableStatement

You can access stored procedures on databases using the OracleCallableStatement interface. This interface extends the OraclePreparedStatement interface. Oracle Database XE does not have any Java, so you can only use PL/SQL stored procedures in your application. The OracleCallableStatement interface consists of standard JDBC escape syntax to call stored procedures. You may use this with or without a result parameter. However, if you do use a result parameter, it must be registered as an OUT parameter. Other parameters that you use with this interface can be either IN, OUT, or both.

These parameters are set by using accessor methods inherited from the OraclePreparedStatement interface. IN parameters are set by using the setXXX methods and OUT parameters are retrieved by using the getXXX methods, XXX being the Java data type of the parameter.

A CallableStatement can also return multiple ResultSet objects.

As an example, you can create an OracleCallableStatement to call foo, as follows:

OracleCallableStatement cs = (OracleCallableStatement)
conn.prepareCall("{call foo(?)}");

You can pass the string bar to this procedure in one of the following two ways:

cs.setString(1,"bar"); // JDBC standard
// or...
cs.setString("myparameter","bar"); // Oracle extension

Using Bind Variables

Bind variables are variable substitutes for literals in a SQL statement. They are used in conjunction with OraclePreparedStatement and OracleCallableStatement to specify parameter values that are used to build the SQL statement. Using bind variables has remarkable performance advantages in a production environment.

For PL/SQL blocks or stored procedure calls, you can use the following qualifiers to differentiate between input and output variables: IN, OUT, and IN OUT. Input variable values are set by using setXXX methods and OUT variable values can be retrieved by using getXXX methods, where XXX is the Java data type of the values. This depends on the SQL data types of the columns that you are accessing in the database.

Creating a SQL Stored Procedure in JDeveloper

JDeveloper allows you to create stored procedures in the database through the Connection Navigator. In these steps, you create a stored procedure that can be used as an alternative way of inserting an employee record in the sample application.

1. Select the Connections tab to view the Connection Navigator.

2. Expand the database connection node (by default called DBConnection1), and the HR node to see the objects in the HR database.

3. Right-click Procedures, and select New PL/SQL Procedure.

4. In the Create PL/SQL Procedure dialog, enter insert_employee as the object name. Click OK.

   The skeleton code for the procedure is displayed in the Source Editor.

5. After the procedure name, enter the following lines of code:

   PROCEDURE "INSERT_EMPLOYEE" (p_first_name employees.first_name%type,
     p_last_name employees.last_name%type,
     p_email employees.email%type,
     p_phone_number employees.phone_number%type,
     p_job_id employees.job_id%type,
     p_salary employees.salary%type
   )
   
   

6. After the BEGIN statement, replace the line that reads NULL with the following:

   INSERT INTO Employees VALUES (EMPLOYEES_SEQ.nextval, p_first_name , 
       p_last_name , p_email , p_phone_number, SYSDATE, p_job_id, 
       p_salary,.30,100,80);
   
   

   You can see that the statement uses the same hard-coded values that are used for the last three columns in the addEmployee method in the DataHandler.java class.

7. Add the procedure name in the END statement:

   END insert_employee;
   
   

8. Save the file, and check whether there are any compilation errors.

The complete code for the stored procedure is shown in Example 6-6.

PROCEDURE "INSERT_EMPLOYEE" ( p_first_name  employees.first_name%type, 
  p_last_name    employees.last_name%type,
  p_email        employees.email%type,
  p_phone_number employees.phone_number%type,
  p_job_id       employees.job_id%type,
  p_salary       employees.salary%type
)
AS
BEGIN
  INSERT INTO Employees VALUES (EMPLOYEES_SEQ.nextval, p_first_name , 
    p_last_name , p_email , p_phone_number, SYSDATE, p_job_id, 
    p_salary,.30,100,80);
END insert_employee;

Creating a Method to Use the Stored Procedure

In these steps, you add a method to the DataHandler.java class that can be used as an alternative to the addEmployee method. The new method you add here makes use of the insert_employee stored procedure.

1. Select the Applications tab to display the Application Navigator.

2. If the DataHandler.java file is not already open in the Java Source Editor, double-click it to open it.

3. Import the CallableStatement interface as follows:

   import java.sql.CallableStatement;
   
   

4. After the addEmployee method, add the declaration for the addEmployeeSP method.

   public String addEmployeeSP(String first_name, String last_name, 
     String email, String phone_number, String job_id,
     int salary) throws SQLException {
   }
   
   

   The method signature is the same as that for addEmployee.

5. Inside the method, add a try block, and inside that, connect to the database.

   try {
     getDBConnection(); 
   }
   
   

6. In addition, inside the try block, create the SQL string:

   sqlString = "begin hr.insert_employee(?,?,?,?,?,?); end;";
   
   

   The question marks (?) in the statement are bind variables, acting as placeholders for the values of first_name, last_name, and so on expected by the stored procedure.

7. Create the CallableStatement:

   CallableStatement callstmt = conn.prepareCall(sqlString);
   
   

8. Set the IN parameters:

   callstmt.setString(1, first_name);
     callstmt.setString(2, last_name);
     callstmt.setString(3, email);
     callstmt.setString(4, phone_number);
     callstmt.setString(5, job_id);
     callstmt.setInt(6, salary);
   
   

9. Add a trace message, and run the callable statement.

   System.out.println("\nInserting with stored procedure: " + sqlString);
     callstmt.execute();
   
   

10. Add a return message:

    return "success"; 
    
    

11. After the try block, add a catch block to trap any errors. Call the logException created in Example 5-5.

    catch ( SQLException ex ) {
      System.out.println("Possible source of error: Make sure you have created the stored procedure"); 
      logException( ex ); 
      return "failure";
    }
    
    

12. Save DataHandler.java.

The complete method is shown in Example 6-7.

public String addEmployeeSP(String first_name, String last_name, 
  String email, String phone_number, String job_id,
  int salary) throws SQLException {
 
  try {
    getDBConnection(); 
    sqlString = "begin hr.insert_employee(?,?,?,?,?,?); end;";
    CallableStatement callstmt = conn.prepareCall(sqlString);
    callstmt.setString(1, first_name);
    callstmt.setString(2, last_name);
    callstmt.setString(3, email);
    callstmt.setString(4, phone_number);
    callstmt.setString(5, job_id);
    callstmt.setInt(6, salary);
    System.out.println("\nInserting with stored procedure: " + sqlString);
    callstmt.execute();
    return "success"; 
  }
  catch ( SQLException ex ) {
    System.out.println("Possible source of error: Make sure you have created the stored procedure"); 
    logException( ex ); 
    return "failure";
  }
}

Allowing Users to Choose the Stored Procedure

The steps in this section add a radio button group to the insert.jsp page, which allows a user to choose between inserting an employee record using the stored procedure and using a SQL query in Java code.

1. Open insert.jsp in the Visual Editor, if it is not already open.

2. Create a new line after the Insert Employee Record heading. With the cursor on this new line, drag UseBean from the JSP page of the Component Palette to add a jsp:useBean tag to the page. Enter empsbean as the ID, browse to select hr.DataHandler as the Class, and set the Scope to session. With the UseBean still selected on the page, set the style of this line to None instead of Heading 3.

3. Drag a Radio Button component from the HTML Forms page of the Component Palette onto the page inside the form above the table. In the Insert Radio Button dialog, enter useSP as the Name, false as the Value, and select Checked. Click OK.

4. In the Visual Editor, position the cursor to the right of the button, and enter text to describe the purpose of the button, for example, 'Use only JDBC to insert a new record'.

5. Press Enter at the end of the current line to create a new line.

6. Drag a second Radio Button below the first one. In the Insert Radio Button dialog, use useSP as the Name, true as the Value, and ensure that the Checked checkbox is not selected.

7. In the Visual Editor, position the cursor directly to the right of the button, and enter text to describe the purpose of the button, for example, 'Use stored procedure called via JDBC to insert a record'.

8. Save the page.

Figure 6-1 shows insert.jsp with the radio button that provides the option to use a stored procedure.

Figure 6-1 Adding a Link to Provide the Stored Procedure Option

Description of "Figure 6-1 Adding a Link to Provide the Stored Procedure Option"

Calling the Stored Procedure from the Application

The steps in this section modify the insert_action.jsp file, which processes the form on the insert.jsp page, to use the radio button selection and select the appropriate method for inserting a new employee record.

1. Open insert_action.jsp in the Visual Editor, if it is not already open.

2. Double-click the scriptlet to invoke the Scriptlet Properties dialog box, and add a new variable after the salary variable, as follows:

   String useSPFlag = request.getParameter("useSP");
   
   

3. Below that, still in the Scriptlet Properties dialog box, replace the existing empsbean.addEmployee line with the following lines of code to select the addEmployeeSP method or the pure JDBC addEmployee method to insert the record.

   if ( useSPFlag.equalsIgnoreCase("true"))
     empsbean.addEmployeeSP(first_name, last_name, email, 
     phone_number, job_id, salary.intValue()); 
   // otherwise use pure JDBC insert
   else
     empsbean.addEmployee(first_name, last_name, email, 
     phone_number, job_id, salary.intValue()); 
   
   

4. Save insert_action.jsp.

You can now run the application and use the radio buttons on the insert page to choose how you want to insert the new employee record. In a browser, the page will appear as shown in Figure 6-2.

Figure 6-2 Using Stored Procedures to Enter Records

Description of "Figure 6-2 Using Stored Procedures to Enter Records"

Oracle REF CURSOR Type Category

To create a cursor variable, begin by identifying a type that belongs to the REF CURSOR category. For example:

dept_cv DeptCursorTyp 
...

Then, create the cursor variable by declaring it to be of the type DeptCursorTyp:

DECLARE TYPE DeptCursorTyp IS REF CURSOR

REF CURSOR, then, is a category of data types, rather than a particular data type. Stored procedures can return cursor variables of the REF CURSOR category. This output is equivalent to a database cursor or a JDBC result set.

Accessing REF CURSOR Data

In Java, a REF CURSOR is materialized as a ResultSet object and can be accessed as follows:

import oracle.jdbc.*;
...
CallableStatement cstmt;
ResultSet cursor;

// Use a PL/SQL block to open the cursor
cstmt = conn.prepareCall
         ("begin open ? for select ename from emp; end;");

cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
cursor = ((OracleCallableStatement)cstmt).getCursor(1);

// Use the cursor like a normal ResultSet
while (cursor.next ())
    {System.out.println (cursor.getString(1));} 

In the preceding example:

1. A CallableStatement object is created by using the prepareCall method of the connection class.

2. The callable statement implements a PL/SQL procedure that returns a REF CURSOR.

3. As always, the output parameter of the callable statement must be registered to define its type. Use the type code OracleTypes.CURSOR for a REF CURSOR.

4. The callable statement is run, returning the REF CURSOR.

5. The CallableStatement object is cast to OracleCallableStatement to use the getCursor method, which is an Oracle extension to the standard JDBC application programming interface (API), and returns the REF CURSOR into a ResultSet object.

Using REF CURSOR in the Sample Application

In the following sections, you enhance the sample application to display a dynamically-generated list of job IDs and job titles in the Job field when they are inserting a new employee record.

• Creating a Package in the Database

• Creating a Database Function

• Calling the REF CURSOR from a Method

• Displaying a Dynamically Generated List

To do this, you create a database function, GET_JOBS, that uses a REF CURSOR to retrieve a result set of jobs from the Jobs table. A new Java method, getJobs, calls this database function to retrieve the result set.

PACKAGE "JOBSPKG" AS
    TYPE ref_cursor IS REF CURSOR;
END;

FUNCTION "GET_JOBS" RETURN JobsPkg.ref_cursor
AS jobs_cursor JobsPkg.ref_cursor;
BEGIN
  OPEN jobs_cursor FOR
  SELECT job_id, job_title FROM jobs;
  RETURN jobs_cursor;
END;

public ResultSet getJobs() throws SQLException {
  try {
    getDBConnection();
    String jobquery = "begin ? := get_jobs; end;";
    CallableStatement callStmt = conn.prepareCall(jobquery);
    callStmt.registerOutParameter(1, OracleTypes.CURSOR);
    callStmt.execute();
    rset = (ResultSet)callStmt.getObject(1);
  } catch ( SQLException ex ) {
  logException( ex );
  }
  return rset;
  }

Displaying a Dynamically Generated List

To create the drop down list displaying the list of job IDs and job titles in the Insert page, you hard-coded the job IDs and job titles. In the following steps, you replace this with a dynamically-generated list provided by the REF CURSOR created in the previous section.

1. Double-click insert.jsp in the Application Navigator to open it in the Visual Editor, if it is not already open.

2. Drag a Page Directive onto the page to the right of the useBean tag. In the Insert Page Directive dialog box, enter Java as the Language, and in the Import field, browse to select java.sql.ResultSet. Click OK.

3. Drag a scriptlet onto the page next to the Page Directive. In the Insert Scriptlet dialog box, add the following code to execute the getJobs method and return a result set containing a list of jobs.

   ResultSet rset = empsbean.getJobs();
   
   

4. Select the ListBox component in the page, and click Scriptlet in the JSP Component Palette. (You need not drag and drop the scriptlet onto the page in this case.) The Insert Scriptlet dialog box appears.

5. Enter the following code into the Insert Scriptlet dialog box, and click OK.

   while (rset.next ())
      {
       out.println("<option value=" + rset.getString("job_id") + ">" + 
       rset.getString("job_title") + "</option> "  );
      }
     empsbean.closeAll();
   
   

6. Remove the hard-coded values as follows.

   With the ListBox component still selected, in the Structure window scroll to Job field. Examine the list of hard-coded options below the select keyword. Delete each of the options, ensuring that you retain the scriptlet.

   Figure 6-3 Structure View of Dropdown ListBox Options

   
   Description of "Figure 6-3 Structure View of Dropdown ListBox Options"
   
   

7. Save the page. Figure 6-4 shows the insert.jsp file in the Visual Editor.

   Figure 6-4 Inserting a Dynamically Generated List

   
   Description of "Figure 6-4 Inserting a Dynamically Generated List"
   
   

Now run the application, click to insert a new employee and use the list to display a list of available jobs. Figure 6-5 shows the dynamic jobs list in the browser.

Figure 6-5 Dynamically Generated List

Description of "Figure 6-5 Dynamically Generated List"