11.1 Creating a database connection

In this section, we describe the process of creating a database connection and generating the Java classes similar to the classes used in the sample scenario included in this chapter.

In section 11.2, "Sample scenario" on page 353, a portlet application project to access a JDBC database is created from a WAR file; in this implementation, the JDBC connection is already included. This section explains how to create the database connection using the wizard provided by WebSphere Studio Site Developer.

Once a portlet application project has been created (for example, we called this project HRPortlet), open the data perspective by selecting Window -> Open Perspective -> Data .

Figure 11-1. Open data perspective

11.1.1 Creating a new connection

In the DB Servers view, select New Connectio n from the context menu to create a new connection. Enter the following values:

• Connection name : for example ConnHR

• Database: sample scenario uses WSSAMPLE

• User ID: sample scenario uses db2admin

• Password: sample scenario uses db2admin

• Database vendor type: Cloudscape, V5.0

• JDBC driver: Cloudscape Embedded JDBC driver

• Database location: C:\LabFiles\Cloudscape\WSSAMPLE

  Note : The sample scenario included in this chapter requires that you download the sample code available as additional materials. See Appendix C, "Additional material" on page 543.

• Class location: the directory where db2.jar file is located

Click Finish .

Figure 11-2. Create new connection

Note that we assume that the database has been created in your system. The new database entry is added to the DB Servers view; you can expand it to see the schemas and tables but no editor will open because this view cannot be used for editing.

11.1.2 Importing to a folder

Now you have to import the tables to use them in the application. Select the connection, right-click it and select Import to Folder .

Figure 11-3. Import the database into a project.

In the Import to Folder window, click Browse to select the portlet application project where you want to gain access to the back-end system. Click Finish .

Figure 11-4. Import to folder

If you are prompted to create a databases folder, click Yes . Now, in the Data Definition view, you can see the schemas and tables as in DB Servers view, but in this view you can update schemas and tables definitions by double-clicking them. In the Navigator view, XML files have been created for the database objects.

11.1.3 Creating an SQL statement

A simple SQL statement will be created. In the Data Definition view, select the statement folder under the sample WSSAMPLE database, right-click and select New -> Select Statement . Enter for example SQLUtility for the Statement Name and click OK .

Figure 11-5. Create a new statement

Note

If you cannot see the statement folder, close the Data perspective and open it again.

When the statement has been created, the editor opens and you can select a table to make a query, for example the Employee table. There are different ways to create a SQL statement, with the wizard or just by writing your query using the editor. For details, see for example the IBM Redbook WebSphere Studio Application Developer V5 Programming Guide , SG24-6585.

11.1.4 Generating Java classes

In the code provided in 11.2, "Sample scenario" on page 353, the SQLUtilities.java class provides methods that execute the SQL statement, returns a DBSelect reference and returns an array of objects representing the rows in the result set. To create this class, right-click the SQLUtility statement and select Generate Java Bean .

Figure 11-6. Generate Java Bean

In the first screen of the Generate Java Bean option, enter the following information:

• Source Folder: enter the Java Source folder of your portlet application project.

• Package: utilities

• Name: SQLUtilities

Click Next .

Figure 11-7. First screen of Generate Java Bean

In the next window, enter the following options:

• Use Driver Manager Connection:

  - Driver name: com.ibm.db2j.jdbc.DB2jDriver

  - URL: jdbc:db2j:C:\\LabFiles\\Cloudscape\\WSSAMPLE

  Note : The sample scenario included in this chapter requires that you download the sample code available as additional materials. See Appendix C, "Additional material" on page 543.

• For the option How will user authentication be provided select By the execute() method's caller .

Click Next .

Figure 11-8. Second screen of Generate Java Bean

Figure 11-9 on page 351 shows the methods which will be created. Click Finish .

Figure 11-9. Review the specification window

11.1.5 Running the SQL statement

In the Data Definition view, right-click the SQL statement and select Execute as shown in Figure 11-10 on page 352.

Figure 11-10. Execute a SQL statement

The results appear in the DB Output view.

Figure 11-11. Results of the execution