// QueryMetaData.java

// Transcribed from "Programming the World Wide Web", 3rd Edition, 
//  by Robert Sebesta

// Transcribed by P. Conrad for CISC474, University of Delaware, Spring 2006

// Modified for the local MySQL environment on porsche.cis.udel.edu

//    (1) The database name is "sebesta" instead of "cars"
//    (2) Added variables
//              String username="pconrad";
//             String password="abcd1234";
//    (3) Added those params to the 
//        DriverManager.getConnection() function call.
//    (4) Added the code in Section 14.7.4 about Metadata


// QueryMetaData.java

//  This Java/JDBC example connects to the cars database, uses
//  a query to select all rows of the Corvettes table in which 
//  the Year column is less than 1992, and displays the results.

// It then calls several methods related to Metadata

import java.sql.*;

public class QueryMetaData {

    // Create connection and statement objects
    
    private Connection myCon;
    private Statement myStmt;

    // The main method - creates an instance of QueryMetaData

    public static void main(String[] args) {

	// Create the Query object

	QueryMetaData que = new QueryMetaData ();

    }


    // The Query constructor

    public QueryMetaData() {

	try {
	    connecter(); 
	    select();
	    metadata();
	    closer();
	}

	catch (SQLException sqlEx) {
	    System.err.println(sqlEx);
	}

    } // end Query()

    // connecter method
    //   Register the database driver and create the database
    //   connection and query statement objects

    void connecter() throws SQLException {
	try {

	    // Register the MySQL database driver

	    Class.forName("org.gjt.mm.mysql.Driver").newInstance();
	    
	    // Create the connection object

	    final String URL = "jdbc:mysql://jaguar.cis.udel.edu:8099/sebesta"; 
	    final String username = "pconrad"; // change to YOUR username
	    final String password = "abcd1234"; // change to YOUR MySQL password (NOT your Unix password)
	    myCon = DriverManager.getConnection(URL, username, password);
	    
	    // Create the statement object for the query

	    myStmt = myCon.createStatement();

	} //* end of try clause

	catch (SQLException sqlEx) {
	    throw sqlEx;
	}
	
	catch (Exception e) {
	    System.err.println(e);
	}

    } // * end of the connecter method

    // select method - create the SQL command string,
    // execute it, and display the results

    void select() throws SQLException {

	// Create the SELECT command as a String
	
	final String select_sql =
	    "SELECT * FROM Corvettes WHERE Year > 1992 ";
	
	// Perform the query

	ResultSet result = myStmt.executeQuery(select_sql);

	// Display output headings

	System.out.println
	    ("\n\n\t\t1993-2001 Corvettes For Sale\n");
	System.out.println
	    ("Vette_d\tBody_style \tMiles \tYear \tState");
	System.out.println();

	// Display the rows of the result
	
	// Note from P. Conrad: you have to do one result.next() call
	// to even "get to" the first result in the result set; you start
	// at a position which is "before" the first item in the set.

	while (result.next()) {
	    int id = result.getInt("Vette_id");
	    String body = result.getString("Body_style");
	    float miles = result.getFloat("Miles");
	    int year = result.getInt("Year");
	    int state = result.getInt("State");
	    System.out.println(id + "\t\t" + body + "\t" + miles +
			       "\t" + year + "\t" + state);
	} // while loop for result set
	
    } // select() method


    void metadata() throws SQLException {

	String tbl[] = {"TABLE"};

	DatabaseMetaData dbmd = myCon.getMetaData();

	ResultSet result = dbmd.getTables(null, null, null, tbl);

	System.out.println("The tables in the database are: \n\n");

	while (result.next()) {
	    System.out.println(result.getString(3));
	}
	
	System.out.println("\n");

    } // select() method


    // closer method - close the database connection
    
    void closer () throws SQLException {
	myCon.close();
    } 
    
} // class Query