Spring Data JPA: Creating Specification Query Fetch Joins

Question

TL;DR: How do you replicate JPQL Join-Fetch operations using specifications in Spring Data JPA?

I am trying to build a class that will handle dynamic query building for JPA entities using Spring Data JPA. To do this, I am defining a number of methods that create Predicate objects (such as is suggested in the Spring Data JPA docs and elsewhere), and then chaining them when the appropriate query parameter is submitted. Some of my entities have one-to-many relationships with other entities that help describe them, which are eagerly fetched when queried and coalesced into collections or maps for DTO creation. A simplified example:

@Entity
public class Gene {

    @Id 
    @Column(name="entrez_gene_id")
    privateLong id;

    @Column(name="gene_symbol")
    private String symbol;

    @Column(name="species")
    private String species;

    @OneToMany(mappedBy="gene", fetch=FetchType.EAGER) 
    private Set<GeneSymbolAlias> aliases;

    @OneToMany(mappedBy="gene", fetch=FetchType.EAGER) 
    private Set<GeneAttributes> attributes;

    // etc...

}

@Entity
public class GeneSymbolAlias {

    @Id 
    @Column(name = "alias_id")
    private Long id;

    @Column(name="gene_symbol")
    private String symbol;

    @ManyToOne(fetch=FetchType.LAZY) 
    @JoinColumn(name="entrez_gene_id")
    private Gene gene;

    // etc...

}

Query string parameters are passed from the Controller class to the Service class as key-value pairs, where they are processed and assembled into Predicates:

@Service
public class GeneService {

    @Autowired private GeneRepository repository;
    @Autowired private GeneSpecificationBuilder builder;

    public List<Gene> findGenes(Map<String,Object> params){
        return repository.findAll(builder.getSpecifications(params));
    }

    //etc...

}

@Component
public class GeneSpecificationBuilder {

    public Specifications<Gene> getSpecifications(Map<String,Object> params){
        Specifications<Gene> = null;
        for (Map.Entry param: params.entrySet()){
            Specification<Gene> specification = null;
            if (param.getKey().equals("symbol")){
                specification = symbolEquals((String) param.getValue());
            } else if (param.getKey().equals("species")){
                specification = speciesEquals((String) param.getValue());
            } //etc
            if (specification != null){
               if (specifications == null){
                   specifications = Specifications.where(specification);
               } else {
                   specifications.and(specification);
               }
            }
        } 
        return specifications;
    }

    private Specification<Gene> symbolEquals(String symbol){
        return new Specification<Gene>(){
            @Override public Predicate toPredicate(Root<Gene> root, CriteriaQuery<?> query, CriteriaBuilder builder){
                return builder.equal(root.get("symbol"), symbol);
            }
        };
    }

    // etc...

}

In this example, every time I want to retrieve a Gene record, I also want its associated GeneAttribute and GeneSymbolAlias records. This all works as expected, and a request for a single Gene will fire off 3 queries: one each to the Gene, GeneAttribute, and GeneSymbolAlias tables.

The problem is that there is no reason that 3 queries need to run to get a single Gene entity with embedded attributes and aliases. This can be done in plain SQL, and it can be done with a JPQL query in my Spring Data JPA repository:

@Query(value = "select g from Gene g left join fetch g.attributes join fetch g.aliases where g.symbol = ?1 order by g.entrezGeneId")
List<Gene> findBySymbol(String symbol);

How can I replicate this fetching strategy using Specifications? I found this question here, but it only seems to make lazy fetches into eager fetches.

Solution 1

Specification class:

public class MatchAllWithSymbol extends Specification<Gene> {
    private String symbol;

    public CustomSpec (String symbol) {
    this.symbol = symbol;
    }

    @Override
    public Predicate toPredicate(Root<Gene> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

        //This part allow to use this specification in pageable queries
        //but you must be aware that the results will be paged in   
        //application memory!
        Class clazz = query.getResultType();
        if (clazz.equals(Long.class) || clazz.equals(long.class))
            return null;

        //building the desired query
        root.fetch("aliases", JoinType.LEFT);
        root.fetch("attributes", JoinType.LEFT);
        query.distinct(true);        
        query.orderBy(cb.asc(root.get("entrezGeneId")));
        return cb.equal(root.get("symbol"), symbol);
    }
}

Usage:

    List<Gene> list = GeneRepository.findAll(new MatchAllWithSymbol("Symbol"));

Solution 2

You can specify the join fetch while creating Specification but since the same specification will be used by pageable methods also like findAll(Specification var1, Pageable var2) and count query will complain because of join fetch. Therefore, to handle that we can check the resultType of CriteriaQuery and apply join only if it is not Long (result type for count query). see below code:

    public static Specification<Item> findByCustomer(Customer customer) {
    return (root, criteriaQuery, criteriaBuilder) -> {
        /*
            Join fetch should be applied only for query to fetch the "data", not for "count" query to do pagination.
            Handled this by checking the criteriaQuery.getResultType(), if it's long that means query is
            for count so not appending join fetch else append it.
         */
        if (Long.class != criteriaQuery.getResultType()) {
            root.fetch(Person_.itemInfo.getName(), JoinType.LEFT);
        }
        return criteriaBuilder.equal(root.get(Person_.customer), customer);
    };
}

Comments

[Mariano D'Ascanio]

Nice tip on how to make a specification work with SDJPA pageable queries. +1.

[hicham abdedaime]

it works but the problem hibernate does pagination in memory and in console i see this message 'HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!' , I Try to resolve it with fetch join and entity graph but I don't find a perfect solution .

[Jin Kwon]

This answer saved by soul.