|
/*
Database Programming with JDBC and Java, Second Edition
By George Reese
ISBN: 1-56592-616-1
Publisher: O'Reilly
*/
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.DriverPropertyInfo;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* Examples 4.4 through 4.6.
*/
public class TerminalMonitor {
static Connection connection = null;
static BufferedReader input;
static public void main(String args[]) {
DriverPropertyInfo[] required;
StringBuffer buffer = new StringBuffer();
Properties props = new Properties();
boolean connected = false;
Driver driver;
String url;
int line = 1; // Mark current input line
if (args.length < 1) {
System.out.println("Syntax: <java -Djdbc.drivers=DRIVER_NAME "
+ "TerminalMonitor JDBC_URL>");
return;
}
url = args[0];
// We have to get a reference to the driver so we can
// find out what values to prompt the user for in order
// to make a connection.
try {
driver = DriverManager.getDriver(url);
} catch (SQLException e) {
e.printStackTrace();
System.err.println("Unable to find a driver for the specified "
+ "URL.");
System.err.println("Make sure you passed the jdbc.drivers "
+ "property on the command line to specify "
+ "the driver to be used.");
return;
}
try {
required = driver.getPropertyInfo(url, props);
} catch (SQLException e) {
e.printStackTrace();
System.err.println("Unable to get driver property information.");
return;
}
input = new BufferedReader(new InputStreamReader(System.in));
// some drivers do not implement this properly
// if that is the case, prompt for user name and password
try {
if (required.length < 1) {
props.put("user", prompt("user: "));
props.put("password", prompt("password: "));
} else {
// for each required attribute in the driver property info
// prompt the user for the value
for (int i = 0; i < required.length; i++) {
if (!required[i].required) {
continue;
}
props
.put(required[i].name, prompt(required[i].name
+ ": "));
}
}
} catch (IOException e) {
e.printStackTrace();
System.err.println("Unable to read property info.");
return;
}
// Make the connection.
try {
connection = DriverManager.getConnection(url, props);
} catch (SQLException e) {
e.printStackTrace();
System.err.println("Unable to connect to the database.");
}
connected = true;
System.out.println("Connected to " + url);
// Enter into a user input loop
while (connected) {
String tmp, cmd;
// Print a prompt
if (line == 1) {
System.out.print("TM > ");
} else {
System.out.print(line + " -> ");
}
System.out.flush();
// Get the next line of input
try {
tmp = input.readLine();
} catch (java.io.IOException e) {
e.printStackTrace();
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 = new StringBuffer();
line = 1;
}
// The user wants to execute the current buffer
else if (cmd.equals("go")) {
if (!buffer.equals("")) {
try {
executeStatement(buffer);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
buffer = new StringBuffer();
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 = new StringBuffer();
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 = new StringBuffer();
line = 1;
}
// The user wants version info
else if (cmd.startsWith("show")) {
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 = new StringBuffer();
line = 1;
}
// The input that is not a keyword should appended be to the buffer
else {
buffer.append(" " + tmp);
line++;
continue;
}
}
try {
connection.close();
} catch (SQLException e) {
System.out.println("Error closing connection: " + e.getMessage());
}
System.out.println("Connection closed.");
}
static public void executeStatement(StringBuffer buff) throws SQLException {
String sql = buff.toString();
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 String prompt(String prop) throws IOException {
String tmp = "";
while (tmp.length() < 1) {
System.out.print(prop);
tmp = input.readLine().trim();
}
return tmp;
}
static public void processResults(ResultSet results) throws SQLException {
try {
ResultSetMetaData meta = results.getMetaData();
StringBuffer bar = new StringBuffer();
StringBuffer buffer = new StringBuffer();
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.append(bar.toString() + "|");
// 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 longer 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.append(label + "|");
}
// Add the lower bar
buffer.append("\n" + bar.toString());
// Format each row in the result set and add it on
while (results.next()) {
row_count++;
buffer.append('|');
// 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;
if (results.wasNull()) {
str = "NULL";
} else {
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.append(str + "|");
}
buffer.append("\n");
}
// Stick a row count up at the top
if (row_count == 0) {
buffer = new StringBuffer("No rows selected.\n");
} else if (row_count == 1) {
buffer = new StringBuffer("1 row selected.\n"
+ buffer.toString() + bar.toString());
} else {
buffer = new StringBuffer(row_count + " rows selected.\n"
+ buffer.toString() + bar.toString());
}
System.out.print(buffer.toString());
System.out.flush();
} catch (SQLException e) {
throw e;
} finally {
try {
results.close();
} catch (SQLException e) {
}
}
}
static public void showVersion(DatabaseMetaData meta) {
try {
System.out.println("TerminalMonitor v2.0");
System.out.println("DBMS: " + meta.getDatabaseProductName() + " "
+ meta.getDatabaseProductVersion());
System.out.println("JDBC Driver: " + meta.getDriverName() + " "
+ meta.getDriverVersion());
} catch (SQLException e) {
System.out.println("Failed to get version info: " + e.getMessage());
}
}
}
|
