FileDocCategorySizeDatePackage
QueryApp.javaAPI DocExample9393Tue Jul 01 03:22:00 BST 1997None

QueryApp

public class QueryApp extends Object

Fields Summary
static Connection
connection
Constructors Summary
Methods Summary
public static java.util.VectorexecuteSQL(java.lang.String sql)

	Vector v = new Vector();
	
	try {
	    Statement statement = connection.createStatement();
	    
	    if( statement.execute(sql) ) {
		ResultSet results = statement.getResultSet();
		ResultSetMetaData meta = results.getMetaData();
		int cols;
		
		cols = meta.getColumnCount();
		while( results.next() ) {
		    Hashtable h = new Hashtable(cols);
		    int i;
		    
		    for(i=0; i<cols; i++) {
			Object ob = results.getObject(i);
			
			h.put(meta.getColumnLabel(i), ob);
		    }
		    v.addElement(h);
		}
		return v;
	    }
	    return null;
	}
	catch( SQLException e ) {
	    e.printStackTrace();
	    return null;
	}
    
public static voidexecuteStatement(java.lang.String sql, java.sql.Connection connection)

	Statement statement = null;
	
	try {
	    statement = connection.createStatement();
	    if( statement.execute(sql) ) { // true means the SQL was a SELECT
		processResults(statement.getResultSet());
	    }
	    else { // no result sets, see how many rows were affected
		int num;
		
		switch(num = statement.getUpdateCount()) {
		case 0:
		    System.out.println("No rows affected.");
		    break;
		    
		case 1:
		    System.out.println(num + " row affected.");
		    break;
		    
		default:
		    System.out.println(num + " rows affected.");
		}
	    }
	}
	catch( SQLException e ) {
	    throw e;
	}
	finally { // close out the statement
	    if( statement != null ) {
		try { statement.close(); }
		catch( SQLException e ) { }
	    }
	}
    
public static voidmain(java.lang.String[] args)

	java.io.InputStreamReader reader;
	java.io.BufferedReader input;
	Properties props = new Properties();
	boolean connected = false;
	String buffer = "", url, driver;
	int line = 1; // Mark current input line
	
	// Need user id and password to connect
	if( args.length < 2 ) {
	    System.out.println("Syntax: <java QueryApp [user] [password]>");
	    return;
	}
	props.put("user",     args[0]);
	props.put("password", args[1]);
	if( args.length > 2 ) {
	    url = args[2];
	}
	else {
	    url = "jdbc:msql://athens.imaginary.com:4333/db_bank";
	}
	if( args.length > 3 ) {
	    driver = args[3];
	}
	else {
	    driver = "COM.imaginary.sql.msql.MsqlDriver";
	}
	// Connect to the database
	try {
	    Class.forName(driver);
	    connection = DriverManager.getConnection(url, props);
	}
	catch( SQLException e ) {
	    System.out.println("Failed to connect to database: " +
			       e.getMessage());
	    return;
	}
	catch( ClassNotFoundException e ) {
	    System.out.println("Unable to find driver class.");
	    return;
	}
	System.out.println("Connected to the database.");
	connected = true;
	reader = new java.io.InputStreamReader(System.in);
	input = new java.io.BufferedReader(reader);
	// Enter into a user input loop
	while( connected ) {
	    String tmp, cmd;
	    
	    // Print a prompt
	    if( line == 1 ) {
		System.out.print(" > ");
	    }
	    else {
		System.out.print(line + "> ");
	    }
	    System.out.flush();
	    // Get the next line of input
	    try {
		tmp = input.readLine();
	    }
	    catch( java.io.IOException e ) {
		return;
	    }
	    // Get rid of extra space in the command
	    cmd = tmp.trim();
	    // The user wants to commit pending transactions
	    if( cmd.equals("commit") ) {
		try {
		    connection.commit();
		    System.out.println("Commit successful.");
		}
		catch( SQLException e ) {
		    System.out.println("Error in commit: " + e.getMessage());
		}
		buffer = "";
		line = 1;
	    }
	    // The user wants to execute the current buffer
	    else if( cmd.equals("go") ) {
		if( !buffer.equals("") ) {
		    try {
			executeStatement(buffer, connection);
		    }
		    catch( SQLException e ) {
			System.out.println(e.getMessage());
		    }
		}
		buffer = "";
		line = 1;
		continue;
	    }
	    // The user wants to quit
	    else if( cmd.equals("quit") ) {
		connected = false;
		continue;
	    }
	    // The user wants to clear the current buffer
	    else if( cmd.equals("reset") ) {
		buffer = "";
		line = 1;
		continue;
	    }
	    // The user wants to abort a pending transaction
	    else if( cmd.equals("rollback") ) {
		try {
		    connection.rollback();
		    System.out.println("Rollback successful.");
		}
		catch( SQLException e ) {
		    System.out.println("An error occurred during rollback: " +
				       e.getMessage());
		}
		buffer = "";
		line = 1;
	    }
	    // The user wants version info
	    else if( cmd.startsWith("show") ) {
		if( cmd.length() < 6 ) { // No argument after show
		    System.out.println("show version");
		}
		else {
		    DatabaseMetaData meta;
		    
		    try {
			meta = connection.getMetaData();
			cmd = cmd.substring(5, cmd.length()).trim();
			if( cmd.equals("version") ) {
			    showVersion(meta);
			}
			else {
			    System.out.println("show version"); // Bad arg
			}
		    }
		    catch( SQLException e ) {
			System.out.println("Failed to load meta data: " +
					   e.getMessage());
		    }
		}
		buffer = "";
		line = 1;
	    }
	    // The input that is not a keyword should appended be to the buffer
	    else {
		buffer = buffer + " " + tmp;
		line++;
		continue;
	    }
	}
	try {
	    connection.close();
	}
	catch( SQLException e ) {
	    System.out.println("Error closing connection: " + e.getMessage());
	}
	System.out.println("Connection closed.");
    
public static voidprocessResults(java.sql.ResultSet results)

	try {
	    ResultSetMetaData meta = results.getMetaData();
	    StringBuffer bar = new StringBuffer();
	    String buffer = "";
	    int cols = meta.getColumnCount();
	    int row_count = 0;
	    int i, width = 0;
	    
	    // Prepare headers for each of the columns
	    // The display should look like:
	    //  --------------------------------------
	    //  |    Column One    |   Column Two    |
	    //  --------------------------------------
	    //  |    Row 1 Value   |   Row 1 Value   |
	    //  --------------------------------------
	    
	    // create the bar that is as long as the total of all columns
	    for(i=1; i<=cols; i++) {
		width += meta.getColumnDisplaySize(i);
	    }
	    width += 1 + cols;
	    for(i=0; i<width; i++) {
		bar.append('-");
	    }
	    bar.append('\n");
	    buffer += bar + "|";
	    // After the first bar goes the column labels
	    for(i=1; i<=cols; i++) {
		StringBuffer filler = new StringBuffer();
		String label = meta.getColumnLabel(i);
		int size = meta.getColumnDisplaySize(i);
		int x;
		
		// If the label is long than the column is wide,
		// then we truncate the column label
		if( label.length() > size ) {
		    label = label.substring(0, size);
		}
		// If the label is shorter than the column, pad it with spaces
		if( label.length() < size ) {
		    int j;
		    
		    x = (size-label.length())/2;
		    for(j=0; j<x; j++) {
			filler.append(' ");
		    }
		    label = filler + label + filler;
		    if( label.length() > size ) {
			label = label.substring(0, size);
		    }
		    else {
			while( label.length() < size ) {
			    label += " ";
			}
		    }
		}
		// Add the column header to the buffer
		buffer = buffer + label + "|";
	    }
	    // Add the lower bar
	    buffer = buffer + "\n" + bar;
	    // Format each row in the result set and add it on
	    while( results.next() ) {
		row_count++;
		
		buffer += "|";
		// Format each column of the row
		for(i=1; i<=cols; i++) {
		    StringBuffer filler = new StringBuffer();
		    Object value = results.getObject(i);
		    int size = meta.getColumnDisplaySize(i);
		    String str = value.toString();
		    
		    if( str.length() > size ) {
			str = str.substring(0, size);
		    }
		    if( str.length() < size ) {
			int j, x;
			
			x = (size-str.length())/2;
			for(j=0; j<x; j++) {
			    filler.append(' ");
			}
			str = filler + str + filler;
			if( str.length() > size ) {
			    str = str.substring(0, size);
			}
			else {
			    while( str.length() < size ) {
				str += " ";
			    }
			}
		    }
		    buffer = buffer + str + "|";
		}
		buffer = buffer + "\n";
	    }
	    // Stick a row count up at the top
	    if( row_count == 0 ) {
		buffer = "No rows selected.\n" + buffer;
	    }
	    else if( row_count == 1 ) {
		buffer = "1 row selected.\n" + buffer;
	    }
	    else {
		buffer = row_count + " rows selected.\n" + buffer;
	    }
	    System.out.print(buffer);
	    System.out.flush();
	}
	catch( SQLException e ) {
	    throw e;
	}
	finally {
	    try { results.close(); }
	    catch( SQLException e ) { }
	}
    
public static voidshowVersion(java.sql.DatabaseMetaData meta)

	String version = "QueryApp 1.0\n";
	
	try {
	    version += "DBMS: " + meta.getDatabaseProductName() + " " +
		meta.getDatabaseProductVersion() + "\n";
	    version += "JDBC Driver: " + meta.getDriverName() + " " +
		meta.getDriverVersion();
	    System.out.println(version);
	}
	catch( SQLException e ) {
	    System.out.println("Failed to get version info: " +e.getMessage());
	}