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

package db;

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

/** 
 * A utility class for using JDBC.
 *
 * This utility class provides convenience and flexibility in connecting to different JDBC compliant 
 * databases without changes in the client code. 
 * The product specific properties and other parameters are specified in a Java properties file named 
 * <code>db.properties</code>.
 * The properties file for two database products, Cloudscape and MS Access, are provided.  
 * The description of the properties can be found in the lecture notes. 
 *
 * This class is a Singleton. 
 *
 * @version 1.1 2001/04/29 
 * @since 1.0
 * @author Xiaoping Jia
 */
public class DBConnector { 
  
  /**
   * Returns the unique instance of this class. 
   * Singleton design pattern. 
   */ 
  public static DBConnector getInstance() { 
    if (theInstance == null) { 
      theInstance = new DBConnector(); 
    }
    return theInstance;  
  }

  /**
   * Sets the name of the database properties file. 
   */ 
  public static void setDBPropertiesFileName(String dbpropname) { 
    DBConnector.dbpropname = dbpropname; 
  }

  /**
   * The unique instance of this class. 
   * Singleton design pattern. 
   */ 
  protected static DBConnector theInstance = null; 

  /**
   * The default name of the database properties file. 
   */ 
  protected static String dbpropname = "db.properties";

  protected static String database;
  protected static String jdbcDriver;
  protected static String dataSource;
  protected static String username; 
  protected static String password;   

  protected static String create;
  protected static String shutdown;

  protected static String homeProperty;
  protected static String homeDefault;

  protected String homeDir = null; 
  protected String dbName = null; 
  protected boolean initialized = false; 
  
  protected Connection conn = null;  
  protected Statement stmt = null;

  /**
   * Sets the home directory of the database. 
   * This method must be called before startup(). Otherwise, it has no effect. 
   */ 
  public void setHomeDir(String homeDir) { 
    this.homeDir = homeDir; 
  }

  /**
   * Starts up the database. 
   * This must be called before calling methods to get connection of execute queries or updates. 
   */ 
  public void startup(String dbName) 
	throws ClassNotFoundException, SQLException { 
		if (!initialized) { 
			System.out.println("Initializing...");
	  		Properties dbprop = new Properties(); 
	  		try { 
				dbprop.load(new FileInputStream(dbpropname));       
	
				database = dbprop.getProperty("database"); 
				jdbcDriver = dbprop.getProperty("jdbcDriver"); 
				dataSource = dbprop.getProperty("dataSource"); 
				username = dbprop.getProperty("username"); 
				password = dbprop.getProperty("password"); 
	
				create = dbprop.getProperty("connection.create"); 
				shutdown = dbprop.getProperty("connection.shutdown"); 
	
				homeProperty = dbprop.getProperty("home.property");
				homeDefault = dbprop.getProperty("home.default");
	
				if (homeProperty != null) { 
					if (homeDir == null) { 
						homeDir = homeDefault;
					}
					if (homeDir != null) { 
						System.setProperty(homeProperty, homeDir);
					}
				}
	
				if (jdbcDriver != null) { 
					jdbcDriver = jdbcDriver.trim(); 
				} else { 
					jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver"; 
				}
	
				if (dataSource != null) { 
					dataSource = dataSource.trim(); 
				} else { 
					dataSource="jdbc:odbc:";
				}
	
				this.dbName = dbName; 
				System.out.println("\nLoading " + database + " JDBC driver...");
				long t1 = System.currentTimeMillis(); 
				Class.forName(jdbcDriver);
				long t2 = System.currentTimeMillis(); 
				System.out.println("Done. " + (t2 - t1) + "ms"); 
				initialized = true; 
	  	} catch (IOException e) {
			System.out.println("Unable to load " + dbpropname);	
			System.out.println(e);	
	  	} 
	}
}

  /**
   * To get a new connection to the database. 
   * The client is responsible for closing the connection. 
   *
   * @return The new connection 
   */
  public Connection getConnection() 
	throws SQLException { 
	if (initialized) {
		System.out.print("Connecting to " + dbName + " database...\n\n");
		String url = dataSource + dbName;
		String createUrl = url;
		if (create != null) { 
		createUrl += create; 
		}
		if (username != null && 
			password != null) { 
		return DriverManager.getConnection(createUrl, username, password);
		} else { 
			return DriverManager.getConnection(createUrl);
		}
	} else { 
		return null; 
	}
}

  /**
   * Executes a SQL update.  
   *
   * @param sql A SQL update statement
   * @return The number of rows updated. 
   */ 
  synchronized public int executeUpdate(String sql)
    throws SQLException { 
	if (initialized) {
		if (conn == null) { 
			conn = getConnection(); 
			System.out.println("Connected to and created database " + dbName);    
			stmt = conn.createStatement();
		}
		return stmt.executeUpdate(sql); 
	} else { 
		return 0; 
	}
  }

  /**
   * Executes a SQL query.  
   *
   * @param sql A SQL query statement
   * @return The result set.  
   */ 
  synchronized public ResultSet executeQuery(String sql) 
    throws SQLException { 
	if (initialized) {
		if (conn == null) { 
			conn = getConnection(); 
			System.out.println("Connected to and created database " + dbName);    
			stmt = conn.createStatement();
		}
		return stmt.executeQuery(sql); 
	} else { 
		return null; 
	}
 }

  /**
   * Shuts down the database. 
   * It must be called when the client is done accessing the database. 
   */ 
  public void shutdown() {
    if (initialized) {    
      try { 
	closeConnection();
      } catch (SQLException se) {}
      
      if (shutdown != null) { 
	try { 
	  DriverManager.getConnection(dataSource + shutdown);
	} catch (SQLException se) {}
      }
      initialized = false; 
    }
  }

  /**
   * To close the database connection used by the DBConnector.
   * Not to be called by the clients. 
   */ 
  synchronized protected void closeConnection() 
    throws SQLException { 
    if (stmt != null) { 
      stmt.close();
    }
    if (conn != null) { 
      conn.close();
    }
    conn = null; 
    stmt = null; 
  }

  /**
   * Protected constructor to prevent instantiation of instances by clients. 
   */ 
  protected DBConnector() {} 
  
}