import java.sql.*;
import java.io.*;
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 commandline 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 dirvers 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("qukt")) 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 result, so loop until there are no more results.
do
{
if (status)
{
//it was a query and returns a ResultSet.
ResultSet rs = s.getResultSet(); //get ResultSet
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 ResultSetl. Instean, we just
** print the number of rows that were affected.
*/
int numUpdates = s.getUpdateCount();
System.out.println("OK. " + numUpdates + " row 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 SQL Exception is thrown ,display an error message
// Note that SQLEXcptions can have a general message and a
// DB-specified 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
// warning. Note that there can be a while 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());
}
}
}
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>");
}
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 nams, 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 lables
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 colums 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 it's positioni
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 dirvers 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 speial 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 talbe contains the colunm labels
//Begin with a blank line, and put the column names and column
// divider characters "|" into it. overwirhte() 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 talbe 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();
}
static void overwrite(StringBuffer b, int pos, String s)
{
int slen = s.length();
int blen = b.length();
if (pos + slen > blen)
{
slen = blen - pos;
}
for (int i = 0;i< slen ;i++ )
{
b.setCharAt(pos + i, s.charAt(i));
}
}
}