File reading in Java and MySQL Database -

i need project i'm working on. first part of project create utility/tool generates fake data , writes csv text file. part working fine.

here's code (if helps)

import sys faker import factory fake = factory.create()  x = 1 param1 = int(sys.argv[1]) f = open('myfile.txt','w') 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 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 spaces in between each line, added them here readability purposes.

now next part of project develop application, in java, imports unstructured data csv text file normalized database.

i have java code, doesn't work way i'm thinking should (btw, feel free correct thinking on how should done). way i'm thinking should work is

1. import data java
2. use prepared statement function create sql statement import data table along .setstring functions required it

however, it's not working properly. first put code , explain 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 null before attempting 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 make sure 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 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 database connection function, no problem there.

the second function problem is. currently, have prepared statement sql script in it. however, not being used yet. i'm first trying done read file line line parse each field.

i asked friend of mine how , said to

1. read file line line, split on comma , pass array
2. make data class has elements each column in file
3. for each line's array created, create object of data class
4. look batchexecute() jdbc
5. write insert statement , execute

for first step says "pass array", use array or arraylist, , either one, mean each record/line have own array/arraylist?

i'm not sure how other steps either. i've been looking on internet answers, i've come short.

i don't think i'm forgetting mention anything, if need more clarification on said, happy try , explain meant. appreciated.

thank in advance.

i suggest use csv parser read or write csv file. below 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 null before attempting 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 make sure 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 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 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 want make use of data class need class 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;     } }