Spicing Up Your Web Services with XSLT - Page 2

1.  Build a piece of business logic that you want to expose as a Web service. For this example, build an SQL statement, called GetEmp, which returns details for a specific employee by joining columns from the EMPLOYEE, EMP_PHOTO, and EMP_RESUME tables.

The following SQL statement takes EMPNO as an input parameter and returns details for a specific employee by joining data from the three aforementioned tables that are part of the SAMPLE database:

SELECT employee.*, 
  (SELECT resume FROM emp_resume 
    WHERE emp_resume.empno = :empno AND 
          emp_resume.resume_format = 'ascii') AS resume, 
  (SELECT picture FROM emp_photo 
    WHERE emp_photo.empno = :empno AND 
          emp_photo.photo_format = 'gif') AS picture 
  FROM employee 
    WHERE employee.empno = :empno

Note: If you followed along in this series, you’ve likely created the SAMPLE database that’s provided with a DB2 installation; however, not all parts in this series use this database. If you don’t have the SAMPLE database, you can create it by entering the db2sampl command in your operating system command prompt. The EMPLOYEE EMP_PHOTO, and EMP_RESUME tables schemas looks like this:

Click for larger image

I used the Overview Diagram feature in IBM Data Studio (introduced in “Part 3: Overview diagrams - The Basics” and “Part 4 Overview diagrams – The Rest of the Story”). You can see that IBM Data Studio automatically detected the business relationship between the EMPLOYEE and DEPARTMENT tables, and gives you details as to the underlying columns of these tables. You can see from the previous figure and SQL statement that this SQL statement returns relational data, as well as data from a character large object (CLOB) column, and a binary large object (BLOB) column. This varying data types will present a challenge (as you will see) for the XML output of our Web service, and XSLT can be used to solve this problem.

Save this SQL statement as GetEmp such that the Data Project Explorer looks like this:

If you need help with this step, refer to “Part 6: Building an SQL Statement” (if you want to use the SQL Editor) or “Part 7: The SQL Builder’s Development Accelerators” (if you want to use the SQL Builder).

2.   Create a new Web service project, called HRTOOL, and drag the GetEmp SQL statement to this folder, as shown below:

In the middle of the previous figure, you can see that the project from which you launched the New Web Service window becomes the default project (as shown in the Project field) for the Web service. You can use the Project drop-down list to select a different project, or even create a new project for this Web service by clicking New.

If you need help with this step, or the next two steps, refer to “Part 11: Transforming Business Logic into Web services”.

3.  Ensure that the target application server is started and build the Web service such that it can be invoked using the REST protocol, as follows:

4.  Locate the URL of the Web service by launching the Web Service Explorer and getting the URL of the Web Services Description Language (WSDL) document (the highlighted portion below):

5.  Locate the URL to invoke your Web service using the GET method of a RESTful invocation (and copy the highlighted text within the quotes in the following figure):

6.  Paste the URL from the previous figure into your Web browser’s address field and append the name of the Web service (getEmp) and a value of 000130 for the EMPNO parameter such that the URL looks like this: http://localhost:8080/DatabaseJournalProjectSOA_FEMALEPERSONNEL/rest/HRTOOL/getEmp?empno=000130.

Leave this Web browser session open for the remainder of this article.

You can see that the Web service was properly executed. Note that it returns the data for employee 000130 from the EMPLOYEE table:

The output of the RESUME CLOB column in the EMP_RESUME table is properly displayed (since it is character data):

However, notice that the output of the PICTURE BLOB column in the EMP_PHOTO table isn’t properly displayed:

Obviously, this isn’t the way you want to display your data in a Human Resources application, but we know we have the data; it’s just a matter of formatting it, and that is where XSLT comes into play.

7.  Associate an XSLT transform for the XML-based output message of the Web service as follows:

8.  Rebuild the Web service using the same steps and options as you did in Step 3.

9.   Return to the Web browser session where you invoked your Web service in Step 6 and press Enter. This action will once again invoke the same Web service and pass it EMPNO=000130 as the input parameter.

Your Web browser window should now look like this:

You can see that your Web service now takes the XML and transforms it into HTML, which is shown in the previous figure: quite a transformation!

Note: When I tested the steps in this article with Microsoft Internet Explorer (IE) as the Web browser, I noticed that the picture (BLOB) column did not render correctly. I’m currently investigating this nuance.