0

I want to compare the values derived from the mysql db with excel sheet values by each row.

I implemented till getting values from MysqlDb and storing into a Arraylist. Now i want to compare these values with the excel file. I hope explained well let me know if didn't understood. Please have a look into my code :

public class DbConnection {

    public static Connection connectJDBCToAWSEC2() {

        System.out.println("----MySQL JDBC Connection Testing -------");

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Where is your MySQL JDBC Driver?");
            e.printStackTrace();
            return null;
        }

        System.out.println("MySQL JDBC Driver Registered!");
        Connection connection = null;

        try {
            /*
             * connection = DriverManager.
             * getConnection("jdbc:mysql://52.53.283.64:3306/testdb", "testadmin",
             * "Stg@work");
             */
            connection = DriverManager.getConnection("jdbc:mysql://13.55.218.42/test_permit_data", "automationadmin",
                    "Make!tYours1976");
        } catch (SQLException e) {
            System.out.println("Connection Failed!:\n" + e.getMessage());
        }

        if (connection != null) {
            System.out.println("SUCCESS!!!! You made it, take control your database now!");
            return connection;
        } else {
            System.out.println("FAILURE! Failed to make connection!");
            return connection;
        }
    }

    public static void connectJDBCToAWSEC21() {

        System.out.println("----MySQL JDBC Connection Testing -------");

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Where is your MySQL JDBC Driver?");
            e.printStackTrace();
            return;
        }

        System.out.println("MySQL JDBC Driver Registered!");
        Connection connection = null;

        try {
            connection = DriverManager.getConnection(""jdbc:mysql://52.53.283.64:3306/testdb", "testadmin",
                    "Stg@work");
        } catch (SQLException e) {
            System.out.println("Connection Failed!:\n" + e.getMessage());
        }

        if (connection != null) {
            System.out.println("SUCCESS!!!! You made it, take control     your database now!");
        } else {
            System.out.println("FAILURE! Failed to make connection!");
        }

    }

    public static ResultSet runQueryResultSet(Connection con, String sql) {
        Statement statement = null;
        ResultSet rs = null;
        int col = 0;
        try {

            System.out.println("Creating statement...");
            statement = con.createStatement();
            rs = statement.executeQuery(sql);
            java.sql.ResultSetMetaData meta = rs.getMetaData();
            // Getting column count from DB
            int colCount = meta.getColumnCount();
            // creating array list to store the values
            ArrayList<String> arrayList = new ArrayList<String>();
            // Getting values from db by each column
            while (rs.next()) {

                for (col = 2; col <= colCount - 2; col++) {

                    arrayList.add(rs.getString(col));
                    if (rs.getObject(col) == null) {
                        System.out.print(" NULL ");
                    } else {
                        System.out.print(" " + rs.getObject(col));
                    }

                }
            }
        }
    }
}
ric
  • 627
  • 1
  • 12
  • 23
Nagaraj S
  • 11
  • 1
  • 1
    You need to create another method to read the excel sheet .You can use HFSS work book for this.Read the excel sheet ,iterate and store that in a list and compare the list with the list from db.https://stackoverflow.com/questions/5578535/get-cell-value-from-excel-sheet-with-apache-poi – Pradeep Dec 12 '17 at 12:10
  • https://stackoverflow.com/questions/1516144/how-to-read-and-write-excel-file – Pradeep Dec 12 '17 at 12:10
  • Thanks Pradeep ....let me check this and let you know. – Nagaraj S Dec 12 '17 at 12:21
  • were u able to compare Nagaraj?.If yes I will reply with the steps so that you can go ahead and accept the answer. – Pradeep Dec 13 '17 at 05:17

0 Answers0