CODEWITHSUNDEEP
javasqlcsv
ninabei
ninabei

Reputation: 65

Java Import data from CSV into SQL prevent double entry

I have the following codes. I am not sure how do I arrange them such that it can check for entry in the CSV before adding the record into the SQL database. Problem is I am still adding double record into the sql

try {
        br = new BufferedReader(new FileReader(file));
        String[] data1 = br.readLine().split(cvsSplitBy);

        while ((line = br.readLine()) != null) {
            String queryCheck = "SELECT Count(Name) from DB WHERE Name = ?";

            PreparedStatement st = conn.prepareStatement(queryCheck);
            //value is the data of the name column in the CSV
            st.setString(1, data1[0]);
            ResultSet rs = st.executeQuery();
            boolean recordFound = rs.next();

            if (recordFound) {
                //dont add record

                System.out.println("found " + data1[0]);

            } else {


                String[] data = line.split(cvsSplitBy);

                String sql2 = "INSERT INTO DB (Name, ID, Age) values (?, ?, ?)";
                pstmt = conn.prepareStatement(sql2);
                pstmt.setString(1, data[0]);
                pstmt.setString(2, data[1]);
                pstmt.setString(3, data[2]);
                pstmt.executeUpdate();
            }

        }

Upvotes: 2

Views: 242

Answers (2)

Baby
Baby

Reputation: 5102

Your code above only check for the existence of the first row data in your database. put your query check inside while loop as well.

And you should simply SELECT * Instead of SELECT count(Name) because SELECT count(Name) will give you result even if the count result is 0

Try this:

    br = new BufferedReader(new FileReader(file));
    String queryCheck, insert;
    PreparedStatement st;
    ResultSet rs;
    boolean recordFound;
    while ((line = br.readLine()) != null) {
        queryCheck = "SELECT * from DB WHERE Name = ?";
        String[] data1 = line.split(cvsSplitBy);
        System.out.println("Name= " + data1[0] + " , ID= " + data1[1] + " , Age= " + data1[2]);
        st = conn.prepareStatement(queryCheck);
        st.setString(1, data1[0]);
        rs = st.getResultSet();
        recordFound = rs.next();

        if (recordFound) {
            System.out.println(data1[0]+" already exist in the database!");
        }
        else{
            String[] data = line.split(cvsSplitBy);
            insert = "INSERT INTO DB (Name, ID, Age) values (?, ?, ?)";
            pstmt = conn.prepareStatement(insert);
            pstmt.setString(1, data[0]);
            pstmt.setString(2, data[1]);
            pstmt.setString(3, data[2]);
            pstmt.executeUpdate();
        }

    }

Upvotes: 5

Durandal
Durandal

Reputation: 5663

Looks like a couple a little mis-steps in logic. You need to use your if statement to control when the insert is called. I inverted your recordFound variable because if the record is found you just want to do nothing, thus no need for the else block. Looks like this:

     if (!recordFound) {
            while ((line = br.readLine()) != null) {

                 String[] data = line.split(cvsSplitBy);

                 String sql2 = "INSERT INTO DB (Name, ID, Age) values (?, ?, ?)";
                 pstmt = conn.prepareStatement(sql2);
                 pstmt.setString(1, data[0]);
                 pstmt.setString(2, data[1]);
                 pstmt.setString(3, data[2]);
                 pstmt.executeUpdate();
           }
     }

Also I believe you're misusing the rs.next() call as well; even if the count is 0 that value is still being returned and it will always result in the rs.next() call being true . You need to get the value of the result and see if it's greater than 0. Something like this:

boolean recordFound = false;
PreparedStatement st = conn.prepareStatement(queryCheck);
//value is the data of the name column in the CSV
st.setString(1, value);
ResultSet rs = st.getResultSet();
rs.first();//move cursor to single result
if(rs.getInt(1) > 0)
    recordFound = true;

Also you are reading the first line of your file in a static fashion and then later in the found block are trying to then loop over it. You need to just loop over it initially instead of looking at the first line and then looping afterwards

Upvotes: 2

Related Questions