Using cursors in Oracle ADF.

Generating master-detail relationships in Oracle ADF usually is done using two (or more) View Objects which are linked together via a View Link. In a stand-alone application, getting the details for each master row can be a tedious task. First, the next row in the master View Object needs to be gotten, then the View Link relationship needs to be followed to get to the detail View Object over which’s row needs to be iterated. Using a cursor in this case can make life a little easier. Read on to see how we did it.

Imagine you’d like to get all Employees in a Department. Using the ADF wizards in JDeveloper, this can easily be set up. Create the Entity Objects for Department and Employee, create an Association between the two, create View Objects for Departments and Employees and finally set up a View Link between Departments and Employees. Next, the ADF layers will take care of everything for you and you’re done.

In some cases it is necessary to iterate over all row in the Departments View Object and get the Employees rows for every Departments row. This can be accomplished by iterating over all Departments rows, get the associated Employees View for every row and iterate over all the Employees rows. Usually this is sufficient, but in some cases it is not.

Enters the cursor. Through a cursor it is possible to get to the detail rows corresponding to a master row. For instance, take this query:

select dep.department_id,
       dep.department_name,
       cursor (select emp.employee_id,
                      emp.first_name,
                      emp.last_name
               from employees emp
               where emp.department_id = dep.department_id) AS emps
from departments dep

This will select the ids and department_names from all departments and the ids, first_names and last_names of all employees in the departments. So, how can this be used in ADF?

The trick is to define an attribute in the Departments View Object using the View Object Editor in JDeveloper. Simply name the attribute “EmpsList” and make it of type “oracle.jdbc.OracleResultSet”. In the Query Column section, enter as expression

cursor (select emp.employee_id,
               emp.first_name,
               emp.last_name
        from employees emp
        where emp.department_id = dep.department_id)

This will make the cursor available as a JDBC Resultset. Since in the current versions of JDBC a ResultSet cannot live apart from the query statement populating it, we’ll use a little trick to store the data in the ResultSet temporarily in another Data Structure.

First we’ll need to define a POJO that may look like this:

package nl.amis.blog.util;

public class MyEmp {

  private int id;
  private String firstName;
  private String lastName;

  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public String getFirstName() {
    return firstName;
  }

  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }

  public String getLastName() {
    return lastName;
  }

  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
}

Next, create ANOTHER attribute called Emps and make it of type java.util.ArrayList. Finally, make sure the ViewRowImpl java source file for the Departments View Object is created. Edit the source and make the getEmps() look like this

  public ArrayList getEmps()
  {
    ArrayList emps = new ArrayList();
    OracleResultSet set = getEmpsList();
    try {
      while (set.next()) 
      {
      	MyEmp myEmp = new MyEmp();
        myEmp.setId(set.getInt(1));
        myEmp.setId(set.getString(2));
        myEmp.setId(set.getString(3));
        emps.add(myEmp);
      }
      //set.beforeFirst();
    } catch (Exception e) 
    {
      e.printStackTrace();
    }
    return emps;
  }

Instead of referring to the column indices, it’s of course also possible to use the column names. The getEmps method will populate an ArrayList with the data retrieved via the ResultSet. Please note this means a database roundtrip is necessary every time the getEmps method is called. On the other hand, making code like this may appeal to developers who favour writing Java code the “good old way” over learning the ins and outs of yet another framework. The choice is yours.