The WwdEmbedded program

This section describes the WwdEmbedded.java program, highlighting details specific to accessing a Derby database from a JDBC program.

Most of the code related to the database activities performed is included in this document but you may find it helpful to open the program file and follow along in a text viewer or editor. The SECTION NAMES referred to in this text can be found in the comments within the program code and serve as cross-reference points between this document and the Java program. The program utilizes routines from the WwdUtils class. The utility class code is not described here but is available for review in the file WwdUtils.java

Initialize the program

INITIALIZATION SECTION: The initial lines of code identify the Java libraries used in the program, then set up the Java class WwdEmbedded and the main method signature. Refer to a standard 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 Objects

DEFINE 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 Derby database. Using variables for this information makes it easy to adapt the program to other configurations and other databases.

driver - stores the name of the Derby embedded driver.
dbName - stores the name of the database.
connectionURL - stores the Derby connection URL that will be 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) " ;

Start the Derby engine

LOAD DRIVER SECTION: Loading the Derby embedded JDBC driver starts the Derby database engine. The try and catch block (the Java error handling construct) catches the exceptions that may occur. A problem here is generally due to an incorrect CLASSPATH setting.

String driver = "org.apache.derby.jdbc.EmbeddedDriver";
...
try{
    Class.forName(driver); 
} catch(java.lang.ClassNotFoundException e) {
...
}

Boot the database

BOOT DATABASE SECTION: The DriverManager class loads the database using the Derby connection URL stored in the variable connectionURL. This URL includes the parameter ;create=true so 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 to execute SQL

INITIAL SQL SECTION: Program objects needed to perform subsequent SQL operations are initialized here and a check is made 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 it is created by executing the SQL stored in the variable createString via this statement object.

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 ? parameter to represent the data that will be inserted by the user. The actual value that is inserted is set later in the code prior to 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 utilizes 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 exit is entered. The data related activities performed in this section are:

The setString method sets the substitution parameter of the psInsert object to the value entered by the User. Then executeUpdate is called to perform 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 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();

Shutdown the Database

DATABASE SHUTDOWN SECTION: When an application starts the Derby engine it should shutdown all databases prior to exiting. The attribute ;shutdown=true in the Derby connection URL performs the shutdown. The shutdown process cleans up records in the transaction log to ensure a faster startup the next time the database is booted.

This section verifies that the embedded driver is being used then issues the shutdown command and catches the shutdown exception to confirm the database shutdown 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");	
   }
}

Important: The XJ015 error is the only exception thrown by Derby that signifies an operation succeeded. All other exceptions indicate an operation failed.

The errorPrint and SQLExceptionPrint methods

DERBY EXCEPTION REPORTING CLASSES: The two methods at the end of the file, errorPrint and SQLExceptionPrint, are generic exception reporting routines that can be used with any JDBC program. This type of exception handling is required because often multiple exceptions (SQLException) are chained together then thrown. A while loop is used to report on each error in the chain. These classes are used by calling the errorPrint method from the catch block of the code that accesses the database.

//  Beginning of the primary catch block: uses errorPrint method
}  catch (Throwable e)  {   
   /*    Catch all exceptions and pass them to 
   **       the exception reporting method   */ 
   System.out.println(" . . . exception thrown:");
   errorPrint(e);
}

The errorPrint routine prints a stack trace for all exceptions except a SQLException. All SQLExceptions are passed to the SQLExceptionPrint method.

static void errorPrint(Throwable e) {
   if (e instanceof SQLException) 
      SQLExceptionPrint((SQLException)e);
   else {
      System.out.println("A non SQL error occured.");
      e.printStackTrace();
   }   
}  // END errorPrint

The SQLExceptionPrint method iterates through each of the exceptions on the stack. For each error the codes, message then stacktrace are printed.

//  Iterates through a stack of SQLExceptions 
static void SQLExceptionPrint(SQLException sqle) {
   while (sqle != null) {
      System.out.println("\n---SQLException Caught---\n");
      System.out.println("SQLState:   " + (sqle).getSQLState());
      System.out.println("Severity: " + (sqle).getErrorCode());
      System.out.println("Message:  " + (sqle).getMessage()); 
      sqle.printStackTrace();  
      sqle = sqle.getNextException();
   }
}  //  END SQLExceptionPrint

If you wish to see the output produced by this method enter a wish list item with more than 32 characters like: I wish to see a Java program fail.