Java/Database SQL JDBC/SQL Interpreter

A general-purpose SQL interpreter program

/*
 * 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.ru/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));
  }
}