Recipe 20.13 Program: SQLRunner

The SQLRunner program is a simple interface to any SQL database for which you have a JDBC driver and a login name and password. Most databases provide such a program, and most of them are more powerful. However, this program has the advantage that it works with any database. The program reads SQL commands from a console window (up to a semicolon), passes them to the driver, and prints the results. If the result is a ResultSet, it is printed using a ResultsDecorator; otherwise, it is printed as a RowCount.

The abstract ResultsDecorator class (ResultsFormatter might have been a better name) is shown in Example 20-15. A text-mode decorator is used by default; an HTML decorator (discussed earlier in Example 20-13) and an SQL generator (potentially useful in dumping the data for insertion into another database) is also available. You can specify the decorator using command-line options or switch using the escape mechanism; for example, a line with \mh; sets the mode to HTML for the results of all following output.

To avoid hardcoding database parameters, they are fetched from a properties file, which defaults to ${user.home}/.db.properties. For example, my .db.properties file contains entries like the following:

# Connection for the "lhbooks" database lhbooks.DBDriver=org.postgresql.Driver lhbooks.DBURL=jdbc:postgresql:ecom lhbooks.DBUser=thisoneistopsecrettoo lhbooks.DBPassword=fjkdjsj # Connection for the "tmclub " database tmclub.DBDriver=org.postgresql.Driver tmclub.DBURL=jdbc:postgresql:tmclub_alliston tmclub.DBUser=dontshowthereaderstherealpassword tmclub.DBPassword=dlkjklzj

I wish I could connect to one of these databases just by saying:

java SQLRunner -c tmclub

But that won't work because I have to provide the driver jar files in the CLASSPATH. So a Unix shell script sqlrunner runs this java command and sets the classpath to include my drivers. So I can say:

sqlrunner -c tmclub

This connects me to my Toastmasters^[7] club database. In this example, I select all the meetings that are scheduled for the year 2004; just to show the use of different ResultsDecorators, I then switch to HTML and print the resultset as HTML, which I paste into an HTML page (in a web application, a servlet would get the results and call the ResultsDecorator directly):

^[7] Toastmasters is an international nonprofit organization dedicated to public speaking and leadership; see http://www.toastmasters.org/ for information on clubs and programs.

SQLRunner: Loading driver org.postgresql.Driver SQLRunner: Connecting to DB jdbc:postgresql:tmclub_alliston SQLRunner: Connected to PostgreSQL SQLRunner: ready. select * from meetings where date > '2004-01-01'; Executing : <<select * from meetings where date > '2004-01-01'>> id      date    theme   maxspeakers     roles_order 21      2004-01-07     Everything Old is New Again      7       null 22      2004-01-14      T.B.A.  7       null 23      2004-01-21      T.B.A.  7       null 24      2004-01-28      T.B.A.  7       null 25      2004-02-04      T.B.A.  7       null 26      2004-02-11      T.B.A.  7       null 27      2004-02-18      T.B.A.  7       null 28      2004-02-25      g Somehing New  7       null 29      2004-03-03      Spring is in the air?   null    null 2       2004-03-05      Peak Performance        null    null 30      2004-03-10      Peak Performance        5       null 31      2004-03-17      Spring Break    null    null \mh; select * from meetings where date > '2004-01-01'; Executing : <<select * from meetings where date > '2004-01-01'>> <table border=1> <tr><th>id<th>date<th>theme<th>maxspeakers<th>roles_order</tr> <tr><td>21<td>2004-01-07<td>Everything Old is New Again<td>7<td>null</tr> <tr><td>22<td>2004-01-14<td>T.B.A.<td>7<td>null</tr> <tr><td>23<td>2004-01-21<td>T.B.A.<td>7<td>null</tr> <tr><td>24<td>2004-01-28<td>T.B.A.<td>7<td>null</tr> <tr><td>25<td>2004-02-04<td>T.B.A.<td>7<td>null</tr> <tr><td>26<td>2004-02-11<td>T.B.A.<td>7<td>null</tr> <tr><td>27<td>2004-02-18<td>T.B.A.<td>7<td>null</tr> <tr><td>28<td>2004-02-25<td>g Somehing New<td>7<td>null</tr> <tr><td>29<td>2004-03-03<td>Spring is in the air?<td>null<td>null</tr> <tr><td>2<td>2004-03-05<td>Peak Performance<td>null<td>null</tr> <tr><td>30<td>2004-03-10<td>Peak Performance<td>5<td>null</tr> <tr><td>31<td>2004-03-17<td>Spring Break<td>null<td>null</tr> </table>

The code for ResultsDecorator and ResultsDecoratorText is shown in Example 20-15 and Example 20-16, respectively. These programs are quite general and have no dependency on SQLRunner.

Example 20-15. ResultsDecorator.java

import java.io.PrintWriter; import java.sql.ResultSet; import java.sql.SQLException; /** Base class for a series of ResultSet printers.  * @version $Id: ch20.xml,v 1.5 2004/05/04 20:13:30 ian Exp $  */ public abstract class ResultsDecorator {     ResultSet rs;     PrintWriter out;     ResultsDecorator(PrintWriter out){         this.out = out;     }     abstract void write(ResultSet rs) throws SQLException;     abstract void write(int rowCount) throws SQLException; }

Example 20-16. ResultsDecoratorText.java

import java.io.PrintWriter; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; /**  * Print a ResultSet in plain text.  */ class ResultsDecoratorText extends ResultsDecorator {          ResultsDecoratorText(PrintWriter out) {         super(out);     }          public void write(ResultSet rs) throws SQLException {         ResultSetMetaData md = rs.getMetaData( );         int cols = md.getColumnCount( );         for (int i = 1; i <= cols; i++) {             out.print(md.getColumnName(i) + "\t");         }         out.println( );         while (rs.next( )) {             for (int i = 1; i <= cols; i++) {                 out.print(rs.getString(i) + "\t");             }             out.println( );         }         out.flush( );     }     void write(int rowCount) throws SQLException {         out.println("OK: " + rowCount);         out.flush( );     } }

Finally, the main program, SQLRunner, is shown in Example 20-17.

Example 20-17. SQLRunner.java

import java.io.BufferedReader; import java.io.File; import java.io.FileReader; import java.io.FileWriter; import java.io.IOException; import java.io.InputStreamReader; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import com.darwinsys.database.DataBaseException; import com.darwinsys.lang.GetOpt; import com.darwinsys.sql.ConnectionUtil; /** Class to run an SQL script, like psql(1), SQL*Plus, or similar programs.  * Command line interface accepts options -c config [-f configFile] [scriptFile].  * <p>Input language is: escape commands (begin with \ and MUST end with semi-colon), or  * standard SQL statements which must also end with semi-colon);  * <p>Escape sequences:   * <ul>  * <li> \m (output-mode), takes character t for text,  * h for html, s for sql, x for xml (not in this version)  * (the SQL output is intended to be usable to re-insert the data into an identical table,  * but this has not been extensively tested!).  * <li> \o output-file, redirects output.  * <li> \q quit the program  * </ul>  * TODO: Fix parsing so escapes don't need to end with SQL semi-colon.  * <p>This class can also be used from within programs such as servlets, etc.  * <p>TODO: knobs to set debug mode (interactively & from getopt!)  * <p>For example, this command and input:</pre>  * SQLrunner -c testdb  * \ms;  * select *from person where person_key=4;  * </pre>might produce this output:<pre>  * Executing : <<select * from person where person_key=4>>  *  insert into PERSON(PERSON_KEY,  FIRST_NAME, INITIAL, LAST_NAME, ... )   * values (4, 'Ian', 'F', 'Darwin', ...);  * </pre>  * @author     Ian Darwin, http://www.darwinsys.com/  */ public class SQLRunner implements ResultsDecoratorPrinter {            /** The set of all valid modes. Short, lowercase names were used       * for simple use in \mX where X is one of the names.       */      enum mode {           t, h, s, x;      };      mode outputMode = mode.t;      /** Database connection */      protected Connection conn;      /** SQL Statement */      protected Statement stmt;            /** Where the output is going */      protected PrintWriter out;            private ResultsDecorator currentDecorator;      private ResultsDecorator textDecorator;      private ResultsDecorator sqlDecorator;            private ResultsDecorator htmlDecorator;            private ResultsDecorator xmlDecorator;            boolean debug = false;      private static void doHelp(int i) {           System.out.println(           "Usage: SQLRunner [-f configFile] [-c config] [SQLscript[ ...]");           System.exit(i);      }      /**       * main - parse arguments, construct SQLRunner object, open file(s), run scripts.       * @throws SQLException if anything goes wrong.       * @throws DatabaseException if anything goes wrong.       */      public static void main(String[] args)  {           String config = "default";           String outputModeName = null;           GetOpt go = new GetOpt("f:c:m:");           char c;           while ((c = go.getopt(args)) != GetOpt.DONE) {                switch(c) {                case 'h':                     doHelp(0);                     break;                case 'f':                     ConnectionUtil.setConfigFileName(go.optarg( ));                     break;                case 'c':                     config = go.optarg( );                     break;                case 'm':                     outputModeName = go.optarg( );                     break;                default:                     System.err.println("Unknown option character " + c);                     doHelp(1);                }           }           try {                Connection conn = ConnectionUtil.getConnection(config);                SQLRunner prog = new SQLRunner(conn, outputModeName);                                if (go.getOptInd( ) == args.length) {                     prog.runScript(new BufferedReader(                          new InputStreamReader(System.in)));                } else for (int i = go.getOptInd( ); i < args.length; i++) {                     prog.runScript(args[i]);                }                prog.close( );           } catch (SQLException ex) {                throw new DataBaseException(ex.toString( ));           } catch (IOException ex) {                throw new DataBaseException(ex.toString( ));           }           System.exit(0);      }      /** Construct a SQLRunner object       * @param driver String for the JDBC driver       * @param dbUrl String for the JDBC URL       * @param user String for the username       * @param password String for the password, normally in cleartext       * @param outputMode One of the MODE_XXX constants.       * @throws ClassNotFoundException       * @throws SQLException       */      public SQLRunner(String driver, String dbUrl, String user, String password,                String outputMode)                throws ClassNotFoundException, SQLException {           conn = ConnectionUtil.createConnection(driver, dbUrl, user, password);           finishSetup(outputMode);      }            public SQLRunner(Connection c, String outputMode) throws SQLException {           // set up the SQL input           conn = c;           finishSetup(outputMode);      }            void finishSetup(String outputMode) throws SQLException {           DatabaseMetaData dbm = conn.getMetaData( );           String dbName = dbm.getDatabaseProductName( );           System.out.println("SQLRunner: Connected to " + dbName);           stmt = conn.createStatement( );                      out = new PrintWriter(System.out);                      setOutputMode(outputMode);      }            /** Set the output mode.       * @param outputMode Must be a value equal to one of the MODE_XXX values.       * @throws IllegalArgumentException if the mode is not valid.       */      void setOutputMode(String outputModeName) {           if (outputModeName == null ||                 outputModeName.length( ) == 0) { throw new IllegalArgumentException(                "invalid mode: " + outputModeName + "; must be t, h, x or s"); }           // Assign the correct ResultsDecorator, creating them on the fly           // using the lazy evaluation pattern.           ResultsDecorator newDecorator = null;           outputMode = mode.valueOf(outputModeName);           switch (outputMode) {                case t:                     if (textDecorator == null) {                          textDecorator = new ResultsDecoratorText(this);                     }                     newDecorator = textDecorator;                     break;                case h:                     if (htmlDecorator == null) {                          htmlDecorator = new ResultsDecoratorHTML(this);                     }                     newDecorator = htmlDecorator;                     break;                case s:                     if (sqlDecorator == null) {                          sqlDecorator = new ResultsDecoratorSQL(this);                     }                     newDecorator = sqlDecorator;                     break;                case x:                     if (xmlDecorator == null) {                          xmlDecorator = new ResultsDecoratorXML(this);                     }                     newDecorator = sqlDecorator;                     break;                default:                     String values = mode.values( ).toString( );                     throw new IllegalArgumentException("invalid mode: "                                         + outputMode + "; must be " + values);           }           if (currentDecorator != newDecorator) {                currentDecorator = newDecorator;                System.out.println("Mode set to  " + outputMode);           }      }            /** Run one script file, by name. Called from cmd line main       * or from user code.       */      public void runScript(String scriptFile)      throws IOException, SQLException {           BufferedReader is;           // Load the script file first, it's the most likely error           is = new BufferedReader(new FileReader(scriptFile));           runScript(is);      }      /** Run one script, by name, given a BufferedReader. */      public void runScript(BufferedReader is)      throws IOException, SQLException {           String stmt;           int i = 0;           System.out.println("SQLRunner: ready.");           while ((stmt = getStatement(is)) != null) {                stmt = stmt.trim( );                if (stmt.startsWith("\\")) {                     doEscape(stmt);                } else {                     runStatement(stmt);                }           }      }      /**       * Process an escape like \ms; for mode=sql.       */      private void doEscape(String str) throws IOException {           String rest = null;           if (str.length( ) > 2) {                rest = str.substring(2);           }           if (str.startsWith("\\m")) {     // MODE                if (rest == null){                     throw new IllegalArgumentException("\\m needs output mode arg");                }                setOutputMode(rest);           } else if (str.startsWith("\\o")){                if (rest == null){                     throw new IllegalArgumentException("\\o needs output file arg");                }                setOutputFile(rest);           } else if (str.startsWith("\\q")){                System.exit(0);           } else {                throw new IllegalArgumentException("Unknown escape: " + str);           }                 }      /** Set the output to the given filename.       * @param fileName       */      private void setOutputFile(String fileName) throws IOException{           File file = new File(fileName);           out = new PrintWriter(new FileWriter(file), true);           System.out.println("Output set to " + file.getCanonicalPath( ));      }             /** Set the output file back to System.out */      private void setOutputFile( ) throws IOException{           out = new PrintWriter(System.out, true);      }      /** Run one Statement, and format results as per Update or Query.       * Called from runScript or from user code.       */      public void runStatement(String str) throws IOException, SQLException {                      System.out.println("Executing : <<" + str.trim( ) + ">>");           System.out.flush( );           try {                boolean hasResultSet = stmt.execute(str);                if (!hasResultSet)                     currentDecorator.write(stmt.getUpdateCount( ));                else {                     ResultSet rs = stmt.getResultSet( );                     currentDecorator.write(rs);                }           } catch (SQLException ex) {                if (debug){                     throw ex;                } else {                     System.out.println("ERROR: " + ex.toString( ));                }           }           System.out.println( );      }            /** Extract one statement from the given Reader.       * Ignore comments and null lines.       * @return The SQL statement, up to but not including the ';' character.       * May be null if not statement found.       */      public static String getStatement(BufferedReader is)      throws IOException {           String ret="";           String line;           boolean found = false;           while ((line = is.readLine( )) != null) {                if (line == null || line.length( ) == 0) {                     continue;                }                if (!(line.startsWith("#") || line.startsWith("--"))) {                     ret += ' ' + line;                     found = true;                }                if (line.endsWith(";")) {                   // Kludge, kill off empty statements (";") by itself, continue scanning.                     if (line.length( ) == 1)                          line = "";                     ret = ret.substring(0, ret.length( )-1);                     return ret;                }           }           return null;      }      public void close( ) throws SQLException {           stmt.close( );           conn.close( );           out.flush( );           out.close( );      }      /* (non-Javadoc)       * @see DatabaseWriterImpl#println(java.lang.String)       */      public void print(String line) throws IOException {           out.print(line);      }            public void println(String line) throws IOException {           out.println(line);           out.flush( );      }      /* (non-Javadoc)       * @see DatabaseWriterImpl#println( )       */      public void println( ) throws IOException {           out.println( );           out.flush( );      }      /* (non-Javadoc)       * @see ResultsDecoratorPrinter#getPrintWriter( )       */      public PrintWriter getPrintWriter( ) {           return out;      } }

I use this program fairly regularly, so it continues to evolve; the code in the online edition may differ from the version shown here.

See Also

As an example of a more specific program, the online source code includes JDAdmin, an administrator's interface using Swing to display and modify the JabaDot user database used in some examples in this chapter.

The file docs/guide/jdbc/getstart/introTOC.doc.html is provided with the JDK and gives some guidance on JDBC. JDBC is given extensive coverage in O'Reilly's Database Programming with JDBC and Java by George Reese. Addison Wesley's JDBC Database Access from Java: A Tutorial and Annotated Reference by Graham Hamilton, Rick Cattell, and Maydene Fisher is also recommended. For general information on databases, you might want to consult Joe Celko's Data and Databases (Morgan Kaufman) or any of many other good general books.