Published on

Return Custom Object from JPQL query. JPQL Constructor Expression And Projection

Authors

Overview

When anyone thinks about returning a custom object from the JPA repository, they're most probably thinking about Projection and I agree that is a preferable way to do so, But there is another way too to archive the same.

You can archive this by using JPQL Constructor Expression in JPQL query.

Note: JPQL Constructor Expression will only work with JPQL query not in a native query.

Let's start with example first:

Create Bean class (POJO)

Let's say we have Author entity with us

@Table(name = "authors")
@Entity
class Author {
    @Id
    @GeneratedValue
    private Long id;
    private String name;
    private Integer age;
    private String qualification;
}

from which we just want to return some fields or may be aggregation of some fields.

Let's take an example of grouping author by age and find count of same age author. In that case, we need to create a Bean class like follow

class AgeCount {
    private Integer age;
    private Long count;

    public AgeCount(Integer age, Long count) {
        this.age = age;
        this.count = count;
    }
}

Write repository and method that return above Bean

public interface AuthorRepository extends JpaRepository<Author, Long> {
    @Query("select new com.demo.api.view.AgeCount(age, count(id)) from Author group by age")
    List<PublicAuthorView> getAgeCount();
}

Here comes the list of Important things

  • Make sure that you write fully qualified name of Bean or POJO class. Like : com.demo.api.view.AgeCount
  • Make sure you put new keyword before constructor call. Like : ... new com.demo.api.view.AgeCount(age, count(id)) ...
  • Make sure you pass argument in same order and type as it in constructor or else it will give you and exception at runtime, or it might provide you the unexpected values.
  • Take a note that it will only work with non-native (JPQL) query for the native query you need to use Projection.

If you see closely first three points are just mansions that you need to call constructor but with fully qualify name.

Discuss on Twitter