import javax.management.Query;
import java.sql.*;
/*
Kevin Grossi
CS435
Lab 4
*/

public class Main
{

    public static void main(String[] args)
    {

        String varSQL, varSQL2;
        try
        {
            //1. Get Connection to database
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/lab4", "root", "kg34vin");

            if(myConn != null)
            {
                System.out.print("Connection Successful.");
            }
            System.out.println();

            //2.Create Statement

            /*
            Question 1
            varSQL = "Select Top.ScheduledStartTime, Top.ScheduledArrivalTime, Top.DriverName, Top.BusID " +
                    "From Trip T, TripOffering Top " +
                    "Where T.TripNumber = Top.TripNumber";

             Question 2A
             varSQL =  "delete" +
                       "From TripOffering " +
                       "Where TripNumber = 123456 AND Date LIKE '2016-12-01' AND ScheduledStartTime LIKE '8:00:00'";

             Question 2B
             varSQL = "insert into TripOffering (TripNumber, Date, ScheduledStartTime, ScheduledArrivalTime, DriverName, BusID)"
                        + "values (?,?,?,?,?,?)";
             //? can be the values of the inserted item

             Question 2C
             varSQL = "update TripOffering set DriverName = ? where DriverName = ?";
             //? can be the value updated

             Question 2D
             varSQL = "update TripOffering set BusID = ? where BusID = ?";
             //? can be the value updated

             Question 3
             varSQL = "Select * " +
                        "From TripStopInfo";

             Question 5
             varSQL = "insert into Driver (DriverName, DriverTelephoneNumber)"
                        + ""values(?,?)";

             Question 6
             varSQL = "insert into Bus (BusID, Model, Year)"
                        + "values(?,?,?)"

             Question 7
             varSQL = "delete" +
                        "From Bus " +
                        "Where BusID LIKE 'O213' AND Model LIKE 'Aston Martin' AND Year = 1985";

             */
            Statement myState = myConn.createStatement();
            varSQL2 = "Select * " +
                    "From Bus";

            System.out.println("Statement Created.");

            //3. Execute Query
            ResultSet rs = myState.executeQuery(varSQL2);
            ResultSetMetaData rsMeta  = rs.getMetaData();
            System.out.println("Query Executed");

            //4. Process the result set
            int varColCount = rsMeta.getColumnCount();

            for (int col = 1; col <= varColCount; col++)
                System.out.print(rsMeta.getColumnName(col) + '\t');

            System.out.println();
            while (rs.next())
            {
                for (int col = 1; col <= varColCount; col++)
                    System.out.print (rs.getString(col) + '\t');
                System.out.println();
            }

            rs.close();
            myState.close();
            myConn.close();

        }
        catch (Exception exc)
        {
            exc.printStackTrace();
        }
    }
}