Integrating Web Services into your ADF Application Part 2

Sorry for the late post but I was constantly travelling and just found some time to finish my second part of integrating web services into your ADF application.

In this demo I have tried to integrate the reporting capabilities that Oracle BI Publisher offers with Oracle ADF through the use of web services.

More specifically, I have used a web service proxy to connect to the PublicReportService BI Publisher web service. Based on the selected employee (rendered using an ADF table), the appropriate employee id is passed to the runReport method of PublicReportService web service. This in turn runs the employee report which creates a pdf report and presents the generated report to the end user.

I have uploaded a complete step-by-step tutorial on how to achieve the above mentioned functionality along with the source code of my demo (ADFBIPublisher).

This tutorial shows you how you can consume an existing web service from your ADF Web Application using a web service proxy. More specifically, how to consume a BI Publisher web service, called PublicReportService, to run a report from your ADF Web Application. For the purpose of this tutorial, I am using Oracle JDeveloper 11g Version 11.1.1.3.0, Oracle Database 11g Standard Edition Version 11.2.0.1.0, Oracle BI Publisher Version 10.1.3.4.1 and Oracle BI Publisher Desktop Version 10.1.3.4.1. All mentioned software can be downloaded from Oracle Technology Network. Please note that this tutorial assumes that you have installed Oracle Database 11g Standard Edition with at least the HR sample schema, Oracle BI Publisher Version 10.1.3.4.1 and Oracle BI Publisher Desktop Version 10.1.3.4.1.

Create a Report using BI Publisher

1. Navigate to the Oracle BI Publisher page (http://:/ xmlpserver/) and sign in.

2. Select the Admin tab to access the Administration area.

3. Under Data Sources, click the JDBC Connection

4. Click the Add Data Source button to create a new data source connection to the HR sample Schema. Specify HRDataSource in the Data Source Name, in the Driver Type select Oracle 11g and in the Database Driver Class ensure that oracle.jdbc.OracleDriver is defined. In the connection string, specify the host, port and sid of your installed database (eg. jdbc:oracle:thin:@localhost:1521:orcl). Specify the username and password for the HR schema and click Test Connection. Once you get a successful connection confirmation, click Apply.

5. Click the Reports tab and from the Reports page, click the Shared Folders link.

6. In the “Folder and Report Tasks” pane, select Create a new folder, assign the name Demo to the folder and click Create.

7. Click the newly created folder Demo and from the “Folder and Report Tasks” pane click Create a new report. In the “Enter Report Name” text box, enter Employee and click Create.

8. From the newly create report “Employee” that has been created click the Edit link to edit the report.

9. The first step is to create a data model for our new report. So click the Data Model menu option under the “Report” tree structure and select New.

10. Under “General Settings”, specify the name to be Employee DataSet and ensure that the “Type” is set to be SQL Query. Under the “Details”section, specify the data source to be the HRDataSource that you have created a few steps before. In the SQL Query, click the Query Builder to declaratively specify your SQL query.

11. From the Query Builder window, click the Employees table and it will bring it to the main centre area.

12. We will be needing the JOBS table, as our main table EMPLOYEES has an external reference JOB_ID to the JOBS table (we want to display the actual job name and not an id), so click the JOBS table, EMPLOYEES tables (again) and the DEPARTMENTS.

13. From the EMPLOYEES table, select all the columns except from the JOB_ID, MANAGER_ID and DEPARTMENT_ID. From the JOBS table select the JOB_TITLE column, from the EMPLOYEES_1 table select FIRST_NAME and LAST_NAME and from the DEPARTMENTS table select DEPARTMENT_NAME.

14. Now it is time to define the external references that the EMPLOYEE table has with the other three tables. From the Employees table, to the right of the JOB_ID column, single click on the blank cell next to “A”. Do the same thing with the JOB_ID column in the JOBS table. You should see the relationship being created.

15. Do the same thing for the MANAGER_ID from the EMPLOYEES table with the MANAGER_ID from the EMPLOYEES_1 table, and the same with DEPARTMENT_ID from the EMPLOYEES table with the DEPARTMENT_ID from the DEPARTMENTS table. Once finished, click Save.

16. You should see now in the SQL Query input box the query being populate as follows: select EMPLOYEES.EMPLOYEE_ID as EMPLOYEE_ID, EMPLOYEES.FIRST_NAME as FIRST_NAME, EMPLOYEES.LAST_NAME as LAST_NAME, EMPLOYEES.EMAIL as EMAIL, EMPLOYEES.PHONE_NUMBER as PHONE_NUMBER, EMPLOYEES.HIRE_DATE as HIRE_DATE, EMPLOYEES.SALARY as SALARY, EMPLOYEES.COMMISSION_PCT as COMMISSION_PCT, JOBS.JOB_TITLE as JOB_TITLE, EMPLOYEES_1.FIRST_NAME as FIRST_NAME, EMPLOYEES_1.LAST_NAME as LAST_NAME, DEPARTMENTS.DEPARTMENT_NAME as DEPARTMENT_NAME from HR.DEPARTMENTS DEPARTMENTS, HR.EMPLOYEES EMPLOYEES_1, HR.JOBS JOBS, HR.EMPLOYEES EMPLOYEES where EMPLOYEES.JOB_ID=JOBS.JOB_ID and EMPLOYEES.MANAGER_ID=EMPLOYEES_1.EMPLOYEE_ID and EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID Please make the following change to the manager first name and manager last name columns, as the data come from the same table and we have the same column names as with employee first name and last name. The change is highlighted in bold: select EMPLOYEES.EMPLOYEE_ID as EMPLOYEE_ID, EMPLOYEES.FIRST_NAME as FIRST_NAME, EMPLOYEES.LAST_NAME as LAST_NAME, EMPLOYEES.EMAIL as EMAIL, EMPLOYEES.PHONE_NUMBER as PHONE_NUMBER, EMPLOYEES.HIRE_DATE as HIRE_DATE, EMPLOYEES.SALARY as SALARY, EMPLOYEES.COMMISSION_PCT as COMMISSION_PCT, JOBS.JOB_TITLE as JOB_TITLE, EMPLOYEES_1.FIRST_NAME as MANAGER_FIRST_NAME, EMPLOYEES_1.LAST_NAME as MANAGER_LAST_NAME, DEPARTMENTS.DEPARTMENT_NAME as DEPARTMENT_NAME from HR.DEPARTMENTS DEPARTMENTS, HR.EMPLOYEES EMPLOYEES_1, HR.JOBS JOBS, HR.EMPLOYEES EMPLOYEES where EMPLOYEES.JOB_ID=JOBS.JOB_ID and EMPLOYEES.MANAGER_ID=EMPLOYEES_1.EMPLOYEE_ID and EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID Before saving our work, please note that we have to manually input a where clause to limit the number of records retrieved by the SQL query to just a single employee record. Please append at the end of the SQL Query the following command: “and EMPLOYEES.EMPLOYEE_ID = :pEmployeeId” Click Save.

17. The next thing is to define the parameter pEmployeeId that we have inserted in our SQL Query, so click on the Parameters link and then New.

18. Under “General Settings”, specify pEmployeeId in the Identifier field. In the “Text Setting” area, specify Employee Id in the Identifier field and click Save.

19. Now we are ready to test our report. To test it, click the View link.

20. In the Employee Id enter the value 200. On the left hand side of the View button there is a drop down menu that if you expand you will notice that only Data is available. Because we did not specify a template yet, the only available output option is Data, which is basically xml. We will need this xml data to construct our template, so click View and once the data have been generated, click the Export button.

21. Save the xml data locally (for example on your desktop) and then open Microsoft Office to design the template. Please note that again, I am assuming that you have installed Oracle BI Publisher Desktop Version 10.1.3.4.1 which attaches some BI add-ons in Microsoft Office products. So open Microsoft Office and design your preferred layout for your Employee Report. I have designed a simple report, with the Oracle log and date and page number on the header field, a title and a table to hold my employee details.

22. The next thing to do is to load the xml data that we have downloaded from running our report to Microsoft Word. So click on the Add-Ins ribbon tab, Data and Load XML Data.

23. Select your Employee.xml and click Open.

24. Click OK to dismiss the “Data Loaded Successfully” window. Place your cursor to the empty table cell next to the Employee Id and select from the menu Insert and Field.

25. As you can notice, the field browser window contains all the fields specified in the SQL query that we have defined in our report using BI Publisher. So select Employee Id and click Insert. The EMPLOYEE_ID has been added next to the Employee Id cell.

26. Perform the same thing, but each time inserting the appropriate field to the report. Please note that in our field browser we have FirstName and LastName, however in our report we have Name. So to concatenate first name and last name, just insert the FirstName, hit the space bar once and insert the LastName field. Your report should look as follows:

27. Although our SQL query will return only one record, the field browser assumes that it may return more than one records. So for our template to work, we need to specify a loop, just in case more than one record is returned. To achieve this place your cursor between the “Employee Report” title and the table, and double click the Row element from the field browser. You should see the ROW element inserted.

28. Close the field browser, place your cursor at the beginning of the ROW element, and select from the menu Oracle BI Publisher, Insert and Repeating Group.

29. On the BI Publisher Properties window, click OK. You will notice that it has created a for-each element and an end element between your ROW element.

30. Select the end element and move it to the end of the table.

31. Save the template as a Rich Text Template and name it Employees. You can now close the word file.

32. Access the Oracle BI Publisher application and edit your Employee report under the Demo folder that we have created and click the Layout menu.

33. Under the “Manage Template Files” click the Browse button and browse to the Employees.rtf template that we have just created, click Open and then Upload. You should see the Employees.rtf file uploaded.

34. Having the Layouts menu option selected, click the New button.

35. In the name, specify Employee Template and ensure that the Employees.rtf template is selected in the Template drop down. In the Output Format, make sure that All Formats radio option is selected and click Save.

36. Click the View link once the report has been saved to test our final report. In the Employee Id input field, enter 200. Next to the Employee Template, select from the drop down menu PDF and then click View. Your report should be rendered in PDF format and display the details for employee Jennifer Whalen.

Consume the Report from ADF

1. Open JDeveloper and create a new Application.

2. In the Create Application dialog box, specify the Application Name to be ADFBIPublisher, in the Application Package Prefix field set the value to be demo and in the Application Templates choose the Fusion Web Application (ADF) and click Finish.

3. Close the ADFBIPublisher Overview window as we will not be using it. In the Application Navigator you'll see two new projects now, one called Model and the other called ViewController. Right click the model project and select New.

4. Under the Business Tier category, select ADF Business Components and then from the items select Business Components from Tables and click OK.

5. In the “Initialize Business Components Project”, click on the green add sign Create a new database connection.

6. In the “Create Database Connection” window, specify your database connection details to connect to your HR schema. In the Connection Name, specify HRConnection, in the Username field HR, in the password your HR schema password and ensure that the “Save Password” check box is checked. In the Driver, select thin, and specify the host name where your datanase resides, the SID (in my case it is XE) and the port number (in my case it is 1521). Click Test Connection and if successful, click OK.

7. The “Initialize Business Components Project” window is now populated with the new connection name that we have created. Leave the default SQL Flavor (Oracle) and Type Map (Oracle) and click OK.

8. The “Create Business Components from Tables” wizard starts, with Step 1 being defining your entity objects. In the package, specify demo.model.entities, in the schema make sure that HR is selected and click Query.

9. The query retrieved all available tables and views from our HR schema. We will be working with the Employees tables, so select Employees from the available pane and shuttle it to the selected pane. In the Entity Name, change the default name from Employees to EmployeesEO and click Next.

10. On Step 2 of the wizard, “Updatable View Objects”, specify in the package name demo.model.views and shuttle from the available pane the EmployeesEO to the selected pane. Change the default object name to EmployeesVO and click Next.

11. On the “Read-Only View Objects” step click Next.

12. In the “Application Module” step, ensure that the Application Module is checked. In the package name, specify demo.model.module, leave the default name (AppModule) and click Finish.

13. We have set up our model, now it is time to design our interface. So right-click the ViewController project and select New.

14. Expand the Web Services category, select JSF and from the items select JSF Page. Click OK.

15. In the “Create JSF Page” window, specify the file name to be Employees.jspx, leave the default directory (C:\JDeveloper\mywork\ADFBIPublisher\ViewController\public_html) and ensure that the Create as XML Document (*.jspx) check box is checked. In the Initial Page Layout and Content select Page Template and from the drop down menu select Oracle Dynamic Tabs Shell. Expand the Page Implementation section and ensure that Do Not Automatically Expose UI Components in a Managed Bean is selected and click OK.

16. The Employees.jspx page should be displayed. If not expand your ViewController project, locate your Employees.jspx page and double click it to open it. Ensure that the Employees.jspx page is in design view.

17. There are two ways of working with the UI. The first one is by dragging and dropping items on the actual page and the second way is by using the Structure window. I will be using the second way. So, to ensure that everyone has their structure window enabled select from the menu View and then Structure (alternatively, click Ctrl+Shift-S).

18. The Structure window should appear in the left lower corner of your JDeveloper. In case the Structure window is empty, just click anywhere inside the Employees.jspx page. Expand f:view, af:document, af:form, af:pageTemplate and /oracle/ui/pattern/dynamicShell/dynamicTabShell.jspx faces.

19. From the Component Palette, ensure that ADF Faces is selected and under the Layout section, select Panel Splitter and drag it onto the welcome facet.

20. Expand af:panelSplitter, panel Splitter facets and change the Orientation of the panel splitter (using the property inspector) from horizontal to vertical.

21. From the Layout section of the ADF Faces, drag Panel Collection onto the first facet of the panel splitter and a Panel Tabbed onto the second facet of the panel splitter.

22. From the second facet of the panel splitter, expand af:panelTabbed and click on the af:showDetailItem – showDetailItem 1 and change the text to Employee Details.

23. Expand the Data Controls and under AppModuleDataControl, select the EmployeesVO1 view and drag it over the af:panelCollection – pc1 component and select Table, ADF Read-only Table ...

24. In the “Edit Table Columns” window, select Row Selection, Sorting and Filtering and click OK.

25. Drag the EmployeesVO1 view object onto the af:showDetailItem – Employee Details component (under the panelTabbed component inside the second facet) and select Form, ADF Form ...

26. On the “Edit Form Fields” just click OK.

27. Let’s run the application to see how our Employees.jspx page looks like and what data are being rendered. To run the page, just right click the Employees.jspx page and select Run. This will start the Integrated WebLogic Server, Deploy the application and render it on your default browser.

28. We can see that the Employees.jspx page is divided in two sections. The first section showing a table with all employees and the second section, below the table, the employee details of the selected record above (for example, Steve King). Try clicking on another record from the record, for example Alexander Hunold. The form does not display the details for the selected record. This is because we have not specified that section (basically the form) to listen to any changes in the table component.

29. So to make the form element reflect changes made from the table component, select the af:panelFormLayout under the af:showDetailItem – Employee Details component and using the property inspector select Edit next to the PartialTriggers.

30. In the Edit Property of the PartialTriggers window, expand facet (welcome), panelSplitter – ps, facet (first), panelCollection* - pc1 and select table* - t1 and click Add to selected. You should see the table* - t1 to the selected pane. Click OK.

31. If you right click the page and choose Run you can see that the detail form reflects the details of the above selected master record.

32. You can change the default branding title (Navigation Shell) to something more meanigfull. To achieve this, click the af:pageTemplate tag under the af:form and from the Property Inspector, under the Other section, just change the brandingTitle property from Navigation Shell to ADF/BI Integration. If you run the page once more, you should see the new title being displayed.

33. The next thing is to create a menu that will give the option to the user to run the report that we have created using BI Publisher. So in the first facet under the panel splitter, expand the panel collection and panel collection facets. Right click the menus facet and select Insert inside menus and choose Menu.

34. Having the af:menu – menu 1 item selected, from the property inspector change the text from menu 1 to File.

35. Right click the af:menu – File item and select Insert inside af:menu – File Menu Item.

36. Select the af:commandMenuItem – commandMenuItem1 from the structure window, and from the property inspector change the text from commandMenuItem 1 to Run Employee Report.

37. From the component palette, select ADF Faces and from the Operations section drag a File Download Action Listener onto the af:commandMenuItem – Run Employee Report.

38. Select the af:fileDownloadActionListener from the structure window and using the property inspector, specify the filename to be Employee.pdf.

39. Next to the method property, click the arrow and select Edit.

40. From the Edit Property window, click New.

41. In the Create Managed Bean, specify EmployeesBean as the Bean Name, Employees as the Class Name, demo.view.beans in the package and click OK.

42. From the Edit Property, click New next to the Method drop down to create a new method.

43. In the Create Method dialog, specify runReport as the method name and click OK.

44. Click OK in the Edit Property window.

45. BI Publisher exposes several of its operations as web services. To see what operations are exposed as web services, open a browser to go to your BI Publisher server home page and append to the url /services (http://:/xmlpserver/services). Some of the services are XMLPService, AdminService, PublicProvisiongService etc.

46. Scroll down and locate the service PublicReportService and click on the wsdl link.

47. You should see the PublicReportService wsdl file. Copy the url location as we will use it in the following steps.

48. Back in JDeveloper, create a new project by selecting File, New.

49. From the General categories, select Projects and from the items select Generic Project and click OK.

50. In the Create Generic Project window specify WebServiceProxy as the project name and click Finish.

51. Right click the newly created project WebServiceProxy and select New.

52. From the Business Tier category select Web Services and from the items select Web Service Proxy and click OK.

53. In step 1 (Create Web Service Proxy) click Next and in step 2 of the wizard (Select Client Style) select JAX-WS Style and click Next.

54. In step 3 (Select Web Service Description), in the WSDL Document URL input field, paste the url of the PublicReportService wsdl location that you have copied (http://:/xmlpserver/services/PublicReportService?wsdl) and click Next.

55. In step 4 (Specify Default Mapping Options), specify demo as the package name and demo.types as the root package for generated types and click Finish.

56. As you can see, the wizard has created all java classes necessary to run the specified web service (PublicReportService). We will use these classes from our ViewController project. To actually have access to these generated classes from our ViewController project, we need to define the project dependencies from our ViewController project. So right click the ViewController project and select Project Properties.

57. Select Dependencies and click on the pencil Edit Dependencies.

58. Expand WebServiceProxy.jsp and select the Build Output check box and click OK.

59. In the Project properties click OK. Open the Employees bean that we have auto-created to insert the actual logic of the runReport method. Expand ViewController, Application Sources, demo.view.beans and double-click in the Employees.java file to open it.

60. In the runReport method, insert the following code: BindingContainer bindings = BindingContext.getCurrent().getCurrentBindingsEntry(); DCIteratorBinding iterator = (DCIteratorBinding)bindings.get("EmployeesVO1Iterator "); Row row = iterator.getCurrentRow(); String employeeId = row.getAttribute("EmployeeId").toString(); PublicReportServiceService publicReportServiceService = new PublicReportServiceService(); PublicReportService publicReportService = publicReportServiceService.getPublicReportService(); ReportRequest reportRequest = new ReportRequest(); reportRequest.setAttributeFormat("pdf"); reportRequest.setAttributeLocale("en-US"); reportRequest.setAttributeTemplate("Simple"); reportRequest.setReportAbsolutePath("/Demo/Employee/Employee.xdo"); ArrayOfParamNameValue pNameValue = new ArrayOfParamNameValue(); ParamNameValue nameValue= new ParamNameValue(); nameValue.setName("pEmployeeId"); ArrayOfString aos = new ArrayOfString(); aos.getItem().add(employeeId); nameValue.setValues(aos); pNameValue.getItem().add(nameValue); reportRequest.setParameterNameValues(pNameValue); try { ReportResponse response = new ReportResponse(); response = publicReportService.runReport(reportRequest, "Administrator", "manager1"); outputStream.write(response.getReportBytes()); } catch (Exception e) { e.printStackTrace(); } What this code does is that based on the Employee View Iterator, gets the selected row and based on the selected row, obtains the value of the Employee Id. It then calls the PublicReportService web service and requests to run a specific report (Employees report), assigning the employee id parameter. The web services responds back to the caller with the bytes of the actual report that we in turn pass to the outputStream object of the fileDownloadActionListener so that the file can be displayed to the users. Please note that when prompted import the following classes: import demo.PublicReportService; import demo.PublicReportServiceService; import demo.types.ArrayOfParamNameValue; import demo.types.ArrayOfString; import demo.types.ParamNameValue; import demo.types.ReportRequest; import demo.types.ReportResponse; import oracle.adf.model.BindingContext; import oracle.adf.model.binding.DCIteratorBinding; import oracle.binding.BindingContainer; import oracle.jbo.Row;

61. Right click Employees.jspx and choose Run.

62. You should see the new menu (File) that we have created being displayed next to the View menu.

63. Select an employee, for example EmployeeId 102 (Lex De Haan) and from the new menu that we have created select File, Run Employee Report.

64. You should be presented with the Employees.pdf file, presenting all employee details of the selected employee.