Saddam Musa Mohammed Saddam Musa Mohammed - 6 months ago 14
SQL Question

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

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 {
BufferedReader br = new BufferedReader(new FileReader(filename));
String line;
while((line=br.readLine())!=null) {
String[]value= line.split(",");
String sq = "select idnumber, courseCode from ResultUpload";
pst=conn.prepareStatement(sq);
rs=pst.executeQuery();
while(rs.next()) {
String idn = rs.getString("idnumber");
String coC = rs.getString("courseCode");
if((idn==value[0])&&(coC==value[14])) {
String sql ="update ResultUpload set idnumber='"+value[0]+"',ca='"+value[1]+"',exams='"+value[2]+"',total='"+value[3]+"',AAA='"+value[4]+"',BPLUS='"+value[5]+"',BBB='"+value[6]+"',CPLUS='"+value[7]+"'"
+ "CCC='"+value[8]+"',DDD='"+value[9]+"',EEE='"+value[10]+"',FFF='"+value[11]+"',gpp='"+value[12]+"',ugp='"+value[13]+"',remarks='"+value[14]+"',unit='"+value[15]+"',level='"+value[16]+"',courseCode='"+value[17]+"',courseName='"+value[18]+"',semester='"+value[19]+"'";
pst=conn.prepareStatement(sql);
pst.executeUpdate();
br.close();
}
else {
String sql ="Insert into ResultUpload (idnumber,ca,exams,total,AAA,BPLUS,BBB,CPLUS,CCC,DDD,EEE,FFF,gpp,ugp,remarks,unit,level,courseCode,courseName,semester) values('"+value[0]+"','"+value[1]+"','"+value[2]+"','"+value[3]+"','"+value[4]+"','"+value[5]+"','"+value[6]+"','"+value[7]+"','"+value[8]+"','"+value[9]+"','"+value[10]+"','"+value[11]+"','"+value[12]+"','"+value[13]+"','"+value[14]+"','"+value[15]+"','"+value[16]+"','"+value[17]+"','"+value[18]+"','"+value[19]+"')";
pst=conn.prepareStatement(sql);
pst.executeUpdate();
br.close();
}

br.close();
}
}
}

Answer

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

try {
        // Create sql statements
        String upadteSql = "update ResultUpload set idnumber = ?,vca = ?, exams = ?, total = ?, ... where idnumber = ? and courseCode = ?";
        String insertSql = "Insert into ResultUpload (idnumber,ca,exams,total, ...) 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;

        // 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 {
                updateRecord(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 preparedStatemtn,
        String[] value) throws SQLException {
    preparedStatemtn.setString(1, value[0]);
    preparedStatemtn.setString(2, value[1]);
    // set values for rest of the fields ....

    preparedStatemtn.executeUpdate();
}

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