DerbyDemo.java

Download DerbyDemo.java

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:

  1. Download the latest version of Derby zip file, then extract the folder inside someplace.
  2. Set the environment variable 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.)
  3. Next update the PATH environment variable to include “%DERBY_HOME%\bin”.
  4. Also check that you have the JAVA_HOME environment variable set to where you installed the JDK.
  5. Finally, you need the installed JAR files from %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.
  6. Test your setup:  Open a new command line window and try this command:
    javap org.apache.derby.jdbc.EmbeddedDriver
    If 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.
  7. Read some of the documentation, especially the “Getting Started” guide.  You can find this at the Apache.org Derby site.
// 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);
      }
   }
}

Sample Output:

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>