File reading in Java and MySQL Database

Question

I need some help with a project that I'm working on. The first part of the project is to create a utility/tool that generates fake data and writes it to a CSV text file. This part is working just fine.

Here's the code for that (if it helps)

import sys
from faker import Factory
fake = Factory.create()

x = 1
param1 = int(sys.argv[1])
f = open('myfile.txt','w')
for x in range (0,param1):
f.write(fake.first_name() + "," + fake.last_name() + "," + fake.job() + "," + fake.email() + ","
    + fake.street_address() + "," + fake.city() + "," + fake.state() + "," + fake.state_abbr() + ","
    + fake.zipcode() + "," + fake.credit_card_provider() + ","
    + fake.credit_card_number() + "," + fake.phone_number() + "\n")

f.close()

Here's what prints out when compiled:

William,James,Careers information officer,clarkjeanette@hotmail.com,9448 Rodriguez Brook Apt. 796,South Lynnbury,South Carolina,VA,26103,JCB 16 digit,3112583369273283,1-002-827-0311x681

Luis,Martin,Air cabin crew,sandra05@holland.com,6154 James Cove,Christianberg,New York,RI,37208,JCB 15 digit,378433042568763,+42(0)3011107909

Jose,Jones,Make,qshaw@peters.org,431 Jessica Pass,East Robertburgh,Texas,SC,46458,Mastercard,4800941995105607,(047)981-1856x1825

Mary,Pope,Field seismologist,reginaldchaney@hotmail.com,00799 Tracy Trace,Robinburgh,Rhode Island,HI,68855,JCB 16 digit,6011260007331949,+66(4)4995888616

Jennifer,Villanueva,Tax adviser,jtravis@hotmail.com,271 Simmons Mountains,Boydmouth,Nebraska,NM,98981,JCB 16 digit,210077713575961,639.575.1338x414

The extra spaces in between each line, I added them here for readability purposes.

Now the next part of the project is to develop an application, in Java, that imports the unstructured data from the CSV text file into a normalized database.

I have some Java code, but it doesn't work the way I'm thinking it should (BTW, feel free to correct my thinking on how it should be done). The way I'm thinking it should work is

1. Import the data into java
2. Use the prepared statement function to create a the SQL statement that will import the data into the table along with the .setString functions that are required for it

However, it's not working properly. First I will put the code and then I will explain what is going on:

package com.company;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

public class Main {

    private static Connection connect() {
        Connection mysql = null; //sets values to null before actually attempting a connection
        PreparedStatement pst = null;
        ResultSet data = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            String connectionStringURL = "jdbc:mysql://us-cdbr-azure-west-b.cleardb.com:3306/acsm_0a00c1270f36f77"; //database name
            mysql = DriverManager.getConnection(connectionStringURL, "username", "password"); //username, password

            if (mysql == null) //check to make sure that it actually connected
                System.out.println("Connection Failed");
            else
                System.out.println("Success");

        }
        catch (Exception ex) //catches connection failure exception
        {
            ex.printStackTrace();
        }
        return mysql;
    }

    public static void main(String[] args) throws Exception {
        String filename = "/Desktop/myfile.txt";

        PreparedStatement pstmt = null;
        Connection mysql = connect();
        try
        {
            pstmt = mysql.prepareStatement("INSERT INTO Customer (First Name, Last Name, Job, Email, Phone Number) VALUES (?,?,?,?,?)");

            Scanner s = new Scanner(new File("/Desktop/myfile.txt"));
        ArrayList<String> list = new ArrayList<String>();
            while (s.hasNextLine())
            {
                list.add(s.nextLine());
            }
            System.out.println(list);
            s.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}

The first function is obviously my database connection function, no problem there.

The second function is where the problem is. Currently, I have a prepared statement with the SQL script already in it. However, that is not actually being used yet. What I'm first trying to get done is to read the file line by line THEN parse it for each field.

I asked a friend of mine how to do it and he said to

1. Read file line by line, split on comma and pass to array
2. make a Data class that has elements for each column in your file
3. for each line's array created, create an object of Data class
4. look up batchExecute() for jdbc
5. write insert statement and execute

For the first step where it says to "pass to array", would that use just an array or an ArrayList, and for either one, would that mean each record/line would have its own array/arraylist?

I'm not exactly sure how to do the other steps either. I've been looking all over the internet for answers, but I've come up short.

I don't think I'm forgetting to mention anything, but if you need some more clarification on something I said, I would be happy to try and explain what I meant. Any help is appreciated.

Thank you so much in advance.

Answer 1

I suggest to use a csv parser to read or write to csv file. Below is an example using opencsv

import com.opencsv.CSVReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NewClass1 {
    public static void main(String[] args) {
        try {

            String fileName = "yourfile.csv";
            List<String[]> customerList = readWholeCsvFile(fileName);
            Connection conn = getConnection();
            persistWithOutDataClass(conn,customerList);

        } catch (IOException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static List<String[]> readWholeCsvFile(String fileName) throws IOException{
        List<String[]> myEntries = new ArrayList<>();
        CSVReader reader = new CSVReader(new FileReader(fileName), ',' ,'\'', 1);
        myEntries = reader.readAll();
        return myEntries;        
    }

    public static List<Customer> readCsvFileLineByLine(String fileName) throws IOException{
        List<Customer> customerList = new ArrayList<>();
        String [] nextLine;
        CSVReader reader = new CSVReader(new FileReader(fileName), ',' ,'\'', 1);
        while ((nextLine = reader.readNext()) != null) {        
            customerList.add(new Customer(nextLine[0], nextLine[1], nextLine[2], nextLine[3], nextLine[4]));
        }       
        return customerList;        
    }

    private static Connection getConnection() {
        Connection conn = null; //sets values to null before actually attempting a connection               
        try{
            Class.forName("com.mysql.jdbc.Driver");
            String connectionStringURL = "jdbc:mysql://us-cdbr-azure-west-b.cleardb.com:3306/acsm_0a00c1270f36f77"; //database name
            conn = DriverManager.getConnection(connectionStringURL, "username", "password"); //username, password
            if (conn == null) //check to make sure that it actually connected
                System.out.println("Connection Failed");
            else
                System.out.println("Success");
        }
        catch (Exception ex){
            ex.printStackTrace();
        }
        return conn;
    }

    private static void persistWithOutDataClass(Connection conn, List<String[]> customerList) throws SQLException{
        String insertStatement = " insert into Customer (First Name, Last Name, Job, Email, Phone Number) values (?, ?, ?, ?, ?)";
        PreparedStatement preparedStmt = conn.prepareStatement(insertStatement);
        for(String[] row : customerList){
            preparedStmt.setString (1, row[0]);
            preparedStmt.setString (2, row[1]);
            preparedStmt.setString (3, row[2]);
            preparedStmt.setString (4, row[3]);
            preparedStmt.setString (5, row[11]);
            preparedStmt.addBatch();
        }
        preparedStmt.executeBatch();
    }

    private static void persistWithDataClass(Connection conn, List<Customer> customerList) throws SQLException{
        String insertStatement = " insert into Customer (First Name, Last Name, Job, Email, Phone Number) values (?, ?, ?, ?, ?)";
        PreparedStatement preparedStmt = conn.prepareStatement(insertStatement);
        for(Customer cust : customerList){
            preparedStmt.setString (1, cust.getFirstName());
            preparedStmt.setString (2, cust.getLastName());
            preparedStmt.setString (3, cust.getJob());
            preparedStmt.setString (4, cust.getEmail());
            preparedStmt.setString (5, cust.getPhone());
            preparedStmt.addBatch();
        }
        preparedStmt.executeBatch();
    }
}

If you want to make use of a data class you need a class like below

public class Customer {

    private String firstName;
    private String lastName;
    private String job;
    private String email;
    private String phone;

    public Customer(String firstName, String lastName, String job, String email, String phone) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.job = job;
        this.email = email;
        this.phone = phone;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}