Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Spring Data JPA: Creating Specification Query Fetch Joins

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.

like image 210
woemler Avatar asked Mar 30 '15 14:03

woemler


People also ask

How can I join JPA specification?

Joining Tables with JPA Specifications select author0_.id as id1_1_, author0_. first_name as first_na2_1_, author0_. last_name as last_nam3_1_ from author author0_ inner join author_books books1_ on author0_.id = books1_. author_id inner join book book2_ on books1_.

Is Spring data JPA an implementation of the JPA specification?

Spring Data JPA is not an implementation or JPA provider, it's just an abstraction used to significantly reduce the amount of boilerplate code required to implement data access layers for various persistence stores.

What is JPA spring data specification?

SPring Data Jpa Specifications helps us to create dynamic queries based on the requirement at run time. Spring Data Jpa Specifications allows a combination of the attributes or properties of a domain or entity class and creates a query.


2 Answers

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")); 
like image 167
DonCziken Avatar answered Oct 05 '22 16:10

DonCziken


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);
    };
}
like image 27
suraj bahl Avatar answered Oct 05 '22 16:10

suraj bahl