Integrating the paging query of WebSphere Adapter for JDBC in IBM Business Process Manager V7.5

WebSphere® Adapter for JDBC runs on IBM® Business Process Manager V7.5 and enables bidirectional communication between databases and applications. Beginning with IBM BPM V7.5, WebSphere Adapter for JDBC added a built-in paging query functionality. Learn how to configure and use the paging query, as well as multiple ways to integrate paging query in IBM BPM.

Share:

Jin Shang, Staff Software Engineer and Technical Support Specialist, IBM BPM Support team, IBM

Photo of Jin ShangJin Shang is a Technical Support Specialist with the WebSphere Adapters and Business Process Manager Technical Support teams at the China Software Development Lab. He has 8 years of experience with the IBM WebSphere Business Integration suite of products.



Xiaocheng Wang (wangxcw@cn.ibm.com), Staff Software Engineer, IBM China

Photo of Xiaocheng Wang Xiaocheng Wang is a Software Engineer and joined IBM in 2008. He has experience in software development, testing, and support. He is interested in software engineering, J2EE, and middleware technology. Xiaocheng is currently focusing on the integration solution development of WebSphere products.



Da Zhang (zdacdl@cn.ibm.com), Software Engineer, IBM China

Photo of Da ZhangDa Zhang is a Software Engineer at the IBM China Development lab. He has worked on WebSphere Integration Developer, IBM Integration Designer, and IBM Business Process Manager for over three years.



19 September 2012

Also available in Chinese Portuguese

Introduction

WebSphere Adapter for JDBC (hereafter called JDBC Adapter) is a powerful database resource adapter provided by IBM Business Process Manager V7.5 (IBM BPM). An application running on IBM BPM enables bidirectional communication with any database via the JDBC Adapter. Specifically, it enables J2EE project and serviced-oriented architecture (SOA) project to exchange data with IBM BPM in the format of a business object.

Database query is one of the basic functions of the JDBC Adapter. In practice, it may suffer poor performance when dealing with large result sets. Based on different database productions, a range of paging query techniques has been developed to improve the efficiency of database query operations. It is common practice to page through the result set to return the query results in a manageable size. Starting in Version 7.5, JDBC Adapter provides a built-in facility for paging query that allows you to handle large result sets in a more efficient manner. This article explores a business process solution that helps you use paging query in IBM BPM.

You will need the following products to follow the steps in the article:

  • IBM WebSphere Adapter for JDBC V7.5
  • IBM Business Process Manager V7.5
  • IBM Integration Designer V7.5

Paging query of WebSphere Adapter for JDBC

In this section, we will introduce the practical use of paging query through a sample business scenario that uses JDBC Adapter.

Sample business scenario

In order to maintain billing information, a developer has created a table in a DB2® database. As shown in Listing 1, there are four columns designed to store the billing details.

Listing 1. Billing information table
CREATE TABLE Bill
(
	ID         INTEGER NOT NULL PRIMARY KEY,
	PERSONID   VARCHAR(50),
	BILLDATE   TIMESTAMP,
	AMOUNT     DOUBLE
);

Within the system, the necessary steps to retrieve information include sorting the billing information by BILLDATE, then querying through all the records until the specific record is found. The whole process may become more expensive and slower as the business grows. However, given that the billing occurs within a specific time period, for example, within one specific day instead of dealing with entire tables, the more efficient way is to use paging query to query a specific part of the records.

Limitation of the existing query function

RetrieveAll is one of the basic database operations provided in the JDBC Adapter outbound process. It allows you to specify the search criteria to retrieve specific records. Prior to Version 7.5, RetrieveAll did not support paging query. You had to implement it in your own code, or by optimizing the database retrieve behavior. However, both approaches are too tedious and inflexible to meet various business requirements.

Paging query mechanism

Staring with Version 7.5, JDBC Adapter provided a built-in capability of paging query by adding three InteractionSpec attributes for the RetrieveAll operation:

  • enablePaging: RetrieveAll will do a paging query when enablePaging is set to true. By default, it is set to false, and the JDBC Adapter performs the query through all the records.
  • pageSize: This specifies the size of the returned resultsets.
  • startIndex: This specifies the start position for a paging query. For example, startIndex=100 and pageSize=50 mean a paging query will return 50 records, starting at the 101th record.

In the pre-configuration wizard, these attributes could also be set dynamically when JDBC Adapter is running on IBM BPM, which supports dynamically changing the InteractionSpec attributes. Thus, you can run their queries against specific target by changing the values of pageSize and startIndex.


Configuring WebSphere Adapter for JDBC

You can complete the configuration of JDBC Adapter in IBM Integration Designer (hereafter called Integration Designer).

Generate components for the outbound process

The sample requires you to first create one JDBC Adapter outbound component. You can create it using the following steps:

  1. You first need to import JDBC Adapter. In IBM Integration Designer, under the Business Integration perspective, select File > New > External Service. In the "Select the Service Type or Registry" view, expand Adapter, and select JDBC. Then select IBM WebSphere Adapter for JDBC (IBM: 7.5.0.0) in the "Select an Adapter" view.
  2. Next, use the JDBC Adapter Outbound wizard to create a service to access the database system. To import the required JDBC drivers, select add JDBC drivers in the "Locate the Required Files and Libraries" view. In this sample, we use DB2, which requires db2jcc.jar and db2jcc_license_cu.jar. Then, in the "Select the Processing Direction" view, select Outbound.
  3. In the "Specify the Discovery Properties" view, enter the database name, IP address, user name, and password.
  4. In the "Specify the Enterprise Properties" view, click the Run Query button. All the database schema is listed in the "Discovered Objects" table. Then select the target table and add it into the Selected objects list.
  5. Now specify the database operation and its properties. In the Specify Composite Properties view, as shown in Figure 1, make sure the list of "Operations for selected business objects" includes the RetrieveAll operation. To specify the operation attributes, click the Advanced button, expand RetrieveAll Configuration properties, and specify the default values for paging the queue properties.
    Figure 1. Composite Properties View
    Composite Properties View

    Here, select Return specified number of records per page beginning with the starting index (enablePaging=true). Then set 0 in the "Starting index to return records" field, and 10 in the "Number of records per page" field (startIndex=0, pageSize=10). Note that the Generate a business graph for each business object must be checked. In the runtime environment, you need the properties of the business graph to change the value of startIndex and pageSize.

  6. Here, use the default settings for the database connection. In the "Specify the Service Generation and Deployment Properties" view, select Using security properties from the managed connection factory, and deselect Join the global transaction. Then, in the dropdown menu of Database connection information, select Specify local database connection information.
  7. Click Finish. The JDBC Adapter Outbound component is shown in the Assembly Diagram, as shown in Figure 2.
    Figure 2. Assembly Diagram
    Assembly Diagram

Sort query results

You have now have finished the JDBC Adapter configuration from the above section. In a real business scenario, in order to improve query efficiency, a developer always sorts query results by certain fields in an ascending or descending order. In this sample, the business scenario requires results to be ordered by BILLDATE. This can be implemented by using the following steps:

  1. Under the Business Integration perspective, expand Data and you will see one business object, which was automatically generated for the bill table. Select the business object and open it in Business Object Edit view. Right-click the billdate element and select Show In-Properties View.
  2. In the Properties view, open the Application info tab, select JDBCAttributeTypeMetadata, select New-OrderBy, and then set DESC (or ASC) in the text field. The query results will be sorted by billdate in descending (or ascending) order.

Integrating paging query through different ways

Since IBM BPM V7.5 supports dynamic changes of the InteractionSpec attributes, there are various ways in which you can integrate paging query with existing projects under the runtime environment. In this article, we will introduce three approaches:

Integrate with the Java code

  1. In Integration Designer, create a Java component in the Assembly Diagram, as shown in Figure 3. Then create a new interface and import it into the Java component.
    Figure 3. Add a Java component in the Assembly Diagram
    Add a Java component in the Assembly Diagram
  2. In the interface edit view, add Request Response Operation, and define four input parameters to specify the values for the InteractionSpec attributes and the query criterion. Then define one output parameter for the returning query results, as shown in Figure 4.
    Figure 4. Edit interface
    Edit interface
  3. In the Assembly Diagram, create a wire between Java component and the JDBC Adapter Outbound component, as shown in Figure 5.
    Figure 5. Wire Java component and JDBC Adapter component in the Assembly Diagram
    Wire Java component and JDBC Adapter component in the Assembly Diagram
  4. Double-click the Java component to implement it. Then replace the operation1 method with the code as shown in Listing 2.
    Listing 2. Implementation code for the Java component
    public DataObject operation1(Boolean enablePaging, Integer pageSize,
            Integer startIndex, DataObject inputBusinessObject) {
            DataObject response = null;
    		try
    		{
               DataObject props = inputBusinessObject.createDataObject
                ("properties");
                props.setBoolean("ISenablePaging", enablePaging);
                props.setInt("ISpageSize", pageSize);
                props.setInt("ISstartIndex", startIndex);
                response = (DataObject) this.locateService_
                 JDBCOutboundInterfacePartner().invoke
                 ("retrieveallDb2adminBillBG", inputBusinessObject);
            }
             catch (Exception ex)
            {
             ex.printStackTrace();
            }
            return response;
       }

    You have completed the development and configuration of the project. To test it in Integration Designer, first deploy the project on to IBM BPM. Then, in the "Universal Test Client" view, enter the values in the startIndex field and the pageSize field. As shown in Figure 6, the JDBC Adapter Outbound component has returned the required records in the response.

    Figure 6. Test result
    Test result

In the runtime environment, you can also dynamically change the values for startIndex and pageSize in their code in each invocation of the above Java component. For example, set startIndex=0 and pageSize=10 to return the first ten (0-9) resultsets in the first invocation, and set startIndex=20 and PageSize=10 to return the next ten (10-19) matched items in the second invocation. This way, a full paging query capability is achieved.

Integrate with a web service

  1. In Integration Designer, open the Assembly Diagram, then create an Export component and wire it with the JDBC Adapter Outbound component, as shown in Figure 7.
    Figure 7. Add an Export component in the Assembly Diagram
    Add an Export component in the Assembly Diagram
  2. Right-click on Export component and select Generate Binding > Web Service Binding, as shown in Figure 8.
    Figure 8. Generate web service binding
    Generate web service binding
  3. In the Web Service Export Configuration wizard, use the default settings and click Finish, as shown in Figure 9.
    Figure 9. c
    Generate web service binding
  4. Now, you can deploy the project on to IBM BPM. Through the Web Service Binding address, as shown in Figure 10, you can perform all the operations that are provided in the JDBC Adapter Outbound component.
    Figure 10. Web Service Binding address
    Web Service Binding address
  5. In this sample, you will use the Firefox® plug-in, REST Client, to test the project, as shown in Figure 11.
    Figure 11. REST Client for Firefox
    REST Client for Firefox
  6. As shown in Listing 3, there are preset values for the startIndex, pageSize, and enablePaging attributes.
    Listing 3. Soap message
    <?xml version="1.0" encoding="UTF-8"?>
    <soap:Envelope xmlns:ns1="http://docs.oasis-open.org/wss/2004/01/
     oasis-200401-wss-wssecurity-secext-1.0.xsd" 
     xmlns:ns3="http://DB2_RetrieveAll_Paging_Test/JDBCOutboundInterface" 
     xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <soap:Header>
        <ns1:Security xsi:type="ns1:SecurityHeaderType">
          <ns1:UsernameToken xsi:type="ns1:UsernameTokenType">
            <ns1:Username/>
            <ns1:Password xsi:type="ns1:PasswordString"/>
          </ns1:UsernameToken>
        </ns1:Security>
      </soap:Header>
      <soap:Body>
        <ns3:retrieveallDb2adminBillBG>
          <ns3:retrieveallDb2adminBillBGInput>
            <properties xmlns="http://www.ibm.com/xmlns/prod/websphere/bo/6.0.0">
                <ISstartIndex xmlns:ns0="http://www.w3.org/2001/XMLSchema" 
                 xmlns="##ONTHEFLY" xsi:type="ns0:int">50</ISstartIndex>
                <ISpageSize xmlns:ns0="http://www.w3.org/2001/XMLSchema" 
                 xmlns="##ONTHEFLY" xsi:type="ns0:int">10</ISpageSize>
                <ISenablePaging xmlns:ns0="http://www.w3.org/2001/XMLSchema" 
                 xmlns="##ONTHEFLY" xsi:type="ns0:boolean">true</ISenablePaging>
            </properties>
            <verb>Create</verb>
            <Db2adminBill>
              <personid>Beckham</personid>
            </Db2adminBill>
          </ns3:retrieveallDb2adminBillBGInput>
        </ns3:retrieveallDb2adminBillBG>
      </soap:Body>
    </soap:Envelope>
  7. As shown in Figure 12, the message payload has included the query records that are sorted by the BILLDATE in descending order.
Figure 12. Test result
Test result

Integrate with BPEL

  1. In Integration Designer, open the Assembly Diagram, create a Process component (BPEL). Then create a new interface and import it into the Process component, as shown in Figure 13.
    Figure 13. Add a BPEL component in the Assembly Diagram
    Add a BPEL component in the Assembly Diagram
  2. In the Interface edit view, add Request Response Operation and define two input parameters to specify the values for pageSize and pageIndex. Then define one output parameter for the returned query results, as shown in Figure 14.
    Figure 14. Edit Interface for the BPEL component
    Edit Interface for the BPEL component
  3. In the Assembly Diagram, create a wire between the Java component and the JDBC Adapter Outbound component, as shown in Figure 15.
    Figure 15. Wire the BPEL component and JDBC Adapter component in the Assembly Diagram
    Wire the BPEL component and JDBC Adapter component in the Assembly Diagram
  4. Double-click the Process component to implement the business logics within it, as shown in Figure 16.
    Figure 16. Business logic within the BPEL
    Business logic within the BPEL
  5. In the BPEL edit view, create a Snippet activity in which you will implement the Java codes to set the values for attributes of pageSize and pageIndex, as shown in Listing 4.
    Listing 4. Codes in the Snippet activity
    commonj.sdo.DataObject __result__1;
    {// create Db2adminBillBG
    	com.ibm.websphere.bo.BOFactory factory =
    	   (com.ibm.websphere.bo.BOFactory) new com.ibm.websphere.sca.
             ServiceManager().locateService("com/ibm/websphere/bo/BOFactory");
    	 __result__1 = factory.create("http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/
            db2adminbillbg","Db2adminBillBG");
    }
    retrieveallDb2adminBillBGInput = __result__1;
    commonj.sdo.DataObject props = retrieveallDb2adminBillBGInput.createDataObjectj
     ("properties");
    props.setBoolean("ISenablePaging", true);
    props.setInt("ISpageSize", pageSize);
    props.setInt("ISstartIndex", pageIndex);
  6. Then, create an Invoke activity to invoke retrieveAllDb2adminBillBG of JDBCOutBound. Now, you have completed the development of the project. After deploying it on IBM BPM, test it in Integration Designer by doing test on the interface defined in Step 5 above. As shown in Figure 17, you can see the query result sets in the response message. For more details, refer to the sample code provided with this article.
Figure 17. Test result
Test result

Conclusion

Paging query is an important feature of WebSphere Adapter for JDBC V7.5. It not only improves the performance of the database query, but also supports changing the size of the returned resultsets dynamically at runtime. To help you understand and use it, this article introduced three different ways of integrating paging query in IBM BPM V7.5.

In practice, the business scenarios might be more complicated. The JDBC Adapter paging query also provides various advanced featured, for example, doing a paging query against database views and performing more complicated queries by using the built-in QueryBO function. For more details, see the WebSphere Adapter Information Center.


Download

DescriptionNameSize
Project interchange fileProjectInterchange.zip39KB

Resources

Learn

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Business process management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Business process management, WebSphere
ArticleID=835623
ArticleTitle=Integrating the paging query of WebSphere Adapter for JDBC in IBM Business Process Manager V7.5
publish-date=09192012