Write a Java program that does the following jobs. Step1. Creates a Car table with car manufacturers, models, fuel efficiency (MPG), and price. Step2. Reads data from a text file and insert the cars to the table created in Step 1. Step3. Interact with the user, where the user can select one of the following actions. - (Q) Quit: quit the program - (A) Add a car: insert a car to the table - (C) Calculate average: calculate the average MPG - (W) Write the entire table to a text file - (P) Print the entire table - (M) Print a subset of the cars based on MPG (ex. Print cars with mpg <= 27) The user can choose one of these actions with typing Q,A,C, W, P, or M in the console. When adding a car, the user must provide manufacturer, model, mpg, and price through the console. When writing the table to a text file, the user must provide output text file name. When selecting a subset of the cars based on price, an upper bound price value must be provided. To help you understand how the program works, a sample run is available in Appendix A in Pages 3 - 4 of this document. Four files are provided with the assignment. 1. CarDB.java: the main class of this assignment. 2. Database.properties: file for Apache Derby 3. SimpleDataSource.java: file for Apache Derby 4. Carmpg.txt: sample text file for Step 2.

Appendix A: sample run Select from the following options

(Q) Quit (A) Add a car (C) Calculate average (W) Write the entire table to a text file (P) Print the entire table (M) Print a subset of the cars based on MPG C Average fuel efficiency (MPG): 28.9444

Select from the following options (Q) Quit (A) Add a car (C) Calculate average (W) Write the entire table to a text file (P) Print the entire table (M) Print a subset of the cars based on MPG A Manufacturer name: Toyota Model name: Corolla MPG: 32 Price: 18600

Select from the following options (Q) Quit (A) Add a car (C) Calculate average (W) Write the entire table to a text file (P) Print the entire table (M) Print a subset of the cars based on MPG C Average fuel efficiency (MPG): 29.1052

Select from the following options (Q) Quit (A) Add a car (C) Calculate average (W) Write the entire table to a text file (P) Print the entire table (M) Print a subset of the cars based on MPG W Output file name: cartable.txt

Select from the following options (Q) Quit (A) Add a car (C) Calculate average (W) Write the entire table to a text file (P) Print the entire table (M) Print a subset of the cars based on MPG P Manufacturer Model Efficiency Price Toyota Prius 52 23475 Kia Optima 31 22600 Hyundai Sonata 31 22050 Nissan Altima 31 23260 Chevrolet Malibu 30 21680 Honda Accord 30 23570 Mazda 6 29 21945 Subaru Legacy 29 22195 Toyota Camry 27 23495 Chrysler 200 27 22115 Ford Fusion 27 22120 Volkswagen Passat 27 22995 Volkswagen CC 25 34475 Chevrolet Impala 25 27895 Buick LaCrosse 25 29565 Nissan Maxima 25 33270 Buick Regal 24 27065 Lincoln MKZ 26 35605 Toyota Corolla 32 18600

Select from the following options (Q) Quit (A) Add a car (C) Calculate average (W) Write the entire table to a text file (P) Print the entire table (M) Print a subset of the cars based on MPG M Upper bound on efficiency (MPG):27 Manufacturer Model Efficiency Price Toyota Camry 27 23495 Chrysler 200 27 22115 Ford Fusion 27 22120 Volkswagen Passat 27 22995 Volkswagen CC 25 34475 Chevrolet Impala 25 27895 Buick LaCrosse 25 29565 Nissan Maxima 25 33270 Buick Regal 24 27065 Lincoln MKZ 26 35605 Select from the following options (Q) Quit (A) Add a car (C) Calculate average (W) Write the entire table to a text file (P) Print the entire table (M) Print a subset of the cars based on MPG Q

CarDB.java:

import java.sql.Connection;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Scanner;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.io.*;

/**

* Code for HW5: Creates the Car table, run SQL statements, outputs to a

text file

* @author

*/

public class CarDB

{

public static void main(String[] args) throws IOException,

SQLException, ClassNotFoundException

{

//Use database.properties

if (args.length == 0)

{

System.out.println("Usage: java CarDB propertiesFile");

System.exit(0);

}

SimpleDataSource.init(args[0]);

try(Connection conn = SimpleDataSource.getConnection();Statement

stat = conn.createStatement())

{

try

{

stat.execute("DROP TABLE Car");

}

catch(SQLException e)

{

// get exception if table doesn't exist yet

}

stat.execute("CREATE TABLE Car (Manufacturer VARCHAR(20),"

+ "Model VARCHAR(20), " + "Efficiency DECIMAL(6,2), " +

"Price DECIMAL(8,2)) ");

String inputFileName = "carmpg.txt";

File inputFile = new File(inputFileName);

Scanner in = new Scanner(inputFile);

// COMPLETE THIS WHILE LOOP to insert all cars from the

input text file

while (in.hasNextLine())

{

...

}

// Main loop of the program. Complete this while loop.

Scanner in2 = new Scanner(System.in);

boolean continueProgram = true;

while(continueProgram){

System.out.println("Select from the following

options");

System.out.println("(Q) Quit");

System.out.println("(A) Add a car");

System.out.println("(C) Calculate average");

System.out.println("(W) Write the entire table to a

text file");

System.out.println("(P) Print the entire table");

System.out.println("(M) Print a subset of the cars

based on MPG");

String select = in2.next();

...

}

in2.close();

in.close();

stat.close();

conn.close();

}

}

}

...//write code

}

// Main loop of the program. Complete this while loop.

Scanner in2 = new Scanner(System.in);

boolean continueProgram = true;

while(continueProgram){

System.out.println("Select from the following

options");

System.out.println("(Q) Quit");

System.out.println("(A) Add a car");

System.out.println("(C) Calculate average");

System.out.println("(W) Write the entire table to a

text file");

System.out.println("(P) Print the entire table");

System.out.println("(M) Print a subset of the cars

based on MPG");

String select = in2.next();

...//write code

}

in2.close();

in.close();

stat.close();

conn.close()

SimpleDataSource.java

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.io.FileInputStream;

import java.io.IOException;

import java.util.Properties;

/**

A simple data source for getting database connections.

*/

public class SimpleDataSource

{

private static String url;

private static String username;

private static String password;

/**

Initializes the data source.

@param fileName the name of the property file that

contains the database driver, URL, username, and password

*/

public static void init(String fileName)

throws IOException, ClassNotFoundException

{

Properties props = new Properties();

FileInputStream in = new FileInputStream(fileName);

props.load(in);

String driver = props.getProperty("jdbc.driver");

url = props.getProperty("jdbc.url");

username = props.getProperty("jdbc.username");

if (username == null) { username = ""; }

password = props.getProperty("jdbc.password");

if (password == null) { password = ""; }

if (driver != null) { Class.forName(driver); }

}

/**

Gets a connection to the database.

@return the database connection

*/

public static Connection getConnection() throws SQLException

{

return DriverManager.getConnection(url, username, password);

}

}

carmpg.txt:

Toyota Prius 52 23475

Kia Optima 31 22600

Hyundai Sonata 31 22050

Nissan Altima 31 23260

Chevrolet Malibu 30 21680

Honda Accord 30 23570

Mazda 6 29 21945

Subaru Legacy 29 22195

Toyota Camry 27 23495

Chrysler 200 27 22115

Ford Fusion 27 22120

Volkswagen Passat 27 22995

Volkswagen CC 25 34475

Chevrolet Impala 25 27895

Buick LaCrosse 25 29565

Nissan Maxima 25 33270

Buick Regal 24 27065

Lincoln MKZ 26 35605

database.properties:

jdbc.url=jdbc:derby:BigJavaDB;create=true # With other databases, you may need to add entries such as these # jdbc.username=admin # jdbc.password=secret # jdbc.driver=org.apache.derby.jdbc.EmbeddedDriver