The WwdEmbedded program

The WwdEmbedded.java program is described here. +Most of the code related to the database activities performed is included +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 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 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 engineLOAD 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 databaseBOOT 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 SQLINITIAL 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 create 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 DatabaseADD / DISPLAY RECORD +SECTION: This section uses the utility method WwdUtils.getWishItem to +gather information from the User. It then utilizes the objects setup 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 DatabaseDATABASE 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 insure 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"); + } +}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 methodsDERBY +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 SQLException 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.