Oracle & JDBC – Passing a ‘table of custom object type’ to a stored procedure – implementing efficient single round trip data exchange (part two of an ADF application on a PL/SQL API)

One very efficient way of passing data from the Java application to the database is through the use of custom Oracle Types. Even when we want to send a heterogeneous data set, for example a data set with master and details records, custom type based objects can provide a powerful exchange of data. However, also one that is not extremely straightforward to get started with. In this article an example of how to send a TABLE OF MY_TYPE objects as an input parameter to a stored PL/SQL procedure.
Note: through the use of JPublisher, custom classes can be generated that map intuitively to the custom Oracle Types. Examples of using JPublisher can be found at several locations on the web. In this article, I do not use JPublisher – I try to keep the solution as straightforward as possible.

Note 2: this article is a sequel to the article “Creating an ADF application based on PL/SQL API – part one: reading data as a Collection of SQL Type Objects“; this predecessor also introduces the Library application we use here as a starting point as well as provides downloads for this application and the supporting Database resources. This former article discusses how to retrieve a TABLE of [custom] TYPE from a PL/SQL Function, in Java/JDBC. This article focuses on the other way round: sending the collection from the Java application to the database.

In this example, I work with two custom SQL Type definitions. One is BOOK and the other is SHELF (table of BOOK).

In the Java Web application, we have a managed bean that controls a book collection, implemented as a List<Map> where each Map contains the properties for a specific Book, such as title, author, isbn and year of publication.

From our Java code, we want to be able to add a Java Collection with Book objects to the persistent library through the add_entire_shelf_to_library operation on the PL/SQL package. In order to be able to do so – without resorting to JPublisher – , we have to go through these steps in our code:

1. create a StructureDescriptor that makes the structure of the BOOK Database Type available to the JDBC code
2. build an array with STRUCT objects, each with the data from a single Book object and each based on the bookStructureDescriptor
3. create an ArrayDescriptor that makes the structure of the SHELF (== TABLE OF BOOK) Database Type available for JDBC processing
4. create an ARRAY object based on the shelfArrayDescriptor for its structure and the bookStructArray for its data
5. create the JDBC Callable statement to call the PL/SQL package; register an input parameter using setARRAY() for the shelfArray
6. execute the statement; handle exceptions, close resources etc.

When applying this to the simple Web Application, starting at the top, I did the following:

1. Converted the outputText components to inputText in the BooksOverview.jspx page

2. Add a commandButton to the page, used to add a new book:

<af:commandButton action="#{LibraryManager.addNewBook}" id="addBook"
                  partialSubmit="true" text="Add Book"/>

3. Add the method addNewBook in the LibraryManager class – this method is called when the Add Book button is pressed

    public String addNewBook() {
        Map newBook = new HashMap();
        newBook.put("TITLE","new");
        newBook.put("AUTHOR","");
        newBook.put("ISBN","");
        newBook.put("YEAR_OF_PUBLICATION","");
        books.add(newBook);
        // now make sure to refresh the table!
        AdfFacesContext.getCurrentInstance().addPartialTarget(this.libraryBooksTable);
        return null;
    }

Note: thanks to the partialSubmit=”true” on the button and the addPartialTarget() call in the method, the new book will immediately be visible in the table, thanks to partial page refresh.

4. Add a button to save the changes made in the books data – including new books we have added

<af:commandButton action="#{LibraryManager.saveBooks}" id="saveBook" partialSubmit="true" text="Save Changes"/>

5. Add the saveBooks method in the LibraryManager class

    public String saveBooks() {
        DCBindingContainer bindings = getCurrentBindingContainer();
        OperationBinding operationBinding =
            bindings.getOperationBinding("saveBooks");
        operationBinding.getParamsMap().put("books", this.books);
        operationBinding.execute();
        return null;
    }

This method relies on the operationBinding called saveBooks. Now where did that come from? How was it added to the PageDefinition?

For this we need to step to the ApplicationModule Impl class LibraryServiceImpl. In this class we have added method saveBooks(List<Map> books). Then we added this method to the client interface. That made it show up in the Data Control Palette and we could create an operationBinding for it in the BooksOveriewPageDef file:

    <methodAction id="saveBooks"
                  InstanceName="LibraryServiceDataControl.dataProvider"
                  DataControl="LibraryServiceDataControl" MethodName="saveBooks"
                  RequiresUpdateModel="true" Action="999"
                  IsViewObjectMethod="false">
      <NamedData NDName="books" NDType="java.util.List"/>
    </methodAction>

The Web Application now looks like this:

Note: using the Add Book method, the last Book record was added. Now we can press Save Changes to invoke the saveBooks() method that will call the PL/SQL procedure, handing it a Collection of Book type instances.

The saveBooks method in the LibraryServiceImpl is the method this whole article really is about.

    public void saveBooks(List<Map> books) {
        Connection conn;

        // create the StructureDescriptor to the Database Type NGN_SET_MEMBER_T

        // A StructDescriptor can be thought of as a "type object". This means that it contains information about the object type, including the typecode, the type name, and how to convert to and from the given type
        // To construct an oracle.sql.STRUCT object for a given Oracle object type, you must:
        //    1. Create a StructDescriptor object (if one does not already exist) for the given Oracle object type.
        //    2. Use the StructDescriptor to construct the STRUCT object.
        // A StructDescriptor is an instance of the oracle.sql.StructDescriptor class and describes a type of SQL structured object (Oracle object).
        try {
            conn = getDBTransaction().createStatement(1).getConnection();
            StructDescriptor bookStructDesc =
                StructDescriptor.createDescriptor("BOOK", conn);

            // the array that will contain the Struct objects - one object for everyt member
            Object[] bookStructArray = new Object[books.size()];

            // for each member in List members
            // create a Struct object and add to the memberArray
            int ctr = 0;
            for (Map book: books) {

                STRUCT m =
                    new STRUCT(bookStructDesc, conn, new Object[] { book.get("TITLE"),
                                                                    book.get("AUTHOR"),
                                                                    book.get("ISBN"),
                                                                    book.get("YEAR_OF_PUBLICATION") });
                bookStructArray[ctr++] = m;
            }


            // create the structure array descriptor for the NGN_SET_MEMBER_TBL_T type

            ArrayDescriptor shelfStructDesc =
                ArrayDescriptor.createDescriptor("SHELF", conn);
            // using the memberListStructDesc descriptor we can create an ARRAY object that contains the bookStructArray (filled with bookStruct objects)
            ARRAY shelfStruct =
                new ARRAY(shelfStructDesc, conn, bookStructArray);

            String saveBooksStatement =
                "begin librarian.add_entire_shelf_to_library(?); end;";


            OracleCallableStatement st = null;
            try {
                st =
 (OracleCallableStatement)getDBTransaction().createCallableStatement(saveBooksStatement,
                                                                     0);
                st.setARRAY(1, shelfStruct);
                st.executeUpdate();


            } catch (SQLException e) {
                throw new JboException(e);
            } finally {
                if (st != null) {
                    try {
                        st.close();
                    } catch (SQLException e) {
                        throw new JboException(e);
                    }
                }
            }
        } catch (SQLException e) {
            // TODO
        }


    }

Resources

Integrating the Oracle Designer Legacy Table API with Oracle JDeveloper 11g ADF Business Components by Chris Muir

Java: Passing arrays to a stored procedure with PLSQL Table Type – Rajesh Shah

Working with Oracle Object Types – Oracle Database JDBC Developer’s Guide and Reference 10g Release 2 (10.2)

SQL for the examples in this article: library_sql.txt.

Download JDeveloper 10.1.3.3 Application: LibraryObjectTypes_update.zip  (add adf-faces-impl.jar and jsf-impl.jar in WEB-INF\lib directory)