File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC compare the content of two tables

 
George Lin
Greenhorn
Posts: 28
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 893
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
George Lin
Greenhorn
Posts: 28
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8898
5
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
let's slide this over to the jdbc forum
[ August 09, 2007: Message edited by: Bert Bates ]
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Remko Strating
Ranch Hand
Posts: 893
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 28
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 28
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 893
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 28
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8791
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic