JDBC OUTBOUND ADAPTER SAMPLE USING IBM WPS

Introduction:

         WebSphere® Adapter for JDBC, you can create integrated applications that can interact and exchange of information with a database. By using the adapter, an application can send requests to the database, as well as receive events from the database, often without the need for SQL code.

The adapter enables two-way communication between an application running on WebSphere Process Server or WebSphere Enterprise Service Bus and a database. Using the adapter, an application can send requests to read, create, modify, or delete data in a database, in many cases without writing any SQL code.

The adapter supports integration of databases that are accessible through the JDBC application programming interface (API) with applications running on WebSphere Process Server or WebSphere Enterprise Service Bus. The adapter provides outbound and inbound processing under the Java 2 Platform, Enterprise Edition (J2EE) Connector Architecture (JCA) and integrates with Service Component Architecture (SCA) components.

Outboundprocessing enables an application to access or modify data in a database. The adapter converts a request from the application to an outbound operation, which it runs to create, retrieve, update, or delete data in the database or to run a database program stored in the database. Processing these requests results in the creation, retrieval, update, or deletion of rows in the corresponding database tables.

The adapter also enables you to run stored procedures or stored functions that are defined in the database, and to run user-defined SELECT, INSERT, UPDATE, and DELETE statements. You can use the adapter to integrate multiple applications with the same database.

Overview:

          An application running in WebSphere Process Server or WebSphere Enterprise Service Bus invokes a service in an outbound module, which sends a request to the adapter to process one or more business objects. The adapter uses the JDBC API to connect to the database server, which accesses the tables and other objects in the database.

Here we are going to insert, update, and delete rows from OrderInfo table by using JDBC adapter.

Software Requirements:

•    WebSphere Integration Developer version 7.0.0.0
•    WebSphere Process Server version 7.0.0.0
•    WebSphere Adapter for JDBC version 7.0.0.0
•           JDBC Driver for Oracle.
•   Oracle 10g.

Implementation:

The following activities need to complete for implement outbound adapter using IBM WPS.

•          Create tables.
•    Create an authentication alias
•    Create a data source
•    Implement JDBC outbound Adapter
•   Implement Module.
•    Test the application.

Create table:

           We must create the following OrderInfo in the Oracle database before starting the scenario.

CREATE TABLE ORDERINFO ( ORDERID VARCHAR2(10) NOT NULL PRIMARY KEY,
ORDERNAME VARCHAR2(10) ,
CUSTOMERNAME VARCHAR2(20) ,
ADDRESS VARCHAR2(20) ,
STATUS VARCHAR2(15),
ORDERDATE TIMESTAMP(6),
STATUSUPDATE TIMESTAMP(6)) ;

The table format will be look like below.

Create an authentication alias:

The authentication alias needs to be set because the data source that is used to generate artifacts will use the username and password set in the authentication alias to connect to the database.

Follow these steps to set the authentication alias in the WebSphere Process Server administrative console.

Start the server, log into admin consol. Select Security àGlobal Security.

On the right, click J2C Authentication Data under Java Authentication and Authorization Service.

Click New to create a new authentication entry. Type the alias name, and username and password to connect to the database. Click OK

Click Save to save the changes.

Create the data source:

Create a data source in WebSphere Process Server, which the adapter will use to connect to the database. This data source will be used later when generating the artifacts for the module.

In the administrative console, select Environment àWebSphereVariables.

On the right, click ORACLE_JDBC_DRIVER_PATH and specify the path of the ojdbc6.jar file in the Value field. Click OK.

Click Save to save the changes. The variable is added and appears in the list.

Select Resources à JDBC àJDBC Providers.

Click New in the JDBC provider’s window. In the Create new JDBC provider page, select an Oracle database with a XA data source for the Oracle JDBC driver. Click Next.

In the Enter database class path information page, enter the following value in the Class path field:

$(ORACLE_JDBC_DRIVER_PATH)/ojdbc6.jar, where

$(ORACLE_JDBC_DRIVER_PATH) is library path for the run time.

Then click on next.

Then click on finish and save the changes.

Click the Oracle JDBC provider you just created. Under Additional Properties, click Data sources. Click New.

Type any value in the JNDI name field, and select the authentication alias. Click Next.

Provide the appropriate URL value and select a data store helper class name from the Data store helper class name list as shown in the following figure. Click Next.

Select the authentication alias you just created from the Component-managed authentication alias list, and click Next.

The Summary of the values entered for the data source is displayed. Click Finish.

Click Save to save the changes. Select the check box corresponding to the data source you created in the previous step and click Test connection.

Implement JDBC outbound Adapter:

Switch to the Business Integration Perspective in WebSphere Integration Developer by selecting Window -> Open Perspective Business Integration.

Start the external service wizard by selecting File-> New –> External Service.

In the Available Types area, select Adapters > JDBC and then click Next.

Select the IBM WebSphere Adapter for JDBC (IBM: 7.0.0.0) and click Next.

In the Connector project field enter CWYBC_JDBC, and in the Target runtime environment field, select the WebSphere Process Server v7.0 runtime. Click Next.

In the JDBC driver JAR files field, click Add, to add the JDBC driver class to connect to the database. Browse to select the driver JAR file and click Next.

Select Outbound and click Next.

Set connection properties for the external service wizard

To connect to the Oracle database:

1. Expand the Oracle node from Database system connection information then select 10.
2. Enter System ID, Host name, Port number, User name and Password fields, and then click Next.

Select the business objects to be used with the adapter

Follow these steps to select the OrderInfo business object:

In the Find Objects in Enterprise System window, click Edit Query.

1.      In the Specify the Query Properties window, select the Prompt for additional configuration settings when adding business objects check box and click OK.

      Click Run Query.

             Expand the SYSTEM (for this tutorial only) node, select Tables and expand it.

Select the ORDERINFO table and click > In the Specify the Configuration Properties for ‘ORDERINFO’ window, click OK.

Click on Next.

Generating business object definitions and related artifacts follow these steps to generate the business object definitions. In the Specify Composite Properties window, accept the default values for all fields and click Next.

In the Specify the Service Generation and Deployment window, perform the following steps:

                      a) Select Other for security options under Deployment Properties.

                      b) Clear the Join the global transaction check box.

             c) Select Specify predefined connection pool DataSource from the Database connection information list.

                     d) Enter OracleDSin the Connection pool DataSource JNDI Name field, and click Next.

Click New in the Specify the Location Properties window. In the Select a Business Integration Project Type window, select Module and click Next. Then click on finish.

Expand the created Business Integration Project and verify whether the artifacts are generated correctly.

Implement Module:

Create a Business Object called ‘OrderInfoBO’ under ‘EAI_JDBCOutboundModule’.

Create a Business Object called ‘RequestBO’ as shown below.

Create an Interface called ‘OrderService’ under ‘EAI_JDBCOutboundModule’.

Create a Business Process called ‘BP_OrderProcess’ and implement look like below.

Drag and drop the BP_OrderProcesss to Assembly diagram, map with ‘JDBCOutboundInterface’ import and export as Web Service. It should be look like below.

Test the application:

Now we have completed implementation of JDBC adapter, and module. We need to test the application and need to verify the Data base.

Deploy the ‘EAI_JDBCOutboundModule’ into WebSphere Process Server.

Create a Project in SOAP UI and import WSDL file. Expand the WSDL file and OrderInfo Request1. Enter the input as shown below and click on submit request.

We have tested for INSERT into OrderInfo table. We have got the below response from application.

We have to validate the insertion happened into OrderInfo table or not. Now log into Oracle DB and run the following Query.

Select * from ORDERINFO;

We can see there will be one row inserted into Orderinfo table.

Similar way we can test for update, delete and retrieve data from OrderInfo table.

Conclusion:

Here we learnt how to implement an outbound adapter and the usage of outbound adapter. The adapter enables two-way communication between an application running on WebSphere Process Server or WebSphere Enterprise Service Bus and a database. Using the adapter, we can to read, create, modify, or delete data in a database. The adapter also enables you to run stored procedures or stored functions that are defined in the database, and to run user-defined SELECT, INSERT, UPDATE, and DELETE statements. You can use the adapter to integrate multiple applications with the same database.

Downloads:

File Name	Description	Size	Download
JDBC Adapter.zip	Sample JDBC Outbound Adapter  implementation	28 KB	Download
JDBC Outbound Adapter.pdf	JDBC Outbound Adapter  in WPS	850 KB	Download

continue reading

WS-Security and Transport Layer Security in IBM Business Process Manager v8.0

Introduction:

This article describes how to protect a web service using transport level security and how to authenticate using the WS-Security Username token. Here we are going to learn WS-Security and Transport Layer Security (TLS) when working with outbound integrations using the web services integration component in IBM® Business Process Manager V8.0.

Overview:

This article will describe how authentication is enabled between two services by using the Username token. It will also describe the use of Secure Sockets Layer (SSL) to transport SOAP messages between a client and a web service over HTTP. IBM Business Process Manager V8.0 provides a web services integration component that enables IBM BPM services to invoke external web services. The integration component provides the support to discover web services, generate IBM BPM data types corresponding to WSDLs, and exchange SOAP messages with remote services.

Web services invocation needs security. Commonly, this is achieved by using Transport Layer Security (TLS) or Secure Sockets Layer (SSL) to secure request and response message exchanges. This approach has some limitations, for example, when using an intermediary to route messages. In this case, because the intermediary will need to decrypt data, it would help to secure message parts independent of transport security. Because WS-Security protocol helps you encrypt SOAP messages, it enhances security when routing messages through intermediaries.

Implementation:

In this article, we will learn at how to secure web services requests from IBM BPM using the web services integration component. The below figure illustrated an instance of the web services requester and a web services provider, each configured to support transport and message-level security.

The following steps to are require to complete WS-Security in IBM BPM.

• Set up the keystores.
• Create Service Provider Sample.
• Configure Transport Layer
• Create an IBM BPM sample.
• Test the Application.

Set up the keystores:

In this section we will set up two keystores, one to be used by IBM BPM and the other by the web services provider. We will use those keystores to contain the personal certificate as well as the signer certificates (public keys) of the interaction partner. In addition, we will use the keystore in our configuration to support SSL as well as message-level security. To create a keystore for IBM BPM, complete the following steps:

Run ‘ikeyman.bat’ under IBM BPM installation directory /bin

Select Key Database File => New.

Select JKS as the key database type; specify IBMBPMKey.jks as the file name, and choose an appropriate directory for the key file,

At the password prompt enter a password of your choice, Here I enter password ‘Ibm@key’ and click OK.

Now we need to create a new self-signed certificate. This represents a personal certificate and contains a public and private key pair. The private key is used by IBM BPM to sign the outbound request message and to decrypt the incoming response message.

The public key will be extracted as the signer certificate and imported into the trust store of the web services provider. This enables the web services provider to verify the digital signature is also used for encrypting the outbound request message. In addition, by adding the IBM BPM public key to the trust store of the web services provider, it can be used to authenticate IBMBPM during SSL handshake (client authentication).

Select Create à New Self-Signed Certificate and specify the following:

Key label: IBMClientCertificate

Common Name: amir-eai.blogspot.in

Organization: EAI

Then click on OK.

Selecting Extract Certificate, then specify the following:

Data type: Base 64 encoded ASCII data

Certificate file name: IBMClientCertificate.arm

Location: D:\Keys (or any directory of your choice)

Then click on Ok.

To create a keystore for Web Service Provider, complete the following steps:

Repeat the steps in Create the keystore for IBM BPM to create a keystore for the web services provider.

Service Provide Key name : ServiceProviderKey.jks

Password : Eai@key

Self-signed certificate

Key label: SPCertificate

Common Name: amir-eai.blogspot.in

Organization: EAI

Service Provider Extracted certificate info:

Data type: Base 64 encoded ASCII data

Certificate file name: SPCertificate.arm

Location: D:\Keys (or any directory of your choice)

Now you're ready to exchange the certificates and import them into the respective truststores: IBMClientCertificate.arm will be added to ServiceProviderKey.jks and SPCertificate.arm to IBMBPMKey.jks. Remember that for the purpose of this article we're using the same file for both the keystore and the trust store.

1.Using ikeyman, open ServiceProviderKey.jks and select Signer Certificates under Key database content. Select Add and specify as shown below:

Click OK and enter a label of IBMClientCertificate.

Open IBMBPMKey.jks and select Signer Certificates under Key database content. Select Add and specify as shown below:

Click OK and specify a label of SPCertificate.

Create Service Provider Sample:

Launch SoapUI and create SOAP UI project called ‘CustomerService’ with initial ‘CustomerService’ WSDL then click on OK.

Right click on ‘CustomerServiceSoapBinding’ and select MockService.

Enter the Path, Port as shown below then click on OK.

Give the MockService name as ‘CustomerServiceSoapBinding MockService’ and click on OK.

Double-click Response1 for getCustomer to open the SOAP Response window and enter the following code, as shown below.

Configure Transport Layer:

In this section we will learn to perform steps required to enable SSL (HTTPS) between IBM BPM and the web services provider.

First we will configure for IBM BPM.

Log in to the Web Sphere Integrated Solutions Console.

Select Security => SSL certificate and key management. Select Key stores and certificates.

Click New to define a new keystore. This keystore will use the IBMBPMKey.jks that we created earlier using ikeyman. Enter the information as shown below Figure and click OK:

Select Security => SSL certificate and key management, and then select SSL Configurations.

Click New to create a new SSL configuration and enter the following information, as shown in below Figure. Click on ‘Get Certificate aliases’ near keystore name. It will load the keystore.

Click OK and save to the master configuration.

Now we need to create a dynamic outbound SSL configuration. To do this, select Security => SSL certificate and key management and select Dynamic outbound endpoint SSL configuration, then click New to create a new configuration entry. Specify the following information, as shown below Figure.

Click OK and save to the master configuration. Restart IBM BPM

Now we will configure web services provider to enable communication using SSL.

Open SOAP UI select File => Preferences => SSL Settings. Specify the information, as shown in below figure.

Click OK. Now the web services provider can accept SSL requests on port 8444.

Create an IBM BPM sample:

Open IBM Process Designer. Create a process application called ‘CustomerProcessApp’. Open the application.

Create Integration Service and name as ‘WSTesterIS’.

Open ‘WSTesterIS’ integration Service, drag and drop Web Service Integration activity from common pallet. Rename as ‘Invok WebService’ wire from start to end.

Go to the Properties tab of Invok WebService, and under Implementation => Discovery, enter the following for the WSDL URI http://<hostname where SOAP UI is running>:8088/mockCustomerServiceSoapBinding?wsdl. then click on Discover and select operation as getCustomer(String). We need to generate Business Object for the WSDl. Click on Generate types and click on next and Finish.

Click on Variable section create Input variable called ‘CustomerID’ and Output variable ‘Customer’ type of CustomerDetails.

Select Data Mapping under Properties. Enter Input Mapping as “C123” and Output Mapping as tw.local.Customer.

Save the changes. Now we are completed IBM BPM application creation.

Test the Application:

Open Soap UI project, Right click on ‘CustomerServiceSoapBinding MockService’ and select Restart.

Switch to IBM Process Designer, Open ‘WSTesterIS’ click on ‘Debug Service’

Now it will open ‘IBM Business Process Manager Service Debug’ click on step to display result.

Now it will display the response from Service provider which we have created in SOAP UI response mock service.

Web services requests and responses are also logged in the web services provider. We can find the details in the mock services message log as shown below Figure.

Conclusion:

In this article, we learned how we can configure IBM BPM in order to enable WS-Security and Transport Layer Security when interacting with secure web services using the web services integration component. Using a tool such as SOAP UI, we can easily verify the configuration independent of the availability of an actual web-service provider

Download:

File Name	Description	Size	Download
WS-Security. zip	Sample Code	780 KB	Download
WS-Security in IBM BPM.pdf	Develop WS security in IBM BPM	1 MB	Download

continue reading