Projection fails when using subquery [DATAJPA-1713]

[spring-projects-issues]

Manu VIDAL opened DATAJPA-1713 and commented

When using a subquery in query defined with @Query, JPA projection fails.
Return type is not of projection class but

org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap**

Example :

@Entity
public class Employee {
   @Id
   @GeneratedValue(strategy=GenerationType.AUTO)
   private Long id;
   private String firstName;
   private String lastName;
   private Integer salary;

   // Constructors, getters and setters
}

Projection class :

public interface EmployeeProjection {

    public Long getId();

    public String getFirstName();
 
    //@Value("#{target.firstName + ' ' + target.lastName}") 
    //String getFullName();
}

Repo :

public interface EmployeeRepository extends CrudRepository<Employee, Long> {
   @Query("select c from Employee c where c.salary >= "
          //+ "500"
          + "(select avg(salary) from Employee )"
   )
   List<EmployeeProjection> findProjections();
}

When using condition "c.salary >= 500", method returns a correct result, i.e. a List<EmployeeProjection>.

But when using a subquery "c.salary >= (select avg(salary) from Employee)", method returns a list of AbstractJpaQuery$TupleConverter$TupleBackedMap.

On this object, calling any getter returns null.
Using @Value (open projection) throws an exception.

Example sources are attached.
Thanks in advance.

 

---

Affects: 2.2.6 (Moore SR6)

Attachments:

• demo.zip (4.51 kB)
• demo2.zip (5.76 kB)

Referenced from: pull request #420

[spring-projects-issues]

Jens Schauder commented

Thanks for raising the issue. Based on your reproducer I was able to add a test demonstrating the issue.

I haven't checked, but I assume this is due to the parser not being able to properly handle the subselect and therefore thinking the select returns single fields.

This might actually be a valid workaround: instead of returning an entity: return the fields of the entity required for the projection.

[spring-projects-issues]

Manu VIDAL commented

Ok, thanks Jens Schauder for your quick response.

I'v tested this Query : @Query("select c.id as id, c.firstName as firstName from Employee c [...]") and it works.
But i have to alias all the properties corresponding to the projection.

I've however another problem : if I have a nested projection, i.e. a @OneToMany Set property on class Employee, and a List getDepartments() in EmployeeProjection, how can I write my Query ?

I point out that it works if @Query is "select c from Employee c"

Thanks

[spring-projects-issues]

Jens Schauder commented

If you want to create a complex object from a SQL query with JPA, you have to use ResultSet mapping. https://thoughts-on-java.org/result-set-mapping-basics/

[spring-projects-issues]

Manu VIDAL commented

Sorry Jens Schauder I think I expressed myself poorly. I've added a second project demo2.zip to illustrate my test case.

First method of repository works fine and retrieves 1 employee and its 2 jobs.

Second method retrieves bad type and wrong numbers of results.
In debug, we can see that tree representation of result is quite strange : data is present but duplicated and hard to extract from TupleBackedMap.

So ... is there a workaround for this precise case ?

Thanks again for your help