Hi,
Today we will work on 'DBAdapter OSB Application' which will use credit card number as input parameter and based on that fetches the customer information from the database.
Before we proceed for the tutorial, you must install JDeveloper 12c and Oracle Database 11g/12c as a pre-requisite on your system.
For download of JDeveloper 12c, refer to the below link:
http://www.oracle.com/technetwork/developer-tools/jdev/downloads/index.html
Also, for installation and certification matrix of JDeveloper 12c, refer to the links provided below:
Certification Matrix: http://www.oracle.com/technetwork/developer-tools/jdev/documentation/121300-cert-2164864.html
Installation Steps: https://docs.oracle.com/middleware/1213/jdev/install/toc.htm
Once JDeveloper 12c is installed, we can begin with the steps for creating 'DBAdapter OSB Application'.
Today we will work on 'DBAdapter OSB Application' which will use credit card number as input parameter and based on that fetches the customer information from the database.
Before we proceed for the tutorial, you must install JDeveloper 12c and Oracle Database 11g/12c as a pre-requisite on your system.
For download of JDeveloper 12c, refer to the below link:
http://www.oracle.com/technetwork/developer-tools/jdev/downloads/index.html
Also, for installation and certification matrix of JDeveloper 12c, refer to the links provided below:
Certification Matrix: http://www.oracle.com/technetwork/developer-tools/jdev/documentation/121300-cert-2164864.html
Installation Steps: https://docs.oracle.com/middleware/1213/jdev/install/toc.htm
Once JDeveloper 12c is installed, we can begin with the steps for creating 'DBAdapter OSB Application'.
STEPS FOR DBADAPTER OSB APPLICATION
Step-1: Create database table named as 'CreditCardInfo' same as below image:
Insert few dummy records for testing purpose into the 'CreditCardInfo' table similar to the image given below:
Step-2: Creation of Datasource and DBAdapter configuration in Weblogic 12c.
Login to Weblogic 12c console using admin credentials.
On Home page within services, click on Data Sources.
In configuration tab, goto Data Sources table and navigate to: New->Generic Data Source
Enter JDBC Data Source Properties as:
then click Next.
Select Database Driver from the dropdown and click Next.
Accept the default values on the next screen and click Next.
Enter the database connection properties and click Next.
Click on Test Configuration button to test database connectivity and once confirmed click Next.
Select server and click Finish.
Check and verify the DataSource created.
Now on the left side, click on Deployments in the Domain Structure pane and then click on DBAdapter in Deployments table.
Navigate to: Configuration->Outbound Connection Pools and then click on New in Outbound Connection Pool Configuration Table.
Select javax.resource.cci.ConnectionFactory as Outbound Connection Group and click Next.
Enter JNDI name for Outbound Connection Instance as 'eis/DB/HRConnection' and click Finish.
Check for the Outbound Connection creation and click on it.
Enter the value for the below field and press enter and then click on Save.
XADataSourceName: jdbc/HRConnection
Navigate to: Domain Structure->Deployments and select the DBAdapter checkbox. Click on Update button in Deployments table.
Accept default value on next screen and click Next.
Click Finish.
We will see below screen with a success message on the top.
Step-3: Creating Service Bus Application with Service Bus Project.
Enter Application name as 'DBAdapterSBApp'.
Enter Project name as 'DBAdapterSBProject' and click Finish.
Step-4: Create Folder hierarchy for the DBAdapterSBProject.
Enter Folder name as Services and click OK.
Again, right click on Services folder and create two nested folders named as Business and Proxy in a similar fashion.
Similarly, create folders for JCA, Pipelines, Schemas, WSDLs and XSLTs in DBAdapterSBProject.
Step-5: Configure Database Adapter.
Click to open servicebus.sboverview file (DBAdapterSBProject Composite).
Drag and Drop Database Adapter Technology in External Services lane.
In Database Adapter Reference wizard, enter Name as CreditCardInfoRef and click on Magnifying glass to select a directory for Service Directory Field.
Navigate to: Services->Business in Choose Directory wizard.
Click Next.
Click on Plus icon to create a new database connection in Service Connection screen.
Enter connection details then Test connection and click OK.
Enter JNDI Name as: eis/DB/HRConnection and click Next.
Select Operation Type: Perform an Operation on a Table and click on Select checkbox. Click Next.
On next screen, click on Import Tables button.
Click on Query button and shuffle the CreditCardInfo table from Available to Selected pane. Click OK.
Click Next.
Select CCNUMBER as the primary key and click Next and then again click on Next.
In Attribute Filtering window, select the attributes you want as response from this service and then click Next.
In Define Selection Criteria screen, click on the Add button next to Parameters filed and enter Parameter Name as ccnumber then click OK.
Click on Edit button next to SQL field.Again click on Add button on top of the Expression Builder screen. Click on Edit button within First Argument and choose Query Key as ccnumber. Finally click OK.
Select Parameter radio button in Second Argument and then click OK.
Click Next and Next till Finish.
Our DBAdapterSBApp would look something like below image:
Now, right click on CreditCardInfoRef_db.jca file and navigate to: Refactor->Move
Click on the Magnifying glass next to Move To filed and Select JCA folder as the destination directory for the CreditCardInfoRef_db.jca file.
Similarly, move xsd and wsdl file's too in their respective directories.
Now, open CreditCardInfoRef.wsdl file and change schemaLocation value to point to the correct schema file.
Similarly, open CreditCardInfoRef-concrete.wsdl file and change Location value to point to correct WSDL.
Now, Navigate to: Build->Clean All.
Navigate to: Build->Make All.
Now our DBAdapterSBApp would look like below image:
Step-6: Configure Proxy Service to access the DBAdapter.
Right click on the Proxy Services lane of the servicebus.sboverview (DBAdapterSBProject Composite) and navigate to: Insert Transports->HTTP
In the Create Service wizard, enter below fields as:
Set Service Type to WSDL-based service selecting the WSDL radio button and then click on Browse WSDLs button next to WSDL radio button.
Select CreditCardInfoRef-concrete.wsdl and click OK.
Click Next.
Accept Default in Transport screen and click Finish.
Now, right click on CreditCardInfoPSPipeline.pipeline file and navigate to: Refactor->Move
Move the CreditCardInfoPSPipeline.pipeline to Pipelines folder.
Connect CreditCardInfoPSPipeline in Pipelines/Split Joins lane to CreditCardInfoRef in External Services lane.
Click on Yes.
Our servicebus.sboverview (DBAdapter Composite) will now looks like the below image:
Step-7: Testing the DBAdapterSB service.
Right click on CreditCardInfoPS in Proxy Services lane of the servicebus.sboverview (DBAdapter Composite) and navigate to: Run
Enter the ccnumber as the Payload input and click on Execute button.
Cross check Request and Response Document to verify.
That's All Amigos, Cheer up :-)
Insert few dummy records for testing purpose into the 'CreditCardInfo' table similar to the image given below:
Step-2: Creation of Datasource and DBAdapter configuration in Weblogic 12c.
Login to Weblogic 12c console using admin credentials.
On Home page within services, click on Data Sources.
In configuration tab, goto Data Sources table and navigate to: New->Generic Data Source
Enter JDBC Data Source Properties as:
- Name: jdbc/HRConnection
- JNDI Name: jdbc/HRConnection
- Database Type: Oracle
then click Next.
Select Database Driver from the dropdown and click Next.
Accept the default values on the next screen and click Next.
Enter the database connection properties and click Next.
Click on Test Configuration button to test database connectivity and once confirmed click Next.
Select server and click Finish.
Check and verify the DataSource created.
Now on the left side, click on Deployments in the Domain Structure pane and then click on DBAdapter in Deployments table.
Navigate to: Configuration->Outbound Connection Pools and then click on New in Outbound Connection Pool Configuration Table.
Select javax.resource.cci.ConnectionFactory as Outbound Connection Group and click Next.
Enter JNDI name for Outbound Connection Instance as 'eis/DB/HRConnection' and click Finish.
Check for the Outbound Connection creation and click on it.
Enter the value for the below field and press enter and then click on Save.
XADataSourceName: jdbc/HRConnection
Navigate to: Domain Structure->Deployments and select the DBAdapter checkbox. Click on Update button in Deployments table.
Accept default value on next screen and click Next.
Click Finish.
We will see below screen with a success message on the top.
Step-3: Creating Service Bus Application with Service Bus Project.
Open JDeveloper 12c and click on 'NewApplication' in the Application Window pane.
From New Gallery Wizard within Categories pane goto:
General->Applications->Service Bus Application with Service Bus Project
Enter Project name as 'DBAdapterSBProject' and click Finish.
Step-4: Create Folder hierarchy for the DBAdapterSBProject.
Now, we need to create certain folders within 'DBAdapterSBProject' to properly manage the project resources.
- Services->Business
- Services->Proxy
- JCA
- Pipelines
- Schemas
- WSDLs
- XSLTs
Right click on DBAdapterSBProject and navigate to: New->Folder
Again, right click on Services folder and create two nested folders named as Business and Proxy in a similar fashion.
Similarly, create folders for JCA, Pipelines, Schemas, WSDLs and XSLTs in DBAdapterSBProject.
Step-5: Configure Database Adapter.
Click to open servicebus.sboverview file (DBAdapterSBProject Composite).
Drag and Drop Database Adapter Technology in External Services lane.
In Database Adapter Reference wizard, enter Name as CreditCardInfoRef and click on Magnifying glass to select a directory for Service Directory Field.
Navigate to: Services->Business in Choose Directory wizard.
Click Next.
Click on Plus icon to create a new database connection in Service Connection screen.
Enter connection details then Test connection and click OK.
Enter JNDI Name as: eis/DB/HRConnection and click Next.
Select Operation Type: Perform an Operation on a Table and click on Select checkbox. Click Next.
On next screen, click on Import Tables button.
Click on Query button and shuffle the CreditCardInfo table from Available to Selected pane. Click OK.
Click Next.
Select CCNUMBER as the primary key and click Next and then again click on Next.
In Attribute Filtering window, select the attributes you want as response from this service and then click Next.
In Define Selection Criteria screen, click on the Add button next to Parameters filed and enter Parameter Name as ccnumber then click OK.
Click on Edit button next to SQL field.Again click on Add button on top of the Expression Builder screen. Click on Edit button within First Argument and choose Query Key as ccnumber. Finally click OK.
Select Parameter radio button in Second Argument and then click OK.
Click Next and Next till Finish.
Our DBAdapterSBApp would look something like below image:
Now, right click on CreditCardInfoRef_db.jca file and navigate to: Refactor->Move
Click on the Magnifying glass next to Move To filed and Select JCA folder as the destination directory for the CreditCardInfoRef_db.jca file.
Similarly, move xsd and wsdl file's too in their respective directories.
Now, open CreditCardInfoRef.wsdl file and change schemaLocation value to point to the correct schema file.
Similarly, open CreditCardInfoRef-concrete.wsdl file and change Location value to point to correct WSDL.
Now, Navigate to: Build->Clean All.
Navigate to: Build->Make All.
Now our DBAdapterSBApp would look like below image:
Step-6: Configure Proxy Service to access the DBAdapter.
Right click on the Proxy Services lane of the servicebus.sboverview (DBAdapterSBProject Composite) and navigate to: Insert Transports->HTTP
In the Create Service wizard, enter below fields as:
- Service Name: CreditCardInfoPS
- Location: browse to Proxy folder using Magnifying glass
- Check the Generate Pipeline field
Click Next.
Set Service Type to WSDL-based service selecting the WSDL radio button and then click on Browse WSDLs button next to WSDL radio button.
Select CreditCardInfoRef-concrete.wsdl and click OK.
Click Next.
Accept Default in Transport screen and click Finish.
Now, right click on CreditCardInfoPSPipeline.pipeline file and navigate to: Refactor->Move
Move the CreditCardInfoPSPipeline.pipeline to Pipelines folder.
Connect CreditCardInfoPSPipeline in Pipelines/Split Joins lane to CreditCardInfoRef in External Services lane.
Click on Yes.
Our servicebus.sboverview (DBAdapter Composite) will now looks like the below image:
Step-7: Testing the DBAdapterSB service.
Right click on CreditCardInfoPS in Proxy Services lane of the servicebus.sboverview (DBAdapter Composite) and navigate to: Run
Enter the ccnumber as the Payload input and click on Execute button.
Cross check Request and Response Document to verify.
That's All Amigos, Cheer up :-)
