Show
code with line numbers
Show output
Note! To compile and run this code you must make sure you have “JavaDB” (which is really a re-branded version of the Apache Derby database) installed. This used to be bundled with the JDK (you still had to choose to install it). The newer versions (since Oracle bought Sun) no longer includes JavaDB with the JDK. You should install it by:
DERBY_HOME
to
where you installed Derby.
On my system that was C:\Java\db
.
(How to set environment variables depends on your system and
version.
On Windows, use the “System” control panel, and
click on the “advanced settings” link to see the
“Environment variables” button.) PATH
environment variable to
include “%DERBY_HOME%\bin
”. JAVA_HOME
environment
variable set to where you installed the JDK
. %DERBY_HOME%
to be found on CLASSPATH.
(The Java compiler might not complain, but java.exe will.)
Set CLASSPATH
to include these by adding
%DERBY_HOME%\lib\*
to CLASSPATH
.
javap org.apache.derby.jdbc.EmbeddedDriverIf you get an error message it means the Jar files can't be found. Next try the Derby command line tool:
C:\Temp>ij ij version 10.15 ij> quit; C:\Temp>If the tool won't run, your
PATH
setting is wrong.// This code demonstrates the creation and use of an embedded Derby // database. If the DB doesn't exist it is created, a couple of records // are inserted, and the results of a query are displayed. // When run, the embedded DB is created in a sub-directory called "DerbyDemoDB", // of the current directory. Also, the text file derby.log is created in the // current directory. // // Written 4/2009 by Wayne Pollock, Tampa Florida USA // Updated 4/2013 by WP: Fixed problem of dropping a non-existent table. // Updated 3/2019 by WP: Updated directions. import java.sql.*; import javax.swing.JOptionPane; public class DerbyDemo { public static void main ( String [] args ) { String dbName="DerbyDemoDB"; String connectionURL = "jdbc:derby:" + dbName + ";create=true"; // The ";create=true" will create the DB if not created yet. String SQL_CreateTable = "create table addresses ( " + "ID int not null generated always as identity " + " (start with 1000), " + "lname varchar(40) not null, fname varchar(40) not null, " + "phone varchar(14), notes varchar(256), " + "primary key (ID) )"; // This SQL inserts three records into the addresses table: String SQL_Insert = "insert into addresses " + "(lname, fname, phone, notes) values " + "('Pollock', 'Wayne', '253-7213', 'Professor'), " + "('Piffl', 'Hymie', NULL, 'Fake student name'), " + "('Jojo', 'Mojo', NULL, 'Super-villan')"; String SQL_Query = "SELECT * FROM addresses"; Connection con = null; Statement stmnt = null; // Try to connect to the DB: try { con = DriverManager.getConnection( connectionURL ); } catch ( Exception e ) { System.err.println( "**** Cannot open connection to " + dbName + "!" ); System.exit(1); } // Drop (delete) the table if it exists. This is common for demo code, // otherwise every time you run the code, it keeps adding copies of the // data. Current versions of Derby throw an Exception if you try to drop // a non-existing table, so check if it is there first: if ( tableExists( con, "addresses" ) ) { System.out.println ( "Dropping table addresses..." ); try { stmnt = con.createStatement(); stmnt.executeUpdate( "DROP TABLE addresses" ); stmnt.close(); } catch ( SQLException e ) { String theError = e.getSQLState(); System.out.println( "Can't drop table: " + theError ); System.exit(1); } } // Create the table addresses if it doesn't exist: if ( ! tableExists( con, "addresses" ) ) { System.out.println ( "Creating table addresses..." ); try { stmnt = con.createStatement(); stmnt.execute( SQL_CreateTable ); stmnt.close(); } catch ( SQLException e ) { String theError = e.getSQLState(); System.out.println( "Can't create table: " + theError ); System.exit(1); } } // Insert records into table (Note if you run this code twice // the same people get added but with different IDs): try { stmnt = con.createStatement(); System.out.println ( "Inserting rows into table addresses..." ); stmnt.executeUpdate( SQL_Insert ); // Add some rows stmnt.close(); } catch ( SQLException e ) { String theError = e.getSQLState(); System.out.println( "Can't insert rows in table: " + theError ); System.exit(1); } // Query the table and display the results: try { stmnt = con.createStatement(); // This is dangerous if the query string contains any external text! ResultSet rs = stmnt.executeQuery( SQL_Query ); displayResults( rs ); stmnt.close(); // When not using your own data in SQL statement, you should use // PreparedStatements instead of Statements, to prevent SQL injection // attacks (a common security vulnerability in "textbook-quality" // code). Here's an example to query the table with untrusted user data: // The SQL Query to use (note case-insensitive comparison): String dangerousQuery = "SELECT * FROM ADDRESSES WHERE UPPER(LNAME) = UPPER(?)"; // Create a prepared statement to use: PreparedStatement pStmnt = con.prepareStatement( dangerousQuery ); // Get the last name to query for, from the user: String lastName = JOptionPane.showInputDialog( "Please enter a last name to search for: " ); if ( lastName != null ) { // Safely substitute data for "?" in query: // (Note there are many type-checking set* methods, e.g. "setInt") pStmnt.setString( 1, lastName ); ResultSet lastNameSearchResults = pStmnt.executeQuery(); System.out.println( "\n\tResults of last name query for \"" + lastName + "\"" ); displayResults( lastNameSearchResults ); } pStmnt.close(); con.close(); } catch ( SQLException e ) { String theError = e.getSQLState(); System.out.println("Can't query table: " + theError ); System.exit(1); } // Shut down all databases and the Derby engine, when done. Note, // Derby always throws an Exception when shutdown, so ignore it: System.out.println ( "Shutting down the database..." ); try { DriverManager.getConnection("jdbc:derby:;shutdown=true"); } catch ( SQLException e ) {} // empty: ignore exception // Note that nothing breaks if you don't cleanly shut down Derby, but // it will start in recovery mode next time (which takes longer to start). } // Derby doesn't support the standard SQL views. To see if a table // exists you normally query the right view and see if any rows are // returned (none if no such table, one if table exists). Derby // does support a non-standard set of views which are complicated, // but standard JDBC supports a DatabaseMetaData.getTables method. // That returns a ResultSet but not one where you can easily count // rows by "rs.last(); int numRows = rs.getRow()". Hence the loop. private static boolean tableExists ( Connection con, String table ) { int numRows = 0; try { DatabaseMetaData dbmd = con.getMetaData(); // Note the args to getTables are case-sensitive! ResultSet rs = dbmd.getTables( null, "APP", table.toUpperCase(), null); while( rs.next() ) ++numRows; } catch ( SQLException e ) { String theError = e.getSQLState(); System.out.println("Can't query DB metadata: " + theError ); System.exit(1); } return numRows > 0; } private static void displayResults ( ResultSet rs ) { // Collect meta-data: try { ResultSetMetaData meta = rs.getMetaData(); String catalog = meta.getCatalogName(1); String schema = meta.getSchemaName(1); String table = meta.getTableName(1); int numColumns = meta.getColumnCount(); // Display results: System.out.print( "\n\t\t---" ); if ( catalog != null && catalog.length() > 0 ) System.out.print( " Catalog: " + catalog ); if ( schema != null && schema.length() > 0 ) System.out.print( " Schema: " + schema ); System.out.println( ", Table: " + table + " ---\n" ); for ( int i = 1; i <= numColumns; ++i ) System.out.printf( "%-12s", meta.getColumnLabel( i ) ); System.out.println(); while ( rs.next() ) { // Fetch next row, quit when no rows left. for ( int i = 1; i <= numColumns; ++i ) { String val = rs.getString( i ); if ( val == null ) val = "(null)"; System.out.printf( "%-12s", val ); } System.out.println(); } } catch ( SQLException e ) { String theError = e.getSQLState(); System.out.println("Can't view resultSet: " + theError ); System.exit(1); } } }
C:\Temp>java DerbyDemo Dropping table addresses... Creating table addresses... Inserting rows into table addresses... --- Schema: APP, Table: ADDRESSES --- ID LNAME FNAME PHONE NOTES 1000 Pollock Wayne 253-7213 Professor 1001 Piffl Hymie (null) Fake student name 1002 Jojo Mojo (null) Super-villan Results of last name query for "piffl" --- Schema: APP, Table: ADDRESSES --- ID LNAME FNAME PHONE NOTES 1001 Piffl Hymie (null) Fake student name Shutting down the database... C:\Temp>