Database Development in Rational Application Developer 7

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Introduction

Rational Application Developer 7.0 offers a wide range of tools to work effectively with relational databases. In this article, you will learn how to establish a database connection, set up a data development project, and work with SQL Builder.

Establishing a Connection

Before any development can begin, a database connection must be established. You will use Derby 10.1 as your sample database. RAD 7.0 comes with the derby.jar file (see the com.ibm.datatools.db2.cloudscape plug-in). In case you want to download a newer version, you can do so by going to http://incubator.apache.org/derby/.

Follow these steps to create a Derby connection:

1. The first step is to open a data perspective. Go to Window &rarr Open Perspective &rarr Data (see Figure 1).



Figure 1: Data Perspective

2. Next, locate Database Pane on the screen and right-click Connection &rarr New Connection (see Figure 2).



Figure 2: Open New Connection Dialog

3. In the New Connection Pane, select Derby 10.1 and fill out Connection URL Details:
• JDBC Driver: Other
• Database: Sample
• JDBC Driver Class: org.apache.derby.jdbc.EmbeddedDriver
• Class Location: YOUR_DRIVER_PATH/derby.jar
• Connection URL: jdbc:derby: YOUR_WORKSPACE_PATH.metadata.pluginscom.ibm.datatools.db2. cloudscape.driver/sample
4. Test your connection. Note that the user name and password can be any value.



Figure 3: Set up and test Derby Connection

Now that you have created a database connection, you can move ahead to create a Data Development Project.

Create a Data Development Project

A Data Development Project is required to develop the following types of objects:

• DB2^® and Derby stored procedures
• DB2 user-defined functions
• SQL scripts

1. Open Data Perspective.
2. Click File → New → Data Development Project.
3. When New Data Development Project Dialog opens, set Project Name, Current Schema Name, and click the “Next” button.
4. Select the Derby connection that you set up previously.
5. Click Finish.



Figure 4: Data Development Project

Create a SQL Statement with SQL Builder

SQL Builder is a wizard/tool that allows you to create SQL Statements interactively. Please go through an exercise where you join two tables in a simple select.

1. Create new SQL Statement by right-clicking SQL Scripts → New → SQL Statement.This is shown in Figure 5.



Figure 5: Create new SQL Statement

2. When the SQL Statement Wizard opens (see Figure 6), you can choose between among SQL Statement Temples: SELECT, UPDATE, INSERT, DELETE, and FULLSELECT. You will select the “SELECT” Statement template. Also, make sure to select the “SQL Builder” checkbox and enter JOIN_EMPLOYEE_PHOTO as a statement name.



Figure 6: New SQL Statement

Once the SQL Statement file is created, you can start building your SQL Statement.

1. Open the EMPLOYEE_AND_PHOTO.SQL file.
2. From the database pane, select Derby Connection → SAMPLE → Schemas → SAMP → Tables (see Figure 7).



Figure 7: Open Table List

3. Drag and drop the EMPLOYEE and EMP_PHOTO tables from the database explorer pane to the editor pane (see Figure 8).



Figure 8: The EMPLOYEE and EMP_PHOTO tables are selected

4. Now, you join the tables by using inner join. Right-click the EMPLOYEE Table and select Inner Join (see Figure 9).



Figure 9: Create an Inner join between EMPLOYEE and EMP_PHOTO

5. The next step is to select what columns the select statement shall return. This can be done simply by checking the checkboxes next to the field in the table.
6. Finally, you add a where clause in which you specify that you want all employees whose last name starts with A (see Figure 10).



Figure 10: Add Where Clause

7. The last step is to execute the statement and view the results. You can do so by right-clicking the SQL Statement and selecting “Run SQL”. The results can be viewed in the Data Output pane, as shown in Figure 11.



Figure 11: View Execution Results in the Data Output Pane

Conclusion

In this article, you have learned how to create a database connection and use SQL Builder using RAD 7. In the next article, you will learn how to create SQL Stored Procedures and User-Defined Functions using RAD.

About the Author

Aleksey Shevchenko has been working with object-oriented languages for over eight years. For the past four years, he has served as a technical lead and a project manager. Aleksey has been implementing Enterprise IT Solutions for Wall Street and the manufacturing and publishing industries.