A General Purpose SQL Interpreter Program

package flanagan.david;
 
/*
 * Copyright (c) 2000 David Flanagan.  All rights reserved.
 * This code is from the book Java Examples in a Nutshell, 2nd Edition.
 * It is provided AS-IS, WITHOUT ANY WARRANTY either expressed or implied.
 * You may study, use, and modify it for any non-commercial purpose.
 * You may distribute it non-commercially as long as you retain this notice.
 * For a commercial use license, or to purchase the book (recommended),
 * visit http://www.davidflanagan.com/javaexamples2.
 */
 
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
 
/**
 * A general-purpose SQL interpreter program.
 */
public class ExecuteSQL {
    public static void main(String[] args) {
	Connection conn = null; // Our JDBC connection to the database server
	try {
	    String driver = null, url = null, user = "", password = "";
 
	    // Parse all the command-line arguments
	    for (int n = 0; n < args.length; n++) {
		if (args[n].equals("-d"))
		    driver = args[++n];
		else if (args[n].equals("-u"))
		    user = args[++n];
		else if (args[n].equals("-p"))
		    password = args[++n];
		else if (url == null)
		    url = args[n];
		else
		    throw new IllegalArgumentException("Unknown argument.");
	    }
 
	    // The only required argument is the database URL.
	    if (url == null)
		throw new IllegalArgumentException("No database specified");
 
	    // If the user specified the classname for the DB driver, load
	    // that class dynamically. This gives the driver the opportunity
	    // to register itself with the DriverManager.
	    if (driver != null)
		Class.forName(driver);
 
	    // Now open a connection the specified database, using the
	    // user-specified username and password, if any. The driver
	    // manager will try all of the DB drivers it knows about to try to
	    // parse the URL and connect to the DB server.
	    conn = DriverManager.getConnection(url, user, password);
 
	    // Now create the statement object we'll use to talk to the DB
	    Statement s = conn.createStatement();
 
	    // Get a stream to read from the console
	    BufferedReader in = new BufferedReader(new InputStreamReader(
		    System.in));
 
	    // Loop forever, reading the user's queries and executing them
	    while (true) {
		System.out.print("sql> "); // prompt the user
		System.out.flush(); // make the prompt appear now.
		String sql = in.readLine(); // get a line of input from user
 
		// Quit when the user types "quit".
		if ((sql == null) || sql.equals("quit"))
		    break;
 
		// Ignore blank lines
		if (sql.length() == 0)
		    continue;
 
		// Now, execute the user's line of SQL and display results.
		try {
		    // We don't know if this is a query or some kind of
		    // update, so we use execute() instead of executeQuery()
		    // or executeUpdate() If the return value is true, it was
		    // a query, else an update.
		    boolean status = s.execute(sql);
 
		    // Some complex SQL queries can return more than one set
		    // of results, so loop until there are no more results
		    do {
			if (status) { // it was a query and returns a ResultSet
			    ResultSet rs = s.getResultSet(); // Get results
			    printResultsTable(rs, System.out); // Display them
			} else {
			    // If the SQL command that was executed was some
			    // kind of update rather than a query, then it
			    // doesn't return a ResultSet. Instead, we just
			    // print the number of rows that were affected.
			    int numUpdates = s.getUpdateCount();
			    System.out.println("Ok. " + numUpdates
				    + " rows affected.");
			}
 
			// Now go see if there are even more results, and
			// continue the results display loop if there are.
			status = s.getMoreResults();
		    } while (status || s.getUpdateCount() != -1);
		}
		// If a SQLException is thrown, display an error message.
		// Note that SQLExceptions can have a general message and a
		// DB-specific message returned by getSQLState()
		catch (SQLException e) {
		    System.err.println("SQLException: " + e.getMessage() + ":"
			    + e.getSQLState());
		}
		// Each time through this loop, check to see if there were any
		// warnings. Note that there can be a whole chain of warnings.
		finally { // print out any warnings that occurred
		    SQLWarning w;
		    for (w = conn.getWarnings(); w != null; w = w
			    .getNextWarning())
			System.err.println("WARNING: " + w.getMessage() + ":"
				+ w.getSQLState());
		}
	    }
	}
	// Handle exceptions that occur during argument parsing, database
	// connection setup, etc. For SQLExceptions, print the details.
	catch (Exception e) {
	    System.err.println(e);
	    if (e instanceof SQLException)
		System.err.println("SQL State: "
			+ ((SQLException) e).getSQLState());
	    System.err.println("Usage: java ExecuteSQL [-d <driver>] "
		    + "[-u <user>] [-p <password>] <database URL>");
	}
 
	// Be sure to always close the database connection when we exit,
	// whether we exit because the user types 'quit' or because of an
	// exception thrown while setting things up. Closing this connection
	// also implicitly closes any open statements and result sets
	// associated with it.
	finally {
	    try {
		conn.close();
	    } catch (Exception e) {
	    }
	}
    }
 
    /**
     * This method attempts to output the contents of a ResultSet in a textual
     * table. It relies on the ResultSetMetaData class, but a fair bit of the
     * code is simple string manipulation.
     */
    static void printResultsTable(ResultSet rs, OutputStream output)
	    throws SQLException {
	// Set up the output stream
	PrintWriter out = new PrintWriter(output);
 
	// Get some "meta data" (column names, etc.) about the results
	ResultSetMetaData metadata = rs.getMetaData();
 
	// Variables to hold important data about the table to be displayed
	int numcols = metadata.getColumnCount(); // how many columns
	String[] labels = new String[numcols]; // the column labels
	int[] colwidths = new int[numcols]; // the width of each
	int[] colpos = new int[numcols]; // start position of each
	int linewidth; // total width of table
 
	// Figure out how wide the columns are, where each one begins,
	// how wide each row of the table will be, etc.
	linewidth = 1; // for the initial '|'.
	for (int i = 0; i < numcols; i++) { // for each column
	    colpos[i] = linewidth; // save its position
	    labels[i] = metadata.getColumnLabel(i + 1); // get its label
	    // Get the column width. If the db doesn't report one, guess
	    // 30 characters. Then check the length of the label, and use
	    // it if it is larger than the column width
	    int size = metadata.getColumnDisplaySize(i + 1);
	    if (size == -1)
		size = 30; // Some drivers return -1...
	    if (size > 500)
		size = 30; // Don't allow unreasonable sizes
	    int labelsize = labels[i].length();
	    if (labelsize > size)
		size = labelsize;
	    colwidths[i] = size + 1; // save the column the size
	    linewidth += colwidths[i] + 2; // increment total size
	}
 
	// Create a horizontal divider line we use in the table.
	// Also create a blank line that is the initial value of each
	// line of the table
	StringBuffer divider = new StringBuffer(linewidth);
	StringBuffer blankline = new StringBuffer(linewidth);
	for (int i = 0; i < linewidth; i++) {
	    divider.insert(i, '-');
	    blankline.insert(i, " ");
	}
	// Put special marks in the divider line at the column positions
	for (int i = 0; i < numcols; i++)
	    divider.setCharAt(colpos[i] - 1, '+');
	divider.setCharAt(linewidth - 1, '+');
 
	// Begin the table output with a divider line
	out.println(divider);
 
	// The next line of the table contains the column labels.
	// Begin with a blank line, and put the column names and column
	// divider characters "|" into it. overwrite() is defined below.
	StringBuffer line = new StringBuffer(blankline.toString());
	line.setCharAt(0, '|');
	for (int i = 0; i < numcols; i++) {
	    int pos = colpos[i] + 1 + (colwidths[i] - labels[i].length()) / 2;
	    overwrite(line, pos, labels[i]);
	    overwrite(line, colpos[i] + colwidths[i], " |");
	}
 
	// Then output the line of column labels and another divider
	out.println(line);
	out.println(divider);
 
	// Now, output the table data. Loop through the ResultSet, using
	// the next() method to get the rows one at a time. Obtain the
	// value of each column with getObject(), and output it, much as
	// we did for the column labels above.
	while (rs.next()) {
	    line = new StringBuffer(blankline.toString());
	    line.setCharAt(0, '|');
	    for (int i = 0; i < numcols; i++) {
		Object value = rs.getObject(i + 1);
		if (value != null)
		    overwrite(line, colpos[i] + 1, value.toString().trim());
		overwrite(line, colpos[i] + colwidths[i], " |");
	    }
	    out.println(line);
	}
 
	// Finally, end the table with one last divider line.
	out.println(divider);
	out.flush();
    }
 
    /** This utility method is used when printing the table of results */
    static void overwrite(StringBuffer b, int pos, String s) {
	int slen = s.length(); // string length
	int blen = b.length(); // buffer length
	if (pos + slen > blen)
	    slen = blen - pos; // does it fit?
	for (int i = 0; i < slen; i++)
	    // copy string into buffer
	    b.setCharAt(pos + i, s.charAt(i));
    }
}