Java 将数据从 CSV 导入 SQL 防止重复输入

Question

我有以下代码。我不确定如何安排它们，以便它可以在将记录添加到 SQL 数据库之前检查 CSV 中的条目。问题是我仍然在 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();
            }

        }

Answer 1

您上面的代码仅检查数据库中是否存在第一行数据。将您的查询检查 也放在while 循环中。

你应该简单地 SELECT * 而不是 SELECT count(Name) 因为 SELECT count(Name) 会给你结果，即使计数结果为 0

试试这个:

    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();
        }

    }