Deep fetch projection

[evilchewits]

Hi Robert,

I just have a question. Do you know whether it is possible to use projections with "join fetch" queries.

In relation to you example, suppose I have one more entity, Country:

@Entity
public class Country {
    @Column(nullable = false)
    private String name;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

And a @ManyToOne relation to it in the Person class:

@Entity
public class Person {
    ...
    @ManyToOne(optional = false)
    private Country country;

    public Country getCountry() {
        return country;
    }

    public void setCountry(Country country) {
        this.country= country;
    }
    ...
}

I also add getCountryName() method to PersonSummary interface:

public interface PersonSummary {
    ...
    String getCountryName();
    ...
}

I wonder, is it possible to get the projected entity with query like this:

public interface PersonRepository extends CrudRepository<Person, Long> {
    ...
    @Query("SELECT p FROM Person p"
        + " LEFT JOIN FETCH p.country "
        + " WHERE p.country.name = :countryName")
    List<PersonSummary> findAllProjectedByCountryNameJpaQuery(@Param("countryName") String countryName);
    ...
}

I didn't try exact that example above, but it doesn't work with my own similar entities and data.

Alexander

[roberthunt]

Should be simply a case of using the @Value annotation to form an 'open' projection: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections.interfaces.open

public interface PersonSummary {
    ...
    @Value("#{target.country.name}")
    String getCountryName();
    ...
}

[roberthunt]

The below strategy also works with Spring Data JPA Ingalls-SR7 (not sure which version they added support for this but it didn't work in Hopper-SR3)

public interface PersonSummary {
    ...
    Country getCountry();

    default String getCountryName()
    {
        return getCountry().getName();
    }
    ...
}

[evilchewits]

Thanks, I've already tried both and one more approach (Example 65 here: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections.interfaces) but catch the same exception:

java.lang.IllegalStateException: No aliases found in result tuple! Make sure your query defines aliases!

I tried with my own code so far, not with your example, so maybe I did something wrong.. I'll try your code now..

[roberthunt]

No aliases found in result tuple!

Did you specify aliases in the query?

EG:

@Query(value = "SELECT name AS name, age AS age FROM Person WHERE id = ?1")

[evilchewits]

No, I didn't. But my goal is to rely only on the interface definition, i.e. if, say, I add one more getter to the interface, it should works properly without modifying the query. That said, I tried this:

    @Query("SELECT p, p.country.name as countryName FROM Person p"
        + " LEFT JOIN FETCH p.country "
        + " WHERE p.country.name = :countryName")

The same error arises.

[roberthunt]

I think you have to alias every field in the query and the alias must match the field name in your interface, so the SELECT p in you case isn't aliased.

Also have you just tried it without using the @Query annotation to see if it works? the PersonSummary findProjectedById(long id) automatically performs the join to country when I tested it without the needs for @Query(...).

[evilchewits]

Yes, in my case it's easier to fetch data without @Query and I likely will do so.

I just tried to find a way to write complex HQL queries with multiple joins etc. and project this data to different interfaces in the most concise and generalized way.

I have not found a fully generalized solution, but you helped me with learning @Value annotation and aliasing. So, given the code I provided earlier, this query works fine:

    @Query("SELECT p.name AS name, p.age AS age, p.country AS country FROM Person p"
        + " LEFT JOIN p.country "
        + " WHERE p.country.name = :countryName")

It is also worth noting that I replaced "JOIN FETCH" with "JOIN".

Thanks, Rob :)