Java – can I check the boundary data by sorting the list in the body of the test case?

I'm testing the results of a query The table storing the results has the following structure:

Id  SomeValue  Date        Hour
-----------------------------------
1   foo1       2015-01-01  700
2   foo2       2015-01-01  800
3   foo3       2015-01-01  900
...
18  foo18      2015-01-01  2400
19  bar1       2015-01-02  100
20  bar2       2015-01-02  200
...
41  bar23      2015-01-02  2300
42  bar24      2015-01-02  2400
43  baz1       2015-01-03  100
44  baz2       2015-01-03  200
(and on...)

And query the receiving parameters to search according to the following date and time columns:

SELECT *
FROM table
WHERE
    (date,hour) >= (:dateFrom,:hourFrom)
    AND (date,hour) <= (:dateTo,:hourTo)
-- there's no ORDER BY clause in the query

For example, if I use the following values:

>Datefrom: '2015-01-01' > hours from 700 > dateto: '2015-01-03' > hourto: 600

This query will return all rows with the value of date between 2015-01-01 and 2015-01-03. The value of hour is higher than or equal to 700 only when date = 2015-01-01, and the value of hour is less than or equal to 600 when date = 2015-01-03 In this example, all rows with date = 2015-01-02 will be retrieved from the data source

I retrieve the execution results of the query in the list To evaluate the results, I use parameter values to check whether the data in the list matches them I'm using a method to check whether the date of the element is between datefrom and dateto, but I want to know how to test the values of hourfrom and hourto I have the following ideas:

>Start checking that the date value is equal to the minimum value of the hour on the element of my datefrom parameter and whether the value is equal to hourfrom Perform a similar operation for hourto, but use the maximum value of those rows whose value of date is equal to the value of dateto parameter. > Sort the list in my test method by date and hour, and then check the first and last elements in the list The sorting method used will be obtained from the programming language I use

Which choice is right? If not, what is the best strategy? I use java to write tests, but this problem focuses more on how to write test methods than technology / framework In addition, I can't modify the query to add an order by Clause (this will ease a lot of work, but it's not feasible)

I am concerned about best practices I'm thinking about sorting the data, so I'll assert the two elements, but I'm worried that if I have to test the comparator for sorting, because it may missort the list, my test will fail. At the same time, manually checking each element means using an if else statement for the fault. I don't know if it's a good practice

Solution

I can see that your main concern is to write unit tests with the simplest logic This will improve your confidence level. When the unit test reports success or failure, it does mean that the query returns good or bad results, rather than the logical error of the unit test you coded For you, the absolute best performance may not be important

If this is the case, I suggest you use the very direct option #1, you simply check each date / time, and once you encounter a date / time that is not within the minimum / maximum value, the unit test fails However, I will adjust the method of comparing 2 groups of dates / times in the following ways to keep the comparison logic very simple:

Connect and format each date / time into the following string format: yyyy-mm-dd HHMM (for example: 2015-01-01 0700) In other words, your string is formatted with the required zero padding so that it always has a length of 15 Following this format has very convenient properties if you use the built-in string CompareTo (comparison 2)) method, which will accurately compare your dates

This allows you to keep the logic very simple and easy to read Here is an example, you can see (you didn't specify it, so I assume your date is a string and the time is a number, but you can adjust it according to your actual type)

// starting boundary values
String dateFrom = "2015-01-01";
int hourFrom = 700;
String dateTo = "2015-01-03";
int hourTo = 600;

String formatString = "%s %04d"; // adjust as necessary.

String fromDateTime = String.format(formatString,dateFrom,hourFrom);
String toDateTime = String.format(formatString,dateTo,hourTo);

// execute query here

while (rs.next()) {
    String dateTime = String.format(formatString,rs.getString("date"),rs.getInt("hour"));

    if (fromDateTime.compareTo(dateTime) > 0 || toDateTime.compareTo(dateTime) < 0) {
        throw new Exception("fail unit test");
    }
}