How to compare records from a CSV file to those in a database before uploading

Question

I'm trying to upload a CSV file into a database that contains student results. I want to update a field for student records that already exist in the database and insert new records for those that do not. The CSV file includes records for both new and existing students.

I am using two fields - idnumber and courseCode to uniquely identify student records in the database and the CSV file.

Here is what I have tried so far... Please can anybody help me?

try{

   String updateSql = "update ResultUpload set idnumber = ?,ca = ?, exams = ?, total = ?, AAA=?,BPLUS=?,BBB=?,CPLUS=?,CCC=?,DDD=?,EEE=?,FFF=?,gpp=?,ugp=?,remarks=?,unit=?,level=?,courseCode=?,courseName=?,semester=? where idnumber=? and courseCode=?";
   String insertSql = "Insert into ResultUpload (idnumber,ca,exams,total,AAA,BPLUS,BBB,CPLUS,CCC,DDD,EEE,FFF,gpp,ugp,remarks,unit,level,courseCode,courseName,semester) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
   String sql = "select idnumber, courseCode from ResultUpload where idnumber = ? and courseCode = ?"; // no need to execute this statement inside a loop

   BufferedReader br = new BufferedReader(new FileReader(filename));
   Connection conn = null;

   PreparedStatement insertStatement = conn.prepareStatement(insertSql);
   PreparedStatement updateStatement = conn.prepareStatement(updateSql);
   PreparedStatement pst = conn.prepareStatement(sql);

   String line;
        while ((line = br.readLine()) != null) {
            String[] value = line.split(","); // check this line that it is not null
            pst.setString(1, value[0]);
            pst.setString(2, value[17]);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) { // if there is an entry in the DB make update
                updateRecord(updateStatement, value);
                                    update_table();
            } else {
                insertRecord(insertStatement, value);
                                    update_table();
            }
            rs.close();
        }

                    br.close();  // close these resources in the finally block
        insertStatement.close();
        updateStatement.close();
        conn.close();





   }
    catch (SQLException | IOException ex) {
 ex.printStackTrace();      // handle exception here
    }
         // handle exception here



   finally{
       try{
       pst.close();
       rs.close();
       }
       catch(Exception e){}
}





    } 
}                                            


  private static void updateRecord(PreparedStatement preparedStatement,String[] value) throws SQLException {
    preparedStatement.setString(1, value[0]);
    preparedStatement.setString(2, value[1]);
            preparedStatement.setString(3, value[2]);
            preparedStatement.setString(4, value[3]);
            preparedStatement.setString(5, value[4]);
            preparedStatement.setString(6, value[5]);
            preparedStatement.setString(7, value[6]);
            preparedStatement.setString(8, value[7]);
            preparedStatement.setString(9, value[8]);
            preparedStatement.setString(10, value[9]);
            preparedStatement.setString(11, value[10]);
            preparedStatement.setString(12, value[11]);
            preparedStatement.setString(13, value[12]);
            preparedStatement.setString(14, value[13]);
            preparedStatement.setString(15, value[14]);
            preparedStatement.setString(16, value[15]);
            preparedStatement.setString(17, value[16]);
            preparedStatement.setString(18, value[17]);
            preparedStatement.setString(19, value[18]);
            preparedStatement.setString(20, value[19]);


    preparedStatement.executeUpdate();
}

   private static void insertRecord(PreparedStatement preparedStatement,String[] value) throws SQLException {
    preparedStatement.setString(1, value[0]);
    preparedStatement.setString(2, value[1]);
            preparedStatement.setString(3, value[2]);
            preparedStatement.setString(4, value[3]);
            preparedStatement.setString(5, value[4]);
            preparedStatement.setString(6, value[5]);
            preparedStatement.setString(7, value[6]);
            preparedStatement.setString(8, value[7]);
            preparedStatement.setString(9, value[8]);
            preparedStatement.setString(10, value[9]);
            preparedStatement.setString(11, value[10]);
            preparedStatement.setString(12, value[11]);
            preparedStatement.setString(13, value[12]);
            preparedStatement.setString(14, value[13]);
            preparedStatement.setString(15, value[14]);
            preparedStatement.setString(16, value[15]);
            preparedStatement.setString(17, value[16]);
            preparedStatement.setString(18, value[17]);
            preparedStatement.setString(19, value[18]);
            preparedStatement.setString(20, value[19]);


    preparedStatement.executeUpdate();

}

What is the problem you are facing; and, is this a homework question? — manish, May 14 '16 at 08:11
the problem i am facing @manish is, the csv file keeps uploading continuously and the records in the database that exists ,are not updated — Saddam Musa Mohammed, May 14 '16 at 08:51
Hints: [`==` does not work for comparing `String`s](http://stackoverflow.com/questions/513832/how-do-i-compare-strings-in-java); `br.close()` should be outside the loops. — manish, May 14 '16 at 09:37
Check if your `updateRecord()` method is matching the positional parameters and the values of the fields correctly. And check the correct type of the fields if, for example, all your fields are of type `String` because we are calling `setString()` method of the `PreparedStatement` for all fields. — ujulu, May 15 '16 at 06:13

score 0 · Answer 1 · answered May 14 '16 at 07:36

0

Why don't use insert ... on duplicate key update?

answered May 14 '16 at 07:36

tomekK

139
7

This should be a comment on the question, not an answer. – manish May 14 '16 at 08:03
I think it's way better approach than coding it with multiple select queries in a loop. – tomekK May 15 '16 at 11:12

ujulu · Accepted Answer · 2016-05-15T06:03:00.027

I modified your code a little bit as follows (but the code is not complete; left as a homework :-)):

try {
        // Create sql statements

        String updateSql = "update ResultUpload set ca = ?, exams = ?, total = ?, AAA=?, BPLUS=?, BBB=?, CPLUS=?, CCC=?, DDD=?, EEE=?, FFF=?, gpp=?, ugp=?, remarks=?, unit=?, level=?, courseName=?, semester=? where idnumber=? and courseCode=?";
        String insertSql = "Insert into ResultUpload (idnumber,ca,exams,total,AAA,BPLUS,BBB,CPLUS,CCC,DDD,EEE,FFF,gpp,ugp,remarks,unit,level,courseCode,courseName,semester) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        String sql = "select idnumber, courseCode from ResultUpload where idnumber = ? and courseCode = ?"; 

        BufferedReader br = new BufferedReader(new FileReader("fileName"));
        Connection conn = null;

        // Create PreparedStatement objects for both queries; and this
        // should be done outside of the loop
        PreparedStatement insertStatement = conn.prepareStatement(insertSql);
        PreparedStatement updateStatement = conn.prepareStatement(upadteSql);
        PreparedStatement pst = conn.prepareStatement(sql);

        String line;
        while ((line = br.readLine()) != null) {
            String[] value = line.split(","); // check this line that it is not null
            pst.setString(1, value[0]);
            pst.setString(2, value[1]); 
            ResultSet rs = pst.executeQuery();
            if (rs.next()) { // if there is an entry in the DB make update
                updateRecord(updateStatement, value);
            } else {
                insertRecord(insertStatement, value);
            }
            rs.close();
        }
        br.close();  // close these resources in the finally block
        insertStatement.close();
        updateStatement.close();
        conn.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
    } catch (IOException ex) {
        ex.printStackTrace();
    }

private static void updateRecord(PreparedStatement preparedStatement,
        String[] value) throws SQLException {
    preparedStatement.setString(19, value[0]); // in where clause: value of idnumber
    preparedStatement.setString(20, value[1]); // in where clause: value of coursecode

    // 
    preparedStatement.setString(1, value[ca]);  // array index for 'ca'
    preparedStatement.setString(2, value[exams]); // array index for 'exams'
    // set values for rest of the fields ....

    preparedStatemtn.executeUpdate();
}

private static void insertRecord(PreparedStatement preparedStatement,
        String[] value) throws SQLException {
    preparedStatement.setString(1, value[0]); // index of array for 'idnumber'
    preparedStatement.setString(2, value[]); // index of array for 'ca'
    // set values for rest of the fields ....

    preparedStatement.executeUpdate();
}

And here you'll find how to use PreparedStatement. Hope it helps.

If you nee any clarification on any part of the code just drop me a comment. — ujulu, May 14 '16 at 13:28
i have tried my best using the modified code, but when i tried to upload the csv file which contains both the records that need update and the new ones that will be inserted nothing happens ...and didn't prompt any error, it neither insert or update the recorda — Saddam Musa Mohammed, May 14 '16 at 20:48
I modified the `updateStatement` as well as the `catch` block so that you should get the stacktrace now. Try it and leave me a comment. I just modified the code and everything works for me: csv file is read, and updates as well as inserts will be made. Indeed, you have to create an `insertRecord()` for the cases of inserting data. — ujulu, May 14 '16 at 22:08
And take care of the positional parameters in the PreparedStatement; they begin counting 1, 2, 3, 4, ..., the correct datatype and order of the fields. — ujulu, May 14 '16 at 22:15
thanks@ujulu you really helped me, i understood all your concept in the code, and used as follows...but still no any insert or update in the database,please will you mind to see whats the logical error i had. — Saddam Musa Mohammed, May 15 '16 at 03:03
due to limitations i uploaded the code to the question section...Please can you help me check — Saddam Musa Mohammed, May 15 '16 at 03:20
I updated the answer and added a couple of comments, please check if you are matching the fields and the corresponding positional params correctly. Because you haven't posted the format of the file I cannot check that. One more thing you can check is if the file is really found and read. Try to use the debugger if you are using any IDE to see at what point the error is happening. And check if you have any stacktrace. Feel free to leave me a comment if it still not working. — ujulu, May 15 '16 at 06:08
Thanks man.. ..the credit all goes to you.. ..I wish you are my mentor, this is the first time I am developing an app using Java, and it's my first programming language that I am learning...please will you mind if I have your email? — Saddam Musa Mohammed, May 15 '16 at 08:09

How to compare records from a CSV file to those in a database before uploading

2 Answers2