A Comprehensive Guide to IBM WebSphere Host Publisher Version 3.5

Chapter 6. Database Integration Objects 169

Figure 6-12 Database Integration Object: connection pool configuration

6.5.3 Step 3: Creating the database Integration Object

In this step, you will create the database Integration Object (JavaBean) to query

the DB2 sample database. Here you will provide the required information to

create the SQL statement that will be sent to DB2 for execution against the

selected database.

In our scenario, we want to query the sample database for male or female

employees having a salary greater than a certain value. Whoever uses this

Integration Object will have to provide (set) the input variables (properties) to

indicate whether the query is for male or female employees and the salary

amount. The Integration Object returns all employees satisfying this condition.

The Integration Object can be invoked from a Java program or a JSP.

Start by selecting the SQL statement type. This is a query to the database, so

you will choose the Select type. Next, for this query, select the employee table

from the sample database, as indicated in Figure 6-13.

dbpool01.poolspec

dbconn01.connspec

dbpool01.userpool

170 IBM WebSphere Host Publisher Version 3.5

Figure 6-13 Selecting SQL statement type and table

The first condition in the SQL statement is the gender. It is input to the Integration

Object, so we create a variable (property) and provide a meaningful name such

as genderVar, as shown in Figure 6-14.

Chapter 6. Database Integration Objects 171

Figure 6-14 SQL Query: Condition 1

172 IBM WebSphere Host Publisher Version 3.5

The gender has to be exactly equal to M for male employees or F for female

employees. We provide a default value, as indicated in Figure 6-15.

Figure 6-15 Defining the Condition 1 for the SQL statement

Click Find on another column to insert a second condition. You therefore create

a Condition 2 tab. The second condition in the SQL statement is the salary. It has

to be greater than a certain value. This value is also input to the Integration

Object. Therefore, we create a second variable (property) and provide a name

(salaryVar) for it, as shown in Figure 6-16 on page 173.

Chapter 6. Database Integration Objects 173

Figure 6-16 Defining Condition 2 for the SQL statement

Next, we configure what columns should be returned by the Integration Object for

the employees satisfying both conditions (gender and salary) in the SQL

statement. In the Columns tab, select the column name and click Add to do this.

In this scenario, we decide to return the employee number, first and last name,

sex, salary and telephone number. See Figure 6-17 on page 174 for details.