Unit testing ordered SQL queries

user_636333 0
Viewed 295

I am writing unit tests on queries where the results must be returned in a specific sorted order. We are using the Java H2 in memory database.

I want to make sure that the "order by" statements are being executed correctly. I'm not trying test H2, I accept it's correct, I'm trying to test that I have issued correct the SQL statement to return the correctly sorted results.

I'm thinking something like a way to tell H2 to randomise the results each time before sorting or perhaps some kind of SQL insertion that inserts rows in a random order to the tables each time. Hopefully this tests the ordering is correct. I know it's no guarantee because the database determines it's own internal order but thought I'd throw it in as an idea I was thinking about.

h2 junit unit-testing java
2 Answers

I think you shouldn't randomize the H2 results. I would suggest you to write two or three tests with same queries and different order by. In this case you ensure that different order parameters leads to results in correct sorted order.

user_110277000

you need to insert only two rows that must not change (don't insert random data). For example, if you have a User entity with the following columns: id, name, surname.

You insert 2 rows:


#id# name # surname #
#####################
#1# john  # doe     #
#2# eva   # nondoe  #

And for example, if you are interested in the name column then you write a test like this.

//BUILD
insertData();

//OPERATE
List<User> result = selectAllDataByNameAsc();

//CHECK
assertThat(result.get(0).getName()).isEqualTo("eva");

This is enough. Two rows are enough to test your sorting query.

user_116092490
Related