Spring data dynamic @Query based on params possible?

Question

I recently switched from JEE to Spring Boot and I'm loving it so far. But I have run into a little issue. I have this method that fetches lazy references that used to look something like this:

public Foo getParentWithChildren(long id, boolean isFetchChild, boolean isFetchPets) {
    StringBuilder sql = new StringBuilder();
    sql.append("select DISTINCT(p) from Parent p");
    if(isFetchChild) {
        sql.append(" left join p.child c");
    } if(isFetchPets) {
        sql.append(" left join p.pets pts");
    } 

    sql.append(" where p.id=:id");

    TypedQuery<Foo> query = em.createQuery(sql.toString(), Foo.class);
    query.setParameter("id", id);
    return query.getSingleResult();
}

Now with spring data and their awesome interfaces I would like to do something simlar using the @Query annotation on the interface instead of having to write a custom implementation. But is it possible to do something similar using only the interface.

The example below will obviously not work but I hope you understand what I am trying to acchieve

@Query("select distinct(p) from Parent p " + 
        (fetchChild ? " left join p.child c" : "") + 
        (fetchPets ? " left join p.pets pts" : "") +
        " where p.id=:id")
Foo getParentWithChildren(@Param("id") long id, @Param("fetchChild") boolean isFetchChild, @Param("fetchPets") boolean isFetchPets);

I something similar possible?

No, but if this is your exact case, you can create 2 methods in the interface and call the appropriate one according to `isFetchChild`. — Nikos Paraskevopoulos, Feb 01 '17 at 18:17
Actually the real parent holds 5 children e.g 5 boolean params. And there are different combinations as well. Sometimes you want child 1 and 3, other times only 4, sometimes all 5 etc. So then I would have to write methods for all possible scenarios? — Johan, Feb 01 '17 at 18:26

score 2 · Accepted Answer · edited May 23 '17 at 12:02

You can create multiple methods in your interface and use the EntityGraph functionality introduced in JPA 2.1 which Spring Data JPA supports in various ways:

http://docs.spring.io/spring-data/jpa/docs/1.11.0.RELEASE/reference/html/#jpa.entity-graph

public interface FooRepository extends JpaRepository<Foo, Long>{

    @EntityGraph(attributePaths = { "children" })
    @Query("select f from Foo f where f.id = :id")
    Foo getFooWithChildren(@Param("id") long id);

    @EntityGraph(attributePaths = { "pets" })
    @Query("select f from Foo f where f.id = :id")
    Foo getFooWithPets(@Param("id") long id);

    @EntityGraph(attributePaths = { "children", "pets" })
    @Query("select f from Foo f where f.id = :id")
    Foo getFooWithChildrenAndPets(@Param("id") long id);
}

One issue with this is you need to repeat the Query for each method. Being able to pass in the entity graphs as parameters to a query method would seem to be useful functionality missing from the Spring Data JPA module.

I raised at ticket for this some time ago but no update yet:

https://jira.spring.io/browse/DATAJPA-645?filter=-2

A link in an answer to this question Spring Data JPA And NamedEntityGraphs however suggests an extension at the below which allows us to do exactly this:

https://github.com/Cosium/spring-data-jpa-entity-graph

With this extension the code is simplified to:

public interface FooRepository extends JpaEntityGraphRepository<Foo, Long>{

}

and call it as:

Foo foo = fooRepository.findOne(1L, 
               new DynamicEntityGraph(Arrays.asList({"children");

Foo foo = fooRepository.findOne(1L, 
               new DynamicEntityGraph(Arrays.asList({"children", "pets"});

Thank you! i'll check this out. Question, are the referenced entities fetched by inner join by default? if so can I specify I want a left join or any other type? — Johan, Feb 01 '17 at 20:35

Spring data dynamic @Query based on params possible?

1 Answers1