Part 4 working with databases

Rational Application Development certification
prep, Part 4: Working with databases
Skill Level: Introductory

Gregory Scott (glscott1@us.ibm.com)
Senior Learning Specialist
IBM

28 Mar 2006

This is the fourth in a series of seven tutorials created to help you prepare for the IBM
Certification Test 255, Developing with IBM Rational® Application Developer for
WebSphere® Software V6. This tutorial uses the Data perspective in Rational
Application Developer 6. It shows you how to connect from Application Developer to
the database, sample the data from within Application Developer, and use visual
tools to create SQL statements and to run them.

Section 1. Before you start

About this series
Rational® Application Developer for WebSphere® Software is a product of the IBM
Software Development Platform that lets you quickly design, develop, analyze, test,
profile and deploy Web, Web services, Java™, J2EE, and portal applications. This
series of seven tutorials helps you prepare to take the IBM certification Test 255,
Developing with IBM Rational Application Developer for WebSphere Software V6 to
become an IBM Certified Associate Developer. This certification targets entry level
developers and is intended for new adopters of IBM Rational Web Developer or IBM
Rational Application Developer for WebSphere Software V6.0, specifically
professionals and students entering into Web development using IBM products.

About this tutorial
This tutorial uses the Data (often called the database) perspective in Rational
Application Developer 6 (hereafter Application Developer). You can work in the Data

Working with databases
© Copyright IBM Corporation 1994, 2007. All rights reserved. Page 1 of 38

developerWorks® ibm.com/developerWorks

perspective of Application Developer to accomplish all, or many, of the typical tasks
involved with databases: creating tables, columns, SQL and DDL statements, and
primary and foreign keys.

Objectives
Application Developer offers the ability to work easily with databases without having
to open other tools, and this tutorial highlights the relevant features. After completing
this tutorial, you should be able to create a database instance, tables, columns, and
primary and foreign keys along with some sample data, from within the Data
perspective and directly from within Cloudscape, the database that comes with
Application Developer. You should also know how to connect from Application
Developer to the database, sample the data from within Application Developer, and
use visual programming features to create SQL statements and to run them. Finally,
you should be able to use JDBC in a servlet originally created in Tutorial 3 to retrieve
data from the tables that you created and populated.

Prerequisites
This tutorial assumes you have gone through the three previous tutorials in this
series or have equivalent knowledge with Application Developer. A basic grasp of
relational database fundamentals is helpful, but not required.

System requirements
To run the examples in this tutorial, install Rational Application Developer for
WebSphere Software or Rational Web Developer for WebSphere Software.
Download a free trial version of Rational Application Developer for WebSphere
Software if you don't have it installed.

The hardware and software requirements for this software can be located at IBM
Rational Application Developer System Requirements.

Section 2. Creating Cloudscape tables and accessing
them through Application Developer

Step 1: Create an instance of Cloudscape
The Cloudscape database that comes with Application Developer is a free database
that is useful for testing and development. You can have only one connection at a

Page 2 of 38 © Copyright IBM Corporation 1994, 2007. All rights reserved.

ibm.com/developerWorks developerWorks®

time with the development version of Cloudscape, therefore do not use it for
production (a version is available that allows multiple connections). Cloudscape is
installed with Application Developer. See Resources for more information.

To start Cloudscape:

1. Create a shortcut to cview.bat. The cview.bat file is installed under the
Application Developer program files. The full path, for example, is:
C:Program
FilesIBMRationalSDP6.0runtimesbase_v6cloudscapebinembedd
Find your own corresponding cview.bat and make a shortcut to it using
your operating system tools. In Windows, right-click the file and choose
Create Shortcut. Place the shortcut on your desktop.
Figure 1. Cloudscape icon

2. Rename the text for the icon to something meaningful to you.

3. Double-click the icon to start Cloudscape. The Cloudview screen appears:
Figure 2. Cloudview screen



4. Create a new database by clicking File > New > Database on the menu
bar or by using the hot key Alt-d.

5. In the New Database window, do not accept the default for the location of
the database files. The default path is created under the
base_v6cloudscape subdirectory (part of the same path as the cview.bat
file). To see the default path name, click Directory. However, you won't
be using the defaults. Instead, enter the name C:WebTutorialDB.

6. Click OK. The following directories making up your Cloudscape database
instance are created under C:WebTutorial:
Figure 3. Cloudscape files created

7. The Cview window opens:
Figure 4. Cview window

In the next section, create two tables in the database, CUSTOMER and ADDRESS,
the first in Cloudscape itself and the second in the Data perspective of Application
Developer.



Step 2: Create and access tables in Cloudscape
To create a table:

1. In the Cview window, with C:WebTutorialDB selected under System, click
the large New icon (with the hammer) next to Action and select Table
from the drop-down list.
Figure 5. New table

2. Name the table CUSTOMER and leave the Schema as APP. Double-click in
the cell that has NEW_COLUMN1 and change its name to ACCTNUM.

3. Click CHAR under Type and select INT from the list.

4. Change Nullable to No. (You will make this column a Primary Key later.)
Figure 6. CUSTOMER table



You can also set features like AutoIncrement, but for this tutorial you are
going to control the primary key incrementing.

5. Click the large plus sign next to Columns. A new column is added to
CUSTOMER. Use the following values:

Name: LASTNAME
Type: Varchar
Nullable: No
Length: 50

6. Repeat the steps for your third and last column:

Name: STATUS
Type: Varchar
Nullable: No
Length: 10

Your table should look like this:

Figure 7. CUSTOMER table details

7. Select ACCTNUM, then select the New icon again (with hammer) and
choose Key.
Figure 8. New Key option



8. In the new screen, type in ACCTNUM for the key and select Primary Key
for Type.
Figure 9. ACCTNUM primary key

9. Click OK. To enter data into the table, select CUSTOMER under Tables
and click on the Data tab.
Figure 10. Entering CUSTOMER data

10. Click the green plus sign below Properties. Enter data for the primary key
(ACCTNUM), LASTNAME, and STATUS as follows (repeat by clicking the
green plus icon to enter a new row of data and then save by clicking OK,
or the Save icon next to the green plus sign). You cannot enter duplicate
ACCTNUMs.
Figure 11. Adding data into rows



Now that you know how to enter and view data within Cloudscape, try it using
Application Developer.

1. Shut down the Cloudscape DB by clicking File > Exit (do not simply click
Close, or you will not be able to connect to it through Application
Developer!).

2. In Application Developer, open the Data perspective and right-click in the
Data Explorer view in the lower-left (not the Data Definition view). The
New Database Connection wizard opens. Name the connection
WebTutorialConn.
Figure 12. New Database connection

3. Click Next. Cloudscape v5.1 should be selected by default.

4. In the location field, browse to C:WebTutorialDB. All the other fields
should be correct by default (no password is needed for Cloudscape and
notice the tool creates the instance of the database if you did not create
it). You should see the following (with your User ID):
Figure 13. Specify connection parameters



5. Click Test Connection. Your connection should return successfully (if
not, make sure you shut down Cloudscape and then check your spelling).

6. After testing the connection successfully, click Next three times until you
see the Summary, then click Finish. The Copy to Project window opens.
Figure 14. Copy to project

7. Click Yes. Name the project WebTutorProject, then click Finish
(accept the box that creates the project for you). The WebTutorProject is
created.

8. To see how Application Developer generates DDL for you, expand
WebTutorProject, select APP.CUSTOMER (remember that APP was the



schema that you accepted by default for the table), right-click and select
Generate DDL.
Figure 15. Generate DDL

9. In the Generate box, browse to WebTutorProject.Keep the other default
values.
Figure 16. Generate SQL DDL



10. Click Finish. Notice the Scripts folder that gets created. To see the sql
that Application Developer wrote for you, drill down and open
CUSTOMER.sql.
Figure 17. Generated customer.sql

11. You do not need this CUSTOMER.sql for this tutorial, so close the file. It
is still available in other contexts (such as running on another database).

Now that you have created a connection to the database, sample the CUSTOMER
data directly from the Data perspective.

1. In the Database Explorer view, right-click APP.CUSTOMER and select
Sample Contents.
Figure 18. Sample contents



2. You should see the data in the DB Output view.
Figure 19. DB Output

3. At any stage, select and right-click the Success lines, then select Delete
or Delete All to wipe away the results and refresh the DB Output pane.
(Data is not deleted from the database itself.)

Step 3: Create a table from the Data perspective
Now it's time to create a very simple ADDRESS table from within Application
Developer, and establish a foreign key to CUSTOMER (because a customer
naturally can have many addresses, for example work and home).

1. In the Data Definition pane, drill down to WebTutorProject >
WebTutorialDBâ#¦ > APP and right-click Tables.
Figure 20. New Table Definition

2. Choose New > Table Definition.

3. The Table Definition wizard opens. Name the table ADDRESS. You can
add a comment if you want in the relevant text area.

4. Click Next. The Table Columns screen lets you add columns.

5. Click Add Another. Name the column ADDRPK, type INTEGER, and
check Key column.
Figure 21. New Table Columns



6. Click Add Another and add another column titled STREET, type
VARCHAR, a String length of 50, and check Nullable.
Figure 22. STREET column

7. Add a column named ZIP, type VARCHAR, length 10, but do not check
nullable.



Figure 23. ZIP column

8. Finally, add the foreign key column. Name it CUSTFK and select the Type
INTEGER. Do NOT click Key Column as this defines it as a Primary Key.
Foreign Keys are set later in the wizard.
Figure 24. Foreign key column

9. Click Next.

10. On the Primary Key screen, change the name of the primary key to
ADDRPK.
Figure 25. Primary key details



11. Click Next.

12. On the Foreign Key screen, click Add Another.

13. Enter CUSTFK for the name.

14. Select APP.CUSTOMER from the Target drop-down list.

15. Select CUSTFK from the Source Columns and click the right arrow to
move it into the right text area.
Figure 26. Foreign key details

16. Click Finish. The following table is added to the Data Definition pane
(however, it is NOT yet added to Cloudscape).
Figure 27. Data definition pane tables



17. Right-click APP.ADDRESS and select Deploy to generate the table in the
database.
Figure 28. Deploy table

18. APP.ADDRESS is selected for you in the Deploy wizard. Click Next.

19. The next screen has many options that you can try later. For now, simply
leave the defaults.
Figure 29. Data Export Options



20. Click Next. In the Database Connection screen, check Use Existing
Connection and select WebTutorialConn from the Existing connection
drop-down list, as shown here:
Figure 30. Deploy Database Connection

21. Click Finish. The ADDRESS table is deployed to the Cloudscape
database.

22. In the Database Explorer view, right-click WebTutorialConn and select
Refresh.
Figure 31. REFRESH WebTutorialConn



23. You can now see both APP.CUSTOMER and APP.ADDRESS under
WebTutorialDB > APP > Tables. Confirm that there is no data under
APP.ADDRESS by right-clicking it and selecting Sample Contents.
Because you already know how to enter data in Cloudscape directly, in
the next section you enter it through Application Developer using SQL
statement generation features.

24. In the Data Definition view, under WebTutorProject > WebTutorialDB,
right-click Statements and select New > Insert Statement.
Figure 32. New Statement



25. Type InsertAddressData in the New Insert Statement window.
Figure 33. New Insert Statement

26. Click OK. You can see the beginnings of your SQL open in an editor. Go
to the middle pane and add the APP.ADDRESS table by right-clicking
anywhere in the Tables text area.
Figure 34. ADDRESS table: middle section

27. Notice that APP.ADDRESS was written automatically after INSERT INTO
in the SQL source text area.

28. In the middle Tables section, check the boxes for all four columns in
ADDRESS.
Figure 35. INSERTADDRESSDATA details



29. Double-click the tab WebTutorialDB - InsertAddressData to maximize
the screen. Adjust the sections by dragging the boundaries with your
mouse so you can see the full SQL statement.
Figure 36. INSERTADDRESSDATA SQL



30. Add the values for the four columns under Value (actually only three are
required because you made STREET nullable).

31. Recall that ADDRPK must be unique; also because CUSTFK points to
your CUSTOMER table, you should choose values that are in that table.
Here is sample data to view (notice how the SQL statement continues to
get written for you).
Figure 37. INSERTADDRESSDATA SQL row data



32. Save the statement (as always, the asterisk on the tab disappears when
the statement is saved).

33. In the Data Definition view, right-click InsertAddressData (under
Statements) and select Execute.
Figure 38. INSERTADDRESSDATA Execute



34. Go to the Database Explorer view and sample the contents under
APP.ADDRESS. You can see in the DB Output that indeed the data got
inserted:
Figure 39. INSERTADDRESSDATA Execute results

35. Enter more data by changing the four (or three) values in the
Column/Value section that you just used (remember to save the
Statement before executing it). If you try to add another row with
ADDRPK of an already-used value, you get an exception and the data is
not inserted into the table, as below:
Figure 40. INSERTADDRESSDATA Execute failure



36. Now that you have a working INSERT statement, copy and paste it into a
Java class and modify it in a JDBC statement to save you from having to
write it from scratch.

37. For a little more practice, create a SELECT statement using the steps
above. Review them if needed. If you have doubts, Figure 41 shows you
what to enter and how your screen should look.
The steps are:

1. Create a new statement.

2. Name it SelectGoldStatusCustomers.

3. Right-click twice in the Table section to bring up both the
ADDRESS and the CUSTOMER tables. Notice you can give them
aliases; use ADDR for the first and CUST for the second.
Note: The difference from creating the INSERT statement is that now you
only check LASTNAME and STATUS in the CUSTOMER table text
boxes, and then click the Conditions tab and fill in the following values:

Column:
CUST.STATUS
Operator: =
Value: type
in "Gold"
(which is
case
sensitive, --
GOLD does not
return data).

38. Save the statement. Execute it by right-clicking it in the Data Definition
pane and selecting Execute. You should see two rows returned:



Figure 41. Execute SELECTGOLDSTATUSCUSTOMERS

Well done! You have now created databases, tables, and columns in both
Cloudscape and Application Developer, sampled table contents, and used the tool to
facilitate writing and testing SQL statements.

Section 3. Invoking the database from the Web
application with JDBC
Now that you have your database working, return to the Web application Tutorial 3
to replace the code there that merely simulated hitting a database. The following
steps show you how to get the end-user's real status from your Cloudscape table.

Step 1: Modify the servlet from the Web tutorial to hit the DB
This section assumes that you have the code for Tutorial 3 (the solution can be
found in Downloads). It also assumes that you entered data as instructed in the
preceding steps (or that you substitute appropriately according to the information
that you entered into the tables). Run the Input.jsp in the FirstWeb module (with
proper output from the InitialOutput.jsp) before continuing.

1. Switch to the Web perspective if you are not there already. Under



FirstWeb, open ModelServlet and in doPost() either delete all the
following code or comment out the last, uncommented lines that
simulated going to a database:

// Simulate going to a database and retrieving
information
// based on the account number:
// Normally, JDBC code or a call to the database
layer
// would be here.
// For simplicity, we will mimic the database call,
// which returns the status and a balance.
String status;
if ((acctnum.length()) % 2 == 0) {
status = "Gold";
} else {
status = "Platinum";
}

2. Leave the Math.random() line because you have not implemented a
balance column in the database, a task you might do on your own after
this tutorial. In lieu of the code you just removed or commented out,
retrieve in the next section the status from the CUSTOMER table.

3. Insert the following code in the section before the Math.random() call:

String status = null;
InitialContext ctx = null;
DataSource ds = null;
Connection conn = null;

4. Use Source > Organize Imports to resolve the errors (by right-clicking
anywhere in the editor). Make sure you select the following packages for
the classes that have multiple choices: javax.sql.DataSource and
java.sql.Connection.

5. You'll see an error pertaining to status, but you can resolve that later -- for
the moment, comment out the pertinent line
cust.setStatus(status); to allow code completion and other
features of Application Developer to work correctly. Your code should look
like this and you should have no errors:

// If you reach here, the acctnum had a
value.
String status = null;
InitialContext ctx = null;
DataSource ds = null;
Connection conn = null;
double balance = (10000) *
Math.random();
Customer cust = new
Customer();
// cust.setStatus(status);
cust.setBalance(balance);
arg0.setAttribute("customer",
cust);



getServletContext().getRequestDispatcher(
"/WEB-INF/jsp/InitialOutput.jsp").forward(arg0, arg1);

6. After the Connection conn line, add: ctx = new
InitialContext();.
You get an error -- resolve it by right-clicking on the light bulb icon and
choosing Surround with try. Notice how the catch block is written for
you.

7. After the "new InitialContext();" call, within the try block, add:

ds = (DataSource)
ctx.lookup("java:comp/env/jdbc/webtutorialDB");

Keep in mind that you have not configured your DataSource yet, but you
will do that in the last part of the tutorial. The name to use in the
java:comp/env namespace is jdbc/webtutorialDB. You are
determining the name here (if your administrator or J2EE deployer wants
you to use a different name, use that instead).

8. In the catch block of the NamingException, add:

System.out.print("Caught NamingException in
doPost()of ModelServlet");

In a real project, use a logging mechanism. For the sake of simplicity
here, use System.out.print().

9. Add the PreparedStatement and SQL statements, along with the
ResultSet processing, as below:

try {
ctx = new InitialContext();
ds =
(DataSource)ctx.lookup("java:comp/env/jdbc/webtutorialDB");
conn = ds.getConnection();
PreparedStatement st =
conn.prepareStatement("SELECT CUST.STATUS FROM APP.CUSTOMER AS
CUST WHERE
CUST.ACCTNUM = ?");
st.setString(1, acctnum);
ResultSet result = st.executeQuery();
while (result.next()){
status = result.getString("STATUS");
}
} catch (NamingException e) {
System.out.print("Caught NamingException in doPost()of
ModelServlet");
e.printStackTrace();
} catch (SQLException e) {
System.out.print("Caught SQLException in doPost()of
ModelServlet");
}



10. Close your connections by adding the following immediately after your
catch blocks:

finally{
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
System.out.print("Could not close
connection");
}
}

11. You are almost ready to test, but first uncomment the line at the bottom of
doPost():

//
cust.setStatus(status);

In the next section, configure the DataSource resource reference you are using in
your ctx.lookup().

Step 2: Setting configuration properties: JNDI and references
If your line:

ds = (DataSource)
ctx.lookup("java:comp/env/jdbc/webtutorialDB");

was only:

ds = (DataSource) ctx.lookup("jdbc/webtutorialDB");

you are using a mere JNDI name. Rather, you are using a Java component
environment resource reference (hence the java:comp/env), which offers another
layer of logical indirection that is very useful, if not absolutely necessary, in
clustering circumstances.

You need to configure your server to map the resource reference (that itself implicitly
makes use of a JNDI name, which you will create later) to the DataSource, which
itself maps to the physical location of the database. Clearly, not hard coding your
database's name and location into your Java code has advantages: if administrators
want to change the location of the database they can change the mapping of the
datasource itself in the admin console and not bother Java programmers at 3 a.m.
during some morning to change the code, recompile, and redeploy.

1. In your Project Explorer pane, drill down to Enterprise Applications >
First > Deployment Descriptor and open it. Click the Deployment tab



(this is new to Application Developer 6 and gives WebSphere users an
"enhanced .ear file" that administrators can use when the .ear is deployed
-- be aware, though, that the settings here are NOT reflected in the
application.xml source code, as you can easily confirm for yourself, and
are hence not portable to other J2EE servers). You should see:
Figure 42. Enhanced .ear file -- application.xml

2. Under the JDBC provider list (top section), click Add. Select Cloudscape
as Database type and Cloudscape JDBC Provider as JDBC provider
type.
Figure 43. Create a JDBC Provider

3. Click Next. In the next screen, type in the name CloudscapeJDBC and



leave all other values as is. Click Finish.

4. Back in the Deployment Descriptor, with the new CloudscapeJDBC
selected automatically, in the lower section called "Data source defined. .
.above,", click Add.

5. Select Cloudscape JDBC Provider. Confirm that Version 5 is selected.
Figure 44. Create a DataSource

6. Click Next. In the new screen fill in these values (note that this is where
you create the JNDI name, either by yourself or in consultation with your
assembler/administrator):

Name: WebTutorialDS
JDNI name: jdbc/webtutorialJNDI

You can change the description if you prefer, but leave the other values
as is.

7. Click Next. In the new screen, leave databaseName selected in the top
section and fill in the Value text field with the address of Cloudscape's
WebTutorialDB, C:WebTutorialDB.
Figure 45. Create DataSource details



8. Click Finish, save the Deployment Descriptor, and close it.

9. So that your servlet code can actually find the JNDI resource (namely, the
DataSource), you must map in the Web application's deployment
descriptor the reference that is used for the lookup by the servlet to the
JNDI name just created.

10. Under FirstWeb in the Project Explorer, open its (Web) deployment
descriptor. Click the References tab.

11. Under the References section, click Add. Choose Resource Reference.
Figure 46. Add Reference



12. Click Next. Use the following values (leave the other as is). Beware of
case! Typos here will only be caught at runtime!:

Name: jdbc/webtutorialDB
Type: javax.sql.DataSource
Authentication: Container

You should see the following:

Figure 47. Resource Reference details



13. Click Finish.

14. Finally, in the Reference view, add the JNDI name in the lower-right
section (under WebSphere Bindings) as jdbc/webtutorialJNDI,
which you established about ten paragraphs ago in the enhanced ear file,
more precisely, in the application's Deployment Descriptor
(application.xml).
Figure 48. Reference details



15. Save and close the file.

16. To test, start the server first, or right-click Input.jsp (under FirstWeb) and
run it on the server. If you did not exit Cloudscape after you created your
ADDRESS table above, and after you tested the INSERT and SELECT
statements you subsequently created (because no instruction was given
in this tutorial to exit it), the Select Tasks window opens.
Figure 49. Select Tasks



17. Leave the Disconnect box checked and click Finish (otherwise, you can
disconnect from the Database Explorer pane in the Data perspective).

18. Make sure you enter a correct account number in the form, one that exists
in the CUSTOMER table (the lastname or age does not matter, as you
have not implemented any functionality to confirm either, even though the
LASTNAME is a legitimate column in the CUSTOMER table).

19. You should see the status returned in the browser, as follows. If you get
any errors, first examine the console output for any error messages.
Naturally, a typo (like a backspace rather than a forward space /) in one
of the JDNI or reference names in either your deployment descriptors or
enhanced .ear can be fatal and is often the culprit. If those names are all
correct and you are still having a problem, try stopping the server again,
and then restarting.
Figure 50. Result from running Input.jsp



Congratulations again! You completed the tutorial and should now have an easier
time using databases in conjunction with Application Developer!



Downloads
Description Name Size Download method
Solution from Tutorial 3 needed for second First.ear 13KB HTTP
half
Entire solution for Tutorial 4 Tutorial4SolutionAsProjectInterchange.zip
30KB HTTP

Information about download methods



Resources
Learn
• In the JDBC Technology section of the Sun Developer Network, get details on
Java Database Connectivity.
• Get details on Standard Query Language (SQL) at Database Language SQL.
• You can find information about the open source IBM Cloudscape Database at
Cloudscape.
• To learn how to master the data perspective, take the tutorial Visual data
modeling in Rational Application Developer 6.0: new data model features .
• To see how to use DB2 to master the data perspective, take the Introduction to
DB2 development with Rational Application Developer tutorial.
• Visit developerWorks Rational zone to expand your Data perspective
WebSphere skills (this link takes you to a series of other links relating to many
aspects of developing within Application Developer, especially those that
pertain to the Data perspective.
• Get certified as an "IBM Certified Associate Developer". Check out the
objectives, sample assessment tests, and training resources for test 255,
"Developing with IBM Rational Application Developer for WebSphere Software
V6".
• Stay current with developerWorks technical events and webcasts.
Get products and technologies
• Download a free trial version of IBM Rational Application Developer.
• Download a free trial version of WebSphere Application Server Version 6.0.
• Build your next development project with IBM trial software, available for
download directly from developerWorks.
Discuss
• Participate in the discussion forum for this content.
• Participate in developerWorks blogs and get involved in the developerWorks
community.

About the author
Gregory Scott
Greg is an instructor in private and public national settings, internal and external to
IBM, primarily related to Java client-side and server-side (J2EE) programming.
Specializations include servlets, JSPs, Enterprise JavaBeans, portlets, XML and Web
Services.


Part 4 working with databases

More Related Content

What's hot

Viewers also liked

Similar to Part 4 working with databases

More from techbed

Recently uploaded

Part 4 working with databases