Member Avatar for George2

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

  • 3 Contributors
  • 8 Replies
  • 2K Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by George2

Dani AI

Generated

you can make this uniform without a giant switch by driving the comparison off ResultSetMetaData and pulling values with getObject(...) (or getObject(index, Class) where supported). That lets the driver return sensible Java types for each SQL column; you then normalize a few tricky cases and do a null-safe compare. ResultSetMetaData.getColumnClassName is handy to see what the driver will hand you. (docs.oracle.com)

Gotchas to normalize before comparing:

  • BigDecimal: equals is scale-sensitive; use compareTo(...) == 0. (docs.oracle.com)
  • byte[]/BLOB: compare with Arrays.equals.
  • CLOB/N(CLOB): read to a String and compare.
  • Dates/times: drivers may map to legacy java.sql.* or java.time.*; prefer getObject(i, LocalDate/LocalDateTime/OffsetDateTime.class) when possible. (docs.oracle.com)
  • Use Objects.equals(a, b) for everything else to handle nulls cleanly. (docs.oracle.com)

Example skeleton (position both result sets on the same row; sort queries by a stable key first):

boolean rowsEqual(ResultSet a, ResultSet b) throws SQLException, IOException {
  ResultSetMetaData md = a.getMetaData();
  int cols = md.getColumnCount();
  for (int i = 1; i <= cols; i++) {
    Object x = a.getObject(i);
    Object y = b.getObject(i);

    if (x instanceof java.math.BigDecimal && y instanceof java.math.BigDecimal) {
      if (((java.math.BigDecimal) x).compareTo((java.math.BigDecimal) y) != 0) return false;
    } else if (x instanceof byte[] && y instanceof byte[]) {
      if (!java.util.Arrays.equals((byte[]) x, (byte[]) y)) return false;
    } else {
      if (!java.util.Objects.equals(x, y)) return false;
    }
  }
  return true;
}

For large tables, consider pushing the diff to SQL first: use EXCEPT/MINUS (or NOT EXISTS anti-joins) to find missing rows by primary key, then inner-join on the key to find rows where any non-key column differs. This cuts Java work to only the mismatches. ’s pointer to getObject was the right direction; the normalization above makes it robust. (docs.oracle.com)

Recommended Answers

All 8 Replies

Member Avatar for jwenting

Yes and no...
Several things come to mind.

An enum with a lookup method by column type that returns a handler for that column type is the most obvious.
Use a Map of column type names to enum instances for that.

Member Avatar for George2

Thanks jwenting,


I am not going to compare the column types, but content of table -- rows.

Yes and no...
Several things come to mind.

An enum with a lookup method by column type that returns a handler for that column type is the most obvious.
Use a Map of column type names to enum instances for that.

The two tables are of the same schema -- same column names and column type.

I am interested in your above method. But I still need to program differently for different column types to write equals (or compareTo) method, right?

Are there any ways to implement in an unified coding way independent of column types?

regards,
George

Member Avatar for masijade

Edit: Nevermind, silly me, thinking of something completely different.

Member Avatar for jwenting

I am interested in your above method. But I still need to program differently for different column types to write equals (or compareTo) method, right?

Of course. How else are you going to get the data out of the resultset?

Member Avatar for George2

Hi masijade, any ideas?

Edit: Nevermind, silly me, thinking of something completely different.

regards,
George

Member Avatar for George2

Hi jwenting,

Of course. How else are you going to get the data out of the resultset?

I want to get the data from each column in a unified way and compare in a unified way. Any ideas?


regards,
George

Member Avatar for jwenting

the JDBC API has several options that may work.
getBytes(int) and getObject(int) on ResultSet come to mind.

Member Avatar for George2

Thanks jwenting,

the JDBC API has several options that may work.
getBytes(int) and getObject(int) on ResultSet come to mind.

I want to use getObject method to get the value of each column,
then use equals method of java.lang.Object to compare two columns' values. Is that method workable?

I have a quick question, I think if I use getObject to get a column value
from a table, for example, an Integer column, then when using equals method to compare, I think I am using equals method from java.lang.Integer, other than java.lang.Object, right?

Object int_column_value1 = getObject (...);
Object int_column_value2 = getObject (...);
// to check
int_column_value1.equals (int_column_value2);


regards,
George

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.