javaspring-boothibernatespring-data-jpacriteria-api

Spring Boot Specifications root's fetch function for join columns effects on performance


Suppose I have a Spring Boot Project that is structured as shown below, where I am storing Campus details, which has a lot of employees. Additionally many employees are also a part of one department only i.e. Department is a Master data Entity.

public class Campus {
    @Id
    private String campusNo;

    @JsonManagedReference
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "employee")
    private List<Employee>employeeList = new ArrayList<>();
}

public class Employee {
    @Id
    private String employeeCode;

    @JsonBackReference
    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "campusNo", nullable = false)
    private Campus campus;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(referencedColumnName = "departMentNo")
    private Department department;
}

public class Department {
    @Id
    private String departMentNo;
}

Now, if I want to use Specifications in my project to create configurable reports as shown below, which of the below two "fetch()" statements (marked as Option 1 and Option 2) would be helpful for performance(reducing the number of "select" statements) and why? Both of them work. I'm only interested in knowing the merits and demerits (if any), of each statement.

public class CampusSpecification implements Specification<Campus> {
    private SearchCriteria searchCriteria;

    @Override
    public Predicate toPredicate(Root<Campus> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
        root.fetch("employeeList").fetch("department"); // Option 1
        root.fetch("employeeList"); // Option 2
        return criteriaBuilder.in(root.get("employeeList").get("department").get("departMentNo")).value(searchCriteria.getValue());
    }
}

Solution

  • As a general note, I wouldn't worry about performance prematurely unless you notice that requests are taking quite long. I personally don't use the CriteriaBuilder when using Spring Boot/Hibernate as the statements become incredibly verbose compared to just using JPQL or SQL, but in the end what really matters for performance is the queries that are the result of this. Those might very well be the same for your 2 options.

    If you want to check this, you could either monitor the queries on your database, or put this in your application.properties:

    spring.jpa.show-sql=true

    So you can see what kind of SQL is generated.

    Mind you, a general tip of Hibernate themselves is to not use the entities when all you want to do is export them as a view.

    For a fairly simple query like this with one ManyToOne join and one OneToMany join, I don't think overall performance is going to be that bad. Once you start joining multiple associations, that's when you probably should start splitting up queries in main table query and associated data table queries.