Comparing resultsets in jdbc

Question

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

Answer 1

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++;
    }

Answer 2

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
}