package Example;

import java.sql.*;
/**
 * Methods for connecting to a database,
 * executing SQL, and reporting the result
 * @author (Rick Watson)
 * @version (2004.08.28)
 */

public class DatabaseAccess {
    // instance variables

    private Connection dbConnect;
    private Statement dbStatement;
    private ResultSet dbResultSet;
    private ResultSetMetaData dbResultSetMetaData;

    public DatabaseAccess() {

    }
    /**
     * Load the driver class
     */
    public void loadDriver(String driver) {

        try {
            Class.forName(driver);
        }
        catch (ClassNotFoundException error) {
            System.out.println("Could not load driver ");
            System.exit(1);
        }
    }
    /**
     * Make a connection to a database
     */
    public void connectDatabase(String db, String user, String password) {
        try {
            dbConnect = DriverManager.getConnection(db, user, password);
        }
        catch(SQLException error) {
            System.err.println("Error connecting to database: "
            + error.toString());
            System.exit(2);
        }
    }
      /**
     * Setting autocommit to false
     */
    public void autoCommit(boolean commit){

        try {
            dbConnect.setAutoCommit(false);
        }
        catch (SQLException error){
            System.out.println("Could not turn off autocommit");
            System.exit(1);
        }
    }
    /**
     * Handling a rollback
     */
    public void dbRollback(){

        try {
            dbConnect.rollback();
            System.out.println("Transaction rollback");
        }
        catch (SQLException error){
            System.out.println("Could not rollback");
            System.exit(1);
        }
    }
    /**
     * Handling a commit
     */
    public void dbCommit(){

        try {
            dbConnect.commit();
            System.out.println("Transaction commit");
        }
        catch (SQLException error){
            System.out.println("Could not commit");
            System.exit(1);
        }
    }
    /**
     * Execute a SELECT
     */
    public  void executeSQL(String query) {
        try {
            dbStatement = dbConnect.createStatement();
            System.out.println(query + "\n");
            dbResultSet = dbStatement.executeQuery(query);
        }
        catch(SQLException error) {
            System.err.println("Error executing SQL: " + error.toString());
            System.exit(3);
        }
    }
    /**
     * Report a SELECT
     */
    public void reportSQL() {
        int i;
        try {
            dbResultSetMetaData = dbResultSet.getMetaData();
            // Get the number of columns in the result set
            int numCols = dbResultSetMetaData.getColumnCount();
            // Fetch until end of the result set
            while (dbResultSet.next()) {
                // Loop through each column
                for (i=1; i<=numCols; i++) {
                    if (i > 1) System.out.print("  ");
                    System.out.print(dbResultSet.getString(i));
                }
                System.out.println("");
            }
        }
        catch(SQLException error) {
            System.err.println("Error displaying SQL results: "
            + error.toString());
            System.exit(4);
        }
    }
     /**
     * INSERT a row
     */
    public void insertSQL(String sql){
        try {
            dbStatement = dbConnect.createStatement();
            int result = dbStatement.executeUpdate(sql);
        }
        catch(SQLException error) {
            System.err.println("Error inserting row: " + error.toString());
            System.exit(6);
        }
    }
    /**
     * Close the connection
     */
    public void closeDatabase() {
        try {
            dbResultSet.close();
            dbStatement.close();
            dbConnect.close();
        }
        catch(SQLException error) {
            System.err.println("Error closing: "
            + error.toString());
            System.exit(5);
        }
    }
}