How to return a custom object from Spring Data JPA? Using JPQL Constructor Expressions in Spring Boot JPA Repository.

Tech Insights
Sandeep

Spring Boot’s @Repository annotated interfaces reduce the amount of effort required to implement data access layer when compared to the old DAO implementation classes. However, the default behavior of the JPA repository returning a database entity object or a list of entity objects may not serve the purpose if you are using a customized query. Using Aggregate functions like COUNT() in the query is the best example you can think of. Since the count field is not present in the entity, you need to create a new custom class which contains all the fields like count that your query returns. Let’s see with an example.

I am trying to get the year wise count of posts on InfoBrisk site. The Post entity will look like this:

@Entity
public class Post {
	@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;
	
	private Integer year;
	// other properties, constructors, getters and setters
}

To get year wise count of posts, what I need in my query is the aggregate function – COUNT() along with a GROUP BY clause. So I’am going to need a custom PostCount class that can be used in the JPQL.

public class PostCountCustom {
	
	private Integer year;
	
	private Long count;
	
	//Constructor
	public PostCountCustom(Integer year, Long count) {
		this.year = year;
		this.count = count;
	}
	// getters and setters
}

PostCountCustom class is not required to be an entity or to be mapped to the database. Following is the JPA repository method using JPQL Constructor Expression to return the required result:

@Query("SELECT new com.infobrisk.entity.PostCountCustom(p.year,COUNT(p.id)) FROM Post p GROUP BY p.year ORDER BY p.year DESC ")
public List<PostCountCustom> getYearWisePostCount();

Note: The order of attributes passed in the constructor expression should be the same as used in the constructor of the custom class. Also the constructor should be called using the new keyword.