SQLRunner public class SQLRunner extends Object implements ResultsDecoratorPrinter Class to run an SQL script, like psql(1), SQL*Plus, or similar programs.
Command line interface accepts options -c config [-f configFile] [scriptFile].
Input language is: escape commands (begin with \ and MUST end with semi-colon), or
standard SQL statements which must also end with semi-colon);
Escape sequences:
\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 another identical table,
but this has not been extensively tested!).
\o output-file, redirects output.
\q quit the program
TODO: Fix parsing so escapes don't need to end with SQL semi-colon.
This class can also be used from within programs such as servlets, etc.
TODO: knobs to set debug mode (interactively & from getopt!)
For example, this command and input:
SQLrunner -c testdb
\ms;
select *from person where person_key=4;
might produce this output:
Executing : <>
insert into PERSON(PERSON_KEY, FIRST_NAME, INITIAL, LAST_NAME, ... )
values (4, 'Ian', 'F', 'Darwin', ...);
Fields Summary public static final String MODE_TXT The mode for textual output public static final String MODE_HTML The mode for HTML output public static final String MODE_SQL The mode for SQL output public static final String MODE_XML The mode for XML output protected Connection conn Database connection protected Statement stmt SQL Statement protected PrintWriter out Where the output is going private ResultsDecorator currentDecorator private ResultsDecorator textDecorator private ResultsDecorator sqlDecorator private ResultsDecorator htmlDecorator private ResultsDecorator xmlDecorator boolean debug
Constructors Summary public SQLRunner (String driver, String dbUrl, String user, String password, String outputFile, String outputMode)Construct a SQLRunner object
conn = ConnectionUtil.createConnection(driver, dbUrl, user, password);
finishSetup(outputFile, outputMode);
public SQLRunner (Connection c, String outputFile, String outputMode)
// set up the SQL input
conn = c;
finishSetup(outputFile, outputMode);
Methods Summary public void close ()
stmt.close();
conn.close();
out.flush();
out.close();
private void display (java.lang.String rest)Display - something
if (rest.equals("t")) {
// Display list of tables
DatabaseMetaData md = conn.getMetaData();
ResultSet rs = md.getTables(null, null, "%", null);
while (rs.next()) {
System.out.println(rs.getString(3));
}
} else if (rest.startsWith("t")) {
// Display one table
String tableName = rest.substring(1).trim();
System.out.println("# Display table " + tableName);
DatabaseMetaData md = conn.getMetaData();
ResultSet rs = md.getColumns(null, null, tableName, "%");
while (rs.next()) {
System.out.println(rs.getString(4));
}
} else
throw new IllegalArgumentException("\\d" + rest + " invalid");
private void doEscape (java.lang.String str)Process an escape like \ms; for mode=sql.
String rest = null;
if (str.length() > 2) {
rest = str.substring(2);
}
if (str.startsWith("\\d")) { // Display
if (rest == null){
throw new IllegalArgumentException("\\d needs display arg");
}
display(rest);
} else 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);
}
private static void doHelp (int i)print help; called from several places in main
System.out.println(
"Usage: SQLRunner [-f configFile] [-c config] [SQLscript[ ...]");
System.exit(i);
void finishSetup (java.lang.String outputFileName, java.lang.String outputMode)
DatabaseMetaData dbm = conn.getMetaData();
String dbName = dbm.getDatabaseProductName();
System.out.println("SQLRunner: Connected to " + dbName);
stmt = conn.createStatement();
if (outputFileName == null) {
out = new PrintWriter(System.out);
} else {
out = new PrintWriter(new FileWriter(outputFileName));
}
setOutputMode(outputMode);
public java.io.PrintWriter getPrintWriter ()
return out;
public static java.lang.String getStatement (java.io.BufferedReader is)Extract one statement from the given Reader.
Ignore comments and null lines.
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 boolean isDebug ()
return debug;
public static void main (java.lang.String[] args)main - parse arguments, construct SQLRunner object, open file(s), run scripts.
String config = "default";
String outputMode = MODE_TXT;
String outputFile = null;
boolean debug = false;
GetOpt go = new GetOpt("df:c:m:o:");
char c;
while ((c = go.getopt(args)) != GetOpt.DONE) {
switch(c) {
case 'h":
doHelp(0);
break;
case 'd":
debug = true;
break;
case 'f":
ConnectionUtil.setConfigFileName(go.optarg());
break;
case 'c":
config = go.optarg();
break;
case 'm":
outputMode = go.optarg();
break;
case 'o":
outputFile = go.optarg();
break;
default:
System.err.println("Unknown option character " + c);
doHelp(1);
}
}
try {
Connection conn = ConnectionUtil.getConnection(config);
SQLRunner prog = new SQLRunner(conn, outputFile, outputMode);
prog.setDebug(debug);
if (go.getOptInd() == args.length) {
prog.runScript(new BufferedReader(
new InputStreamReader(System.in)));
} else for (int i = go.getOptInd()-1; 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);
public void print (java.lang.String line)
out.print(line);
public void println (java.lang.String line)
out.println(line);
out.flush();
public void println ()
out.println();
out.flush();
public void runScript (java.lang.String scriptFile)Run one script file, by name. Called from cmd line main
or from user code.
BufferedReader is;
// Load the script file first, it's the most likely error
is = new BufferedReader(new FileReader(scriptFile));
runScript(is);
public void runScript (java.io.BufferedReader is)Run one script, by name, given a BufferedReader.
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);
}
}
public void runStatement (java.lang.String str)Run one Statement, and format results as per Update or Query.
Called from runScript or from user code.
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();
public void setDebug (boolean debug)
this.debug = debug;
private void setOutputFile (java.lang.String fileName)Set the output to the given filename.
File file = new File(fileName);
out = new PrintWriter(new FileWriter(file), true);
System.out.println("Output set to " + file.getCanonicalPath());
private void setOutputFile ()Set the output file back to System.out
out = new PrintWriter(System.out, true);
void setOutputMode (java.lang.String outputMode)Set the output mode.
if (outputMode.length() == 0) { throw new IllegalArgumentException(
"invalid mode: " + outputMode + "; must be t, h or s"); }
// Assign the correct ResultsDecorator, creating them on the fly
// using the lazy evaluation pattern.
ResultsDecorator newDecorator = null;
switch (outputMode.charAt(0)) {
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:
throw new IllegalArgumentException("invalid mode: "
+ outputMode + "; must be t, h or s");
}
if (currentDecorator != newDecorator) {
currentDecorator = newDecorator;
System.out.println("Mode set to "
+ currentDecorator.getName());
}
