Tutorial - Creating a database application 

---

In this tutorial you will create a service provider model against a sample database using the SQL Table Create builder, which creates a database table and provides full CRUD (create, retrieve, update and delete) capability, exposed as service operations, and then create a user interface service consumer model, using the Service Consumer and Data Services UI builder.

Using these tutorials

This set of tutorials are a great starting point for a new WebSphere® Portlet Factory developer. While most of the four parts of this tutorial take between 30 and 45 minutes to complete, you should set aside an hour to and hour and one half for this tutorial. The tutorials should be completed in the following order, as each builds on knowledge obtained in the previous tutorial. In addition, we recommend visiting the WebSphere Portlet Factory Learning Roadmap on the WebSphere Portlet Factory wiki after completing these tutorials to further advance your WebSphere Portlet Factory learning experience.

Table 1.

Tutorial order	Description
Creating a Web Application Project	In this tutorial you will learn how to create a web application project.
Creating an application	In this tutorial, you will build a simple "hello world" application and then create a portlet from that application.
Creating a database application	In this tutorial you will create a service provider model against a sample database using the SQL Table Create builder, and then create a user interface service consumer model, using the Service Consumer and Data Services User Interface builder.
Using profiling in your new application	Continuing from the "hello world" application tutorial you add the power of profiling to your application.

What you will learn

• About the service-oriented architecture (SOA) Consumer/Provider paradigm.
• Using the SQL Table Create builder to create a sample database table and generate a service provider with create, retrieve, update and delete operations.
• How to use a Data Services User Interface builder in a Service Consumer to automation generation of the user interface for a service provider's operations.
  

You will create a service provider model against a sample database using the SQL Table Create builder and create a user interface service consumer model using the Service Consumer and Data Services User Interface builder. Your provider model will use the Table Create builder to create a database table from sample data provided in an XML file, and to generate the service operations that turn the model into a service provider. What you learn in this tutorial, including the consumer/provider paradigm and the Data Services User Interface usage, will give you knowledge you will need when using other data providers such as Domino.

Setup Requirements

The following setup is required to run the tutorial.

• You need to have created and published a project. The project must include the "Tutorials and Samples /Tutorials" feature set. Creating a project is described in detail in the tutorial "Creating a WebSphere Portlet FactoryProject".
• WebSphere Portal server or WebSphere Application Server Community Edition must be running.
  

Creating your database application

1. In this part of the tutorial, we will create a new WebSphere Portlet Factory model for our provider.
1. Launch WebSphere Portlet Factory Designer.
2. Open the MyFirstProject WebApp project you created in the "Creating a WebSphere Portlet Factory Project" tutorial.
3. Choose File -> New -> WebSphere Portlet Factory Model. This command will invoke a wizard to assist you in creating a new model.
4. Choose the project in which this model should be created. The MyFirstProject project should be highlighted. Click Next to continue.
5. When creating a new model, you can choose from a list of starter models. In the Select Model page, choose Empty from the Factory Starter Models category and click Next.
6. The Save New Model dialog displays. Models are saved under the models folder in the project. Choose the default folder location and enter MyProviderModel and click Finish. WebSphere Portlet Factory Designer will now create your new model and open it for you.
   
2. Adding a SQL Table Create builder
   
Note: This part of the tutorial we will create a new database table using the SQL Table Create builder. If you have an existing database table that you want to use instead, you would use the SQL Data Services builder in this step instead of SQL Table Create. The specific fields in your table would of course be different than what we describe here, but the other steps and builders we describe would be the same. If you are running on WAS CE you can use the sample EMPLOYEE table which is similar to our sample data.

1. In the lower left corner of the workspace, there is a tab labeled Outline which contains all the builders that the model uses, or the model's "builder call list". Models are constructed from builders, and each builder in a model is represented by a builder call. Builder calls can be edited by double-clicking them in the builder call list.
2. With the MyProviderModel model selected, click to display a list of builders that can be added to this model.
3. Type the word SQL in the search box and find and select the SQL Table Create as the type of builder call to add and click OK. This new builder call displays the builder call editor in the pane on the right. This builder creates and populates a table for the sample, and calls the SQL Data Services builder, which generates create, retrieve, update and delete operations as a service provider.
   
Note: If you had an existing database and table, you would likely use the SQL Data Services builder instead of the SQL Table Create builder.

4. You must give this builder call a name. In the Name field, enter MyDB
5. Click on the Fetch DataSource Names button. This feature will obtain any existing datasources defined on the server named in the Server Configuration dialog and it will also allow you to select a sample datasource which can be created for you if it does not already exist. In our case, since we configured our environment to work with WebSphere Portal, this function will retrieve any database information from that server.
6. In the SQL Datasource field, select jdbc/CloudscapeforWPF If this did not exist yet, you would have been prompted to create that datasource. If you are using WAS CE, select java:comp/env/jdbc/MyDataSource
7. Click on the chooser icon to the right of the Import Data input and select /WEB-INF/samples/data/Employees.xml to use for initial table data and click OK.
8. In the Table Name field, enter MyDBTable
9. Click on Scan Import Data to populate the table column fields.
10. Open the Sample Data Formats Section and change the Maximum string size to 512.
11. Click on the Create Table button to create the table and populate it with sample data.
12. Click in the Application tree and see what we have just created. Click on WebApp/Data Services/MyDB and Schemas to get familiar with the objects created here. Note that the column types in the schema match the data types for the DB columns, and that the schema will be used by UI consumers to help generate the page controls
13. Return to the Builder Call Editor tab for the SQL Table Create builder you just created and in the Advanced section of SQL Table Create builder, and check the Enable Testing Support checkbox, which will generate a test user interface for this provider model. In this way, each operation can be run standalone prior to building the user interface consumer model.
14. Click OK and then run the model. You will be prompted to save your model. Click Yes to continue. Your default browser will display the test UI page.
    
    Click on listMyDBRecords to view a list of records in your database table. Copy one of the employee ID fields, then click the Back button at the bottom of the list of records, click retrieveMyDBRecord and paste in the employee ID, then click OK. This retrieves details for that row in the database table and displays those details in an automatically generated test page. These steps illustrate the automated testing support available to WebSphere Portlet Factory service provider developers, that enables them to test data providers before the user interface developers have finished creating user interface consumer models for those providers.
    
You have completed the step to build a service provider! In the next steps we will create and use a service consumer to work with this provider.

3. In this part of the tutorial, we will create a new WebSphere Portlet Factory model, this time for the Consumer which will provide the user interface for the above data service provider.
1. Open the MyFirstProject WebApp project you created in the "Creating a WebSphere Portlet Factory Project" tutorial.
2. Choose File -> New -> WebSphere Portlet Factory Model. This command will invoke a wizard to assist you in creating a new model.
3. Choose the project in which this model should be created. The MyFirstProject WebApp project should be highlighted. Click Next to continue.
4. When creating a new model, you can choose from a list of starter models. In the Select Model page, choose Empty from the Factory Starter Models category and click Next.
5. The Save New Model dialog displays. Models are saved under the models folder in the project. Choose the default folder location and enter MyConsumerModel and click Finish. WebSphere Portlet Factory Designer will now create your new model and open it for you.
   
4. Adding a Service Consumer builder to bring in the data service, select the provider that you just created
1. With the MyConsumerModel model selected, click to display a list of builders that can be added to this model.
2. Type the word Service in the search box and find an select the Service Consumer as the type of builder call to add and click OK. This new builder call displays the builder call editor in the pane on the right.
3. You must give this builder call a name. In the Name field, enter MyConsumer
4. In the Provider Model field, click on the chooser to the right of the input field and pick MyProviderModel from the list and click OK.
5. Click OK to save your changes. The builder call editor closes and displays the Application tree.
6. Let's look in the Application tree and see what we have just created. Click on WebApp/Data Services/MyConsumer and Schemas to get familiar with the objects created here.
   
5. Adding new Data Services User Interface builder
1. With the MyConsumerModel model selected, click to display a list of builders that can be added to this model.
2. Type the word Data in the search box and find an select the Data Services User Interface as the type of builder call to add and click OK. This new builder call displays the builder call editor in the pane on the right.
3. You must give this builder call a name. In the Name field, enter MyDataUI.
   
Note: Leave the rest of the inputs at their default values, because in this scenario, there is only one data service available and because it was able to determine the key field for that database table, the key field is filled in for you already. This would be a good time to look over the other inputs to get an idea of what can be specified.

4. Click OK to save your changes. The builder call editor closes and displays the Application Tree.
5. Click on the Pages tab and then click on each of the pages in Pages view, and notice that the Design view shows a graphical view of your application.
   
6. Run the model by clicking from the row of icons immediately below the menu at the top of the workspace. You will be prompted to save you model. Click Yes to continue.
   
What you should see




You should see a generated web user interface displaying the first 5 rows of your database table with paging links and buttons and a create button. Try using the application at this point, paging through the rows, updating, creating and deleting records. The entire web application, including the database access, the operations that manipulate the data and the web based user interface including view, details and update pages were generated for you, as a result of the builder calls you created in the last steps. Unlike other wizard based frameworks where you would have to delete the artifacts and start over if you wanted a different variation on this application, you may reopen any of the previous builder calls and modify the inputs, and you may add and remove additional builder calls to customize the generated application.

In a later tutorial, you will also learn how to use profiling to allow for logic based customization of those builder inputs, resulting in automation of variations on the application based on user groups or roles or other custom logic or information available at runtime. Note that the default list user interface is fairly reasonable, but includes all rows and generated labels. We will show how to customize the generated user interface later in this tutorial.

7. Add a Portlet Adapter builder to enable this model to be used as a portlet
1. With the MyConsumerModel model selected, click to display a list of builders that can be added to this model.
2. Type the word Portlet in the search box and find an select the Portlet Adapter as the type of builder call to add and click OK. This new builder call displays the builder call editor in the pane on the right.
3. You must give this builder call a name. In the Name field, enter WPFTutorialDBAccess. This will be used as the "Portlet Name" by the portal.
4. In the Portlet Title field enter "WPF Tutorial DB Access". This will be used as the Portlet Title by the portal.
5. Click OK to save your changes. The builder call editor closes and displays the Application tree.
6. If your project is configured against WebSphere Portal (as opposed to WebSphere Application Server Community Edition), you may now publish your project to the portal server, and then add your portlet to a portal page. If you are using WebSphere Application Server Community Edition, you may skip this step at this time.
   
8. Adding a Data Field Settings builder
1. Open the page MyDataUIList, click the page view for the list page and right click on the Employee Number column and select Data Field Settings -> Field.
2. Using the Sort drop down list box, change the Field Type to Integer (Sorted) to make the employee number column sortable based on numeric value. This step adds a Data Field Settings builder to your model, with the field type set for the employee number and the rest of the columns left as they were. You could have also added the Data Field Settings builder directly from the builder palette.
3. Now that you have this Data Field Settings builder call, double click on it in the builder call list to open up the builder user interface so that you can see what it has set for you, and we'll also add some more customization of the columns here.
   
   
Note: In may fields, you can type the entry directly instead of selecting a choice from a drop down list box.

4. Use this table as a guide to the correct field values
   

Name	Label	Hide	Sort	Field Type
EMPNO	[Employee Number]	[Show Always]	On	Integer
FIRSTNME	First Name	[Show Always]	[Off]	String
MIDINIT	Middle	[Show Always]	[Off]	String
LASTNAME	[Last Name]	[Show Always]	On	String
WORKDEPT	[Work Department]	Hide only in tables only	[Off]	String
PHONENO	[Phone Number]	[Show Always]	[Off]	String
GENDER	[Gender]	Hide only in tables only	[Off]	String
BIRTHDATE	[Birth Date]	Hide only in tables only	[Off]	Date: yyyy-MM-dd
SALARY	[Salary]	Hide only in tables only	[Off]	Currency: #,###.00
EMAIL	[Email]	Hide only in tables only	[Off]	String
COMMENTS	[Comments]	Hide only in tables only	[Off]	Rich Text



5. Click OK.
   
9. Run the model by clicking from the row of icons immediately below the menu at the top of the workspace. You will be prompted to save you model. Click Yes to continue.
   
What you should see

Note that the initial view page showing a tabular list of records has a more manageable number of columns for a portlet user interface. At this point, try to edit, delete, and create items in each view.

Lessons learned and next steps

In this tutorial you created a service provider model against a sample database using the SQL Table Create builder, and then created a user interface service consumer model, using the Service Consumer and Data Services UI builder. You then customized the user interface with Data Field Settings builder. Again, if you already have a database table, then you would use the SQL Data Services builder directly, rather than the SQL Table Create builder (which wraps SQL Data Services, and adds table creation) like you had above.

Note: The approach taken in this tutorial was to expose the structure of the Provider/Consumer model. If you have an existing database, use the Data Services User Interface wizard to quickly build a complete user interface (UI) for a selected service. This wizard automatically generates pages, forms, actions, buttons and other user interface components for the operations from a service provider model. You can use the SQL Data Services builder to create a provider, or you can use other builders such as Domino Data Access or Service Definition.

Now, you are ready to complete the Using profiling in your new application tutorial where you will you add the power of profiling to an application.

Parent topic: Tutorials for IBM WebSphere Portlet Factory: wpf7