/* 
 * 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 is hard-coded to use the CloudScape database and its JDBC driver. 
 * CloudScape (<a href="http://www.cloudscape.com">http://www.cloudscape.com</a>) 
 * is a pure-Java DBMS runs on all major platforms, including 
 * Windows 9x/2000/NT/ME and Linux. 
 * Some minor changes in the source code are necessary in order to use a different database product.
 * Necessary changes to use Microsoft Access 97/2000 are included in the comments.  
 *
 * Another version of this program, <code>BuildUserDB</code>, can work with different JDBC compliant 
 * databases without changes to the source code.  
 *
 * @version 1.1 2001/04/29 
 * @since 1.0
 * @author Xiaoping Jia
 */
public class BuildUserDB_Cloudscape {

	/**
	* It creates the database and inserts a number records of usernames and passwords.  
	* 
	* To compile and run this program cloudscape.jar must be included in the classpath. 
	* The database will be created in a subdirectory named Users under the current working directory.  
	*/ 
	public static void main(String args[]) {

		String dbName = "Users"; 
		String tableName = "UserPass"; 
		Connection conn = null;  
		Statement stmt = null;

		System.out.print("\nLoading JDBC driver...\n\n");
		try {
			// Cloudscape JDBC driver
			Class.forName("COM.cloudscape.core.JDBCDriver");
			// MS Access, use default JDBC-ODBC driver 
			// Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
		} catch(ClassNotFoundException e) {
			e.printStackTrace(); 
			System.exit(1);
		}

		try { 
			System.out.print("Connecting to " + dbName + " database...\n\n");      
			// Cloudscape
			String url = "jdbc:cloudscape:" + dbName; 
			// MS Access 
			// String url = "jdbc:odbc:" + dbName; 
			
			// Cloudscape
			conn = DriverManager.getConnection(url + ";create=true");
			// MS Access 
			// conn = DriverManager.getConnection(url);

			System.out.println("Connected to and created database " + dbName);

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

			stmt = conn.createStatement();
		} catch (SQLException se) { 
			se.printStackTrace(); 
			System.exit(1);
		}

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

		try { 
			String createString =
			"CREATE TABLE " + tableName + 
			" (username VARCHAR(128) NOT NULL PRIMARY KEY," +  
			"  password VARCHAR(128))";
			stmt.executeUpdate(createString);
			System.out.print("Inserting rows in User table...\n\n");
			String insertString =
			"INSERT INTO " + tableName + " VALUES ('Aladdin', 'open sesame')";
			stmt.executeUpdate(insertString);
			insertString =
			"INSERT INTO " + tableName + " VALUES ('Xiaoping Jia', 'crouching tiger hidden dragon')";
			stmt.executeUpdate(insertString);
			insertString =
			"INSERT INTO " + tableName + " VALUES ('Scott McNealy', 'lavender')";
			stmt.executeUpdate(insertString);
			insertString =
			"INSERT INTO " + tableName + " VALUES ('Steve Jobs', 'aqua')";
			stmt.executeUpdate(insertString);
			insertString =
			"INSERT INTO " + tableName + " VALUES ('Bill Gates', 'blue')";
			stmt.executeUpdate(insertString);
			
			ResultSet rset = stmt.executeQuery("SELECT * FROM " + tableName);
			while (rset.next()) {
				System.out.println(rset.getString("username") + ":" + 
				rset.getString("password"));
			}
		
			rset.close();
			stmt.close();
			conn.close();
		
		} catch (SQLException se) {}

		// Cloudscape only 
		
		try {
			DriverManager.getConnection("jdbc:cloudscape:;shutdown=true");
		} catch (SQLException se) {}
			System.out.println("shutdown database " + dbName);
		
		}

}