Since I have written and rewritten certain utility programs that I have found very commonly useful for me, I figured I would put them somewhere where I can find them easily in the future. This one is a simple command-line JDBC SQL client. When you query, it displays the result in a spreadsheet-looking scrollable table. It's not sophisticated, but it is very convenient for doing quick lookups and updates.
import java.io.*;
import java.net.*;
import java.sql.*;
import java.util.*;
import java.util.List;
import java.util.logging.Logger;
import java.awt.*;
import javax.swing.*;
import javax.swing.table.*;
public class Sql
{
private SqlClassLoader classLoader;
private String inSpec;
private String outSpec;
private BufferedReader in;
private PrintWriter out;
private boolean info = true;
private boolean debug = false;
private boolean trace = false;
private boolean interactive = true;
private String connString;
private Connection connection;
private Sql () throws IOException
{
setIn("stdin");
setOut("stdout", false);
classLoader = new SqlClassLoader();
}
private void executeQuery (String sql) throws SQLException
{
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
Results results = new Results(sql, rs);
}
private void executeCall (String sql) throws SQLException
{
throw new UnsupportedOperationException("Callable statements not supported at this time.");
}
private void executeUpdate (String sql) throws SQLException
{
Statement statement = connection.createStatement();
int result = statement.executeUpdate(sql);
info("Executed update statement: " + sql);
info("Result: " + result);
}
private void checkWarnings (Connection connection) throws SQLException
{
if (connection != null)
{
for (SQLWarning warning = connection.getWarnings();
warning != null;
warning = warning.getNextWarning())
{
info(warning);
}
connection.clearWarnings();
}
}
private void checkWarnings (ResultSet resultSet) throws SQLException
{
if (resultSet != null)
{
for (SQLWarning warning = resultSet.getWarnings();
warning != null;
warning = warning.getNextWarning())
{
info(warning);
}
resultSet.clearWarnings();
}
}
private void executeSql (String sql) throws SQLException
{
trace("executeSql(" + sql + ")");
checkWarnings(connection);
String verb = new StringTokenizer(sql, "{?= ").nextToken();
if ("select".equalsIgnoreCase(verb))
{
executeQuery(sql);
}
else if ("call".equalsIgnoreCase(verb))
{
executeCall(sql);
}
else
{
executeUpdate(sql);
}
checkWarnings(connection);
}
private void process (String command) throws SQLException, MalformedURLException, IOException, Exception
{
trace("Processing command " + command);
if (command == null)
{
info("Command stream empty, reverting to stdin");
setIn("stdin");
}
else
{
command = command.trim();
if (command.startsWith("logLevel"))
{
if (command.length() > 9)
{
String requestedLevel = command.substring(9).trim();
setLogLevel(requestedLevel);
}
info("Log Levels: ERROR-true INFO-" + info + " DEBUG-" + debug + " TRACE-" + trace);
}
else if (command.startsWith("readFrom"))
{
if (command.length() > 9)
{
String source = command.substring(9).trim();
setIn(source);
}
info("Reading from " + inSpec);
}
else if (command.startsWith("writeTo"))
{
if (command.length() > 7)
{
String [] params = command.substring(7).trim().split(" ");
if (params.length == 1)
{
setOut(params[0], false);
}
else if (params.length == 2)
{
setOut(params[0], Boolean.parseBoolean(params[1]));
}
}
info("Writing to " + outSpec);
}
else if (command.startsWith("loadDriver"))
{
if (command.length() > 10)
{
loadDriver(command.substring(10).trim());
}
else
{
command = "help";
}
}
else if (command.startsWith("unloadDriver"))
{
if (command.length() > 12)
{
unloadDriver(command.substring(12).trim());
}
else
{
command = "help";
}
}
else if (command.startsWith("connect"))
{
if (command.length() > 7)
{
setConnection(command.substring(7).trim());
}
info("Connected to " + connString);
}
else if (command.startsWith("addToClasspath"))
{
if (command.length() > 14)
{
addToClasspath(command.substring(14).trim());
}
else
{
command = "help";
}
}
else if (command.equalsIgnoreCase("status"))
{
outputStatus();
}
else if (command.equalsIgnoreCase("drivers"))
{
Enumeration<Driver> drivers = DriverManager.getDrivers();
while (drivers.hasMoreElements())
{
out.println(drivers.nextElement());
}
}
else if (command.equalsIgnoreCase("tables"))
{
if (connection != null)
{
Results tables = new Results("Tables", connection.getMetaData().getTables(null, null, null, new String[] {"TABLE"}));
}
else
{
error("Must be connected to list tables.");
}
}
else if (!command.equalsIgnoreCase("help") && command.length() > 0)
{
executeSql(command);
}
if (command.equalsIgnoreCase("help"))
{
out.println("Any SQL command or:");
out.println(" logLevel [error|info|debug|trace] - set/show log level");
out.println(" readFrom [source] - set/show source of commands");
out.println(" writeTo [dest] - set/show output destination");
out.println(" connect - show current connection");
out.println(" connect dbUrl - connect to specified database using JDBC URL");
out.println(" connect dbUrl propertiesUrl - connect using JDBC URL and properties loaded from given location");
out.println(" connect dburl username password - connect using JDBC URL, username, and password");
out.println(" tables - list tables in database");
out.println(" loadDriver classname - load a JDBC driver class");
out.println(" unloadDriver classname - unload a JDBC driver class");
out.println(" addToClasspath url - adds URL to classloader");
out.println(" help - this list of commands");
out.println(" quit - quit the program");
out.println("Use --usage as command line argument to see command line options.");
}
}
}
private void execute ()
{
String inline = null;
do
{
try
{
if (interactive)
{
out.print("SQL> ");
out.flush();
}
inline = in.readLine();
process(inline);
}
catch (Exception ex)
{
error("Exception caught while executing " + inline, ex);
}
}
while (!"quit".equalsIgnoreCase(inline));
}
private BufferedReader getIn ()
{
return in;
}
private void setIn (String source)
{
trace("setIn(" + source + ")");
if (source == null)
{
throw new IllegalArgumentException("source may not be null");
}
if ("stdin".equalsIgnoreCase(source))
{
in = new BufferedReader(new InputStreamReader(System.in));
if (!interactive)
{
out.println("HELP for available commands");
}
interactive = true;
inSpec = source;
}
else
{
URL inUrl = null;
try
{
in = new BufferedReader(new InputStreamReader(new URL(source).openStream()));
interactive = false;
inSpec = source;
}
catch (Exception ex)
{
error("Unable to load commands from URL " + source, ex);
}
}
}
private PrintWriter getOut ()
{
return out;
}
private void setOut (String dest, boolean append) throws IOException
{
trace("setOut(" + dest + ", " + append + ")");
if (dest == null)
{
throw new IllegalArgumentException("dest may not be null");
}
if ("stdout".equalsIgnoreCase(dest))
{
out = new PrintWriter(new OutputStreamWriter(System.out));
outSpec = dest;
}
else if (dest != null)
{
File outFile = null;
if (dest.startsWith("file:"))
{
try
{
URI outUri = new URI(dest);
outFile = new File(outUri);
}
catch (Exception ex)
{
error("Could not open output URI " + dest, ex);
}
}
else
{
outFile = new File(dest);
}
if (outFile.exists())
{
if (!outFile.isFile())
{
error(outFile + " is not a file. I will print to standard output.");
}
else if (!append)
{
error(outFile + " exists. Use --append to append to existing file. I will print to standard output.");
}
else
{
out = new PrintWriter(new FileWriter(outFile, true));
outSpec = dest;
}
}
else
{
if (!outFile.isFile())
{
error(outFile + " is not a file. I will print to standard output.");
}
else
{
out = new PrintWriter(new FileWriter(outFile, false));
outSpec = dest;
}
}
}
}
private void loadDriver (String className) throws Exception
{
trace("loadDriver(" + className + ")");
try
{
Class<?> driverClass = classLoader.loadClass(className);
Driver driver = (Driver)driverClass.newInstance();
SqlDriver sqlDriver = new SqlDriver(driver);
DriverManager.registerDriver(sqlDriver);
if (debug)
{
debug("Loaded " + driver.getClass() + " from " + classLoader.findResource(className.replace('.', '/') + ".class"));
}
}
catch (Exception ex)
{
error("Unable to load " + className, ex);
}
}
private void unloadDriver (String className) throws Exception
{
trace("unloadDriver(" + className + ")");
Enumeration<Driver> drivers = DriverManager.getDrivers();
boolean done = false;
while (!done && drivers.hasMoreElements())
{
SqlDriver nextDriver = (SqlDriver)drivers.nextElement();
if (nextDriver.getName().equals(className))
{
DriverManager.deregisterDriver(nextDriver);
done = true;
if (debug)
{
debug("Removed " + nextDriver.getName());
}
}
}
}
private void addToClasspath (String url) throws MalformedURLException
{
trace("addToClasspath(" + url + ")");
classLoader.addURL(new URL(url));
if (debug)
{
debug(printClasspath());
}
}
private String printClasspath ()
{
StringWriter sout = new StringWriter();
PrintWriter pout = new PrintWriter(sout);
pout.println("Loading classes from:");
URL [] urls = classLoader.getURLs();
for (int i=0; i<urls.length; ++i)
{
pout.print(" ");
pout.println(urls[i]);
}
return sout.toString();
}
private void setConnection (String connStr) throws SQLException, MalformedURLException, IOException
{
if (connStr == null)
{
throw new IllegalArgumentException("connection string may not be null");
}
Connection oldConn = connection;
String [] params = connStr.split(" ");
if (params.length == 1)
{
connection = DriverManager.getConnection(params[0]);
connString = connStr;
if (oldConn != null)
{
oldConn.close();
}
}
else if (params.length == 2)
{
Properties props = new Properties();
if (params[1].endsWith(".xml"))
{
props.loadFromXML(new URL(params[1]).openStream());
}
else
{
props.load(new URL(params[1]).openStream());
}
connection = DriverManager.getConnection(params[0], props);
connString = connStr;
if (oldConn != null)
{
oldConn.close();
}
}
else if (params.length == 3)
{
connection = DriverManager.getConnection(params[0], params[1], params[2]);
connString = connStr;
if (oldConn != null)
{
oldConn.close();
}
}
}
private void setLogLevel (String logLevel)
{
if ("info".equalsIgnoreCase(logLevel))
{
info = true;
debug = trace = false;
}
else if ("debug".equalsIgnoreCase(logLevel))
{
info = debug = true;
trace = false;
}
else if ("trace".equalsIgnoreCase(logLevel))
{
info = debug = trace = true;
}
else if ("error".equalsIgnoreCase(logLevel))
{
info = debug = trace = false;
}
}
private void outputStatus ()
{
out.println("Log Levels: ERROR-true INFO-" + info + " DEBUG-" + debug + " TRACE-" + trace);
out.println("Reading from " + inSpec);
out.println("Writing to " + outSpec);
out.println("Connected to " + connString);
out.println(printClasspath());
}
private void log (String level, String message, Throwable t)
{
out.print(level);
out.print (" - ");
if (message != null)
{
out.print(message);
}
if (t != null)
{
if (message != null)
{
out.println();
}
t.printStackTrace(out);
}
out.println();
}
private void error (String message)
{
log("ERROR", message, null);
}
private void error (Throwable t)
{
log("ERROR", null, t);
}
private void error (String message, Throwable t)
{
log("ERROR", message, t);
}
private void info (String message)
{
if (info)
{
log("INFO", message, null);
}
}
private void info (Throwable t)
{
if (info)
{
log("INFO", null, t);
}
}
private void info (String message, Throwable t)
{
if (info)
{
log("INFO", message, t);
}
}
private void debug (String message)
{
if (debug)
{
log("DEBUG", message, null);
}
}
private void debug (Throwable t)
{
if (debug)
{
log("DEBUG", null, t);
}
}
private void debug (String message, Throwable t)
{
if (debug)
{
log("DEBUG", message, t);
}
}
private void trace (String message)
{
if (trace)
{
log("TRACE", message, null);
}
}
private void trace (Throwable t)
{
if (trace)
{
log("TRACE", null, t);
}
}
private void trace (String message, Throwable t)
{
if (trace)
{
log("TRACE", message, t);
}
}
private static Sql sql;
public static void main (String [] args) throws Exception
{
sql = new Sql();
BufferedReader in = null;
for (int i = 0; i < args.length; i += 2)
{
sql.trace("Processing " + args[0]);
if ("--logLevel".equalsIgnoreCase(args[i]))
{
sql.setLogLevel(args[i+1]);
}
else if ("--in".equalsIgnoreCase(args[i]))
{
sql.setIn(args[i+1]);
}
else if ("--out".equalsIgnoreCase(args[i]))
{
sql.setOut(args[i+1], false);
}
else if ("--append".equalsIgnoreCase(args[i]))
{
sql.setOut(args[i+1], true);
}
else if ("--driver".equalsIgnoreCase(args[i]))
{
sql.loadDriver(args[i+1]);
}
else if ("--connect".equalsIgnoreCase(args[i]))
{
sql.setConnection(args[i+1]);
}
else if ("--loadFrom".equalsIgnoreCase(args[i]))
{
sql.addToClasspath(args[i+1]);
}
else if ("--usage".equalsIgnoreCase(args[i]))
{
System.out.println("Usage: java Sql [args]");
System.out.println(" --logLevel <error|info|debug|trace>");
System.out.println(" --in <source URL>");
System.out.println(" --out <destination file or URL>");
System.out.println(" --append <destination file or URL>");
System.out.println(" --driver <JDBC Driver classname>");
System.out.println(" --connect <JDBC URL>");
System.out.println(" --loadFrom <URL for loading classes> (multiple allowed)");
System.out.println(" --usage (this list)");
System.out.println("HELP at SQL> prompt to see available interactive commands.");
sql = null;
}
}
if (sql != null)
{
sql.execute();
System.exit(0);
}
}
private class Results extends JFrame
{
private JTable table;
Results (String sql, ResultSet resultSet) throws SQLException
{
super(sql);
setLayout(new GridLayout(1,1));
checkWarnings(resultSet);
ResultSetMetaData metaData = resultSet.getMetaData();
int numCols = metaData.getColumnCount();
String [] columnNames = new String [numCols];
for (int i = 1; i <= numCols; ++i)
{
trace("Adding column " + i + " name " + metaData.getColumnName(i));
columnNames[i-1] = metaData.getColumnName(i);
}
List<String []> valuesList = new LinkedList<String []>();
while (resultSet.next())
{
String [] row = new String[numCols];
for (int i = 1; i <= numCols; ++i)
{
trace("Adding row " + valuesList.size() + " column " + i + " value: " + resultSet.getString(i));
row[i-1] = resultSet.getString(i);
}
valuesList.add(row);
}
resultSet.close();
if (valuesList.isEmpty())
{
info("No values returned from " + sql);
}
else
{
String [][] values = (String [][]) valuesList.toArray(new String[valuesList.size()][]);
DefaultTableModel model = new DefaultTableModel(values, columnNames);
table = new JTable(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setPreferredScrollableViewportSize(table.getPreferredSize());
JScrollPane scrollPane = new JScrollPane(table, ScrollPaneConstants.VERTICAL_SCROLLBAR_ALWAYS, ScrollPaneConstants.HORIZONTAL_SCROLLBAR_ALWAYS);
add(scrollPane);
setVisible(true);
setSize(400, 300);
}
}
}
}
class SqlClassLoader extends URLClassLoader
{
SqlClassLoader ()
{
super(new URL [0], Thread.currentThread().getContextClassLoader());
}
public void addURL (URL url)
{
super.addURL(url);
}
}
class SqlDriver implements Driver
{
private Driver driver;
SqlDriver (Driver d)
{
this.driver = d;
}
public boolean acceptsURL(String u) throws SQLException
{
return this.driver.acceptsURL(u);
}
public Connection connect(String u, Properties p) throws SQLException
{
return this.driver.connect(u, p);
}
public int getMajorVersion()
{
return this.driver.getMajorVersion();
}
public int getMinorVersion()
{
return this.driver.getMinorVersion();
}
public DriverPropertyInfo[] getPropertyInfo(String u, Properties p) throws SQLException
{
return this.driver.getPropertyInfo(u, p);
}
public boolean jdbcCompliant()
{
return this.driver.jdbcCompliant();
}
public Logger getParentLogger () throws SQLFeatureNotSupportedException
{
return this.driver.getParentLogger();
}
public Driver getDriver ()
{
return driver;
}
public String getName ()
{
return driver.getClass().getName();
}
public String toString ()
{
return getName();
}
}
No comments:
Post a Comment