Select Operation using Database Adapter in SOA suite 12C

In my previous article I have discussedA�the configuration steps of database resource in SOA Administration Console. Now I am going A�to give example ofA�select operation on Database adapter.

Create a table XXSOA_EMP_TAB in database. Data will be selected based on the parameter dept id.

SQL Script:

CREATE TABLE XXSOA_EMP_TAB (empid NUMBER

,empname VARCHAR2(20)

,dob DATE

,salary NUMBER

,deptid NUMBER

);

INSERT INTO XXSOA_EMP_TAB (100, a�?sachina��, a��04-jun-1978a��, 12000, 10);

1. Create a SOA project. Right click on application -> Project. Enter project name a�?DBAdapterPrja��
2. Click on Next and select composite as a�?Composite With Mediatora��
3. a�?Create Mediatora�� wizard will open. Enter Mediator name a�?DBAdapterTestMediatora�� and select template type as a�?Define Interface Latera��.
4. Click on OK
5. Drag a database adapter from components palate and drop it in the a�?External Referencesa�� swim lane.
6. a�?Database adapter configurationa�� wizard will open. Enter a reference name.
7. Click on green plus icon to create new database connection and Enter XE database connection details.
8. Then select JNDI Name by clicking on search icon.
9. Click on OK. Now Service Connection wizard will look like below screenshot.
10. Select operation type as a�?Selecta��.
11. Click on NEXT and select database table for operation
12. Click on a�?Import Tablesa�� and select table.
13. Click on OK
14. Click on NEXT and select primary key in the table
15. Click on NEXT
16. Click on NEXT and select the attributes which will be displayed in output.
17. Now create parameter for selecting data from table based on dept id.
18. Click on ADD and enterparameter name
19. Click on OK and expression builder window will open. Select second argument as a�?parametera�� and select parameter name from drop-down list.
20. Click on OK and SQL statement will be changed automatically according to parameter.
21. Click on NEXT and select default advanced options also.
22. Click on NEXT and then FINISH. DB adapter configuration is finished.
23. Select SOAP service and drag and drop it in a�?Exposed Servicea�� area. Create Web Service wizard will open. Enter service name and select type as a�?Servicea��.
24. Click on a�?coga�� icon to create WSDL for the SOAP service. a�?Create WSDLa�� wizard will open.
25. Select Interface type as a�?Synchronous Interfacea��
26. Now create input and output message. Click on green plus sign to create input message part
27. Create output message part and select XSD from project that is used by db adapter.
28. Click on OK
29. a�?Create WSDLa�� wizard will be looking like this.
30. Click on OK
31. Click on OK and SOAP service will be created.
32. ConnectA�all the components.
33. Double click on mediator and mapping file will open.
34. Click on mapping icon in request part and create new mapper file for request. Click on green plus icon and Enter name of the mapper file.
35. Now map the input request to the dept id (input of the DB adapter).
36. Click on mapping icon in synchronous reply and create new mapper file for output
37. Mapper file for response will look like the below screenshot.
38. Map the output of the DB adapter (source) to the response output (target).
39. Deploy the project. For deployment process refer my previous article HelloWorld Mediator.
40. The project can be tested from jdeveloper also. Go to Application Servers tab and select the project.
41. Right click on project (dona��t select default one) and select a�?Test Web servicea��
42. Test window will open. Give input string in the in the request SOAP header part. Click on a�?Send Requesta�� and the response will appear.