/** 
 * Demonstrates how to use JDBC. 
 */ 

import java.sql.*; 

public class Example { 

  public static void main(String[] args) { 

    // Register the Mckoi JDBC Driver 
    try { 
      Class.forName("com.mckoi.JDBCDriver"); 
    } 
    catch (Exception e) { 
      System.out.println("Can't load JDBC Driver. " + 
                         "Make sure classpath is correct"); 
      return; 
    } 

    // This URL specifies we are creating a local database.  The 
    // config 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. 
    // So delete .\data\*  when you want to run this again. 
    String url = "jdbc:mckoi:local://ExampleDB.conf?create=true"; 

    //  Use a real username/password in a real application 
    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("Connect problem: " + 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.executeUpdate( 
   "    CREATE TABLE Person ( " + 
   "       name      VARCHAR(100) PRIMARY KEY, " + 
   "       age       INTEGER, " + 
   "       lives_in  VARCHAR(100) ) " ); 

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

      statement.executeUpdate( 
   "    INSERT INTO Person ( name, age, lives_in ) VALUES " 
   + "      ( 'Robert Bellamy', 24, 'England' ), " 
   + "      ( 'Grayham Downer', null, 'Africa' ), " 
   + "      ( 'Timothy French', 24, 'Africa' ), " 
   + "      ( 'Butch Fad', 53, 'USA' ), " 
   + "      ( 'Judith Brown', 34, 'Africa' ) "); 

      System.out.println("-- SQL queries --"); 
      // get average age of the people 
      result = statement.executeQuery("SELECT AVG(age) FROM Person"); 
      if (result.next()) { 
         System.out.println("Av. age:  " + result.getDouble(1)); 
      } 
      System.out.println(); 
      // List the names of all the people that live in Africa 
      result = statement.executeQuery( 
        "SELECT name FROM Person WHERE lives_in = 'Africa' "); 

      System.out.println("All people that live in Africa:"); 
      while (result.next()) { 
         System.out.println("  " + result.getString(1)); 
      } 

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

    } 
    catch (SQLException e) { 
      System.out.println( 
      "An SQLException occurred: " + e.getMessage()); 
    } 
    catch (Exception e) { 
      e.printStackTrace(System.err); 
    } 
  } 
}