/*
** JDBC code for step-by-step tutorial
**
** Created By Tony Faizal
**
** May 24, 1999
**
** This program is used as a sample program for MySQL In Action.
** In this program, the user will be able to connect to the MySQL database
** server, to do some SQL queries, and data manipulation using record set.
** This is a server-side application program. This program can be run on 
** cochise, since the MySQL driver and JDBC package installed on that 
** machine.
**
** The default url is cochise's url, but the user can overide it by giving
** another url on command-line execution.
*/

import java.sql.*;
import java.io.*;
import twz1.jdbc.mysql.*; // MySQL driver


/*
** The main class for this tutorial code.
*/
public class tutorial
{
    public static void main(String[] args)
    {
	Connection conn = null; // JDBC connection to the database server

	try
	{
	    // This is the driver to MySql database.
	    String driver   = "twz1.jdbc.mysql.jdbcMysqlDriver";

	    // The url where the database is located.
	    //
	    // The server that is used is cochise
	    // The port number that is used to connect to the database is 3306
	    // The database name is video_rental
            String url      = "jdbc:z1MySQL://cochise:3306/video_rental";

	    // Login name and password of the user.
	    String user     = "";
	    String password = "";

	    // Parse all the command-line arguments that are passed in by the user through
	    // execution.
	    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
		    // ERROR
		    // Throw an illegal argument exception.
                    throw new IllegalArgumentException("Unkown argument.");
	    } // End of for loop

            // The only required argument is the database URL.
            if (url == null)
                throw new IllegalArgumentException("No database specified");

	    // If the driver is specified, register it so that we can connect and execute 
	    // the program.
	    if (driver != null)
		Class.forName(driver);

	    // Now, open a connection to the specified database
	    conn = DriverManager.getConnection(url, user, password);

	    // Now, create the statement object to talk to the database
	    Statement s = conn.createStatement();

	    // Get a stream to read from a console
	    BufferedReader in = new BufferedReader(new InputStreamReader(System.in));

	    // Loop forever, reading the user's queries and executing them.
	    while (true)
	    {
		// Display a prompt on the screen as user start point
		System.out.print("tutorial> ");
		System.out.flush();
		String sql = in.readLine();

		// Quit when the user type "quit"
		if ((sql == null) || (sql.equals("quit")))
		    break;

		// Ignore a blank lines
		if (sql.length() == 0)
		    continue;
		
		// Attemp tp execute user's command
		try
		{
		    // Since we do not know whether the statement that a user writes is a
		    // query or an update, we can use EXECUTE command.
		    // This will give TRUE if the statement is a query and FALSE otherwise
		    boolean status = s.execute(sql);

		    // Loop until there are no more results to return.
		    do
		    {
			if (status) // The statement is a query
			{
			    // Get the set of results and display them
			    ResultSet rs = s.getResultSet();
			    printResultsTable(rs, System.out);
			}
			else
			{
			    // The statement is an update.
			    // Just display the number of rows that are affected
			    int numberOfRow = s.getUpdateCount();
			    System.out.println("Ok. " + numberOfRow + " rows affected.");
			}

			// Is there any other results?
			// Continue to the loop if there is any.
			status = s.getMoreResults();

		    } while(status || s.getUpdateCount() != -1);  // end of do-while

		} // End of try

		// If it is a SQLException, display the warning.
		catch (SQLException e)
		{
		    System.err.println("SQLException: " + e.getMessage() + ":" + 
				       e.getSQLState());
		} // End of the catch statement.

		// Display any possible warning, if any.
		finally
		{
		    for (SQLWarning warning = conn.getWarnings(); warning != null; 
			 warning = warning.getNextWarning())
			System.err.println("WARNING: " + warning.getMessage() + ":" + 
					   warning.getSQLState());
		} // End of finally
		
	    } // End of while loop

	} // End of try 


	// Handle the exception that occur during argument parsing, database connection,
	// etc. For SQLExceptions, print the message.
	catch (Exception e)
	{
	    // Print the error message
	    System.err.println(e);
	    
	    // If it is a SQL exception, display the state of SQL
	    if (e instanceof SQLException)
		System.err.println("SQL state: " + ((SQLException) e).getSQLState());

	    System.err.println("Usage: java tutorial [-d <driver>] [-u <user>] " +
			       "[-p <password>] <database url>");
	} // End of catch


	// Always close the database.... whether the program is terminated by exception 
	// or it is terminated normally.
	finally
	{
	    try { conn.close(); }
	    catch (Exception e) {}
	} // End of finally

    } // end of main function


    /*
    ** This function will output the result of the statement execution.    
    **
    */
    static void printResultsTable(ResultSet rs, OutputStream output) throws SQLException
    {
	// Set up the output channel
	PrintWriter out = new PrintWriter(new OutputStreamWriter( output ));

	// Get metadata from the database
	ResultSetMetaData metadata = rs.getMetaData();

	// Variables to hold the information.
	int numCols     = metadata.getColumnCount();
	String[] labels = new String[numCols];
	int[] colWidths = new int[numCols];
	int[] colPos    = new int[numCols];
	int lineWidth;

	// Figure out how wide the columns are and how wide the rows will be.
	lineWidth = 1;
    
	for (int i = 0; i < numCols; i++)
        {
	    colPos[i] = lineWidth;
	    labels[i] = metadata.getColumnLabel(i + 1);
	
	    // Get the column width. If the database does not specify,
	    // use the default => 30 characters.
	    int size = metadata.getColumnDisplaySize(i + 1);
	    if (size == -1) 
		size = 30;

	    int labelSize = labels[i].length();
	    if (labelSize > size)
		size = labelSize;

	    colWidths[i] = size + 1;
	    lineWidth   += colWidths[i] + 2;

	} // End for-loop

	// Create a horizontal line
	StringBuffer divider   = new StringBuffer( lineWidth );
	StringBuffer blankLine = new StringBuffer( lineWidth );

	for (int i = 0; i < lineWidth; i++)
	{
	    divider.insert(i, '-');
	    blankLine.insert(i, " ");
	} // End of for-loop

	// Put special marks in the divider line at the column position
	for (int i = 0; i < numCols; i++)
	    divider.setCharAt(colPos[i] - 1, '+');

	divider.setCharAt(lineWidth - 1, '+');

	// Begin the table output with the divider line
	out.println(divider);

	// The following the row column.
	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], " |");
	}  // End for-loop

	// Then, output the labels and another divider
	out.println(line);
	out.println(divider);

	// Now, output the database data.
	while (rs.next())
	{
	    line = new StringBuffer(blankLine.toString());
	    line.setCharAt(0, '|');
	
	    for (int i = 0; i < numCols; i++)
	    {
		Object value = rs.getObject(i + 1);
		overwrite (line, colPos[i] + 1, value.toString().trim());
		overwrite (line, colPos[i] + colWidths[i], " |");
	    } // End of for-loop

	    out.println(line);

	} // end of while

	// Finally, end the table with the last divider.
	out.println(divider);
	out.flush();

    } // End of printResultTable function


    /*
    ** This is used in printing the table results
    **
    */
    static void overwrite(StringBuffer buf, int pos, String str)
    {
	int length = str.length();

	for (int i = 0; i < length; i++)
	    buf.setCharAt(pos + i, str.charAt(i));

    }  // End of overwrite function   
    
} // End of tutorial class