aspose file tools
The moose likes JDBC and Relational Databases 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


Win a copy of OCA Java SE 8 Programmer I Study Guide 1Z0-808 this week in the OCAJP forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
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: 19100
    
    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: 19100
    
    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: 31394
    
184

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.


[OCA 8 book] [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: 19100
    
    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]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Compare result sets from different database