Most of the code related to the database activities performed is
included in this section, but you might find it helpful to open the program
file and follow along in a text viewer or editor. The SECTION NAMES referred
to in this section can be found in the comments within the program code and
serve as cross-reference points between this section and the Java program.
The program uses methods from the WwdUtils class. The utility
class code is not described here but is available for review in the file WwdUtils.java.
Initialize the programINITIALIZATION SECTION:
The initial lines of code identify the Java packages used in the program,
then set up the Java class WwdEmbedded and the main method
signature. Refer to a Java programming guide for information on these program
constructs.
import java.sql.*;
public class WwdEmbedded
{
public static void main(String[] args)
{
Define key variables and objectsDEFINE VARIABLES
SECTION: The initial lines of the main method define
the variables and objects used in the program. This example uses variables
to store the information needed to connect to the database.
The use of variables for this information makes it easy to adapt the program
to other configurations and other databases.
- driver
- Stores the name of the embedded
driver.
- dbName
- Stores the name of the database.
- connectionURL
- Stores the connection
URL that is used to access the database.
- createString
- Stores the SQL CREATE statement for the WISH_LIST table.
String driver = "org.apache.derby.jdbc.EmbeddedDriver";
String dbName="jdbcDemoDB";
String connectionURL = "jdbc:derby:" + dbName + ";create=true";
...
String createString = "CREATE TABLE WISH_LIST "
+ "(WISH_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY "
...
+ " WISH_ITEM VARCHAR(32) NOT NULL) " ;
Boot the databaseBOOT DATABASE SECTION:
The DriverManager class loads the embedded driver and connects
to the database using the
connection URL stored
in the variable connectionURL. This URL includes the parameter ;create=true so
that the database will be created if it does not already exist. The primary try and catch block
begins here. This construct handles errors for the database access code.
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. myWishes.close();
Shut down the database DATABASE SHUTDOWN
SECTION: If an application starts the engine,
the application should shut down all databases before exiting. The attribute ;shutdown=true in
the connection URL
performs the shutdown. When the engine
is shutdown, all booted databases will automatically shut down. The shutdown
process cleans up records in the transaction log to ensure a faster startup
the next time the database is booted.
You can shut down
individual databases without shutting down the engine by including the database
name in the connection URL."This section verifies that the embedded
driver is being used, then issues the shutdown command and catches the shutdown
exception to confirm that the engine
shut down cleanly. The shutdown status is displayed before the program exits.
if (driver.equals("org.apache.derby.jdbc.EmbeddedDriver")) {
boolean gotSQLExc = false;
try {
DriverManager.getConnection("jdbc:derby:;shutdown=true");
} catch (SQLException se) {
if ( se.getSQLState().equals("XJ015") ) {
gotSQLExc = true;
}
}
if (!gotSQLExc) {
System.out.println("Database did not shut down normally");
} else {
System.out.println("Database shut down normally");
}
} The XJ015 error (successful shutdown
of the engine) and
the 08006 error (successful shutdown of a single database) are the only exceptions
thrown by that might
indicate that an operation succeeded. All other exceptions indicate that an
operation failed. You should check the log file to be certain.
Handle errors
The catch block at the end of the program calls the
Throwable.printStackTrace method to display the chain of
exceptions that are thrown if a problem occurs.
// Beginning of the primary catch block: prints stack trace
} catch (Throwable e) {
/* Catch all exceptions and pass them to
* the Throwable.printStackTrace method */
System.out.println(" . . . exception thrown:");
e.printStackTrace(System.out);
}
To view the error output, type a wish-list item with more than 32 characters,
such as I wish to see a Java program fail.