Set up program to execute SQL
Getting Started with Derby
30
String connectionURL = "jdbc:derby:" + dbName + ";create=true";
...
try {
conn = DriverManager.getConnection(connectionURL);
... <most of the program code is contained here>
} catch (Throwable e) {
...
}
Set up program to execute SQL
INITIAL SQL SECTION: The program initializes the objects needed to perform
subsequent SQL operations and checks to see if the required data table exists.
The statement object
s
is initialized. If the utility method
WwdUtils.wwdChk4Table
does not find the
WISH_LIST
table, the statement object's
execute
method creates the
table by executing the SQL stored in the variable
createString
.
s = conn.createStatement();
if (! WwdUtils.wwdChk4Table(conn))
{
System.out.println (" . . . . creating table WISH_LIST");
s.execute(createString);
}
The
INSERT
statement used to add data to the table is bound to the prepared statement
object
psInsert
. The prepared statement uses the question mark parameter
?
to
represent the data that will be inserted by the user. The program sets the actual value to
be inserted later on, before executing the SQL. This is the most efficient way to execute
SQL statements that will be used multiple times.
psInsert = conn.prepareStatement
("insert into WISH_LIST(WISH_ITEM) values (?)");
Interact with the database
ADD / DISPLAY RECORD SECTION: This section uses the utility method
WwdUtils.getWishItem
to gather information from the user. It then uses the objects
set up previously to insert the data into the
WISH_LIST
table and then display all
records. A standard
do
loop causes the program to repeat this series of steps until the
user types
exit
. The data-related activities performed in this section are as follows:
· The
setString
method sets the substitution parameter of the
psInsert
object
to the value typed by the user. Then the
executeUpdate
method performs the
database insert.
psInsert.setString(1,answer);
psInsert.executeUpdate();
· The statement object
s
is used to select all the records in the
WISH_LIST
table and
store them in the
ResultSet
named
myWishes
.
myWishes = s.executeQuery("select ENTRY_DATE, WISH_ITEM
from WISH_LIST order by ENTRY_DATE");
The
while
loop reads each record in turn by calling the
next
method. The
getTimestamp
and
getString
methods return specific fields in the record in the
proper format. The fields are displayed using rudimentary formatting.
while (myWishes.next())
{
System.out.println("On " + myWishes.getTimestamp(1) +
" I wished for " + myWishes.getString(2));
}
Close the
ResultSet
to release the memory being used.
