Database Management Program

Alex Edwards 0 Tallied Votes 245 Views Share

I'm not sure if it has been done yet or not, but I thought this program might be helpful to those that want to practice SQL in Java, because it sure is helpful for me! =)

The assumption is that you're running Java 5+ and that you have a Database linked already.

You can provide your own Driver argument as well - you'll have to add a comma an the String version of the location of your Driver in the DatabaseAccess object in the main method of the DatabaseProgram class.


I'm hoping to make a much better front-end version of this program in the future.

Anyways, Have fun! =)

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: 
 */
Alex Edwards 321 Posting Shark

Quite a few flaws noted in this version--


-It would probably be more advantageous to use ConsoleIOProgram

-Invoking a thread on the EDT isn't desirable. It would be more beneficial to run a thread from the EDT (via swing or awt) to run a runnable that executes this program.

-This version doesn't account for password-protected Databases (i.e., Oracle)

-Ridiculous use of 2 Design Patterns. It probably would have been more beneficial had I allowed the user to load commands for the Database Program. Either that or allow a user to supply a factory to produce Command objects. Even then, there's the question of heap usage - why make things hard for the Garbage Collecting threads? A Factory that retrieves a reference to a singleton command (one that is instantiated once) would be more preferable, though depending on the necessity of reuse and the future of querying (or the type of Database you're accessing), etc.


-Pluses

-One good implementation in this program is the getOptions[]() method that produces a new set of the existing Instructions. Though this doesn't adhere to reusable code, the idea is straightforward to prevent catastrophes like this--

public class MyTypes{

	private String values[] = {"North", "South", "East", "West"};

	public String[] getValues(boolean theReference){
		return (theReference) ? values : new String[]{"North", "South", "East", "West"};
	}

	@Override public String toString(){
		String temp = "";
		for(String element : getValues(true))
			temp += element + "\t";
		temp += "\n";
		for(String element : getValues(false))
			temp += element + "\t";
		return temp;
	}

	public static void main(String... args){
		MyTypes mt = new MyTypes();

		String thief[] = mt.getValues(true); // thief points to the global array

		// changing the address the pointer at mt.values[2] poitns to from "East" to "CENTER"
		thief[2] = "CENTER";

		System.out.println(mt); // Strings are immutable, but in java pointers aren't unless marked final!
	}
}

-- but it can easily be make in a reusable manner for all commands registered in a factory, or if commands are loaded into the program, one can call ArrayList<T>.<T>toArray(new T[ArrayList<T>.size()]); granted T is known before compile time.

- The double-checked lock is a plus, though it's not immediately apparent in this version because I'm not using multi-threading in this example and I also screwed up with running the program in the EDT.


-More updates to come in the future.

newtonorina 0 Newbie Poster

isn't that code long for one to understand

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.