Compare two oracle tables of two different databases in java

Question

I have 2 different databases. Actually the second database is with all the records that are identical to the first one. Mean when i create/delete/update any entry in the first database, then after a certain while it is visible in the second database table as well. Although there are some minor differences as well like two rows are different or additional in the first table.

My question is how to compare these two tables from two different databases in java.

I connect to the databases like this but i don't know how to proceed further. Any help would be appreciated

        String SQL_Statement = "SELECT * FROM Table_All_Customers";

        for (Connection con : getDBConnections())
        {

        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(SQL_Statement);
        int count=0;
        int rowCount= 0;
        while (rs.next())
        {
            count+=1;
            rowCount+=1;
            String productNumber = rs.getString("Customer");
            System.out.println("This is the result ... " + rowCount + " :" + productNumber);
        }
    }

With the above code, i am just fetching the data from the columns of these tables and printing them

the tables look like this

table 1 in database 1:

UserID  Customer    Order    number  
----------------------
2334e3    David       Pizza    2       
2334e4    David       Cola     3       
2334e5    Lisa        Cola     4       
2334e6     Lina       Pizza    5      
2334e7    Greco       Cola     6       

table 1 in database 2:

 UserID   Customer    Order    number  
    ----------------------  
  2334e3  David       Pizza    2       
  2334e4  David       Cola     3       
  2334e5  Lisa        Cola     4       
  2334e6  Lina        Pizza    5       

My aim is to compare these two tables above to check if data is updated successfully in two different databases. Also is there any way to check the differences as well? How to achieve it via Java. Would be thankful for any help.

Updated:

I tried something like this but i am unable to compare the differences, is it possible to print the differences between two tables?

public static boolean isDBSame()
    {
        try
 {
        Connection con = DriverManager.getConnection(URL, userName, pass);
        Connection con2 = DriverManager.getConnection(URL2, userName, pass);
        Statement stmt = con.createStatement();
        Statement stmt2 = con2.createStatement();
        ResultSet rs = stmt.executeQuery(SQL_Statement);
        ResultSet rs2 = stmt2.executeQuery(SQL_Statement_2);
        int count = 0;
        int rowCount = 0;
        int firstDB_count = 0;
        int firstDB_rowCount = 0;
        List<String> tags = new ArrayList<String>();
        List<String> tags2 = new ArrayList<String>();
        List<String> tags3 = new ArrayList<String>();
        Set<String> ad = new HashSet<String>(tags);
        Set<String> bd = new HashSet<String>(tags2);
        while (rs.next())
        {
            String Customer = rs.getString(1);
            System.out.println("This is the content of 1st database: " + rowCount + " : " + Customer.toString());
            count += 1;
            rowCount += 1;
            tags.add(rs.getString("UserID"));
   while (rs2.next())
                {
String Customer2 = rs2.getString(1);
tags2.add(rs2.getString("CUserID"));
 }

        }
        for (String e : tags2)
        {
            System.out.println("This is the second database e.... " + e);
        }
        if (tags.equals(tags2))
        {
            System.out.println("Hi ");
            System.out.println("Content is same...");
            return true;
        }
        else
        {
            System.out.println("Content is not same and check properly....... " );
        }
    }
    catch (Exception e)
    {

        System.out.println("It is false...");
        System.out.println(e.toString());
    }
    return false;
}


       

Even i tried this

Set<String> ad = new HashSet<String>(tags);
Set<String> bd = new HashSet<String>(tags2);
ad.removeAll(bd);

But it result in false or true. Is there anyway that i can print the difference between two lists? Also any efficient way to compare different columns of two different tables in the 2 different databases.

Answer 1

The more convenient way to do this is by having an Orignal table in your single database and make another table with similar characteristics and then compare them but for your scenario.

The easy way to do this is by making two separate connection to your 2 databases in single method like this:

    public boolean isSame(){
        try{
           Connection con = DriverManager.getConnection(URL, userName, pass);
           Connection con2 = DriverManager.getConnection(URL, userName, pass);
           Statement stmt = con.createStatement();
           Statement stmt2 = con2.createStatement();
           ResultSet rs = stmt.executeQuery(SQL_Statement);
           ResultSet rs2 = stmt2.executeQuery(SQL_Statement);
              int count=0;
              int rowCount= 0;
              while (rs.next())
              {
                 while(rs2.next()){
                      String Customer = rs.getString(1);
                      String Customer2 = rs2.getString(1);
                      if(Customer.equals(Customer2)){
                          return true;
                      }
                 }
            }
       } catch(Exception e){
            System.out.println(e.toString())
     }
     return false; 
}

Con and Con2 will be two separate connections for your database and then you can create separate statement and result set for them. In getting the values you will get the required one's and compare them and can use && operator to compare many at once which will solve your problem.

Answer 2

You could structure your code a bit better. For example by breaking down what you are trying to achieve into logical steps. It works better than trial-and-error development:

1. Read the results from both databases, ordered by UserID
2. Compare the row and column counts of both results and exit with failure if they differ
3. Iterate over both results simultaneously until there is a difference, or the end of both lists is reached.
4. Within the iteration, take the next row from each result
5. Iterate over the columns of both rows
6. Compare the two column values and exit with failure if they are not equal
7. On reaching the end of the result iteration, exit with success

Something like this (untested):

class DatabaseComparisonUtility {

    private ResultSet resultSet1;
    private ResultSet resultSet2;
    private int columnCount;
    private boolean equals;

    public boolean areSame(Database db1, Database db2, String query) throws Exception {
       
        equals = true;

        readResultSets(db1, db2, query);

        checkDimensions();

        while (equals && resultSet1.next() && resultSet2.next()) {
            compareRows();
        }

        return equals;
    }

    private void readResultSets(Database db1, Database db2, String query) throws Exception  {
        resultSet1 = readResultSet(db1, query);
        resultSet2 = readResultSet(db2, query);
    }

    private ResultSet readResultSet(Database db, String query) throws Exception  {
        Connection conn = DriverManager.getConnection(db.getUrl(), db.getUserName(), db.getPassword());
        Statement stmt = conn.createStatement();
        return stmt.executeQuery(query);
    }

    private void checkDimensions() {

        columnCount = getColumnCount(resultSet1):
        equals = equals && (columnCount == getColumnCount(resultSet2));

        int rowCount = getRowCount(resultSet1);
        equals = equals && (rowCount == getRowCount(resultSet2));
    }

    private int getColumnCount(ResultSet resultSet) {
         return resultSet.getMetaData().getColumnCount();
    }

    private int getRowCount(ResultSet resultSet) {
         resultSet.last();
         int rowCount = resultSet.getRow();
         resultSet.first();
         return rowCount;
    }

    private void compareRows() {
        for(int column = 1; column <= columCount; column++) {                
            equals = equals && equalColumns(column);
        }
    }

    private boolean equalColumns(int column) {       
        String value1 = resultSet1.getString(column);
        String value2 = resultSet2.getString(column);
        equals = equals && (value1.equals(value2));
    }
}

I am assuming here that it is ok to compare the String representation of your column values, which should be ok for most cases.

The statements like equals = equals && (rowCount == getRowCount(resultSet2)) are making sure the existing value of equals is not overwritten. To assure that once equals becomes false, subsequent checks will not make it true again.

The part rowCount == getRowCount(resultSet2) is a boolean expression which evaluates to true if the two rowCounts are equal and false otherwise.

You need to create the Database class yourself, containing the fields needed to connect to the database (url, userName, password).

The SQL query needs to sort the rows by UserId, so:

"SELECT * FROM Table_All_Customers ORDER BY UserID"

Otherwise the order of the results is unspecified and comparing them sequentially might fail.