/*
 * TestDB: Test the Mysql Java interface.
 *         Input and output to/from terminal interface.
 *
 * @author Marti Hearst 10/01/01
 * @version 1.0
*/

import java.io.*;
import java.sql.*;

class TestDB {

	private Connection connection = null;

	public void closeConnection() {
		try {
			connection.close();
		} catch (Exception f) {
			System.out.println(
				"Problem closing database connection " + f.getMessage());
		}
	}

	public void makeConnection(
		String serverName,
		String dbName,
		String uName,
		String pName) {

		try {
			// Load the JDBC driver
			String driverName = "org.gjt.mm.mysql.Driver";
			// MySQL MM JDBC driver
			Class.forName(driverName);

			// Create a connection to the database
			String url = "jdbc:mysql://" + serverName + "/" + dbName;
			// a JDBC url
			connection = DriverManager.getConnection(url, uName, pName);
		} catch (ClassNotFoundException e) {
			System.out.println(
				"Could not find the database driver\n" + e.toString());
			// Could not find the database driver
		} catch (SQLException e) {
			System.out.println(
				"Could not connect to the database\n" + e.toString());
			// Could not connect to the database
		} catch (Exception e) {
			System.out.println("Some other driver problem\n" + e.toString());
		}
	}

	/*
	    Runs a query; prints the results as tuples (records), one per line.
	First create a statement object and instantiate it with the query.
	Then get result sets and metadata result sets.
	Use metadata result sets to determine how many columns there are in the
	    resulting table, and what the names of those columns are.  Print those out.
	Then cycle through the table of results, printing out one record per line.
	 
	 */

	public void runQuery(String query) {

		try {

			Statement stmt = connection.createStatement();

			ResultSet rs = stmt.executeQuery(query);
			ResultSetMetaData rsm = rs.getMetaData();
			int numcolumns = rsm.getColumnCount();

			System.out.print("\t");
			for (int j = 0; j < numcolumns; j++) {

				System.out.print(rsm.getColumnName(j + 1) + "\t");
			}
			System.out.println();

			int i = 1;
			while (rs.next()) {

				System.out.print(i + "\t");
				for (int j = 1; j < numcolumns + 1; j++) {
					System.out.print(rs.getString(j) + "\t");
				}
				System.out.println();
				i++;
			}

			rs.close();
			stmt.close();

		} catch (Exception f) {
			System.out.println(
				"Problem running query " + query + "\n" + f.getMessage());
		}
	}

	/* Accepts a query on the input and executes it. */

	public void acceptInput() {

		try {
			InputStreamReader isr = new InputStreamReader(System.in);
			;
			BufferedReader keyboard = new BufferedReader(isr);
			String query = "";
			System.out.println("Type C^D or quit to exit");
			System.out.print("Query > ");
			while ((query = keyboard.readLine()) != null) {

				if (query.equals("quit"))
					break;
				this.runQuery(query);
				System.out.print("Query > ");

			}
		} catch (Exception e) {
			System.out.println("Problem in acceptInput " + e.getMessage());
		}
	}

	public static void main(String args[]) {

		TestDB db = new TestDB();

		// each person's login name goes here.

		String dblogin = "yourlogin";

		db.makeConnection("dream.sims.berkeley.edu", dblogin, dblogin, dblogin);
		db.acceptInput();
		db.closeConnection();
	}

}