// Query.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

import java.sql.*;

public class Query {

    // Create connection and statement objects

    private Connection  myCon;  
    private Statement   myStmt; 

    // The main method - creates an instance of Query

    public static void main(String[] args) {

        // Create the Query object

        Query que = new Query();
    }

        // The Query constructor

    public Query() {
        try {
            connecter();
            select();
            closer();
        }
        catch (SQLException sqlEx) {
            System.err.println(sqlEx);
        }
    }

    // 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

            myCon = DriverManager.getConnection (
                           "jdbc:mysql://localhost/cars?user=root);

            // 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_id\tBody_style \tMiles \tYear \tState");
        System.out.println();

        // Display the rows of the result

        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);
        }
        
    }
    
    // closer method - close the database connection

    void closer() throws SQLException {
        myCon.close();
    }
}