Create a JAVA program that performs file IO and database interaction via SQL. Th
ID: 3929096 • Letter: C
Question
Create a JAVA program that performs file IO and database interaction via SQL. The program needs to read data from the provided file: "Project.csv" and insert the data into a database. Then the program needs to create a report from the database sorted by price descending. The report should be in the format demonstrated below.
id (primary key - generated by the database)
cpuname
performance
price
Project.csv contents:
CPU Name Performance Price (USD) Intel Core i7-3770K @ 3.50GHz 9,556 $560.50 Intel Core i7-3770 @ 3.40GHz 9,327 $335.55 Intel Core i7-3820 @ 3.60GHz 8,990 $404.38 AMD FX-8350 Eight-Core 8,940 $149.99 Intel Core i7-2600K @ 3.40GHz 8,501 $379.97 Intel Core i7-2600 @ 3.40GHz 8,242 $214.99 Intel Core i7-4720HQ @ 2.60GHz 8,046 NA AMD FX-8320 Eight-Core 8,008 $145.99 Intel Core i7-6700HQ @ 2.60GHz 7,997 $1 509 Intel Core i7-4710HQ @ 2.50GHz 7,826 NA Intel Core i5-6600K @ 3.50GHz 7,762 $239.99 Intel Core i7-4700HQ @ 2.40GHz 7,754 $383.00 Intel Core i7-4700MQ @ 2.40GHz 7,736 $467.40 Intel Core i5-4690K @ 3.50GHz 7,690 $239.99 AMD FX-8150 Eight-Core 7,619 $165.99 Intel Core i7-3630QM @ 2.40GHz 7,604 $304.49 Intel Core i5-4670K @ 3.40GHz 7,598 $249.99 Intel Core i5-4690 @ 3.50GHz 7,542 $224.99 Intel Core i7-3610QM @ 2.30GHz 7,460 $399.99 Intel Core i5-4670 @ 3.40GHz 7,342 $226.99 Intel Core i5-4590 @ 3.30GHz 7,174 $199.99 Intel Core i7-4702MQ @ 2.20GHz 7,146 NA Intel Core i5-3570K @ 3.40GHz 7,130 $477.23Explanation / Answer
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.Arrays;
public class Main
{
/**
* This the main function that runs at the start
* param args - input arguments from the command line
*/
static public void main(String[] args)
{
CPUList cpuList = new CPUList(); //The CPUList used to retrieve data from the fiile and store in the db
CPUList cpuListRetrieved = new CPUList(); //The CPUList used to retrieve data from the database
CpuDb cpuDb = new CpuDb(); //The database object used to move data to and from the CPU Lists
try
{
//Read in the file and store each line into the CPU objects in a list
Files.lines(Paths.get("Project04Data.csv"))
.map(line -> line.split(" ")) // Stream<String[]>
.flatMap(Arrays::stream) // Stream<String>
.forEach(line -> cpuList.AddCpu(line));
//Clear the list table for the new listing
cpuDb.Clear();
//Insert the Cpu List into the database
cpuDb.SetCpuList(cpuList);
//Retrieve the Cpu List into a different CPU List object from the database
cpuDb.GetCpuList(cpuListRetrieved);
//Show the report from the new list that was retrieved from the database
cpuListRetrieved.ShowReport();
} catch (IOException e)
{
e.printStackTrace();
}
}
}
CPUList.java
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
public class CPUList
{
ArrayList<CPU> theList = new ArrayList<>();
/**
* Default constructor for the CPU
*/
public void CPUList()
{
}
/**
* param strInputLine Input line to be used in creating the CPU object
*/
public void AddCpu(String strInputLine)
{
theList.add(new CPU(strInputLine));
}
/**
* param tempCPU - A CPU object to add to the list
*/
public void AddCpu(CPU tempCPU)
{
theList.add(tempCPU);
}
/**
* return the combined string showing the CPU list
*/
public String toString()
{
String strString = "";
for (CPU cpu : theList)
{
strString += cpu;
}
return(strString);
}
/**
* Print the CPU List Statistics
* average price of CPU's
* highest priced CPU
* lowest priced CPU
* best value CPU (performance / price )*
*/
public void PrintStatistics()
{
System.out.printf("average price CPU: %5.2f ", theList.stream().mapToDouble(CPU::getPrice).average().getAsDouble());
System.out.printf("highest price CPU: %5.2f ", theList.stream().mapToDouble(CPU::getPrice).max().getAsDouble());
System.out.printf("lowest price CPU: %5.2f ", theList.stream().mapToDouble(CPU::getPrice).min().getAsDouble());
System.out.println("Best Value CPU: " + Collections.max( theList, new CpuComparator() ).getCpuName());
}
/**
*Create the report from the database table with the following format
*
* Intel Core i7-6700HQ 2.60GHz: $1,509.00
* Intel Core i7-3770K 3.50GHz: $560.50
* Intel Core i5-3570K 3.40GHz: $477.23
* Intel Core i7-4700MQ 2.40GHz: $467.40
*/
public void ShowReport()
{
for(CPU objCpu : theList)
{
System.out.printf("%s: %5.2f ", objCpu.getCpuName(), objCpu.getPrice());
}
}
/**
* Remove the CPU objects that do not have all of the required values parsed out of the line
*/
public void CleanCpuList()
{
Iterator<CPU> it = theList.iterator();
while( it.hasNext() )
{
CPU objCpu = it.next();
if(objCpu.getValid() == false)
{
it.remove();
}
}
}
}
CPU.java
public class CPU
{
private boolean m_bValid;
private String m_strCPULine;
private String m_strCPUName;
private double m_dPerformance;
private double m_dPrice;
private double m_dValue; //Performance / Price
private static final double NA_VALUE = 9999.99;
/**
* param strCPULine is the CPU line from the file
*/
public CPU(String strCPULine)
{
m_strCPULine = strCPULine;
m_bValid = ParseCPULine(strCPULine);
}
/**
* Constructor to be used when the fields are already known
* param strCPUName - name of the CPU
* param iPerformance - performance factor of the CPU
* param dPrice - price of the CPU
*/
public CPU(String strCPUName, int iPerformance, double dPrice)
{
m_strCPUName = strCPUName;
m_dPerformance = (double)iPerformance;
m_dPrice = dPrice;
m_dValue = m_dPerformance / m_dPrice; //Performance / Price
m_bValid = true;
}
/**
* param m_strCPULine - the input file line to be parsed
* return Indicate if the ParseCPULin succeeded or not
*/
private boolean ParseCPULine(String m_strCPULine)
{
boolean bRetValue = true;
String strTemp;
String strNumeric;
//Use a regular expression to parse the line into the individual members
//TODO - Figure out why regex not working for 1,509.00
//messes up on the comma in the value
String[] tokens = m_strCPULine.split(",(?=([^"]*"[^"]*")*[^"]*$)"); //http://rubular.com/ is helpful for regex
//Do we have the correct number of tokens from the split. If not then mark as invalid
if(tokens.length != 3)
{
bRetValue = false;
}
m_strCPUName = tokens[0];
//Get the performance
try
{
strTemp = tokens[1];
strNumeric = strTemp.replaceAll("[^0-9.]+", ""); //Get rid of non digit characters
m_dPerformance = Double.parseDouble(strNumeric);
}
catch(NumberFormatException ex)
{
m_dPerformance = NA_VALUE;
bRetValue = false;
}
//Get the price
try
{
strTemp = tokens[2];
strNumeric = strTemp.replaceAll("[^0-9.]+", ""); //Get rid of non digit characters
m_dPrice = Double.parseDouble(strNumeric);
}
catch(NumberFormatException ex)
{
m_dPrice = NA_VALUE; //Bogus Value
bRetValue = false;
}
//If we have valid Performance and Price values then return the calculated value otherwise set to zero
if(bRetValue)
{
m_dValue = m_dPerformance / m_dPrice;
}
else
{
m_dValue = 0.0;
}
// for(String strTemp1 : tokens)
// {
// System.out.printf("%s ", strTemp1);
// }
//
// System.out.printf(" ");
return bRetValue;
}
/**
* return A string representing the CPU
*/
public String toString()
{
return String.format("[%b] %s [%5.2f] [%5.2f] ", m_bValid, m_strCPUName, m_dPerformance, m_dPrice);
//return String.format("%s ", m_strCPULine); //Just return the original line
}
/**
* Getter
* return the performance as an double
*/
public double getPerformance(){return(m_dPerformance);};
/**
* Getter
* return the price as a double
*/
public double getPrice(){return(m_dPrice);};
/**
* Getter
* return the value as a double
*/
public double getValue(){return(m_dValue);};
/**
* Getter
* return the CPU Name
*/
public String getCpuName(){return(m_strCPUName);};
/**
* return if the cpu's line was parsed with all valid values for the fields or not
*/
public boolean getValid(){return(m_bValid);};
}
CPUDb.java
import java.sql.*;
public class CpuDb
{
//SQL objects
Connection c = null;
boolean bConnected = false;
/**
* Default constructor
*/
public CpuDb()
{
bConnected = Connect("cpudb", "tcc2016", "tcc2016");
}
/**
* param strTable - table for which the column names will be shown
*/
public void ShowColumns(String strTable)
{
Statement s = null;
ResultSet r = null;
try
{
s = c.createStatement();
r = s.executeQuery("SELECT * from " + strTable);
ResultSetMetaData m = r.getMetaData();
int col = m.getColumnCount();
for (int i = 1; i <= col; i++)
{
System.out.printf("%s ", m.getColumnName(i));
}
System.out.println();
}
catch ( SQLException e)
{
e.printStackTrace();
}
}
/**
* Connect to the database
* param strDatabase - database name
* param strUser - database user
* param strPassword - user password
* return true if the connection is made
*/
private boolean Connect(String strDatabase, String strUser, String strPassword)
{
boolean bConnectStatus = false;
try
{
//Had to suppress a SSL warning message that kept popping up
c = DriverManager.getConnection("jdbc:mysql://localhost/" + strDatabase + "?autoReconnect=true&useSSL=false", strUser, strPassword);
System.out.println("Database connection made ");
bConnectStatus = true;
}
catch ( SQLException e)
{
e.printStackTrace();
}
return(bConnectStatus);
}
/**
* Provide the insert into the database of the CPU list
* param lstCpu - list of CPUs to insert
* return true if the setting of the db via inserts succeeded
*/
public boolean SetCpuList(CPUList lstCpu)
{
boolean retValue = false;
Statement s = null;
String strSql;
ResultSet r = null;
try
{
s = c.createStatement();
//"insert into cputable( cpuname, performance, price) values( 'CPU1', 123, 55.66)"
for(CPU objCPU : lstCpu.theList)
{
//Only insert if valid
if(objCPU.getValid() == true)
{
//Create the CPU
strSql = "insert into cputable( cpuname, performance, price) values('"
+ objCPU.getCpuName() + "',"
+ objCPU.getPerformance() + ","
+ objCPU.getPrice() + ")";
//System.out.println(strSql);
s.execute(strSql);
}
}
}
catch ( SQLException e)
{
e.printStackTrace();
}
return (retValue);
}
/**
* Get the CPU list from the the database and return the list
* param lstCpu - list of CPUs in the database
* return indicates if the loading of the cpu list from the database succeeded or not
*/
public boolean GetCpuList(CPUList lstCpu)
{
boolean retValue = false;
Statement s = null;
ResultSet r = null;
try
{
//Get the statement object connected to the database
s = c.createStatement();
//Get all of the fields from the cpu table
r = s.executeQuery("SELECT * from cputable order by price DESC");
//Get the results set for the query
ResultSetMetaData m = r.getMetaData();
//Iterate through the record set and add to the CPU list
while( r.next() ){
lstCpu.AddCpu(new CPU(r.getString( "cpuname" ),r.getInt( "performance" ),r.getFloat( "price" )));
}
// //How many columns do we have
// int col = m.getColumnCount();
//
// //Show all of the data in the results set
// while( r.next() ){
// for( int i = 1; i <= col; i++ ){
// int t = m.getColumnType( i );
// switch( t ){
// case Types.INTEGER:
// System.out.print( r.getInt( i ) );
// break;
// case Types.VARCHAR:
// System.out.print( r.getString( i ) );
// break;
// case Types.DATE:
// System.out.print( r.getDate( i ) );
// break;
// case Types.FLOAT:
// System.out.print( r.getFloat( i ) );
// break;
// default:
// System.out.print("Unk("+ r.getType() + ")");
// break;
// }
// System.out.print( ";" );
// }
// System.out.println();
// }
retValue = true;
}
catch ( SQLException e)
{
e.printStackTrace();
}
return (retValue);
}
/**
* Clear the data out of the database
* return if the table was cleared or not
*/
public boolean Clear()
{
boolean retValue = false;
Statement s = null;
ResultSet r = null;
try
{
//Get the statement object connected to the database
s = c.createStatement();
//Get all of the fields from the cpu table
s.execute("delete from cputable");
}
catch ( SQLException e)
{
e.printStackTrace();
}
return (retValue);
}
}
Project04Data.csv
CPU Name,Performance,Price (USD)
Intel Core i7-3770K @ 3.50GHz,"9,556",$560.50
Intel Core i7-3770 @ 3.40GHz,"9,327",$335.55
Intel Core i7-3820 @ 3.60GHz,"8,990",$404.38
AMD FX-8350 Eight-Core,"8,940",$149.99
Intel Core i7-2600K @ 3.40GHz,"8,501",$379.97
Intel Core i7-2600 @ 3.40GHz,"8,242",$214.99
Intel Core i7-4720HQ @ 2.60GHz,"8,046",NA
AMD FX-8320 Eight-Core,"8,008",$145.99
Intel Core i7-6700HQ @ 2.60GHz,"7,997",$1,509.00
Intel Core i7-4710HQ @ 2.50GHz,"7,826",NA
Intel Core i5-6600K @ 3.50GHz,"7,762",$239.99
Intel Core i7-4700HQ @ 2.40GHz,"7,754",$383.00
Intel Core i7-4700MQ @ 2.40GHz,"7,736",$467.40
Intel Core i5-4690K @ 3.50GHz,"7,690",$239.99
AMD FX-8150 Eight-Core,"7,619",$165.99
Intel Core i7-3630QM @ 2.40GHz,"7,604",$304.49
Intel Core i5-4670K @ 3.40GHz,"7,598",$249.99
Intel Core i5-4690 @ 3.50GHz,"7,542",$224.99
Intel Core i7-3610QM @ 2.30GHz,"7,460",$399.99
Intel Core i5-4670 @ 3.40GHz,"7,342",$226.99
Intel Core i5-4590 @ 3.30GHz,"7,174",$199.99
Intel Core i7-4702MQ @ 2.20GHz,"7,146",NA
Intel Core i5-3570K @ 3.40GHz,"7,130",$477.23
CpuComparator.java
import java.util.Comparator;
public class CpuComparator implements Comparator< CPU >
{
/**
* Compare function to return which cpu has better performance
* @param c1 - first cpu object
* @param c2 - second cpu object
* @return
*/
public int compare(CPU c1, CPU c2 )
{
double c1Value = c1.getValue();
double c2Value = c2.getValue();
return (int)(c1Value - c2Value);
}
}