GeeCON Prague 2014*
The moose likes JDBC and the fly likes Compare result sets from different database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Compare result sets from different database" Watch "Compare result sets from different database" New topic
Author

Compare result sets from different database

kalyan chakravarthi kotamarthi
Greenhorn

Joined: Jan 09, 2011
Posts: 5
Hi all,

I have two development databases
1.QA database
2.Dev database
I need to compare certain tables from the both databases and see the results, if anything is missing. is there any way to do this.

Thanks,
kalyan.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

Yes, there is.

Did you have a particular question about that?
kalyan chakravarthi kotamarthi
Greenhorn

Joined: Jan 09, 2011
Posts: 5
Can you explain the solution?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

It's what you said, basically. You read the tables from the two databases, compare the data you read in, and report on records which are missing. Did you have a specific question about that?

But I asked you that already. Let's try something else: what have you done so far?
kalyan chakravarthi kotamarthi
Greenhorn

Joined: Jan 09, 2011
Posts: 5
i can query the database but i want to compare the result sets dynamically with out storing the data in list.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30580
    
154

Your choices are to export the two tables as csv and do a diff. Or to query both and loop through simultaneously so you aren't storing data.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

Whenever I see the word "dynamically" I assume it to mean "In a complicated way which I don't understand". Nobody has ever contradicted me and provided a different meaning, either. So that's what I'm going to assume here.

What you do is this: Read the rows in sequence from the two databases and compare them. "In sequence" is important because if you read them in a random order it's going to be much more difficult. So they had better have keys. Here's the process:

(1) Read the first row from each database. (Call the databases X and Y.)

(2) If key X = key Y then compare the two rows and report differences; read the next row from both databases; repeat from (2).

(3) If key X < key Y then report "Key X missing from Y"; read the next row from X; repeat from (2).

(4) If key X > key Y then report "Key Y missing from X"; read the next row from Y; repeat from (2).

Actually this is a simplified version because (as you should have noticed) it doesn't tell you how to handle end-of-file in the two databases. But really, this is such an easy-to-understand process that you should be able to add on that little detail.
Hebert Coelho
Ranch Hand

Joined: Jul 14, 2010
Posts: 754

You could do in a simple way like this:



After the select in both databases you could do:


I know the code is not good, but it will give an idea! =D


[uaiHebert.com] [Full WebApplication JSF EJB JPA JAAS with source code to download] One Table Per SubClass [Web/JSF]
 
GeeCON Prague 2014
 
subject: Compare result sets from different database