Creating an ADF application based on PL/SQL API – part one: reading data as a Collection of SQL Type Objects

In this article something you may never do in exactly this way. Yet something that may become increasingly more useful, as our databases will have ever more Data Service APIs and wrappers around core tables. Instead of querying tables or even views, we may very well be talking to APIs from our Java applications, getting custom SQL Collections filled with custom SQL Type based objects instead of a regular result set from a simple SQL query.

The function in question: librarian.get_books_by_author( p_author in varchar2) return shelf;

Shelf? Yeah, that is a Collection, defined as:

create type shelf is table of book

And book, you will not be surprised to learn, is custom SQL type. Defined like this:

create or replace type BOOK as object
( title               varchar2(200)
, author              varchar2(100)
, isbn                varchar2(20)
, year_of_publication number(4)
)

In this article a simple example of accessing such a PL/SQL API that returns such a collection. Note: the example is not necessarily the perfect use case. In this particular case, we could easily have wrapped the PL/SQL function call in a View or ADF BC View Object, never to be confronted with Collection or custom SQL Types again. For example this query:

select title, author, isbn, year_of_publication
from   table (librarian.get_books_by_author('j'))

makes the Collection look like a regular relational data source to client applications. However, to show you how we can deal with the Collection in our Java application, we will not use this SQL wrapping.

Note: the SQL (DDL scripts) required to set up the tables and types and data used in this article can be found in this article: Juggling met SQL Types in SQL and PL/SQL – fading borders between Collection and Table.

We will perform these steps:

1. create a new JDeveloper application, template ADF BC and JSF. (I have called mine LibraryObjectTypes)

2. create an ADF BC Application Module LibraryService.

Using the New Gallery, Business Components, Application Module – create a new Application Module – LibraryService. Note that this application module will not contain any Entity Objects or View Objects this time, just a single method in the ApplicationModuleImpl class.

3. create a single method in the Application Module’s LibraryServiceImpl class to return a List<Map> with data on the books returned in the SHELF Collection.

This method – getBooks( String) – is to return a List<Map> that contains the Book details that are retrieved from the PL/SQL function LIBRARIAN.get_books_by_author, that returns a SHELF object. It is implemented like this:

    public List<Map> getBooks(String author) {
        List<Map> books = new ArrayList();
        String getBooksStatement= "begin ? := LIBRARIAN.get_books_by_author(?); end;";
        ARRAY nt = null;

        CallableStatement st = null;
        try {
            st =
               getDBTransaction().createCallableStatement(getBooksStatement, 0);
            // Register the first bind variable for the return value
            st.registerOutParameter(1, OracleTypes.ARRAY, "SHELF");
            st.setString(2, author);
            st.executeUpdate();
            // 6. Return the value of the first bind variable
            nt = ((OracleCallableStatement)st).getARRAY(1);
            // the ARRAY object can yield an array of objects
            Object[] booksArray = (Object[])nt.getArray();
            // the objects returned in the ARRAY are in fact STRUCTs
            // these are created based on the BOOK SQL TYPE definition
            // each of the properties in the BOOK TYPE correspond to a
            // an attribute in the STRUCT
            for (int i = 0; i < booksArray.length; i++) {
                STRUCT bookStruct = (STRUCT)booksArray[i];
                Map book = new HashMap();
                book.put("TITLE", bookStruct.getAttributes()[0]);
                book.put("AUTHOR", bookStruct.getAttributes()[1]);
                book.put("ISBN", bookStruct.getAttributes()[2]);
                book.put("YEAR_OF_PUBLICATION", bookStruct.getAttributes()[3]);
                books.add(book);
            } // for
        } catch (SQLException e) {
            throw new JboException(e);
        } finally {
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    throw new JboException(e);
                }
            }
        }
        return books;
    }

The essence of this method is the call to the PL/SQL function that yields an ARRAY. This object contains an array (Object[]) of the things that represent the elements in the original SQL (SHELF) Collection. These things are complex types themselves – BOOK – and those are available as STRUCTs. A STRUCT makes its data elements available through the getAttributes() method that returns another Object[]. We find the original BOOK properties in the same sequence in this array.

4. publish the method on the client interface for the LibraryService

In the ViewController project:

1. create a new JSPX page – I have called it BooksOverview

2. create a PageDefinition for this page and add a methodBinding for the getBooks method in the DataControl Palette under the LibraryService Data Control.

    <methodAction id="getBooks" MethodName="getBooks" RequiresUpdateModel="true"
                  Action="999" IsViewObjectMethod="false"
                  DataControl="LibraryServiceDataControl"
                  InstanceName="LibraryServiceDataControl.dataProvider"
                  ReturnName="LibraryServiceDataControl.methodResults.LibraryServiceDataControl_dataProvider_getBooks_result">
      <NamedData NDName="author" NDType="java.lang.String"/>
    </methodAction>

3. create a class LibraryManager. Configure this class as a managed bean.

  <managed-bean>
    <managed-bean-name>LibraryManager</managed-bean-name>
    <managed-bean-class>nl.amis.library.view.LibraryManager</managed-bean-class>
    <managed-bean-scope>session</managed-bean-scope>
  </managed-bean>

4. add these properties and generate accessor methods for them:

    private String author;
    private List<Map> books;
    private CoreTable libraryBooksTable;

5. add button to the page; action = LibraryManager.findBooks; partialSubmit=true

    <af:commandButton action="#{LibraryManager.findBooks}"
                      partialSubmit="true" text="Find Books"/>

This button will be used to execute the query – that means: execute the method binding for the getBooks operation on the LibraryService.

6. Add an inputText for entering the search criteria. This component has its value bound to the LibraryManager bean’s author property:

    <af:inputText value="#{LibraryManager.author}" label="Author" />

7. add table component to the page; the value for the table is provided by the books property in the LibraryManager bean:

          <af:table emptyText="No items were found"
                    value="#{LibraryManager.books}" var="book" banding="row"
                    bandingInterval="1"
                    binding="#{LibraryManager.libraryBooksTable}">
            <af:column sortProperty="TITLE" sortable="true" headerText="Title"
                       formatType="text">
              <af:outputText value="#{book.TITLE}"/>
            </af:column>
            <af:column sortable="false" headerText="Isbn">
              <af:outputText value="#{book.ISBN}"/>
            </af:column>
            <af:column sortProperty="AUTHOR" sortable="true"
                       headerText="Author">
              <af:outputText value="#{book.AUTHOR}"/>
            </af:column>
            <af:column sortProperty="YEAR_OF_PUBLICATION" sortable="true"
                       headerText="Year">
              <af:outputText value="#{book.YEAR_OF_PUBLICATION}"/>
            </af:column>
          </af:table>

The table has columns based on the fields in the Map objects that are within the books List<Map> return by the LibraryManager.

8. add a method findBooks() in the LibraryManager class:

    public String findBooks() {
        DCBindingContainer bindings = getCurrentBindingContainer();
        OperationBinding operationBinding =
            bindings.getOperationBinding("getBooks");
        operationBinding.getParamsMap().put("author", this.author);
        books = (List<Map>)operationBinding.execute();
        // now make sure to refresh the table!
        AdfFacesContext.getCurrentInstance().addPartialTarget(this.libraryBooksTable);
        return null; // stay on the same page
    }

    private DCBindingContainer getCurrentBindingContainer() {
        FacesContext ctx = FacesContext.getCurrentInstance();
        HttpServletRequest request =
            (HttpServletRequest)ctx.getExternalContext().getRequest();
        BindingContext bcx = DCUtil.getBindingContext(request);
        DCBindingContainer container = null;
        if (bcx != null) {
            container =
                    bcx.findBindingContainerByPath(ctx.getViewRoot().getViewId());
        }
        return container;
    }

this method is pretty important. It uses the methodBinding to get the Books List from the ApplicationModule – that in turns acquires it from the PL/SQL API. It then makes the List<Map> available through the books property. Finally it makes the table refresh.

Now we can run the application.

Enter a “j” in the Author field and press the Find Books button:

Note that we can sort the table by clicking on the column headers.

Resources

A zipfile with the JDeveloper 10.1.3.3 Application (add the jar files adf-faces-impl.jar and jsf-impl.jar to WEB-INF\lib yourself): LibraryObjectTypes_read.zip.