7.6 Communicating with a Database

For Oracle BPEL Process Manager and a database to communicate, the database is exposed as a web service. As with other web services, this communication is defined in a partner link. However, to give a BPEL process access to database tables, you also need to configure an adapter. The Adapter Configuration Wizard feature of Oracle BPEL Process Manager enables you to integrate BPEL processes with database tables, as well as file systems, database queues, Java Message Services (JMS), and Oracle Applications. After you create the partner link and define a database adapter service, a WSDL file that includes the adapter configuration settings is created.

The database adapter connects to any relational database. For nonrelational databases and legacy systems, application and mainframe adapters are available. See Oracle Application Server Adapter Concepts for information about application and mainframe adapters.

To access an existing relational schema, you use the Adapter Configuration Wizard to do the following:

• Import a relational schema and map it as an XML schema (XSD)

• Abstract SQL operations such as SELECT, INSERT, and UPDATE as web services

While your BPEL process deals with XML and invokes web services, database rows and values are queried, inserted, and updated.

Features of the database adapter include:

• Compliance with open standards. The database adapter is an implementation of J2EE Connector Architecture (J2CA) 1.5 standards and is deployed as a resource adapter in the same Oracle Application Server Containers for J2EE (OC4J) container as Oracle BPEL Process Manager.

• Connectivity to any relational (SQL 92) database using JDBC, or ODBC using the Sun JdbcOdbcBridge

• Ability to map any existing relational schema to XML. The mapping is nonintrusive to the schema and no changes need to be made to it.

• Web services abstraction of SQL operations. The generated WSDL operations are merge, insert, update, write, delete, select, queryByExample, and inbound polling, which includes physical delete, logical delete, and sequencing-based polling strategies.

• Leveraging of Oracle TopLink technology, an advanced object-to-relational persistence framework. You can access the underlying TopLink project, and use the TopLink Workbench interface for advanced mapping and configuration, sequencing, batch and joined relationship reading, batch writing, parameter binding, statement caching, connection pooling, external transaction control (JTS and JTA), UnitOfWork for minimal updates, caching, optimistic locking, advanced query support, and query by example.

See Oracle Application Server Adapter for Files, FTP, Databases, and Enterprise Messaging User's Guide for more information.

7.6.1 How to Configure a Database Adapter

Use the Adapter Configuration Wizard to configure a database adapter. With the wizard, you can import tables from the database, specify relationships spanning multiple tables, generate corresponding XML schema definitions, and create services to expose the necessary SQL or database operations. These services are consumed to define partner links that are used in the BPEL process. You use the Adapter Configuration Wizard to both create and edit adapter services.

To configure a database adapter:

1. Drag and drop a Database Adapter service into one of the Services swim lanes.

2. Enter a service name.

3. Use the Adapter Configuration Wizard - Service Connection page to provide a database connection and a Java Naming and Directory Interface (JNDI) name, or accept the default JDNI name.

   The JNDI name acts as a placeholder for the connection used when your service is deployed to Oracle BPEL Server. This enables you to use different databases for development and production. The Adapter Configuration Wizard captures the design-time connection in the generated WSDL as well, to serve as a fallback in case the runtime lookup fails.

4. Use the Adapter Configuration Wizard - Operation Type page to select an operation type.

   • Call a Stored Procedure or Function: Select this option if you want the service to execute a stored procedure or function.

   • Perform an Operation on a Table: Select this option for outbound operations. You can select Insert or Update, Delete, Select, or any combination of the three. These operations loosely translate to SQL INSERT, UPDATE, DELETE, and SELECT operations. If you select all three, then after you run the wizard, you see the following operations in the Operation list of the Invoke window: merge, insert, update, write, delete, serviceNameSelect, and queryByExample.

   • Poll for New or Changed Records in a Table: Select this option for an inbound operation (that is, an operation that is associated with a receive activity). This operation type polls a specified table and returns for processing any new rows that are added. You can also specify the polling frequency.

5. Use the Adapter Configuration Wizard - Select Table page to select already imported tables or to import tables.

   Select the root database table for your operation. If you are using multiple, related tables, then this is the highest-level table (or highest parent table) in the relationship tree. All the tables that have been previously imported in the project (including tables that were imported for other partner links) are displayed. This enables you to reuse configured table definitions across multiple partner links in a given BPEL project. These are the generated TopLink descriptors.

   If you are importing tables, click Import Table, select a schema, click Query, and move the tables you want from the Available list to the Selected list.

   If you want to reimport a table (if the table structure has changed on the database, for example), import it again. This overwrites the previously configured table definition. If you reimport a table, you lose any custom relationships you may have defined on that table, as well as any custom WHERE clauses (if the table being imported was the root table).

6. Click Next.

7. Use the Adapter Configuration Wizard - Define Primary Key page to define primary keys for any of the tables you selected in the previous step.

   This page appears if primary keys are not already defined. You must specify a primary key for all imported tables. You can select multiple fields if you need to specify a multipart primary key. The primary key that you specify here is recorded in the offline database table and is not persisted back to the database schema; the database schema is left untouched.

8. Use the Adapter Configuration Wizard - Relationships page to create a new relationship between two tables or to remove relationships between tables.

   1. Click Create.

   2. Select the parent and child tables.

   3. Select the mapping type (one-to-many, one-to-one, or one-to-one with the foreign key on the child table).

   4. Associate the foreign key fields to the primary key fields.

   5. Name the relationship or accept the default name.

   
   

   Note: Only tables that are reachable from the root table can be selected as a parent.

   
   

9. Use the Adapter Configuration Wizard - Object Filtering page to review the object model.

10. Use the Adapter Configuration Wizard - Define Selection Criteria page to further define a SQL query.

    1. In the Parameters section, click Add to add a parameter (for an outbound operation only). To remove a parameter, highlight the parameter and click Remove.

    2. In the SQL section, edit the SQL syntax for an outbound operation (inbound operations syntax is read-only.) Or click Edit to use the Expression Builder to edit both outbound and inbound operations.

    3. Use the Return a Single Result Set option to query multiple related tables with a single SQL SELECT statement and return a single result set.

See Oracle Application Server Adapter for Files, FTP, Databases, and Enterprise Messaging User's Guide for information on the following topics:

• SQL operations as web services

• Polling strategies

• Creating relationships

• Stored procedures and functions

• Performance issues related to returning a single result set

• Other adapters--AQ, File, FTP, JMS, MQ, Oracle Applications, and so on

7.6.2 What Happens When You Configure a Database Adapter

When you import a table, the offline table support of Oracle JDeveloper BPEL Designer creates an offline snapshot of the database table. You can modify this offline version of the table (for example, you can add a foreign key constraint) without affecting the real database table. This creates a TopLink descriptor and associated Java source file for the table, and all the attributes in the descriptor are automapped to their corresponding database columns. The TopLink descriptor maps the Java class to the offline database table.

Most typical data columns are mapped as direct-to-field mappings, meaning that the value in the database column is directly mapped to the attribute. For example, a SALARY column in the database is mapped to a salary attribute in the object model, and that attribute contains the value of that column.

If foreign key constraints are already present in the imported tables, then relationship mappings are autogenerated between the tables. To cover as many scenarios as possible, two mappings are generated for every foreign key constraint encountered: a one-to-one mapping from the source table to the target table, and a one-to-many mapping in the opposite direction. After this is done, you have an Oracle TopLink Workbench project in your BPEL project.

Note: The Java classes that are created as part of the descriptor generation process are never actually deployed with your process or used at runtime. They are present in the design time because Oracle TopLink Workbench is expecting each descriptor to be associated with a Java class. When your process is deployed, the mapping metadata is stored in toplink_mappings.xml.

When you have finished importing tables, you must select a root database table. In doing so, you are actually selecting which TopLink descriptor stores the autogenerated query.

At design time, the following files are generated:

• service_name.wsdl: Contains the database adapter service definition

• RootTable.xsd: The XML type definition of the root object

• toplink_mappings.xml: Contains the TopLink mapping metadata for your BPEL project. It is the only Toplink artifact that is deployed to the server.

Example 7-6 shows the source code for the database adapter WSDL file, Order.wsdl, which is used to write order information to a database in the SOA Order Booking application.

Example 7-6 Source Code for Order.wsdl

<?xml version="1.0" encoding="UTF-8"?>
<definitions
    name="Order"
    targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/db/Order/"
    xmlns:tns="http://xmlns.oracle.com/pcbpel/adapter/db/Order/"
    xmlns:plt="http://schemas.xmlsoap.org/ws/2003/05/partner-link/"
    xmlns:jca="http://xmlns.oracle.com/pcbpel/wsdl/jca/"
    xmlns:pc="http://xmlns.oracle.com/pcbpel/"
    xmlns:top="http://xmlns.oracle.com/pcbpel/adapter/db/top/Order"
    xmlns:hdr="http://xmlns.oracle.com/pcbpel/adapter/db/"
    xmlns="http://schemas.xmlsoap.org/wsdl/">
    <import namespace="http://xmlns.oracle.com/pcbpel/adapter/db/"
            location="DBAdapterOutboundHeader.wsdl"/>
    <types>
        <schema xmlns="http://www.w3.org/2001/XMLSchema">
            <import namespace="http://xmlns.oracle.com/pcbpel/adapter/db/top/Order"
                schemaLocation="Order_table.xsd"/>
        </schema>
    </types>
    <message name="OrdersCollection_msg">
        <part name="OrdersCollection" element="top:OrdersCollection"/>
    </message>
    <portType name="Order_ptt">
        <operation name="merge">
            <input message="tns:OrdersCollection_msg"/>
        </operation>
        <operation name="write">
            <input message="tns:OrdersCollection_msg"/>
        </operation>
    </portType>
    <binding name="Order_binding" type="tns:Order_ptt">
        <jca:binding />
        <operation name="merge">
            <jca:operation
                InteractionSpec="oracle.tip.adapter.db.DBWriteInteractionSpec"
                DescriptorName="Order.Orders"
                DmlType="merge"
                DetectOmissions="true"
                OptimizeMerge="true"
                MappingsMetaDataURL="Order_toplink_mappings.xml" />
            <input>
                <jca:header message="hdr:OutboundHeader_msg" part="outboundHeader"/>
            </input>
        </operation>
        <operation name="write">
            <jca:operation
                InteractionSpec="oracle.tip.adapter.db.DBWriteInteractionSpec"
                DescriptorName="Order.Orders"
                DmlType="write"
                MappingsMetaDataURL="Order_toplink_mappings.xml" />
            <input>
                <jca:header message="hdr:OutboundHeader_msg" part="outboundHeader"/>
            </input>
        </operation>
    </binding>
    <!-- Your runtime connection is declared in
        J2EE_HOME/application-deployments/default/DbAdapter/oc4j-ra.xml
        These 'mcf' properties here are from your design time connection and save you from having
        to edit that file and restart the application server if eis/DB/SOADemo is missing.
        These 'mcf' properties are safe to remove.
    -->
    <service name="Order">
        <port name="Order_pt" binding="tns:Order_binding">
            <jca:address location="eis/DB/soademo"
                UIConnectionName="SOADemo"
            />
        </port>
    </service>
    <plt:partnerLinkType name="Order_plt" >
        <plt:role name="Order_role" >
            <plt:portType name="tns:Order_ptt" />
        </plt:role>
    </plt:partnerLinkType>
</definitions>