Recently I’ve encountered a tricky and hard to spot problem with JPA2, joins with null values and ordering. But finally I managed to solve it and as I couldn’t find anything related to this issue in Google, I think it is worth to share our working solution.

Problem occurs when we try to sort list of users using something like “from User u order by u.country.name desc” and sometimes country is null. In such case entities without country are simply disappearing from our result list. Longer description of this issue and how we solved it can be read below.

Problem

Let’s say we have three entities: User, City and Country. User has a City and also has a Country. City and Country are not connected in any way to make this example less complicated.

What we want to achieve is to load all users and display their details in the datatable. We want to show user name, surname and also some data from city (its name) and  country (name as well and population). User can fill this data in his profile page but it is not mandatory so we might have user without city and/or country in our database. Of course this datatable on the web page should be sortable using all displayed properties. For  such functionality we need a service class:

and enum defining available order by options

So far everything looks just fine. So let’s write some tests. As we are using JEE6, tests will be written using Arquillian and utility class AbstractDBTest from softwaremill-common. More about this class and how to use it can be found in Adam Warski’s blog. And believe me, it makes testing with Arquillian so, so much easier.

Our test class looks like that:

These both tests will pass. But let’s add another one with order by country name:

And when we re-run, we will see that this one is failing with error saying that expected number elements was 10 but actual is 8.

Ok, so we have a problem now. We have a query that should work just fine but apparently JPA omits entities with null property when we try to sort list using it.

Solution

To solve this problem we have to approach it from a different side. Instead of returning a list of users with countries and cities we could return a descriptor containing all the data we need to display in the database. So let’s create this class:

and then adjust method in UserService:

This code fragment needs longer explanation. So basically for each returned row we create new object, the UserDescriptor. It consists of all data we need to display in our datatable. But instead of writing “u.country.name, u.country.population” we type “country.name, country.population” and add proper joins to the query. Of course our SortFieldEnum needs little attention too as we must tune sort fields a bit to be in line with modified query.

After these steps we are ready to make our tests green. But when we re-run them we will see following error message:

 

So now we have 10 items in our list so there are no ommited ones but another problem appeared. Null values are at the beggining. If it is not a problem for you, this could be the end of this post. If you want the know how to solve this, keep reading.

Moving nulls to proper location

If we want to have nulls at the end of our result list, we must add new rule in order by clause. So our query needs some tweaking:

The first thing worth to notice is that we’ve added case when “ + sortField.getEntity() + ” is null then 2 else 1 end as nullOrderer. And we use nullOrderer as a first element in order by clause. Thanks to this, our records are sorted first by not null and then using requested field. Moreover as you can see, our sort enum was enhanced with new property, an entity, which is used to define on which object we want to check nullability. For fields from User entity it will be User alias u, for Country country and for City city. So now enum looks like that:

And when we run our failing test, everything looks just perfect and green. But to make sure that we didn’t omit anything, let’s add a few more testing methods:

Summary

So after some struggles we have working query returning proper results and sorting objects as client requested.  And what is more important, we were able to test it with Arquillian in an embedded Weld container.

All sources are available in my GitHub repository