http://www.apache.org/licenses/LICENSE-2.0
• | A Java development kit version 1.3 or higher | |||||||
• | The binary (bin) installation of Apache Derby version
10.2 | |||||||
• | A basic knowledge of the computer command line interface
|
1.
| Verify that the JAVA_HOME environment variable is set and points
to a Java development kit version 1.3 or higher. Open a command
window and run the command java -version using the appropriate
syntax for your system: On Windows platforms:
On UNIX Korn Shell platforms:
The output from the command will look
something like this: The output you see may be different from what is shown here, because the java -version command outputs vendor-specific information. If the command produced an error or the version listed is not 1.3 or higher, please install a Java development kit before continuing. | |
2.
| Verify that the DERBY_HOME environment variable is set and points to
the filesystem path to the root directory of the Derby 10.2 installation: Open a command window and run the appropriate command for your system: On Windows platforms:
On UNIX Korn Shell platforms:
The output from the command will look something like this:
If Derby is not installed or cannot be found, please install a copy now. For instructions, see Getting Started with Java DB. |
1.
| Setting the DERBY_HOME environment variable. | |
2.
| Creating the DERBYDBS work directory | |
3.
| Changing the directory (cd) to the work
directory | |
4.
| Copying the SQL scripts that create the tables and data for toursdb
from the Derby demo\programs\toursdb subdirectory into the DERBYDBS directory. |
set DERBY_HOME=C:\derby md DERBYDBS cd DERBYDBS copy %DERBY_HOME%\demo\programs\toursdb\*.sql .
export DERBY_HOME=/opt/derby mkdir DERBYDBS cd DERBYDBS cp $DERBY_HOME/demo/programs/toursdb/*.sql .
1.
| Run the Derby ij tool. On Windows platforms:
On UNIX Korn Shell platforms:
| |||||||
2.
| Create the database and open a connection to it using the embedded
driver.
| |||||||
3.
| Create a table with two columns using standard SQL.
| |||||||
4.
| Insert three records.
| |||||||
5.
| Perform a simple select of all records in the table.
| |||||||
6.
| Preform a qualified select of the record with column ID=20.
| |||||||
7.
| Load the SQL script ToursDB_schema.sql to
create the tables and other schema objects (this step is optional).
| |||||||
8.
| Exit the ij tool.
| |||||||
9.
| Browse the key files created by the activity.
|
• | jdbc:derby: - JDBC protocol specification for the Derby
driver. | |
• | firstdb - the name of the database, this can be any string.
Because no filepath is specified the database will be created in the default
working directory (DERBYDBS). | |
• | ;create=true - The Derby URL attribute used
to create databases. Derby does not have an SQL create database command. |
1.
| Open a command window that we'll call Shell-1.
Change directory (cd) to the DERBYDBS directory
and set the DERBY_HOME environment variable. | |
2.
| Start the Network Server. On Windows platforms:
On UNIX Korn Shell platforms:
A Network Server startup message is displayed
in the Shell-1 command window. | |
3.
| Open another command window that we'll call Shell-2.
Change directory (cd) to the DERBYDBS directory
and set the DERBY_HOME environment variable. | |
4.
| Start ij. On Windows platforms:
On UNIX Korn Shell platforms:
All subsequent commands are entered from
the network client, and are therefore entered in the Shell-2 command
window. | |
5.
| Create and open a connection to the database using the client driver.
Remember: A client connection URL contains a hostname
and a port number: //localhost:1527/. | |
6.
| Create a table with two columns (ID and NAME)
using SQL.
| |
7.
| Insert three records into the table.
| |
8.
| Select all of the records in the table.
| |
9.
| Select a subset of records from the table by qualifying the command.
| |
10.
| Exit ij.
| |
11.
| Shut down the Derby Network Server. On Windows platforms:
On UNIX Korn Shell platforms:
The server shutdown confirmation appears
in both command windows. |
Apache Derby Network Server - 10.2.1.6 - (452058) started and ready to accept connections on port 1527 at 2006-09-22 00:08:30.049 GMT ...( database engine messages not shown )... Apache Derby Network Server - 10.2.1.6 - (452058) shutdown at 2006-09-22 00:16:44.223 GMT
1.
| Copy the program files into the DERBYDBS directory
and set the CLASSPATH: On Windows platforms:
On UNIX Korn Shell platforms:
>
Important: Include the dot (.) at the end
of each command so that your current working directory is included in the CLASSPATH and
the files are copied to the correct location. | |
2.
| Compile the WwdEmbedded.java program:
>
Important: Only a command prompt will be displayed
if the compilation is successful. The binary file WwdEmbedded.class will
be created. If an error is displayed please verify that the Java development
kit is properly installed. | |
3.
| Run the program: The WwdEmbedded.java program
populates a table with wish list items. It is a simple Java program that prompts
the User for text input (up to 32 characters), stores the text entered in
a database table and then lists the items stored in the table. The program
will continue to ask for wish list items until the word exit is
entered or a problem is encountered. Some basic information on program progress
is displayed at the beginning and the end of the program.
|
import java.sql.*; public class WwdEmbedded { public static void main(String[] args) {
• | 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) " ;
String driver = "org.apache.derby.jdbc.EmbeddedDriver"; ... try{ Class.forName(driver); } catch(java.lang.ClassNotFoundException e) { ... }
String connectionURL = "jdbc:derby:" + dbName + ";create=true"; ... try { conn = DriverManager.getConnection(connectionURL); ... <most of the program code is contained here> } catch (Throwable e) { ... }
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.s = conn.createStatement(); if (! WwdUtils.wwdChk4Table(conn)) { System.out.println (" . . . . creating table WISH_LIST"); s.execute(createString); }
psInsert = conn.prepareStatement ("insert into WISH_LIST(WISH_ITEM) values (?)");
psInsert.setString(1,answer); psInsert.executeUpdate();
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.myWishes = s.executeQuery("select ENTRY_DATE, WISH_ITEM from WISH_LIST order by ENTRY_DATE");
Close the ResultSet to release the memory being used.while (myWishes.next()) { System.out.println("On " + myWishes.getTimestamp(1) + " I wished for " + myWishes.getString(2)); }
myWishes.close();
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 errorPrint routine prints a stack trace for all exceptions except a SQLException. All SQLExceptions are passed to the SQLExceptionPrint method.// 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 SQLExceptionPrint method iterates through each of the exceptions on the stack. For each error the codes, message then stacktrace are printed.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
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.// 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
1.
| Create the WwdClient program.
| |||||||||||||||||||
2.
| Set up the client/server environment. Before you run the WwdClient program, the Network
Server needs to be started.
| |||||||||||||||||||
3.
| Run the client program.
| |||||||||||||||||||
4.
| Shut down the Network Server. On Windows platforms:
On UNIX Korn Shell platforms:
The server shutdown confirmation appears
in both command windows. |
Use this link: WorkingWithDerby Resources page browser URL: http://wiki.apache.org/db-derby/WorkingWithDerby