Working with databases

Comments

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 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.

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 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 Related topics 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 Files\IBM\Rational\SDP\6.0\runtimes\base_v6\cloudscape\bin\embedded\cview.bat. 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
    shortcut to 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
    Cloudview screen
    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_v6\cloudscape 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
    Cloudscape files created
    Cloudscape files created
  7. The Cview window opens:
    Figure 4. Cview window
    Cview window
    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
    New Table
    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
    CUSTOMER Table
    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
    CUSTOMER table details
    CUSTOMER table details
  7. Select ACCTNUM, then select the New icon again (with hammer) and choose Key.
    Figure 8. New Key option
    New Key option
    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
    ACCTNUM primary key
    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
    Entering CUSTOMER data
    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
    Adding data into rows
    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
    New Database connection
    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
    Specify connection parameters
    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
    Copy to Project
    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
    Generate DDL
    Generate DDL
  9. In the Generate box, browse to WebTutorProject.Keep the other default values.
    Figure 16. Generate SQL DDL
    Generate SQL DDL
    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
    Generated Customer.sql
    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
    Sample contents
    Sample contents
  2. You should see the data in the DB Output view.
    Figure 19. DB Output
    DB Output
    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
    New Table definition
    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
    NEW Table Columns
    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
    STREET column
    STREET column
  7. Add a column named ZIP, type VARCHAR, length 10, but do not check nullable.
    Figure 23. ZIP column
    ZIP column
    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
    Foreign key column
    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
    Primary key details
    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
    Foreign key details
    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
    Data definition pane Tables
  17. Right-click APP.ADDRESS and select Deploy to generate the table in the database.
    Figure 28. Deploy table
    Deploy table
    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
    Data Export options
    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
    DEPLOY Database Connection
    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
    REFRESH WebTutorialConn
    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
    New Statement
    New Statement
  25. Type InsertAddressData in the New Insert Statement window.
    Figure 33. New Insert Statement
    New Insert Statement
    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
    ADDRESS table: middle section
    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
    INSERTADDRESSDATA details
    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
    INSERTADDRESSDATA SQL
    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
    INSERTADDRESSDATA SQL row data
    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
    INSERTADDRESSDATA Execute
    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
    INSERTADDRESSDATA Execute results
    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
    INSERTADDRESSDATA Execute failure
    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
    Execute SELECTGOLDSTATUSCUSTOMERS
    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.

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 Downloadable resources). 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");
      e.printStackTrace();
    }
  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");
         e.printStackTrace();
         } 
    }
  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
    Enhanced .ear file -- application.xml
    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
    Create a JDBC Provider
    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
    ver5
    ver5
  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
    Create a DataSource details
    Create a 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
    Add Reference
    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
    Resource Reference details
    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
    Reference details
    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
    Select Tasks
    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
    Result from running Input.jsp
    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!


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Rational, DevOps
ArticleID=106687
ArticleTitle=Working with databases
publish-date=03282006