DerbyDemo.java

Show code with line numbers
Show output

// 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.
//
// Written 4/2009 by Wayne Pollock, Tampa Florida USA

import java.sql.*;

public class DerbyDemo {

  public static void main ( String [] args ) {

     String driver = "org.apache.derby.jdbc.EmbeddedDriver";
     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) )";

     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";

    // Load the Derby Embedded DB driver into the JRE:
    // Note this should not be needed for Java >=6, it is automatic!
    try { new org.apache.derby.jdbc.EmbeddedDriver();
    } catch ( Exception e ) {
       System.out.println( "**** Cannot load Derby Embedded DB driver!" );
       return;
    }

    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);
    }

    // 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 );
      } 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
    } 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();
      ResultSet rs = stmnt.executeQuery( SQL_Query );
      displayResults( rs );      

      con.close();
    } catch ( SQLException e ) {
        String theError = e.getSQLState();
        System.out.println("Can't query table: " + theError );
        System.exit(1);
    }
  }

  // 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
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

C:\Temp>