CODEWITHSUNDEEP
javaloopsjdbcresultset
Rajesh Vaddala
Rajesh Vaddala

Reputation: 43

Can I compare resultsets like this? I'm facing the below error

I have 2 ResultSets. 1st ResultSet contains the records from table1 from database1 and 2nd ResultSet contains the records from table2 from database2. I need a list of records from resultset1 which are not present in resultSet2. For this I wrote this logic but it is not working and throwing me the following error.

java.sql.SQLException: Invalid operation for read only resultset: deleteRow

if ( table1ResultSet != null )
  {
    while ( table1ResultSet.next() )
    {
      final String table1Record = table1ResultSet.getString( 1 );

      if ( table2ResultSet != null )
      {
        while ( table2ResultSet.next() )
        {
          final String table2Record = table2ResultSet.getString( 1 );

          if ( table1Record.toString().equalsIgnoreCase( table2Record.toString() ) )
          {
            table1ResultSet.deleteRow();

            break;
          }
        }
      }
    }


  }

  return table1ResultSet;

Upvotes: 2

Views: 50

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 220762

Your logic:

  1. Assumes the records come in some order (which may or may not be true, depending on your SQL)
  2. Consumes the entire result set 2 for each row of result set 1, which is unlikely your intent
  3. Deletes things, which is also not what you mentioned in the question

Your question can be implemented easily as such:

Set<String> list1 = new HashSet<>();
while (table1ResultSet.next())
    list1.add(table1ResultSet.getString(1).toLowerCase());

while (table2ResultSet.next())
    list1.remove(table2ResultSet.getString(1).toLowerCase());

System.out.println(list1);

This will print all the values (without duplicates) that are present in the first result set, but not in the second.

Upvotes: 1

Veselin Davidov
Veselin Davidov

Reputation: 7071

That exception says what the problem is - your result set doesn't support delete. In order to have updateable result set there are some requirements:

When you prepare statement did you make it with ResultSet.CONCUR_UPDATABLE?

A query can select from only a single table without any join operations. The query must select all non-nullable columns and all columns that do not have a default value. A query cannot use "SELECT * ". Cannot select derived columns or aggregates such as the SUM or MAX of a set of columns.

You might want to move the results sets into Java sets before working doing what you are doing though because using deleteRow will actually delete the row from the database (unless that's the expected result)

There is another problem with your code though. Even if delete works your code will fail on the second iteration of result set 1 because you never reset table2ResultSet and for the second iteration there won't be more results in table2resulset.

But on top of all that. Why would you go through all that hussle and get all that rows that you don't need instead of doing it with one single query like:

select * from table 1 where id not in select id from table 2

or 

delete from table 1 where id not in select id from table 2

if that's the goal

Upvotes: 1

Related Questions