Create a JAVA program that performs file IO and database int

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.23

Solution

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\\t\", strTemp1);
//        }
//
//        System.out.printf(\"\ \");

        return bRetValue;
    }

    /**
     * return A string representing the CPU
     */
    public String toString()
    {
        return String.format(\"[%b]\\t%s\\t\\t[%5.2f]\\t[%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\\t\\t\", 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);
    }
}