Compare csv file with MySQL database

Question

I am creating a program in Java and I need to compare if data in a csv file is exactly the same that exists on a mysql table? How can i do that? For example, i have a table "Suplyers" with columns "Id, Name and Adress". Thanks

Below is the code that i have that read csv file and that connect to database and shows the data in the table.

    public static void le_csv() {
            String row;
            BufferedReader csvReader = null;
            try {
                csvReader = new BufferedReader(new FileReader("C:\\Users\\User\\Desktop\\ficheiros\\fornecedores.csv"));
            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                while ((row = csvReader.readLine()) != null) {
                    String[] data = row.split(",");
                    System.out.println(data[0] + "\t" + data[1] + "\t" + data[2]);
                }
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                csvReader.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    > 
    > 
    > 
    public static void query(){
        try {
                String url = "jdbc:mysql://127.0.0.1:3306/database";
                String user = "user";
                String password = "password";
                Class.forName("com.mysql.jdbc.Driver");
                Connection conn = DriverManager.getConnection(url, user, password);
    
                String sql = "SELECT * FROM SUPLYERS";
                Statement st = conn.createStatement();
                ResultSet rs = st.executeQuery(sql);
    
                while (rs.next()) {
                    System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3);
                }
    
                rs.close();
                st.close();
                conn.close();
    
            } catch (Exception exc) {
                exc.printStackTrace();
            }
}

Answer 1

You may collect the CSV data and DB data into lists of String and then compare the lists using equals:

    public static List<String> readCsvData() {
        List<String> csvData = new ArrayList<>();
        // use try-with-resources to auto close reader
        try (BufferedReader csvReader = new BufferedReader(new FileReader("C:\\Users\\User\\Desktop\\ficheiros\\fornecedores.csv"))){
            String row;
            while ((row = csvReader.readLine()) != null) {
                String[] data = row.split(",");
                row = String.join("\t", data[0], data[1], data[2]);
                System.out.println(row);
                csvData.add(row);
            }
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return csvData;
    }

    public static void initDb() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
    
    public static List<String> readDbData(){
        initDb();
        String url = "jdbc:mysql://127.0.0.1:3306/database";
        String user = "user";
        String password = "password";
        String sql = "SELECT * FROM SUPLYERS";
        List<String> dbData = new ArrayList<>();

        // use try-with-resources to auto close SQL connection, etc.
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery(sql);
        ) {
            while (rs.next()) {
                String row = String.join("\t", rs.getString(1), rs.getString(2), rs.getString(3));
                System.out.println(row);
                dbData.add(row);
            }
        } catch (Exception exc) {
            exc.printStackTrace();
            throw new RuntimeException(exc);
        }
        return dbData;
    }
    
    public static boolean areCsvDataSameAsDb() {
        List<String> csvData = readCsvData();
        List<String> dbData = readDbData();
        
        return csvData.equals(dbData);
    }

Or you can read the data row by row to shortcut the check as soon as any discrepancy is detected.