JDBC

A Review of Relational Databases

• 1-1 ,
• 1-to-many or
• many-to-many

• First Normal Form: All attributes consist of exactly one data item
• Second Normal Form: The table is in First Normal Form and every non-key attribute depends on the primary key.
• Third Normal Form: The table is in Second Normal Form and all non-key attributes are independent of each other.

SQL

• CREATE and INSERT to create tables and insert records into them
• SELECT to query the database
• UPDATE to change the values in existing records
• DELETE and DROP to remove records and tables from the database.

JDBC

• For SELECT use executeQuery(String s). This will return a set of tuples, ResultSet.
• For INSERT, UPDATE, DELETE, CREATE and DROP use executeUpdate(String s). This will return the count of tuples changed.
• For stored procedures use execute(String s). This will return true if the results is a ResultSet, false otherwise.

/**
 * Demonstrates how to create a database through the JDBC driver.  This
 * will create a database in the local directory, create three tables and
 * fill them with sample data.
 * 

 * This demo distribution should contain a created database in the local
 * directory.  If the 'data' directory doesn't exist or was deleted then it
 * can be recreated by running this.
 */

import java.sql.*;

public class SimpleDatabaseCreateDemo {

  /**
   * Application start method.
   */
  public static void main(String[] args) {

    System.out.println();

    // Register the Mckoi JDBC Driver
    try {
      Class.forName("com.mckoi.JDBCDriver").newInstance();
    }
    catch (Exception e) {
      System.out.println(
	 "Unable to register the JDBC Driver.\n" +
	 "Make sure the classpath is correct.\n" +
	 "For example on Win32;  java -cp ../../mckoidb.jar;. SimpleApplicationDemo\n" +
	 "On Unix;  java -cp ../../mckoidb.jar:. SimpleApplicationDemo");
      return;
    }

    // This URL specifies we are creating a local database.  The
    // configuration file for the database is found at './ExampleDB.conf'
    // The 'create=true' argument means we want to create the database.  If
    // the database already exists, it can not be created.
    String url = "jdbc:mckoi:local://ExampleDB.conf?create=true";

    // The username/password for the database.  This will be the username/
    // password for the user that has full control over the database.
    // ( Don't use this demo username/password in your application! )
    String username = "user";
    String password = "pass1212";

    // Make a connection with the database.  This will create the database
    // and log into the newly created database.
    Connection connection;
    try {
      connection = DriverManager.getConnection(url, username, password);
    }
    catch (SQLException e) {
      System.out.println(
	 "Unable to create the database.\n" +
	 "The reason: " + e.getMessage());
      return;
    }

    // --- Set up the database ---

    try {
      // Create a Statement object to execute the queries on,
      Statement statement = connection.createStatement();
      ResultSet result;

      System.out.println("-- Creating Tables --");

      // Create a Person table,
      statement.execute(
	  "    CREATE TABLE Person ( " +
	  "       name      VARCHAR(100) NOT NULL, " +
	  "       age       INTEGER, " +
	  "       lives_in  VARCHAR(100) ) " );

      // Create a ListensTo table (which person listens to what music)
      statement.execute(
	  "    CREATE TABLE ListensTo ( " +
	  "       person_name      VARCHAR(100) NOT NULL, " +
	  "       music_group_name VARCHAR(250) NOT NULL ) ");

      // Create a MusicGroup table
      statement.execute(
	  "    CREATE TABLE MusicGroup ( " +
	  "       name              VARCHAR(250) NOT NULL, " +
	  "       country_of_origin VARCHAR(100) ) ");

      // Insert records into the tables,

      System.out.println("-- Inserting Data --");

      System.out.println("-- Adding to Person Table --");
      statement.execute(
	  "    INSERT INTO Person ( name, age, lives_in ) VALUES " +
	  "      ( 'Robert Bellamy', 24, 'England' ) ");
      statement.execute(
	  "    INSERT INTO Person ( name, age, lives_in ) VALUES " +
	  "      ( 'Grayham Downer', 59, 'Africa' ) ");
      statement.execute(
	  "    INSERT INTO Person ( name, age, lives_in ) VALUES " +
	  "      ( 'Timothy French', 24, 'Africa' ) ");
      statement.execute(
	  "    INSERT INTO Person ( name, age, lives_in ) VALUES " +
	  "      ( 'Butch Fad', 53, 'USA' ) ");
      statement.execute(
	  "    INSERT INTO Person ( name, age, lives_in ) VALUES " +
	  "      ( 'Judith Brown', 34, 'Africa' ) ");
      statement.execute(
	  "    INSERT INTO Person ( name, age, lives_in ) VALUES " +
	  "      ( 'Elizabeth Kramer', 24, 'USA' ) ");
      statement.execute(
	  "    INSERT INTO Person ( name, age, lives_in ) VALUES " +
	  "      ( 'Yamnik Wordsworth', 14, 'Australia' ) ");
      statement.execute(
	  "    INSERT INTO Person ( name, age, lives_in ) VALUES " +
	  "      ( 'Domonic Smith', 25, 'England' ) ");
      statement.execute(
	  "    INSERT INTO Person ( name, age, lives_in ) VALUES " +
	  "      ( 'Ivan Wilson', 23, 'England' ) ");
      statement.execute(
	  "    INSERT INTO Person ( name, age, lives_in ) VALUES " +
	  "      ( 'Lisa Williams', 24, 'England' ) ");
      statement.execute(
	  "    INSERT INTO Person ( name, age, lives_in ) VALUES " +
	  "      ( 'Xenia, Warrior Princess', 32, 'Rome' ) ");
      statement.execute(
	  "    INSERT INTO Person ( name, age, lives_in ) VALUES " +
	  "      ( 'David Powell', 25, 'New Zealand' ) ");

      System.out.println("-- Adding to MusicGroup Table --");
      statement.execute(
	  "    INSERT INTO MusicGroup " +
	  "      ( name, country_of_origin ) VALUES " +
	  "      ( 'Oasis',       'England' ), " +
	  "      ( 'Fatboy Slim', 'England' ), " +
	  "      ( 'Metallica',   'USA' ), " +
	  "      ( 'Nirvana',     'USA' ), " +
	  "      ( 'Beatles',     'England' ), " +
	  "      ( 'Fela Kuti',   'Africa' ), " +
	  "      ( 'Blur',        'England' ), " +
	  "      ( 'Muddy Ibe',   'Africa' ), " +
	  "      ( 'Abba',        'Sweden' ), " +
	  "      ( 'Madonna',     'USA' ), " +
	  "      ( 'Cure',        'England' ) " );

      // Who listens to what music?
      System.out.println("-- Adding to ListensTo Table --");
      statement.execute(
	  "    INSERT INTO ListensTo " +
	  "      ( person_name, music_group_name ) VALUES " +
	  "      ( 'David Powell',             'Metallica' ), " +
	  "      ( 'David Powell',             'Cure' ), " +
	  "      ( 'Xenia, Warrior Princess',  'Madonna' ), " +
	  "      ( 'Lisa Williams',            'Blur' ), " +
	  "      ( 'Lisa Williams',            'Cure' ), " +
	  "      ( 'Lisa Williams',            'Beatles' ), " +
	  "      ( 'Ivan Wilson',              'Cure' ), " +
	  "      ( 'Ivan Wilson',              'Beatles' ), " +
	  "      ( 'Yamnik Wordsworth',        'Abba' ), " +
	  "      ( 'Yamnik Wordsworth',        'Fatboy Slim' ), " +
	  "      ( 'Yamnik Wordsworth',        'Fela Kuti' ), " +
	  "      ( 'Elizabeth Kramer',         'Nirvana' ), " +
	  "      ( 'Judith Brown',             'Fela Kuti' ), " +
	  "      ( 'Judith Brown',             'Muddy Ibe' ), " +
	  "      ( 'Butch Fad',                'Metallica' ), " +
	  "      ( 'Timothy French',           'Blur' ), " +
	  "      ( 'Timothy French',           'Oasis' ), " +
	  "      ( 'Timothy French',           'Nirvana' ), " +
	  "      ( 'Grayham Downer',           'Fela Kuti' ), " +
	  "      ( 'Grayham Downer',           'Beatles' ), " +
	  "      ( 'Robert Bellamy',           'Oasis' ), " +
	  "      ( 'Robert Bellamy',           'Beatles' ), " +
	  "      ( 'Robert Bellamy',           'Abba' ), " +
	  "      ( 'Robert Bellamy',           'Blur' ) " );

      System.out.println("--- Complete ---");

      // Close the statement and the connection.
      statement.close();
      connection.close();

    }
    catch (SQLException e) {
      System.out.println(
	"An error occured\n" +
	"The SQLException message is: " + e.getMessage());

    }

    // Close the the connection.
    try {
      connection.close();
    }
    catch (SQLException e2) {
      e2.printStackTrace(System.err);
    }

  }

}

/**
 * This demonstrates a simple stand-alone database application.  This will
 * start up the database that's found in this directory and perform some
 * queries on the data.
 * 

 * The demo distribution should contain the database data files already
 * prepared.  However, if the 'data' directory was removed then run
 * 'SimpleDatabaseCreateDemo' to remake it.
 */

import java.sql.*;

public class SimpleApplicationDemo {

  /**
   * The demonstation 'main' method.
   */
  public static void main(String[] args) {

    System.out.println();

    // Register the Mckoi JDBC Driver
    try {
      Class.forName("com.mckoi.JDBCDriver").newInstance();
    }
    catch (Exception e) {
      System.out.println(
	 "Unable to register the JDBC Driver.\n" +
	 "Make sure the classpath is correct.\n" +
	 "For example on Win32;  java -cp ../../mckoidb.jar;. SimpleApplicationDemo\n" +
	 "On Unix;  java -cp ../../mckoidb.jar:. SimpleApplicationDemo");
      return;
    }

    // This URL specifies we are connecting with a local database.  The
    // configuration file for the database is found at './ExampleDB.conf'
    String url = "jdbc:mckoi:local://ExampleDB.conf";

    // The username/password for the database.  This is set when the database
    // is created (see SimpleDatabaseCreateDemo).
    String username = "user";
    String password = "pass1212";

    // Make a connection with the database.
    Connection connection;
    try {
      connection = DriverManager.getConnection(url, username, password);
    }
    catch (SQLException e) {
      System.out.println(
	 "Unable to make a connection to the database.\n" +
	 "The reason: " + e.getMessage());
      return;
    }

    // --- Do some queries ---
    System.out.println();

    try {
      // Create a Statement object to execute the queries on,
      Statement statement = connection.createStatement();
      ResultSet result;

      // How many rows are in the 'Person' table?
      result = statement.executeQuery("SELECT COUNT(*) FROM Person");
      if (result.next()) {
	System.out.println("Rows in 'Person' table: " + result.getInt(1));
      }
      System.out.println();

      // What's the average age of the people stored?
      result = statement.executeQuery("SELECT AVG(age) FROM Person");
      if (result.next()) {
	System.out.println("Average age of people:  " + result.getDouble(1));
      }
      System.out.println();

      // List the names of all the people that live in Africa ordered by name
      result = statement.executeQuery(
	  "SELECT name FROM Person WHERE lives_in = 'Africa' ORDER BY name");
      System.out.println("All people that live in Africa:");
      while (result.next()) {
	System.out.println("  " + result.getString(1));
      }
      System.out.println();

      // List the name and music group of all the people that listen to
      // either 'Oasis' or 'Beatles'
      result = statement.executeQuery(
	  "   SELECT Person.name, MusicGroup.name " +
	  "     FROM Person, ListensTo, MusicGroup " +
	  "    WHERE MusicGroup.name IN ( 'Oasis', 'Beatles' ) " +
	  "      AND Person.name = ListensTo.person_name " +
	  "      AND ListensTo.music_group_name = MusicGroup.name " +
	  " ORDER BY MusicGroup.name, Person.name ");
      System.out.println("All people that listen to either Beatles or Oasis:");
      while (result.next()) {
	System.out.print("  " + result.getString(1));
	System.out.print(" listens to ");
	System.out.println(result.getString(2));
      }
      System.out.println();

      // Close the statement and the connection to end,
      statement.close();
      connection.close();

    }
    catch (SQLException e) {
      System.out.println(
	"An error occured\n" +
	"The SQLException message is: " + e.getMessage());
      return;
    }

  }

}