CODEWITHSUNDEEP
javaspringspring-bootjpapageable
wojtek1902
wojtek1902

Reputation: 611

Spring pageable sort on other entity's nullable attribute

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.

Upvotes: 3

Views: 1150

Answers (3)

wojtek1902
wojtek1902

Reputation: 611

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...

Upvotes: 1

Jonathan JOhx
Jonathan JOhx

Reputation: 5968

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());
        }
    };
}

Upvotes: 4

tuhin47
tuhin47

Reputation: 6058

You need to implement left join for fetching null conent:

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

Similarly for department

Upvotes: 1

Related Questions