I was recently looking for a some tutorial on using Embedded Apache Derby (JavaDB). The best example I found was provided by Wayne Pollock and it can be found here https://wpollock.com/AJava/DerbyDemo.htm. Here is the code on that page;

import java.sql.*;
import javax.swing.JOptionPane;

public class DerbyDemo {

  public static void main ( String [] args ) {
      
      System.out.println(System.getProperty("user.dir"));

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

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

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

    // 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 your name: " );

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

  }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s