Driver Property Info

package reese.george;
 
/*
 
 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());
	}
    }
}