/home/wpollock1/public_html/AJava/DerbyDemo.java
// 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;
// Could ignore Exception instead of testing.
// 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);
}
}
}