package _SQL_Training_;
import java.sql.*;
import java.util.ArrayList;
 
/**
 * Responsible for establishing a connection with the
 * argument Database and sending queries to be executed
 * on the target Database.
 */
public final class DatabaseAccess{
 
    private Connection con = null;
    private ResultSet rs = null;
    private Statement st = null;
    private String dbName = "", driverName = "sun.jdbc.odbc.JdbcOdbcDriver", 
                   theTable = "";
    private ArrayList< String > queryList = 
        new ArrayList< String >(0);
    private ArrayList< ArrayList<String> > tableList = 
        new ArrayList< ArrayList<String> >(0);
 
    /**
     * Accepts the name of the String as a Database and attempts
     * to load the default Driver supported by Java SE 6.0+
     */
    public DatabaseAccess(String dbName){
        setDatabase(dbName);
        setDriver(driverName);
    }
 
    /**
     * Accepts the name of the first String as the name
     * of the argument Database, with the argument Driver.
     */
    public DatabaseAccess(String dbName, String driver){
        setDatabase(dbName);
        setDriver(driver);
    }
 
    /**
     * Attempts to load the argument Driver
     */
    public void setDriver(String name){
        driverName = name;
        try{
            Class.forName(driverName);
            con = DriverManager.getConnection("jdbc:odbc:" + dbName);
            st = con.createStatement();
        }catch(Exception e){
            System.out.println("An error occurred with the"
                            +  "argument Driver.");
        }
    }
 
    /**
     * Sets the database to be that of the argument.
     * Throws an exception if the database cannot be found
     * or accessed.
     */
    public void setDatabase(String dbName){
        this.dbName = dbName;
    }
 
    /**
     * Attempts to update the existing table
     */
    public void doUpdate(String query){
        try{
            st.executeUpdate(query);
            queryList.add(query);
        }catch(Exception e){
            System.out.println("An error occurred with the update query--");
        }
    }
 
    /**
     * Attempts to perform a query for the existing table
     */
    public void doQuery(String query){
        try{
            rs = st.executeQuery(query);
            queryList.add(query);
        }catch(Exception e){
            System.out.println("An error occurred with the action query--");
        }        
    }
 
    /**
     * Prints a list of successful queries executed during a session. 
     */
    public void printQueries(){
        for(String element : queryList){
            System.out.println(element);
        }
        System.out.println();
    }
 
    /**
     * Attempts to display the current table, based on the last
     * selection query.
     */
    public void displayTable(){
 
        ResultSetMetaData metaData = null;
        try{
            if(rs != null)
                metaData = rs.getMetaData();
            else{
                System.out.println("Error - Null ResultSet!");
                return;
            }
 
            if(metaData != null){
                theTable = "";
                int numberOfColumns = metaData.getColumnCount();
                System.out.println( );
                String other = "";
                for ( int i = 1; i <= numberOfColumns; i++ ){
                    other = "\t" + metaData.getColumnName(i);
                    theTable += other;
                }
 
                theTable += "\n";
 
                while ( rs.next() ){
                    String temp = "";
                    for ( int i = 1; i <= numberOfColumns; i++ ){
                        temp = "\t" + rs.getObject(i);
                        theTable += temp;
                    }
                    theTable += "\n";
                }
            }else{
                System.out.println("Last query made no visual changes. "
                                 + "Displaying current Table\n");
            }
            System.out.println(theTable);
        }catch(Exception e){
            System.out.println("An error occurred while attempting to display "
                             + "the table.");
        }
    }
 
    /**
     * Simply returns the String value of the current database the
     * user is accessing via this DatabaseAccess object.
     */
    @Override public String toString(){
        return this.dbName;
    }
}
 
/////////////
 
package _SQL_Training_;
import java.util.Scanner;
 
/**
 * The abstraction/commonality of all DatabaseCommands
 */
public abstract class DatabaseCommand {
 
    protected DatabaseAccess da;
    protected Scanner getInput;
 
    /**
     * Constructor
     */
    public DatabaseCommand(DatabaseAccess da, Scanner input){
        this.da = da;
        getInput = input;
    }
 
    /**
     * Execute the command
     */
    public abstract void execute();
}
 
 
/////////////
 
package _SQL_Training_;
import javax.swing.SwingUtilities;
import java.util.Scanner;
 
/**
 * A program that gives the user the ability to execute
 * commands and queries for a the given Databases, as well
 * as enable displays for the user to see the current information
 * in the database.
 */
public class DatabaseProgram {
 
    private DatabaseAccess dbObjs[], chosenDatabase = null;
    private volatile boolean locked = false;
    private DatabaseCommandFactory dcf =
            new DatabaseCommandFactory();
    private final Scanner kb = new Scanner(System.in);
 
    /**
     * Constructs a DatabaseProgram
     */
    public DatabaseProgram(DatabaseAccess... objs){
        if(objs.length > 0)
            setDatabaseObjects(objs);
        else{
            System.out.println("Error! You must submit at least 1 DatabaseAccess "
                             + "object!");
        }
    }
 
    /**
     * Driver method - entrypoint of the program
     */
    public static void main(String... args){
        DatabaseAccess da = new DatabaseAccess("TD");
        DatabaseProgram dp = new DatabaseProgram(da);
        dp.startProgram();
    }
 
    /**
     * Sets the DatabaseObjects if the class isn't locked.
     */
    public void setDatabaseObjects(DatabaseAccess... objs){
        if(!locked)
            dbObjs = objs;
    }
 
    /**
     * Applies a Double-Check lock before officially starting the program.
     */
    public void startProgram(){
        if(!locked 
           && dbObjs != null) // not thread safe, but negates slowing from sync in the future
            synchronized(this){ // halts threads except the one with the lock
                if(!locked
                   && dbObjs != null){ // thread with the lock does its job here while others wait
                    System.out.println("Program Started!\n");
                    locked = true;
                    SwingUtilities.invokeLater(rt);
                }
            }        
    }
 
    /**
     * Escape-Thread for the Thread that is capable of triggerring
     * the program before a lock. This technique should allow other
     * threads to fail the start and continue performing other tasks.
     */
    private Runnable rt = new Runnable(){
        boolean stopped = false;
        @Override public void run(){
            do{
                System.out.println("Which Database will you access?\n");
 
                for(DatabaseAccess element : dbObjs)
                    System.out.println(element);
 
                String result = kb.nextLine();
                boolean validAnswer = false;
                for(DatabaseAccess element : dbObjs){
                    if(result.equalsIgnoreCase(element.toString())){
                        validAnswer = true;
                        chosenDatabase = element;
                        dcf.setHandles(chosenDatabase, kb);
                    }
                }
 
                if(!validAnswer){
                    System.out.println("Invalid Database selected--\n");
                    System.out.println("Continue? Enter EXIT to end or any "
                                     + "other key to continue.\n");
                    if(!kb.nextLine().equalsIgnoreCase("EXIT"))
                        continue;
                    else stopped = true;
                }
                if(!stopped){
                    boolean doingCommands = true;
                    do{
                        System.out.println("Which action would you like to do for "
                                + chosenDatabase + "?");
                        String options[] = dcf.getOptions();
                        for(String element : options)
                            System.out.println(element);
                        System.out.println("\nIf no action, then enter EXIT\n\n");
                        String choice = kb.nextLine();
                        DatabaseCommand dc = dcf.getCommand(choice);
 
                        if(dc != null){
                            dc.execute();
                        }else{
                            if(choice.equalsIgnoreCase("Exit")){
                                doingCommands = false;
                            }
                            else System.out.println("Invalid Command\n");
                        }
                    }while(doingCommands);
                }
            }while(!stopped);
            locked = false;
        }
    };
 
    /**
     * The QueryAndDisplay Command - this is a combination command
     * that supports the execution of a query and additionally displays
     * the current table.
     */
    private class QueryAndDisplay extends QueryCommand{
        public QueryAndDisplay(DatabaseAccess da, Scanner s){super(da, s);}
        @Override public void execute(){super.execute();da.displayTable();}
    }
 
    /**
     * The Query Command - responsible for sending a query to the target
     * database. In addition, a query is likely to update the resultSet
     * and therefore update the display.
     */
    private class QueryCommand extends DatabaseCommand{
        public QueryCommand(DatabaseAccess da, Scanner s){super(da, s);}
        @Override public void execute(){
            System.out.println("Enter a query to execute:\n");
            da.doQuery(getInput.nextLine());
        }
    }
 
    /**
     * The PrintQueries Command - responsible for printing all
     * queries performed during a DatabaseProgram session..
     */
    private class PrintQueriesCommand extends DatabaseCommand{
        public PrintQueriesCommand(DatabaseAccess da, Scanner s){super(da, s);}
        @Override public void execute(){
            System.out.println("Printing Queries...\n");
            da.printQueries();
            System.out.println();
        }        
    }
 
    /**
     * The Update Command - responsible for sending a query
     * to the target database.
     */
    private class UpdateCommand extends DatabaseCommand{
        public UpdateCommand(DatabaseAccess da, Scanner s){super(da, s);}
        @Override public void execute(){
            System.out.println("Enter an update query:\n");
            da.doUpdate(getInput.nextLine());
        }
    }
 
    /**
     * The DisplayCommand - responsible for displaying the current table.
     */
    private class DisplayCommand extends DatabaseCommand{
        public DisplayCommand(DatabaseAccess da, Scanner s){super(da, s);}
        @Override public void execute(){da.displayTable();}
    }    
 
    /**
     * Producer class for commands relative to the DatabaseProgram
     */
    private class DatabaseCommandFactory{
        private Scanner theInput;
        private DatabaseAccess myDBObj;
 
        /**
         * Sets the handle for the Database and the Input
         * for each DatabaseCommand.
         */
        public void setHandles(DatabaseAccess da, Scanner kb){
            theInput = kb;
            myDBObj = da;
        }
 
        /**
         * Factory method that produces a command to use
         */
        public DatabaseCommand getCommand(String arg){
            DatabaseCommand tempDC = (arg.equalsIgnoreCase("QUERY"))
                    ? new QueryCommand(myDBObj, theInput):(arg.equalsIgnoreCase("UPDATE")) 
                    ? new UpdateCommand(myDBObj, theInput):(arg.equalsIgnoreCase("DISPLAY")) 
                    ? new DisplayCommand(myDBObj, theInput):(arg.equalsIgnoreCase("PRINTQUERIES")) 
                    ? new PrintQueriesCommand(myDBObj, theInput):(arg.equalsIgnoreCase("QnD"))
                    ? new QueryAndDisplay(myDBObj, theInput): null;
            return tempDC;
        }
 
        /**
         * Returns a list of options for the commands available
         * from this factory.
         */
        public String[] getOptions(){
            String temp[] ={
                "Query",
                "Update",
                "Display",
                "QnD",
                "PrintQueries"
            };
            return temp;
        }
    }
}
 
/////////////////////////////////////////////
 
/**
 A test run of the program using NetBeans 6.1--
 
init:
deps-jar:
compile-single:
run-single:
Program Started!
 
Which Database will you access?
 
TD
TD
Which action would you like to do for TD?
Query
Update
Display
QnD
PrintQueries
 
If no action, then enter EXIT
 
 
QnD
Enter a query to execute:
 
SELECT Name FROM MyTable
 
        Name
        Mark
 
Which action would you like to do for TD?
Query
Update
Display
QnD
PrintQueries
 
If no action, then enter EXIT
 
 
PRINTQUERIES
Printing Queries...
 
SELECT Name FROM MyTable
 
 
Which action would you like to do for TD?
Query
Update
Display
QnD
PrintQueries
 
If no action, then enter EXIT
 
 
QUERY
Enter a query to execute: 
 */