CODEWITHSUNDEEP
javadatabase
edbras
edbras

Reputation: 4404

how to ensure java compareTo resturn same result as Database ORDER BY?

In unit tests I verify the result from a Db query that contains an "ORDER BY DESC" part and return String values.

To be clear: I check that the query returns the expected result in the expected order

SQL query:

SELECT member.name FROM member ORDER BY member.name DESC

Test result:

MTH_TESTER_TAXER_W_HLPERS_AFcCmhecUo
MTH_TESTER_TAXER_wCfRUAQuzT

When verifying the result with java, I do:

foreach(String value : values) {
  if (previous != null) {
   assertTrue(value.compareToIgnoreCase(previous) <= 0);
  }  
  previous = value;
}

Which fails using the above DB result from the ORDER BY (descending order in above example). So basically the following in java fails but not in DB:

assertTrue("MTH_TESTER_TAXER_wCfRUAQuzT".compareToIgnoreCase("MTH_TESTER_TAXER_W_HLPERS_AFcCmhecUo") <= 0)

Why is the ORDER BY not compatible with the Java compareTo natural String ordering?

And how to make them compatible?

I use Postgresql 9.X on Windowsd 7, default installation.

Upvotes: 0

Views: 386

Answers (4)

Aaron Digulla
Aaron Digulla

Reputation: 328780

There are two possible solutions here:

  1. You don't rely on ORDER BY at all. You can use it but before you compare, you sort the actual output again in Java to make sure the order is always the same.

    Use this approach if you only need to make sure that the actual sort order of the query doesn't really matter, only the number and content of the elements.

  2. Instead of comparing each string individually, join the results into one multi-line string and then do a single assertEquals() with a constant string.

The second solution looks like this:

String actual = Joiner.on('\n').join(Ordering.natural().sortedCopy(values));
assertEquals(
     "MTH_TESTER_TAXER_W_HLPERS_AFcCmhecUo\n" +
     "MTH_TESTER_TAXER_wCfRUAQuzT",
     actual
);

The second test checks all values at once, allowing you to see with a single glance which values have changed, which are unexpected and which are missing.

It also allows you to safely test the ordering of results as returned by the database.

Upvotes: 0

tddmonkey
tddmonkey

Reputation: 21184

Are you trying to verify that your database query works correctly? If so, then i would first ensure I know exactly what is coming back from the database. There's no information in your code, but I would hope you're seeding the data before the test runs either through direct SQL or something like dbUnit.

Once you have this then you can do an exact comparison on the returned results in the test. I would avoid the test you currently have as it gives you no diagnostics about what is wrong when the test fails. When you have this in place your test can be something like:

List<String> expected = Arrays.asList("MTH_TESTER_TAXER_W_HLPERS_AFcCmhecUo", "MTH_TESTER_TAXER_wCfRUAQuzT");
List<String> actual = myCode.performQuery(); // <-- your method under test goes here
assertThat(expected, contains(actual));

This will ensure the collection you return has the same items in the same order as you specify and if something differs it will tell you exactly what.

Upvotes: 0

Don Roby
Don Roby

Reputation: 41145

If you change your sql to

SELECT member.name FROM member ORDER BY UPPER(member.name) DESC

your Java code will likely verify this order.

Alternatively, if you change your Java code to 

foreach(String value : values) {
  if (previous != null) {
   assertTrue(value.compareTo(previous) <= 0);
  }  
  previous = value;
}

it should verify the case-sensitive order being done by your current sql.

Upvotes: 0

SMA
SMA

Reputation: 37093

Try using string's CASE_INSENSITIVE_ORDER:

 String[] str = {"abc", "pqr", "zxt", "Zxy", "xyz"};
 Arrays.sort(str, String.CASE_INSENSITIVE_ORDER.reversed());
 System.out.println(Arrays.toString(str));

Upvotes: 2

Related Questions