![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Introducing WebSphere Application Portlet Builder
Many users of DB2® data don't have the ability or desire to roll up their sleeves and use the DB2 command line to manipulate DB2 data. End users of the database might be business users who would not touch SQL with a ten foot pole. However, the business may require these individuals to be able to create, read, update, and delete data in your database. In this article, you will see how to use WebSphere® Application Portlet Builder (WPAI) as a way to provide access through WebSphere Portal Server to data in DB2 Universal Database™.
WPAI consists of a family of portlet building applications, namely:
- PeopleSoft 8 Portlet Builder
- Siebel Portlet Builder
- SAP Portlet Builder
- JDBC Portlet Builder
- Portlet Builder for Domino
- WPCP Portlet Builder
All of these portlet builders are themselves portlets which run in WebSphere Portal and enable you to create new portlets which can view and modify information from a myriad of data providers, including IBM DB2 Universal Database, Oracle, PeopleSoft, Siebel, SAP, and Domino Server. In this article, you will see how to create a portlet that will interact with DB2, using the JDBC Portlet Builder.
Using the resulting portlet, you will be able to create a setup where you can manipulate your DB2 data using a Web browser such as Internet Explorer 6 or Netscape 7.
WPAI is a framework that allows for rapid portlet development through portlet-based tools. The framework consists of three different types of components:
- Backend application adapters -- These adapters represent the code that talks the language of a particular back-end application. They present a uniform interface for it to the rest of the framework. Some of these adapters use JCA connectors. (For example, PeopleSoft and Siebel JCA connectors are shipped with the WPAI).
- Runtime Portlets (Portlet Templates) -- These are portlets that understand metadata created by the builder and communicate with the adapters to present data to the user and submit user input back to the target backend.
- Portlet Builders (Application Portlet Builders) -- These are portlets that allow the user to go through a wizard-like interface to build their own backend accessing portlets.
The architecture of our setup for this article might best be described with a picture. We will use the JDBC Portlet Builder to create a portlet. This portlet will run in the WebSphere Portal container. The portlet will allow us to interact with DB2 database and perform CRUD (CREATE-READ-UPDATE-DELETE) functions on a DB2 table. In particular we will target the EMPLOYEE table of the DB2 Sample database. A JDBC Data Source resource entry in WebSphere Application Server will facilitate communication with the database.
Figure 1. Architectural overview
If your DB2 database does not reside on the same physical machine as WebSphere Portal, you will need to use the DB2 Configuration Assistant to catalog the Sample database locally.
To reap the benefits of the setup described in this article, you will need to have WebSphere Portal 5 installed. JDBC Portlet Builder also runs on WebSphere Portal Version 4.1 and Version 4.2. I am going to assume that you have an existing knowledge of how to administer WebSphere Application Server Version 5, WebSphere Portal Server Version 5 and DB2 UDB Version 8.
WebSphere Portal 5 comes out of box with a version of the JDBC Portlet Builder installed. This version unfortunately has some known problems. Accordingly, you need to uninstall the version of the JDBC Portlet builder that shipped with WebSphere Portal. Do this by logging into Portal as a user who has Portal administrative privileges. Using the Administration tab and then choosing the Manage Applications option under Portlets (see below), uninstall the Web modules named BusinessObjectFrameworkStruts.war and BOBuilderPortlet.war.
Figure 2. Uninstalling the WAR files of the JDBC Portlet Builder version which ships with WebSphere Portal
You will set up communication with DB2 for the portlet built using the JDBC Portlet Builder by establishing a data source. You configure a data source using the WebSphere Administrative Console which is by default accessed using the url: http://<yourhostname>:9090/admin.
In the Task Navigation frame of the console, expand the Resources option and select JDBC Providers. In the Work Area frame, choose the scope that you want (cell, node, or server) and click Apply. Click New to create a JDBC provider, specifying the Name of “DB2 JDBC Provider". Set the property Classpath to the location of your db2java.zip file. At this point, you should have a JDBC Provider named “DB2 JDBC Provider", as shown in Figure 3.
Figure 3. Use the DB2 JDBC provider to facilitate communication with DB2
A J2C Authentication Data Entry needs to be created to connect to our data source. Set this up by going to the Task Navigation Frame and choosing Security -> JAAS Configuration ->J2C Authentication Data. Use the New button to create an authentication entry that contains the user name and password of a database user which has access to the Sample database. Figure 4 shows the creation of an Authentication Data Entry named db2admin which has a user ID of Administrator.
Figure 4. Create a J2C authentication data entry
Now, go back to the Task Navigation Frame and choose Resources-> JDBC Providers. Click on the DB2 JDBC provider entry you created earlier. Scroll down to the Additional Properties section and click on Data Sources.
Figure 5. Create a data source entry
Use the New button to create a new data source. Provide the following properties:
- Name: SampleDS
- JNDI Name: jdbc/sampleDS
Check the option to enable the data source for container-managed persistence. For the Component-managed Authentication Alias and Container-managed Authentication Alias, choose the db2admin alias representing the J2C Authentication Data Entry you created earlier. When you are done, you should have a new data source named SampleDS, as you see in Figure 6.
Figure 6. The SampleDS data source
You should use the Test Connection facility to ensure that you have properly established connectivity between WebSphere Application Server and DB2.
Figure 7. Test the SampleDS data source
It is a good idea to stop and restart WebSphere Application Server and WebSphere Portal Server at this point in order for the newly established DB2 data source to be recognized.
We will be using the EMPLOYEE table of the Sample database for our study. The JDBC Portlet Builder requires that the target table have a primary key. Note, when you install the Sample database, the EMPLOYEE table is not automatically assigned a primary key. To establish this primary key, you can use the DB2 Command Line Processor and the following SQL syntax:
db2=> alter table employee add primary key (EMPNO) |
Obtain and install the JDBC Portlet Builder
Now that we have set up WebSphere Application Server and created our data source which will facilitate WebSphere Portal’s communication to DB2, we are now ready to roll up our sleeves and start dealing with the Portal side of things.
You can download the latest version of the JDBC Portlet Builder from the Portlet Catalog for free. Unzip the zip file you download. In it will be a number of WAR files. Install the WAR files: WPAIPortletBuilder50.war and WPAIDefaultTemplate50.war using the Install option (see Figures 8 and 9).
Figure 8. Install the WPAI Portlet Builders (WPAIPortletBuilder50.war)
Figure 9. Install the WPAI Default Runtime Template Portlet (WPAIDefaultTemplate50.war)
Go to My Portal->My Work-> Developers Area -> JDBC Builder. This page is where the previous version of the JDBC Portlet Builder used to reside. Now it is blank since you uninstalled the version of the JDBC Portlet Builder that shipped with Portal. Let’s use this same page to house an instance of the new version of the JDBC Portlet Builder Portlet. Click Edit Page and then click the Add portlets button. Use the Search Utility to locate the JDBC Portlet Builder Portlet and add it to the page. When the portlet has been added, click Done (see Figure 10).
Figure 10. Add the JDBC Portlet Builder Portlet to the JDBC Builder page
You should see the resulting JDBC Builder Page which shows the JDBC Portlet Builder with a Welcome message (see Figure 11).
Figure 11. The JDBC Portlet Builder portlet successfully installed and added to the JDBC Builder page
The JDBC Portlet Builder allows you to create business objects which map directly to columns in your database. Let us say for example that we have a database table that contains a huge number of columns consisting of esoteric names like l_nme and f_nme. In the real world these might represent “Last Name" and “First Name" respectively. When establishing our business objects, we can provide more aesthetically pleasing and understandable names for the end-user of a portlet created by the JDBC Portlet Builder. Our business objects can also be designed to show the users only information they are interested in. We will demonstrate these advantages shortly.
Now we are ready to create a portlet using the JDBC Portlet Builder. Click on the Configure icon (the wrench) of the JDBC Portal Builder portlet.
Figure 12. Start configuring a new portlet
In the next screen, you will be asked to specify the JNDI name for the data source you want to connect to. Specify the name of “jdbc/sampleDS" and also specify the user name and password for your database as shown in Figure 13.
Figure 13. Specify the target data source for your portlet
Next, click the New portlet button to start creating a new portlet.
Figure 14. Start the process of creating a new portlet
In the next screen, specify settings for the new portlet. Specify a portlet name of “SampleJDBCPortlet." Specify a slot name of “SampleSlot" and enter the appropriate user name and password for the database again. For the “Select a Template:" option, specify the template of “WPAI Default Runtime Template." When you have entered the parameters as shown below, click Next.
Figure 15. Specify portlet parameters
Next, we need to define a new business object for our portlet. As I stated earlier, this business object will map to column data values in our database. Click the New business object button.
Figure 16. Define a new business object
In the following screen, you are presented with an interface that allows you to specify a schema name for the database you are pointing to. You can also use the “Get list" feature to see all the tables in the schema.
Figure 17. Point your business object to a table of your database
Go ahead and use the wizard to point to the EMPLOYEE table as shown in Figure 18. After you have chosen the EMPLOYEE table, click OK.
Figure 18. Specify the Employee table
In the next screen, specify “SampleBO" as the name of the business object. Check the “Search automatically" option. Note that you can specify the markup language which you want to configure. We will choose html since we are targeting standard Web browsers. Also, here you can specify actions which are allowed for the business object. By default, search, create, update, and delete actions are allowed.
Figure 19. Specify business object name and allowed actions
In the next window, you are will see the columns of the EMPLOYEE table. Here, you can click which fields you want to be added to your business object. Check only the EMPNO, FIRSTNME, LASTNAME and MIDINIT columns. For the display name, as you've already seen, we can provide more human-friendly names (Employee Number, First Name, Last Name, and Middle Initial respectively). See Figures 20 and 21 below. Once you have specified the column mappings for your business object, click Next.
Figure 20. Map columns to your business object fields
In the next screen, you can specify the order that your fields will appear in your portlet. You can also specify how many rows and columns will display at one time. Go ahead and keep the defaults and click Finish.
Figure 21. Specify display properties for your business object
In the next screen, you should see confirmation that your portlet has been created by seeing an entry in the business object area.
Figure 22. See your business object registered as a new object
Click OK. In the next screen you should see that your portlet, which you named SampleJDBCPortlet, has been created.
Figure 23. List of created portlets with the SampleJDBCPortlet
In order to use the portlet you just created, you need to add the portlet to a page. Refer to WebSphere Portal documentation in the Information Center to learn how to go about doing this. To summarize, you need to add the SampleJDBCPortlet to a page.
When you see the portlet on a page, you will see the first 20 rows in the PEOPLE table. In particular, you will see only the columns that we allowed through our business object. To view or edit a row, simply click on the icon next to the data you are interested in changing. As you can see in Figure 24, I decided to manipulate the row with last name of “KWAN" and first name of “SALLY".
Figure 24. Using the portlet you created to view and edit data in the PEOPLE table
Next, you should see a page showing the details of the entry you selected. To manipulate the values, click the Edit button.
Figure 25. Opting to edit a particular business object
After clicking Edit, you will be able to change values for the database record. In Figure 26, I have changed the last name of “SALLY" from “KWAN" to “FEONA."
Figure 26. Change the values of a record
After clicking Update, you will be asked to confirm your changes. Click OK to confirm.
Figure 27. Confirm your record changes
At this point, you should be able to see the change to your data in your portlet.
Figure 28. See your data changes in the portlet
From the DB2 command line, you should be able to see the same change taking effect in DB2 with the query of select LASTNAME from EMPLOYEE where EMPNO = ‘000030’.
Figure 29. See the update in DB2
If you recall, earlier we made our business object fields searchable. Let’s test out the search features of the portlet we built. At the top of the portlet, select the Search button as shown in Figure 30.
Figure 30. Try out the search feature
Enter the search parameters you’d like. I entered ‘FEONA’ for the last name (the record we edited earlier).
Figure 31. Enter search criterion
After clicking Search, you should see the record we were looking for.
Figure 32. The results of your search
Earlier we created a JDBC data source to facilitate communication to our DB2 server. Data sources allow us to take advantage of connection pooling. Each portlet you will develop using WPAI to talk to DB2 will use a connection. If a number of portlets are used simultaneously, consider increasing the size of the connection pool in WebSphere Application Server for the data source.
For those who would like to interact with their DB2 database remotely using a pervasive device, WPAI caters to the gadget loving crowd with WML markup support. The caveat is that the target device must support WML 1.1. You can use a WAP-enabled device to interact with a DB2 database using WebSphere Portal as your proxy. I will show you how to set this up in a follow up developerWorks DB2 article.
It is quite possible that you might have a number of portlets wanting to access DB2, so you may want to consider single sign-on (supported through the Portal Server Credential Vault) to allow users to only have to enter their credentials once. I won't delve too deeply into the subject, but note that portlets can use information they find in the credential vault to automatically log in.
In this article, we barely scratched the surface of the WebSphere Portal Application Integrator’s extensive offerings. You've had a chance to see how JDBC Portlet Builder Portlet of WPAI can be used to create a portlet that can view and edit data in your DB2 database. However, you can also use WPAI to create portlets that can access and manipulate data in a myriad of other backend systems. For example, you can use WPAI to interact with PeopleSoft, Siebel, SAP, and Domino. The beauty is that a programming knowledge of the systems is not required to tie into these different systems. Also, you can leverage WebSphere Portal’s innate support of pervasive computing devices in the form of WML markup. Using WPAI, you can effectively facilitate remote manipulation of the data of various systems that are critical to your enterprise.
You can learn more about WPAI and how to tie it into various systems by reading the documentation included in WPAI’s download file. A good amount of documentation is included in the doc subdirectory of the zip file.
The author would like to thank Cesar A. Wong of IBM for his help with this article.
-
WPAI Product Page
-
developerWorks WebSphere Portal Zone
-
WebSphere Portal documentation
-
WebSphere Portal Information Center
-
Redbook A Portal Composite Pattern Using WebSphere Portal V5
-
Browse for books on these and other technical topics.
Kulvir Singh Bhogal works as an IBM consultant, devising and implementing Java-centric solutions at customer sites across the nation. You can reach Kulvir at kbhogal@us.ibm.com.
Table of contents
- Introducing WebSphere Application Portlet Builder
- Architecture
- Prerequisites and assumptions
- Hook up with DB2
- Obtain and install the JDBC Portlet Builder
- The role of business objects
- Use the JDBC Portlet Builder
- Install and run your portlet
- Performance considerations
- Support for WML
- A word about security
- Conclusion
- Acknowledgement
- Resources
- About the author
- Comments
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
