File reading in Java and MySQL Database
<p>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.</p>

<p>Here's the code for that (if it helps)</p>

<pre><code>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()
</code></pre>

<p>Here's what prints out when compiled:</p>

<pre><code>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
</code></pre>

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

<p>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.</p>

<p>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</p>

<ol>
<li>Import the data into java</li>
<li>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</li>
</ol>

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

<pre><code>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&lt;String&gt; list = new ArrayList&lt;String&gt;();
            while (s.hasNextLine())
            {
                list.add(s.nextLine());
            }
            System.out.println(list);
            s.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}
</code></pre>

<p>The first function is obviously my database connection function, no problem there.</p>

<p>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.</p>

<p>I asked a friend of mine how to do it and he said to</p>

<ol>
<li>Read file line by line, split on comma and pass to array</li>
<li>make a Data class that has elements for each column in your file</li>
<li>for each line's array created, create an object of Data class</li>
<li>look up batchExecute() for jdbc</li>
<li>write insert statement and execute</li>
</ol>

<p>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?</p>

<p>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.</p>

<p>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.</p>

<p>Thank you so much in advance.</p>