How to insert values from csv into database in java

Question

I have tried the following code to split the csv values and now how do insert it in to DB? Do I have save the values in to separate variables to match column names? I am unable to figure out.

Note: I don't want to use any csv parser right now. I just want to do it manually

public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        String name;
        String email;
        String phone;
        String ID;

        Connection con = OracleDBConnection.getConnection();
        String query = "Insert into NEWSTUDENT values(?,?,?,?)";
                PreparedStatement st = con.prepareStatement();
                st.executeUpdate(query);        

        try {
            BufferedReader bReader = new BufferedReader(new FileReader("1000rows.csv"));

            while (bReader != null) {
                String read;
                try {
                    read = bReader.readLine();
                    if (read != null) 
                    {
                        String[] array = read.split(",+");
                        for(String result:array)
                        {
                            System.out.println(result);
                        }
                    } 
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
                finally
                {
                   if (bReader == null) 
                    {
                        bReader.close();
                    }
                }
            }
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        }
    }
}

output:

1Kiriti
[email protected]
880789939

Column names in Database:

Name Email Phone ID

Why no parser? Is it a homework? If not, just use a parser. Writing your own will hit you with bugs later. — Ondra Žižka
– Ondra Žižka, Commented Oct 8, 2018 at 13:02

Rookie007 · Accepted Answer · 2015-06-09 09:04:27Z

8

Use Parepared statement and build a query in while Loop and execute it. For more on Prepared Statement please check Check this link

String sql = " INSERT INTO TABLE_(name,email,phone,id) VALUES(?,?,?,?) ";


try { 
        BufferedReader bReader = new BufferedReader(new FileReader("1000rows.csv"));
        String line = ""; 
        while ((line = bReader.readLine()) != null) {
            try {

                if (line != null) 
                {
                    String[] array = line.split(",+");
                    for(String result:array)
                    {
                        System.out.println(result);
 //Create preparedStatement here and set them and excute them
                PreparedStatement ps = yourConnecionObject.createPreparedStatement(sql);
                 ps.setString(1,str[0]);
                 ps.setString(2,str[1]);
                 ps.setString(3,str[2]);
                 ps.setString(4,strp[3])
                 ps.excuteUpdate();
                 ps. close()
   //Assuming that your line from file after split will folllow that sequence

                    }
                } 
            } catch (IOException ex) {
                ex.printStackTrace();
            }
            finally
            {
               if (bReader == null) 
                {
                    bReader.close();
                }
            }
        }
    } catch (FileNotFoundException ex) {
        ex.printStackTrace();
    }

edited Jun 9, 2015 at 9:04

answered Jun 9, 2015 at 8:44

Rookie007

1,2192 gold badges19 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kittu Over a year ago

My question how to put in the loop and match the columns with values in array when I am looping it

Neeraj Jain · Accepted Answer · 2015-06-09 09:23:39Z

8

You can use Prepare Statement and set the value in parameter at each iteration:

Connection con = OracleDBConnection.getConnection();
String query = "Insert into NEWSTUDENT values(?,?,?,?)";
PreparedStatement ps=con.prepareStatement(query);
ps.setString(1,array[0]);
ps.setString(2,array[1]); // and so on
ps.executeUpdate();

If No of Rows are more you can also use Batch Processing :

String sql = "Insert into NEWSTUDENT values(?,?,?)";


PreparedStatement preparedStatement = null;
try{
    preparedStatement =
            connection.prepareStatement(sql);

    preparedStatement.setString(1, "Gary");
    preparedStatement.setString(2, "Larson");
    preparedStatement.setString (3, "Test");

    preparedStatement.addBatch();

    preparedStatement.setString(1, "Stan");
    preparedStatement.setString(2, "Lee");
    preparedStatement.setString (3, 456);

    preparedStatement.addBatch();

    int[] affectedRecords = preparedStatement.executeBatch();

}finally {
    if(preparedStatement != null) {
        preparedStatement.close();
    }
}

edited Jun 9, 2015 at 9:23

answered Jun 9, 2015 at 8:44

Neeraj Jain

7,7308 gold badges36 silver badges65 bronze badges

2 Comments

kittu Over a year ago

I have about 1000 rows

Neeraj Jain Over a year ago

Then you can use Batch Processing !

Jens · Accepted Answer · 2015-06-09 08:43:17Z

2

You can store your data in an array and bind them to your statement:

 String query = "Insert into NEWSTUDENT values(?,?,?,?)";

 PreparedStatement st = con.prepareStatement(query);
 st.setString(1,array [0]);
 st.setString(2,array[1]);
 ....
 st.executeUpdate();

For more informations about prepared statements see the oracle documentation

answered Jun 9, 2015 at 8:43

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Collectives™ on Stack Overflow

How to insert values from csv into database in java

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related