Spring pageable sort on other entity's nullable attribute

Question

I came across the following problem... I have three entities:

@Entity
class Contract {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @ManyToOne
    private Employee employee;
}

@Entity
class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @ManyToOne
    private Department department;
}

@Entity
class Department {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String name;
}

and method using Specification for fetching information about Contracts:

Page<Contract> getContracts(Integer employeeId, Pageable pageable) {
    return contractRepository.findAll(createSpecification(employeeId), pageable);
}

Specification<Contract> createSpecification(Integer employeeId) {
    return Specification.where(equalEmployeeId(employeeId));
}

Specification<Contract> equalEmployeeId(Integer employeeId) {
        return (root, criteriaQuery, criteriaBuilder) -> {
            if (Objects.nonNull(employeeId)) {
                Join<Contract, Employee> joinParent = root.join("employee");
                return criteriaBuilder.equal(joinParent.get("id"), employeeId);
            } else {
                return criteriaBuilder.isTrue(criteriaBuilder.literal(true));
            }
        };
    }

and now, my application gives the possibility to sort Contract entities by Department name, so there comes Pageable object with sort parameter set to employee.department.name. And the problem arises when Employee object has department parameter set to null... E.g., if all Employee objects have department parameter set to null, then empty collection is returned. What can I do to change this behavior to have all the Contract entities returned, regardless Employee's department is null or not?

I have already tried different things: adding fetch join to the Specification, setting spring.jpa.properties.hibernate.order_by.default_null_ordering to last, but nothing helped.

Thank you in advance for any help!

PS: Please don't advise me to get rid of Specifications, etc. - the code I provided is simplified for the sake of readability. In reality, much more attributes are there and using Specifications for filtering is the most convenient approach.

what kind of join do you use for employee.department? did you already try left join? if you did not joined it "manually" you should add left join manually when there is a sort on employee.department.name and you don't want to filter out employees without department — tremendous7, May 20 '21 at 00:47
Yes, I have already tried it, unfortunately with no success. What I suspect is that there is a problem with getting department.name if department is null... — wojtek1902, May 21 '21 at 10:01
after you manually join department as d the sort should be on d.name not on employee.department.name — tremendous7, May 21 '21 at 10:17
what version of hibernate are you using? is it between 5.4.4 and 5.4.8? if it is the problem might be this one https://hibernate.atlassian.net/browse/HHH-13670 — tremendous7, May 21 '21 at 10:23
Can you update the question to add the code where you setup the Pageable to make a sorted query request? I believe there may be a way to include a Null-Handling directive directly in the criteria you're submitting (i.e. Sort.by(new Sort.Order(Sort.Direction.DESC, "lastName", Sort.NullHandling.NULLS_FIRST))) — Atmas, May 21 '21 at 14:47
That's really strange. I implement simple test with your data model and mysql. And I get result even for employees who hasn't department. Here is sql that generates hibernate. — Maxim Tulupov, May 21 '21 at 14:53
Hibernate: select contract0_.id as id1_0_, contract0_.employee_id as employee2_0_ from contract contract0_ inner join employee employee1_ on contract0_.employee_id=employee1_.id left outer join department department2_ on employee1_.department_id=department2_.id where employee1_.id=7 order by department2_.name asc limit ? — Maxim Tulupov, May 21 '21 at 14:54
Hibernate: select employee0_.id as id1_2_0_, employee0_.department_id as departme2_2_0_, department1_.id as id1_1_1_, department1_.name as name2_1_1_ from employee employee0_ left outer join department department1_ on employee0_.department_id=department1_.id where employee0_.id=? Sql looks correct. — Maxim Tulupov, May 21 '21 at 14:54
Maybe not for your question description, but please, consider review [this Github issue](https://github.com/spring-projects/spring-data-jpa/issues/1882), it largely resembles your problem, it may be related. — jccampanero, May 25 '21 at 17:50

score 4 · Answer 1 · answered May 25 '21 at 03:28

Based on what you want to return all Contract entities if Department is null.

Specification<Contract> equalEmployeeId(Integer employeeId) {
    return (root, criteriaQuery, criteriaBuilder) -> {
        Join<Contract, Employee> joinParent = root.join("employee");
        if (Objects.nonNull(employeeId)) {
            return criteriaBuilder.equal(joinParent.get("id"), employeeId);
        } else {
            return criteriaBuilder.isTrue(joinParent.get("department").isNull());
        }
    };
}

score 1 · Answer 2 · answered May 23 '21 at 03:08

1

You need to implement left join for fetching null conent:

Join<Contract, Employee> joinParent = root.join("employee",JoinType.LEFT);

Similarly for department

answered May 23 '21 at 03:08

tuhin47

5,172
4
19
29

score 1 · Accepted Answer · answered May 26 '21 at 12:22

1

Do you know what helped? Updating Spring Boot to 2.5.0... Now it works as expected... (thank you @tremendous7 for an inspiration!) However, I suppose that answer provided by @jonathan-johx might work with older versions...

answered May 26 '21 at 12:22

wojtek1902

503
2
10
25

Spring pageable sort on other entity's nullable attribute

3 Answers3