wood burning stoves 2.0*
The moose likes JDBC and the fly likes JDBC compare the content of two tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC compare the content of two tables" Watch "JDBC compare the content of two tables" New topic
Author

JDBC compare the content of two tables

George Lin
Greenhorn

Joined: Feb 22, 2006
Posts: 28
Hello everyone,


I am using JDBC to compare the content of two database tables -- writing a general function and two tables are input. The two tables have various types of columns, like VARCHAR, BLOB, INT, FLOAT, etc.

I want to save my time to write various statements to get values by different types and compare them, for example, for VARCHAR, I need to use getString and compare the content by String compare function, and for INT column, I need to use getInt and compare with Integer compare function.

I am wondering whether there are any smart way to implement in an uniformed way so that I do not need to write various switches according to column type.


thanks in advance,
George
Remko Strating
Ranch Hand

Joined: Dec 28, 2006
Posts: 893
I don't know what you exactly mean but have a look at the following url

ResultSet MetaData

With this class you can retrieve metadata of your resultset and this you could use for writing a generic method.


Remko (My website)
SCJP 1.5, SCWCD 1.4, SCDJWS 1.4, SCBCD 1.5, ITIL(Manager), Prince2(Practitioner), Reading/ gaining experience for SCEA,
George Lin
Greenhorn

Joined: Feb 22, 2006
Posts: 28
Thanks Remko,


Originally posted by Remko Strating:
I don't know what you exactly mean but have a look at the following url

ResultSet MetaData

With this class you can retrieve metadata of your resultset and this you could use for writing a generic method.


I think in your method, I still need to check the type of columns and write a large switch (for each column type, I need to invoke the type's compare method), right?

Are there any unified way to implement without a large swithc code segment?


regards,
George
Bert Bates
author
Sheriff

Joined: Oct 14, 2002
Posts: 8806
    
    5
let's slide this over to the jdbc forum
[ August 09, 2007: Message edited by: Bert Bates ]

Spot false dilemmas now, ask me how!
(If you're not on the edge, you're taking up too much room.)
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
I've done this very thing. I made a Key object that holds an array of values and implements Comparable. You tell it which columns to use for keys, and it assumes that both result sets are in ascending order.

Just looking at it, I used getString() for all columns. I wonder how I didn't get burned by "10".compareTo("2"). Maybe I just got lucky? In the reporting phase I check to see if the column type should be right justified, so I do line up 2 and 10 correctly there.

If you figure out which columns won't work as String and get a good comparable key, the basic algorithm I drag out often is:

Note that a key at end always compares as larger. For my purposes, when the keys match, I optionally do another comparison on all the rest of the columns.

Any of that sound helpful?
[ August 09, 2007: Message edited by: Stan James ]

A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Remko Strating
Ranch Hand

Joined: Dec 28, 2006
Posts: 893
If you want to compare if the rows are equal. You could just use getString() for every field and then add the values of the different fields to one string and compare this string with an another row-string.

I've tested this with a simple table and query and it worked. For a good working you have to order your table first.

But what database are you using. It could be that your database has a better solution for comparing two tables.
George Lin
Greenhorn

Joined: Feb 22, 2006
Posts: 28
Thanks Remko,


Originally posted by Remko Strating:
If you want to compare if the rows are equal. You could just use getString() for every field and then add the values of the different fields to one string and compare this string with an another row-string.

I've tested this with a simple table and query and it worked. For a good working you have to order your table first.

But what database are you using. It could be that your database has a better solution for comparing two tables.


I am wondering whether getString could work for all types of columns of database?


regards,
George
George Lin
Greenhorn

Joined: Feb 22, 2006
Posts: 28
Thanks Stan,


Originally posted by Stan James:
I've done this very thing. I made a Key object that holds an array of values and implements Comparable. You tell it which columns to use for keys, and it assumes that both result sets are in ascending order.

Just looking at it, I used getString() for all columns. I wonder how I didn't get burned by "10".compareTo("2"). Maybe I just got lucky? In the reporting phase I check to see if the column type should be right justified, so I do line up 2 and 10 correctly there.

If you figure out which columns won't work as String and get a good comparable key, the basic algorithm I drag out often is:

Note that a key at end always compares as larger. For my purposes, when the keys match, I optionally do another comparison on all the rest of the columns.

Any of that sound helpful?

[ August 09, 2007: Message edited by: Stan James ]


I am wondering whether getString could work for all types of columns of database, like BLOB, BIT?


regards,
George
Remko Strating
Ranch Hand

Joined: Dec 28, 2006
Posts: 893
I already told you I did a simple test and it worked. I could get a string from an integer,bit,blob field.

Why don't you try it yourself.
George Lin
Greenhorn

Joined: Feb 22, 2006
Posts: 28
Thanks Remko,

Originally posted by Remko Strating:
I already told you I did a simple test and it worked. I could get a string from an integer,bit,blob field.

Why don't you try it yourself.


I have tried and it works! Cool!


regards,
George
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
Strings are cheap and easy, but be careful about the numbers. The example I gave

"10".compareTo("2")

doesn't give the result you want if you care about greater than and less than. If you only care for equals or not equals it might get you by, but that's not enough to run the algorithm I suggested to detect mis-matched keys, ie missing or extra rows.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC compare the content of two tables
 
Similar Threads
Storing enums in database
Hibernate - Creating .hbm file for link table
Mapping Varchar to Number
getString, nulls, and try
Array as input to stored procedure.