import java.sql.*;

public class DataBase
{
    public static void main(String [] args) throws Exception
    {
        //final String DB_URL="jdbc:derby:CarDB;create=true";
        final String DB_URL="jdbc:derby:CarDB;";

        try
        {

            //create connection to database
            Connection conn = DriverManager.getConnection(DB_URL);
            System.out.println("Connection established.");

            System.out.println("Now creating Car table...");

            Statement stmt = conn.createStatement();

            //delete existing Car table
            System.out.println("Dropping existing Car Table");
            stmt.execute("DROP TABLE Car");

            System.out.println("Car table dropped, now creating Car table");

            stmt.execute("CREATE TABLE Car(Make CHAR(25), Model CHAR(25), BuildYear CHAR(25))");
            System.out.println("Table Car has been created");

            String sql = "INSERT INTO Car VALUES ('Jeep', 'Wrangler', '1990')";
            stmt.executeUpdate(sql);

            String newsql = "INSERT INTO Car VALUES ('Ford', 'Focus', '2014')";
            stmt.executeUpdate(newsql);

            sql = "INSERT INTO Car VALUES ('Dodge', 'Viper', '2018')";
            stmt.executeUpdate(sql);

            System.out.println("Added 3 cars to the Table Car");

            String sqlStatement = "SELECT Make, Model, BuildYear FROM Car";
            ResultSet result = stmt.executeQuery(sqlStatement);
            System.out.println("Queried all cars from Car table");

            Car [] carArray = new Car[10];
            int carCounter = 0;
            while(result.next())
            {
                Car car = new Car();
                car.Make = result.getString("Make");
                //System.out.print(result.getString("Make"));

                car.Model = result.getString("Model");
                //System.out.print(result.getString("Model"));

                car.BuildYear = result.getString("BuildYear");
                //System.out.print(result.getString("BuildYear"));

                carArray[carCounter] = car;
                carCounter++;
                System.out.println();
            }

            for(int i = 0; i < carCounter; i++)
            {
                System.out.println("Car number " + i + " :");
                System.out.println(carArray[i].Make + "  " + carArray[i].Model + "  " +
                        carArray[i].BuildYear + " \n");

            }
            System.out.println("We have " + carCounter + " cars.");

            //close statements
            stmt.close();
            conn.close();
            System.out.println("Connection closed.");

        }
        catch (Exception ex)
        {
            System.out.println("ERROR:  " + ex.getMessage());

        }



    }
}