/* 
 * Copyright (c) 2001, Xiaoping Jia.  
 * All Rights Reserved. 
 */

package db;

import java.sql.*;
import java.io.*;
import java.util.*;

/** 
 * A simple application that uses JDBC to create a database named <code>Users</code>.
 * The database contains a single table named <code>UserPass</code> with two columns: 
 * <code>username</code> and <code>password</code>. 
 *
 * This program can uses a JDBC utility class DBConnector  
 *
 * @version 1.1 2001/04/29 
 * @since 1.0
 * @author Xiaoping Jia
 */
public class BuildUserDBUsingDBConnector {

  /**
   * It creates the database and inserts a number records of usernames and passwords.  
   */ 
  public static void main(String args[]) {
    String dbName = "Users"; 
    String tableName = "UserPass"; 
    DBConnector db = DBConnector.getInstance(); 
    try { 
      db.startup(dbName); 
    } catch(ClassNotFoundException e) {
      System.exit(1);      
    } catch (SQLException se) { 
      System.exit(1);      
    }

    try { 
      String dropString = "DROP TABLE " + tableName; 
      db.executeUpdate(dropString);
    } catch (SQLException se) {} 

    try { 
      System.out.print("Building new " + tableName + " table...\n\n");

      String createString =
	"CREATE TABLE " + tableName + 
	" (username VARCHAR(128) NOT NULL PRIMARY KEY," +  
	"  password VARCHAR(128))";
      db.executeUpdate(createString);
      System.out.print("Inserting rows in User table...\n\n");
      String insertString =
	"INSERT INTO " + tableName + " VALUES ('Aladdin', 'open sesame')";
      db.executeUpdate(insertString);
      insertString =
	"INSERT INTO " + tableName + " VALUES ('Xiaoping Jia', 'crouching tiger hidden dragon')";
      db.executeUpdate(insertString);
      insertString =
	"INSERT INTO " + tableName + " VALUES ('Scott McNealy', 'lavender')";
      db.executeUpdate(insertString);
      insertString =
	"INSERT INTO " + tableName + " VALUES ('Steve Jobs', 'aqua')";
      db.executeUpdate(insertString);
      insertString =
	"INSERT INTO " + tableName + " VALUES ('Bill Gates', 'blue')";
      db.executeUpdate(insertString);

      ResultSet rset = db.executeQuery("SELECT * FROM " + tableName);
      while (rset.next()) {
	System.out.println(rset.getString("username") + ":" + 
			   rset.getString("password"));
      }
    
    } catch (SQLException se) {}

    db.shutdown(); 
  }

}