Java/Database SQL JDBC/SQL Interpreter
A general-purpose SQL interpreter program
<source lang="java">
/*
* 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));
}
}
</source>
