wood burning stoves*
The moose likes JDBC and the fly likes comparing two ResultSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "comparing two ResultSet" Watch "comparing two ResultSet" New topic
Author

comparing two ResultSet

Ashu Bharadwaj
Greenhorn

Joined: Nov 16, 2010
Posts: 20
Hi,

I have two ResultSet from two different DBMS. I want to check if the entire row of the first ResultSet exists in the second ResultSet.
It could be anywhere in the second ResultSet. Number of rows and columns in second ResultSet may be more than the first one.

So, I want a class which will return true, if the one row of first ResultSet exists anywhere in the second ResultSet table.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

What you describe is a way to locate any record in a resultset based on column values. Short of iterating through all records and comparing the column values searching for the match I don't see another solution. However, I'd probably run a query searching just for the desired column values. Much better than bringing all of the table from the database to the client and iterating through it.

However, using either of the above to really compare two resultsets (ie. repeating the search for every record in another resultset) would be grossly inefficient, especially with large resultsets. It is really a bad idea.

The best way to compare resultsets, in my opinion, is to sort both ResultSets (using ORDER BY clause) by the same criteria, ideally by a primary key, if it is the same in both tables, or by all of the common columns of the two queries. Then it is possible to identify identical and missing records in one pass over the contents of the two resultsets (you'll go to next record in the resultset that contains the "lesser" of the two records, if the two records are equal, you'll go to next record in both resultsets).

Another way would be to bring the two databases together (eg. using a database link, if your database supports one) and doing all of the work using MINUS and UNION operators. This would be much less code to write, however the database links bring up some administration overhead.
Ashu Bharadwaj
Greenhorn

Joined: Nov 16, 2010
Posts: 20
Martin Vajsar wrote:What you describe is a way to locate any record in a resultset based on column values. Short of iterating through all records and comparing the column values searching for the match I don't see another solution. However, I'd probably run a query searching just for the desired column values. Much better than bringing all of the table from the database to the client and iterating through it.

However, using either of the above to really compare two resultsets (ie. repeating the search for every record in another resultset) would be grossly inefficient, especially with large resultsets. It is really a bad idea.

The best way to compare resultsets, in my opinion, is to sort both ResultSets (using ORDER BY clause) by the same criteria, ideally by a primary key, if it is the same in both tables, or by all of the common columns of the two queries. Then it is possible to identify identical and missing records in one pass over the contents of the two resultsets (you'll go to next record in the resultset that contains the "lesser" of the two records, if the two records are equal, you'll go to next record in both resultsets).

Another way would be to bring the two databases together (eg. using a database link, if your database supports one) and doing all of the work using MINUS and UNION operators. This would be much less code to write, however the database links bring up some administration overhead.


Thank you for your insight.
Could you please provide me pseudocode for iterating through records. The resultset is not too large, so we can iterate through the records.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Ashu Bharadwaj wrote:
Could you please provide me pseudocode for iterating through records. The resultset is not too large, so we can iterate through the records.

Very briefly:


The compareRS method compares values of columns that were used in the ORDER BY clause of the two recordsets in order in which they were specified. If the values in the first column are equal, compare values of the second one, till all columns are compared or a difference is found. The returning value should follow the standard Java convention.

The comparison must be done identically with the database. If your data may contain null values, handle them as the database does (ORDER BY usually allows to specify NULLS FIRST or NULLS LAST, if not, determine the convention of your database and use it). If you process strings that may contain international characters, the best bet is to use non-lexicographical ordering in the database. It is usually the default, however it might also be affected by database connection/session settings.
Ashu Bharadwaj
Greenhorn

Joined: Nov 16, 2010
Posts: 20
Martin Vajsar wrote:
Ashu Bharadwaj wrote:
Could you please provide me pseudocode for iterating through records. The resultset is not too large, so we can iterate through the records.

Very briefly:


The compareRS method compares values of columns that were used in the ORDER BY clause of the two recordsets in order in which they were specified. If the values in the first column are equal, compare values of the second one, till all columns are compared or a difference is found. The returning value should follow the standard Java convention.

The comparison must be done identically with the database. If your data may contain null values, handle them as the database does (ORDER BY usually allows to specify NULLS FIRST or NULLS LAST, if not, determine the convention of your database and use it). If you process strings that may contain international characters, the best bet is to use non-lexicographical ordering in the database. It is usually the default, however it might also be affected by database connection/session settings.


Could you please give code for compareRS. Thanks
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

Ashu Bharadwaj wrote:Could you please give code for compareRS. Thanks

The ranch is NotACodeMill. Martin has put a lot of effort in showing you the logic, you could ShowSomeEffort too and try to make a comparison method yourself.


[My Blog]
All roads lead to JavaRanch
Ashu Bharadwaj
Greenhorn

Joined: Nov 16, 2010
Posts: 20
Martin Vajsar wrote:
Ashu Bharadwaj wrote:
Could you please provide me pseudocode for iterating through records. The resultset is not too large, so we can iterate through the records.

Very briefly:


The compareRS method compares values of columns that were used in the ORDER BY clause of the two recordsets in order in which they were specified. If the values in the first column are equal, compare values of the second one, till all columns are compared or a difference is found. The returning value should follow the standard Java convention.

The comparison must be done identically with the database. If your data may contain null values, handle them as the database does (ORDER BY usually allows to specify NULLS FIRST or NULLS LAST, if not, determine the convention of your database and use it). If you process strings that may contain international characters, the best bet is to use non-lexicographical ordering in the database. It is usually the default, however it might also be affected by database connection/session settings.



I wrote this Class for comparing rows. What changes do I need to make to see if the if row of rs1 is subset of rs2, i.e., exists anywhere in rs2.

Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Hi Ashu Bharadwaj , rather than comparing the ResultSet directly , What you can do is take the ResultSet data into ArrayList and compare both the ArrayLists .




Save India From Corruption - Anna Hazare.
Ashu Bharadwaj
Greenhorn

Joined: Nov 16, 2010
Posts: 20
Ravi Kiran Va wrote:Hi Ashu Bharadwaj , rather than comparing the ResultSet directly , What you can do is take the ResultSet data into ArrayList and compare both the ArrayLists .



[Thanks Ravi, could you please tell me the advantages that I will have in using ArraLists. Please let me know more about the implementation.
Thanks]
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Ashu,

first of all, please restate your goal. The code I've written searches for rows that exist only in the first, only in the second or in both resultsets, processing all rows from both resultsets. This is what I understand by "comparing resultsets". If you only want to see whether a single row from one resultset is present in the other one, please see my first response.

Secondly - if you want to create a generic solution, that will be lots and lots of work. I've assumed that the column names and types are known. Since I've never need to process a resultset generically, I can't really effectively help you with this approach. If you want some help from me, please start with a non-generic example, where the query is known and the resultset contains a known set of columns of various types. I can help you make such example work, but to turn it into generic algorithm will be then up to you.

And lastly, you didn't follow the template I've already given you. The logic in your solution will not work. See how I have arranged calls to the resultsets' next() methods and try to figure out why is this so.
Ashu Bharadwaj
Greenhorn

Joined: Nov 16, 2010
Posts: 20
Martin Vajsar wrote:Ashu,

first of all, please restate your goal. The code I've written searches for rows that exist only in the first, only in the second or in both resultsets, processing all rows from both resultsets. This is what I understand by "comparing resultsets". If you only want to see whether a single row from one resultset is present in the other one, please see my first response.

Secondly - if you want to create a generic solution, that will be lots and lots of work. I've assumed that the column names and types are known. Since I've never need to process a resultset generically, I can't really effectively help you with this approach. If you want some help from me, please start with a non-generic example, where the query is known and the resultset contains a known set of columns of various types. I can help you make such example work, but to turn it into generic algorithm will be then up to you.

And lastly, you didn't follow the template I've already given you. The logic in your solution will not work. See how I have arranged calls to the resultsets' next() methods and try to figure out why is this so.

[/Hi Martin,
I wanted this class as I had to migrate rows from one DBMS to other DBMS. The first one is Oracle 10G and the otherone is Oracle 11G. Befrore I migrate rows I need to check if the rows already exist in the new DBMS or not. We are moving to 11G but we are using both 10G and 11G for inserting app data for some time.

Now, number of columns in 11G may be equal or more than 10G.

]
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Ashu,

in this case, I'd consider using database tools to do it. It would certainly be much less work to do, even if it was just one table to handle. These approaches seem to be viable:

1) Export table(s) from source to destination databases using EXP or EXPDP and import them into target database using IMP or IMPDP, probably into another schema. Once both versions of the table are in one database, use the MERGE SQL command to move the data all in one go.

2) Establish database link between the two databases. Then use the MERGE command as in the above example.

3) Use SQL COPY command to move the data between databases. This seems to be very easy option from the administrative point of view but I have no experiences with it altogether. Also, not all data types are supported by the COPY command (Oracle stopped supporting it after version 8).

Let me know whether these options seem viable to you. The links seem most promising, as it seems that you'll want to run this process periodically.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: comparing two ResultSet
 
Similar Threads
How To compare two excel sheets using java
ResultSet
ResultSet
PreparedStatement (displaying records)
Looks right to me