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.

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(); } } }

Datasource

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); } } Carpgtxt.

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