MUST BE ANSWERED WITH JAVA ECLIPSE Programming Question (30 points, modified from E24.2 from the textbook) 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.

Submit one source file (CarDB.java). In other words, Database.properties, SimpleDataSource.java, or Carmpg.txt are not submitted. In modifying the source file,

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

}

}

}

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

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

}

}