user2024438
user2024438

Reputation:

How to compare the values of two resultset in java

I have two tables. And these tables have the same schema consisting of userid, username. I want to check is there any common username in table1 and table2.

 rs1 = statement.executeQuery("select username from table1")
 rs2 = statement.executeQuery("select username from table2")

My logic is:

Is there a way to achieve this in java... Please anyone help me ... Thanks...

Upvotes: 6

Views: 18121

Answers (6)

Pravesh Kaushik
Pravesh Kaushik

Reputation: 1

This answer is already given by Akash5288 and Edited by Pavel Smirnov. And this worked for me like magic. I don't have access to Like or Comment on the original answer, that's why I am re-posting it. Thanks a lot. Just one edit from my side: rs2.beforeFirst(); will work better than rs2.first();.

I am giving an example to solve this:

rs1 = statement.executeQuery("select username from table1")
rs2 = statement.executeQuery("select username from table2")

while(rs1.next()) {
// Compare till rs1 reachs its last record.
  while(rs2.next()) {
     if() {
       // Do your code here...
     }
  }

// this will move resultSet cursor to the first position.
rs2.first();
}

Upvotes: -1

Akash5288
Akash5288

Reputation: 1945

I am giving an example to solve this:

rs1 = statement.executeQuery("select username from table1")
rs2 = statement.executeQuery("select username from table2")

while(rs1.next()) {
// Compare till rs1 reachs its last record.
  while(rs2.next()) {
     if() {
       // Do your code here...
     }
  }

// this will move resultSet cursor to the first position.
rs2.first();
}

Upvotes: 0

vishal ajwani
vishal ajwani

Reputation: 1

One way could be:-

String query = "(" + query1 +") intersect ("+ query2 + ")";

Where in the intersect operation will anyway give you the common columns.

P.S. :- I know this question is old, but it might help somebody.

Upvotes: 0

1218985
1218985

Reputation: 8032

You can either solve it through SQL statement IN and NOT IN or you can try something like this:

public boolean compareResultSets(ResultSet resultSet1, ResultSet resultSet2) throws SQLException{
        while (resultSet1.next()) {
            resultSet2.next();
            ResultSetMetaData resultSetMetaData = resultSet1.getMetaData();
            int count = resultSetMetaData.getColumnCount();
            for (int i = 1; i <= count; i++) {
                if (!resultSet1.getObject(i).equals(resultSet2.getObject(i))) {
                    return false;
                }
            }
        }
        return true;
    }

Upvotes: 1

Khaled.K
Khaled.K

Reputation: 5960

Pseudo-Code:

if ( A.type = B.type )
{
    PRINT same type

    if ( A.format = B.format )
    {
        PRINT same format

        if ( A.value = B.value )
        {
            PRINT same value
        }
        else
        {
            PRINT different value
        }
    }
    else
    {
        PRINT different format
    }
}
else
{
    PRINT different type
}

Upvotes: 0

NPE
NPE

Reputation: 500883

I would use a single SQL statement:

select table1.username from table1, table2 where table1.username = table2.username

This will only return usernames that appear in both tables, so no post-processing will be needed.

This construct is called an inner join. If you also want to identify usernames that are unique to table1 and/or table2, you could use an outer join.

Upvotes: 4

Related Questions