6

In my java code i have two resultsets rs1 and rs2 obtained as follows :

rs1 = statement.executeQuery("select * from tableA")
rs2 = statement.executeQuery("select * from tableB")

Both the tables have the same schema consisting of field ID,Name and Address and i want to compare the two resultsets. Can i directly do rs1 == rs2 ?. If no how should i go about comparing the two resultsets ?. Some example would be really appreciated.

Thank You

Bhushan
  • 18,329
  • 31
  • 104
  • 137
Jim
  • 61
  • 1
  • 1
  • 2
  • You don't compare two object using `==` in Java. You should use `.equals()` instead. – Jonas Jul 05 '11 at 11:46
  • 1
    If you want to get difference between 2 tables then you should update your query like `select * from tableA except select * from tableB` instead of taking 2 resultsets and comparing them. – Harry Joy Jul 05 '11 at 11:48
  • possible duplicate of [Resultset comparison utilities](http://stackoverflow.com/questions/2688589/resultset-comparison-utilities) – dogbane Jul 05 '11 at 11:50

4 Answers4

5

This code checks all the columns of two resultsets, and also the number of rows must be equal in them.

    int col = 1;
    while (rs1.next() && rs2.next()) {
        final Object res1 = rs1.getObject(col);
        final Object res2 = rs2.getObject(col);
        // Check values
        if (!res1.equals(res2)) {
            throw new RuntimeException(String.format("%s and %s aren't equal at common position %d",
                res1, res2, col));
        }

        // rs1 and rs2 must reach last row in the same iteration
        if ((rs1.isLast() != rs2.isLast())) {
            throw new RuntimeException("The two ResultSets contains different number of columns!");
        }

        col++;
    }
gyorgyabraham
  • 2,550
  • 1
  • 28
  • 46
5

With JDBC, you will have to iterate over both ResultSet objects and compare every field in them.

If you can do it with SQL, then I'd try

select * from tableA
except -- or minus in oracle
select * from tableB

and

select * from tableB
except -- or minus in oracle
select * from tableA

Both should return an empty result

If using a library is an option for you, you could try jOOQ (I work for the company behind jOOQ). jOOQ wraps many useful features around JDBC. With jOOQ, you could run

Result<Record> r1 = create.fetch("select * from tableA");
Result<Record> r2 = create.fetch("select * from tableB");

or also:

r1 = create.fetch(rs1);
r2 = create.fetch(rs2);

And then

if (r1.equals(r2)) {
    // the results are equal
}
else {
    // the results are not equal
}
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
0

rs1 and rs2 wont be the same objects, so comparing them is rather senseless. I guess you want to compare the content of the resultsets.

You may get the table scheme from the metadata by rs1.getMetaData().

Xote
  • 21
  • 2
0

As rs1 & rs2 are different objects, they point to different memory location, hence comparison of rs1 & rs2 is not proper, rather you use, the code below :-

while (rs.next()&& rs1.next())
{
  int userId = rs.getInt("userId")
  int userId1 = rs1.getInt("userId")
  if(userId == userId1){
   // do some thing
  }

}
Gagan
  • 129
  • 2
  • 10