import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.Properties;
import java.util.Vector;
public class QueryApp {
static Connection connection;
static public void main(String args[]) {
java.io.InputStreamReader reader;
java.io.BufferedReader input;
Properties props = new Properties();
boolean connected = false;
String buffer = "", url, driver;
int line = 1; // Mark current input line
// Need user id and password to connect
if( args.length < 2 ) {
System.out.println("Syntax: <java QueryApp [user] [password]>");
return;
}
props.put("user", args[0]);
props.put("password", args[1]);
if( args.length > 2 ) {
url = args[2];
}
else {
url = "jdbc:msql://athens.imaginary.com:4333/db_bank";
}
if( args.length > 3 ) {
driver = args[3];
}
else {
driver = "COM.imaginary.sql.msql.MsqlDriver";
}
// Connect to the database
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, props);
}
catch( SQLException e ) {
System.out.println("Failed to connect to database: " +
e.getMessage());
return;
}
catch( ClassNotFoundException e ) {
System.out.println("Unable to find driver class.");
return;
}
System.out.println("Connected to the database.");
connected = true;
reader = new java.io.InputStreamReader(System.in);
input = new java.io.BufferedReader(reader);
// Enter into a user input loop
while( connected ) {
String tmp, cmd;
// Print a prompt
if( line == 1 ) {
System.out.print(" > ");
}
else {
System.out.print(line + "> ");
}
System.out.flush();
// Get the next line of input
try {
tmp = input.readLine();
}
catch( java.io.IOException e ) {
return;
}
// Get rid of extra space in the command
cmd = tmp.trim();
// The user wants to commit pending transactions
if( cmd.equals("commit") ) {
try {
connection.commit();
System.out.println("Commit successful.");
}
catch( SQLException e ) {
System.out.println("Error in commit: " + e.getMessage());
}
buffer = "";
line = 1;
}
// The user wants to execute the current buffer
else if( cmd.equals("go") ) {
if( !buffer.equals("") ) {
try {
executeStatement(buffer, connection);
}
catch( SQLException e ) {
System.out.println(e.getMessage());
}
}
buffer = "";
line = 1;
continue;
}
// The user wants to quit
else if( cmd.equals("quit") ) {
connected = false;
continue;
}
// The user wants to clear the current buffer
else if( cmd.equals("reset") ) {
buffer = "";
line = 1;
continue;
}
// The user wants to abort a pending transaction
else if( cmd.equals("rollback") ) {
try {
connection.rollback();
System.out.println("Rollback successful.");
}
catch( SQLException e ) {
System.out.println("An error occurred during rollback: " +
e.getMessage());
}
buffer = "";
line = 1;
}
// The user wants version info
else if( cmd.startsWith("show") ) {
if( cmd.length() < 6 ) { // No argument after show
System.out.println("show version");
}
else {
DatabaseMetaData meta;
try {
meta = connection.getMetaData();
cmd = cmd.substring(5, cmd.length()).trim();
if( cmd.equals("version") ) {
showVersion(meta);
}
else {
System.out.println("show version"); // Bad arg
}
}
catch( SQLException e ) {
System.out.println("Failed to load meta data: " +
e.getMessage());
}
}
buffer = "";
line = 1;
}
// The input that is not a keyword should appended be to the buffer
else {
buffer = buffer + " " + tmp;
line++;
continue;
}
}
try {
connection.close();
}
catch( SQLException e ) {
System.out.println("Error closing connection: " + e.getMessage());
}
System.out.println("Connection closed.");
}
static public Vector executeSQL(String sql) {
Vector v = new Vector();
try {
Statement statement = connection.createStatement();
if( statement.execute(sql) ) {
ResultSet results = statement.getResultSet();
ResultSetMetaData meta = results.getMetaData();
int cols;
cols = meta.getColumnCount();
while( results.next() ) {
Hashtable h = new Hashtable(cols);
int i;
for(i=0; i<cols; i++) {
Object ob = results.getObject(i);
h.put(meta.getColumnLabel(i), ob);
}
v.addElement(h);
}
return v;
}
return null;
}
catch( SQLException e ) {
e.printStackTrace();
return null;
}
}
static public void executeStatement(String sql, Connection connection)
throws SQLException {
Statement statement = null;
try {
statement = connection.createStatement();
if( statement.execute(sql) ) { // true means the SQL was a SELECT
processResults(statement.getResultSet());
}
else { // no result sets, see how many rows were affected
int num;
switch(num = statement.getUpdateCount()) {
case 0:
System.out.println("No rows affected.");
break;
case 1:
System.out.println(num + " row affected.");
break;
default:
System.out.println(num + " rows affected.");
}
}
}
catch( SQLException e ) {
throw e;
}
finally { // close out the statement
if( statement != null ) {
try { statement.close(); }
catch( SQLException e ) { }
}
}
}
static public void processResults(ResultSet results) throws SQLException {
try {
ResultSetMetaData meta = results.getMetaData();
StringBuffer bar = new StringBuffer();
String buffer = "";
int cols = meta.getColumnCount();
int row_count = 0;
int i, width = 0;
// Prepare headers for each of the columns
// The display should look like:
// --------------------------------------
// | Column One | Column Two |
// --------------------------------------
// | Row 1 Value | Row 1 Value |
// --------------------------------------
// create the bar that is as long as the total of all columns
for(i=1; i<=cols; i++) {
width += meta.getColumnDisplaySize(i);
}
width += 1 + cols;
for(i=0; i<width; i++) {
bar.append('-');
}
bar.append('\n');
buffer += bar + "|";
// After the first bar goes the column labels
for(i=1; i<=cols; i++) {
StringBuffer filler = new StringBuffer();
String label = meta.getColumnLabel(i);
int size = meta.getColumnDisplaySize(i);
int x;
// If the label is long than the column is wide,
// then we truncate the column label
if( label.length() > size ) {
label = label.substring(0, size);
}
// If the label is shorter than the column, pad it with spaces
if( label.length() < size ) {
int j;
x = (size-label.length())/2;
for(j=0; j<x; j++) {
filler.append(' ');
}
label = filler + label + filler;
if( label.length() > size ) {
label = label.substring(0, size);
}
else {
while( label.length() < size ) {
label += " ";
}
}
}
// Add the column header to the buffer
buffer = buffer + label + "|";
}
// Add the lower bar
buffer = buffer + "\n" + bar;
// Format each row in the result set and add it on
while( results.next() ) {
row_count++;
buffer += "|";
// Format each column of the row
for(i=1; i<=cols; i++) {
StringBuffer filler = new StringBuffer();
Object value = results.getObject(i);
int size = meta.getColumnDisplaySize(i);
String str = value.toString();
if( str.length() > size ) {
str = str.substring(0, size);
}
if( str.length() < size ) {
int j, x;
x = (size-str.length())/2;
for(j=0; j<x; j++) {
filler.append(' ');
}
str = filler + str + filler;
if( str.length() > size ) {
str = str.substring(0, size);
}
else {
while( str.length() < size ) {
str += " ";
}
}
}
buffer = buffer + str + "|";
}
buffer = buffer + "\n";
}
// Stick a row count up at the top
if( row_count == 0 ) {
buffer = "No rows selected.\n" + buffer;
}
else if( row_count == 1 ) {
buffer = "1 row selected.\n" + buffer;
}
else {
buffer = row_count + " rows selected.\n" + buffer;
}
System.out.print(buffer);
System.out.flush();
}
catch( SQLException e ) {
throw e;
}
finally {
try { results.close(); }
catch( SQLException e ) { }
}
}
static public void showVersion(DatabaseMetaData meta) {
String version = "QueryApp 1.0\n";
try {
version += "DBMS: " + meta.getDatabaseProductName() + " " +
meta.getDatabaseProductVersion() + "\n";
version += "JDBC Driver: " + meta.getDriverName() + " " +
meta.getDriverVersion();
System.out.println(version);
}
catch( SQLException e ) {
System.out.println("Failed to get version info: " +e.getMessage());
}
}
}
|
