Developing Database
Applications
Peter Komisar © Conestoga College  version 1.0  Fall / 2007

Reference:  Third Draft Document for Review, Sept. 17,2007,
SG24-7501-00 'Rational Application Developer V7 Programming
Guide'.

This is a synopsis of information found in the latter part of
the 8th chapter, Java Applications and the first half of the 9th
chapter, 'Developing Java Database Applications' found in the
third draft Document for Review, dated October 23, SG24-7501-00
Rational Application Developer V7 Programming Guide. It has
been prepared for private academic purposes and is not an
effort to publish the contained material.

Any anonymous quotes are from the above noted reference. 

Java Applications Continued


The first section is ties up the remaining part of the
very long chapter, on Java Applications.

Following are features found in the last chapter
of the Redbook draft, which we will explain briefly
in class and other wise omit from the content of
the course.

Additional features used for Java applications
We pick up on page 300 of the Java application
chapter in the Redbook pdf. This is a section with
details regarding the use of the Java Editor which
has many useful features we should look at.


Java Editor and Rapid Application Development

Check the Toolbar // From Left to Right
Navigating in the Package Explorer

The Package Explorer can be used to navigate
packages, down to the source or class level of
a Java hierarchy. The Outline view takes you the
rest of the way into source files.


Navigating in the Outline View  // source structure

"The Outline view displays an outline of a structured
file that is currently open in the editor area, and lists
structural elements."   // good definition

In a Java source file the structural elements are:
package name, import declarations, class, fields,
and methods. We use the RAD7Java project to
demonstrate the use of the Outline view to navigate
through the code:

Navigating in the Outline

Using Bookmarks in Navigating Code

The Bookmarks view displays all bookmarks in the workspace.

Showing Bookmarks

Setting Bookmarks

Viewing  Bookmark

Double clicking  the symbol in the gray sidebar
opens the file and navigates to the line where
the bookmark has been set.


Removing Bookmarks

Right click the symbol in the gray sidebar
and select delete.

// some shortcuts

Source Folding

Showing Type Hierarchy for A Chosen Type

Smart Insert  

When the editor is in smart insert mode  when you cut
and paste code from a Java source to another Java
source, all the needed imports are automatically added
to the target Java file.

Configuring Smart Insert Mode

// check the dialog for different features

To toggle the editor between smart insert and insert
modes, press Ctrl+Shift+Insert.


Marking Occurrences

"When enabled, the editor highlights all occurrences of
types,  methods, constants, non-constant fields, local
variables, expressions throwing a declared exception,
method exits, methods implementing an interface, and
targets of break and continue statements, depending
on the current cursor position in the source code."


Enable or Disable Feature
Configuring Mark occurrences
// Skipping  Smart Compilation, compile as you save
// File Searching, Working Sets

Quick Fix

Quick Fix completes tasks quickly and works
with Quick assists. They depend on cursor
positions.  Pressing Ctrl-1 provides suggestions
to complete tasks quickly.

// see text for configuration

Content Assists

Provide code assists via drop down menus.
For example different method suggestions.

// called Intellisense in other applications


Import Generation // not the import code function

This function simplifies finding the correct import
statements to use in the Java code.

select Source → Add Import, or
select the type and press Ctrl+Shift+M.

// does explicitly what we saw done earlier
// automatically

Example  // In a simple class


Search

The search function is sophisticated and is left
for you to explore. You can get at several ways:

Activating Search


Adding Constructors

This feature automatically adds constructors. Constructors
can be added from the superclass or using fields.

Adding Constructors From Superclass
Constructor using Fields
Delegate Methods Function

We skip this function as we need to make time,
This is sophisticated feature that enables relocating
methods to different classes to make for better
encapsulation. // see the pdf at page  316


Refactoring

During the process of developing an application
it may be necessary to rename classes, move them
between classes between packages, or break out
code into separate methods, all time consuming
and error prone tasks.

The refactoring function helps with this process.

Refactoring Example // renaming a class

Refactoring Example 2  // changing an method
Refactor Actions

Database Applications

RAD 7 offers a rich feature set to support database applications.

The chapter is written for three types of users:
// the database user
// the database developer
// the database designer


Open Source Derby Database Server

Chapter examples are demonstrated against the open source
Derby database server which is embedded inside  RAD 7.
The examples can also be applied to DB2 databases.

JDBC Overview

Java DataBase Connectivity (JDBC) is similar to Open DataBase
Connectivity (ODBC) however instead of relying on sections of
C code, it is based on Java. JDBC allows dynamically connecting
to different databases from a Java application or applet.

JDBC is vendor neutral and can be used to access a wide range
of relational databases and even tabular sources of data such as
flat files or spreadsheets.

JDBC is especially well adapted for use on the Web applications
where database connections can be made using standard network
connections.

While JDBC 1.x connected to databases via the DriverManager
interface, with high connection overhead, JDBC 2.x provided a
DataSource class that allowed accessing a pool of connections
which improved performance, and simplified resource allocation.

// resources are allocated only from data source objects

Data Source Operations

When a servlet or client needs a connection, it looks up
a data source object by name from a JNDI server (Java
Naming and Directory Interface).

The servlet / client requests a connection from the data
source

If the data source object has no more connections, it
may ask the database manager for more connections
(up to a specified limit on the number of connections).

When finished  the client releases the connection.

The data source object then returns the connection
to the pool making it available again.


Installing the ITSOBank Database

The text supplies in the appendix a method for
setting up the ITSOBank database for RAD 7.
The data is housed in the zip file called 7501code.

It uses batch files that address the directories
that are conventional for RAD 7.

In the event that only the Enterprise version of
Eclipse is available,and not RAD 7, the extracts
may be put under C: and the batch files that create,
load and list the database may be modified to suit
the changed locations. This is the approach taken b
below.

Following is the procedure that was used to install
the Derby database, load the ITSOBank database
and connect to it via the Enterprise version of
Eclipse.

Download The Derby Database

The Derby database is a lightweight database
that can be embedded in an application. Derby
is a sub-project of the Apache DB project.

The Derby Page

http://db.apache.org/derby/

Click Downloads and take the latest release
which at the time of this writing is 10.3.1.4.
It is a relatively small download.

Extract the zip file and move it directly under
the C: directory. You will end up with a directory
as follows.   

// I used C but you can put it anywhere as
// long as this is later reflected in the batch
// file modifications *

C:\db-derby-10.3.1.4-bin

The bin directory has a number of commands
to start the database. Activation can be done
through Eclipse.


The 7501 Page

The Derby Application utilizes the whole directory
of the download we did last week where we used
only the contents of the java\imports  directory.

ftp://www.redbooks.ibm.com/redbooks/SG247501

The download yields a zip file called 7501code.
Unzip it and put the extracted directory under C:

//* ditto

Under C:\7501code\database\derby find
Open in a text editor like notepad DerbyCreate.

Example DerbyCreate Batch File

SET WAS_HOME=C:\IBM\SDP70\runtimes\base_v61
SET JAVA_HOME=C:\IBM\SDP70\runtimes\base_v61\java
SET DERBY_HOME=C:\IBM\SDP70\runtimes\base_v61\derby

set PATH=%WAS_HOME%\bin;%JAVA_HOME%\bin;%PATH%

@echo Creating Derby EJBBANK database
java -classpath "%DERBY_HOME%\lib\derby.jar;%DERBY_HOME%\lib\derbytools.jar" org.apache.derby.tools.ij tables.bat

pause

The first, Set WAS_HOME references the WebSphere
Application Server which I am not using. so that line
I deleted by changing it to a remark. ( REM )


Alternate Batch File Created Called MyDerbyCreate // dropped the pause

REM SET WAS_HOME=C:\IBM\SDP70\runtimes\base_v61

SET JAVA_HOME=C:\Program Files\Java\jdk1.6.0_01
SET DERBY_HOME=C:\db-derby-10.3.1.4-bin

set PATH=%WAS_HOME%\bin;%JAVA_HOME%\bin;%PATH%

@echo Creating Derby EJBBANK database

java -classpath "%DERBY_HOME%\lib\derby.jar;%DERBY_HOME%\lib\derbytools.jar" org.apache.derby.tools.ij tables.bat


Do the same for each of DerbyLoad and DerbyList

MyDerbyLoad

REM SET WAS_HOME=C:\IBM\SDP70\runtimes\base_v61
SET JAVA_HOME=C:\Program Files\Java\jdk1.6.0_01
SET DERBY_HOME=C:\db-derby-10.3.1.4-bin

set PATH=%WAS_HOME%\bin;%JAVA_HOME%\bin;%PATH%

@echo Creating Derby EJBBANK database

// java -classpath "%DERBY_HOME%\lib\derby.jar;%DERBY_HOME%\lib\derbytools.jar" // org.apache.derby.tools.ij tables.bat



@echo Loading Derby EJBBANK database
java -classpath "%DERBY_HOME%\lib\derby.jar;%DERBY_HOME%\lib\derbytools.jar" org.apache.derby.tools.ij load.bat


MyDerbyList

REM SET WAS_HOME=C:\IBM\SDP70\runtimes\base_v61
SET JAVA_HOME=C:\Program Files\Java\jdk1.6.0_01
SET DERBY_HOME=C:\db-derby-10.3.1.4-bin

set PATH=%WAS_HOME%\bin;%JAVA_HOME%\bin;%PATH%

@echo Creating Derby EJBBANK database

// java -classpath "%DERBY_HOME%\lib\derby.jar;%DERBY_HOME%\lib\derbytools.jar" // org.apache.derby.tools.ij tables.bat


@echo Loading Derby EJBBANK database
java -classpath "%DERBY_HOME%\lib\derby.jar;%DERBY_HOME%\lib\derbytools.jar" org.apache.derby.tools.ij list.bat

Run

Run each Batch file from the Command line,
MyDerbyCreate, MyDerbyLoad and MyDerbyList.


Connecting to a Database

With our database in place we can pick up with
our Redbook at pdf page 358.

To connect to a Database in RAD 7 do the following.

Open the Data perspective
The long and short, you need to point to where the Database
file is located and where the the database class files reside.

//  Via J2EE Eclipse the view is called the Database Development Perspective.

// look at Figure 9.1 Screenshot

Filtering the Database Display

Filters are available to exclude elements from the view.
Check what you want to from the checklist.

Right click Tables under, ITSO, click properties --> Filters

The dialog offers the filtering by expression or selection.
filter

Browsing a Database with the Database Explorer

The Database Explorer view allows browsing of the
database system in a typical hierarchical fashion,
where the database connection resource is the top-
level and the database with it's contained objects
are shown under it.

Database connections can be managed and created
from this view.


Browsing Databases

Edit, Extract, and Load

Right-clicking the Customer table has the following options.
// The following appears in RAD 7 but not in J2EE Eclipse
// 'Extract as XML' --> to generate an XML file from a table


Creating SQL Statements

SQL statement can be created using the SQL builder
in the Data perspective. The SQL builder supports the
following:

SQL Builder Supported Features
In this section, an SQL query to retrieve a customer name
based on the social security number, and the total amount
of money involved in each transaction type whether credit,
or debit is created.

The SQL select statement includes
table aliases
table joins
a query condition
a column alias
a sort type
a database function expression and
a grouping clause.


Creating a Data Development Project

"Before you create routines or other database development
objects, you must create a data development project to store
your objects. A data development project is linked to one
database connection in the Database Explorer."

A data development project stores routines and queries
of the following types:
 These objects can be tested, debugged, exported, and
deployed from a data development project.

The data development project wizards use connection
information to help develop objects targeted for a specific
database.

Creating a Data Development Project
Populating the Transactions Table

The TRANSACTIONS table is populated with more data.
We might have just right-click over the table and used the
edit function. In the RAD 7 trail, a file is used via the Import
function.  Following is the route described in the pdf. It is
shorter to right click over the table, select Data, select Load
and browse for the file.

To load the records into this table:


Executing an SQL Select Statement.

In Eclipse, an SQL File is opened by selecting

File --> New --> SQL File.

Select the Connection Name and Type and the Database.

Enter SQL as required such as the following.

SQL Example

Select  * from ITSO.CUSTOMER where ( TITLE = 'MS');
// required single quotes

Right-click on SQL Editor screen and select Execute.

The status of the execution is shown in one window and
the results in another.


Creating a Select Statement in RAD 7

RAD 7 has a slightly different GUI for doing statements.

Object: To retrieve a customer name and the total amount
of money involved in each transaction type whether credit
or debit.

To Create the Select statement:

In the Data Project Explorer view:

Using the SQL Builder

The SQL Builder has three main sections:

SQL Source Pane

The SQL Source pane shows source code for SQL
statements. SQL statements can be typed in or built
using the SQL builder. In both RAD 7 and Eclipse,
content assist is available as you type and through
the pop-up menu. The popup menu shows simple
examples of the statement type that is being created.

Tables Pane

The Tables pane provides a graphic of the tables used
in the statement.  tables can be added, removed or
aliased. Also, columns can be selected or excluded.
Joins can also be defined between tables.

Design Pane

The design pane supplies options, that vary depending on
the statement type being created. Multiple options appear
as notebook pages. For example, for a SELECT statement,
options include selecting columns, creating conditions,
creating groups, and creating group conditions.


What we are skipping in the Database Chapter

A lot of the next part of the chapter shows RAD 7s SQL
Visual Builder in action.  (If this area is of interest to you
you should give it a closer look.)

These pages show how RAD 7 can be used to build
more and more sophisticated SQL  statements. Note,
we know from our work with J2EE Eclipse, that these
statements, however elaborate, can be entered as text
into an SQL file and executed for testing purposes.
Other SQL tools could be used to generate statements
which could be cut and pasted into the editor. 

This becomes the domain of Database specialists.
We have covered enough of this topic to position a
database to handle the generic back-end needs of
different types of J2EE application.

In the bigger picture of using Eclipse with a back-end
database, once an SQL statement has proven to be
correct, it would typically be integrated into a Java
program via JDBC. With respect to Container managed
Enterprise JavaBeans,  the J2EE container would supply
wizards for creating data objects and all the database
connections would be handled in the background once
initial configuration was complete.

There are three more topics in the Chapter we will
also have to skip. These are discussed briefly below.


Developing Java Stored Procedures

Stored procedures can be built directly using
regular Java code. Stored procedures are custom
functions that are stored on the database and
executed there to provide some efficiencies to
a networked system by reducing throughput
between the database and the client. Again this
is an elaboration of SQL facilities.


Developing SQLJ applications

More interesting is SQLJ which for Java zealots is
appealing as it seamlessly dovetails SQL into Java.
Following is a sample of SQLJ code from the text.
There is quite a bit of wizarding that goes on to set
up the connections and create the SQL before this
code can be run.

While an interesting study, it is just another way to
script accesses to a database which can be done
easily with conventional JDBC.

SQLJ Example // from reference text noted above

package itso.rad7.sqlj;

public class TestSQLJ {
public static void main(String[] args) {
try {
CustomerAccountInfo info = new CustomerAccountInfo();
info.execute(99999); <==== minimum balance displayed
while (info.next()) {
System.out.println("Customer name: " + info.getCUSTOMER_TITLE()
+ " " + info.getCUSTOMER_FIRSTNAME() + " "
+ info.getCUSTOMER_LASTNAME());
System.out.println("Account ID: " + info.getACCOUNT_ID() +
" Balance: " + info.getACCOUNT_BALANCE());
System.out.println("---------------------------------------");
}
info.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

 Data Modeling

RAD 7 supplies sophisticated tools for creating
a database from the ground up. RAD 7 is unique
in that it supplies a visual builder based on UML.
J2EE Eclipse also supplies modeling tools.

// page 365 to end of chapter is omitted from
// course coverage

Assignment

Install the Derby ITSOBank application. Connect
to it with Eclipse or RAD 7. Modify one of the tables.
Submit a screen shot of the table contents before
and after in the Edit window.