Projection in Hibernate/JPA in Spring Boot to pull data from multiple OneToMany and manyToOne relationships

I need to create a JPA projection that will pull all the records from Entity1 and include a record count from Entity4.

Here are the four entities that are mapped and their relationships:

Entity1 (ManyToOne) Entity2 (OneToMany) Entity3 (OneToMany) Entity4

In SQL, this could easily be solved with a simple inline select statement. In the JPA projection, I am trying to do the following.

@Value("#{target.getEntity2().getEntity3().getEntity4().size()}")
String  getEntity4Count();	

In this code above, the projection is on Entity1. It throws the exceptions when I add in Entity4. The below code works.

@Value("#{target.getEntity2().getEntity3().size()}")
String  getEntity3Count();

Is this even the correct method to pull this data? All the examples seemed to point to relationships between two entities which is great for really simple applications but the SQL in business applications tends to be very complex.

Is there a way to complete the above with another method such as JPQL? What is the best method for handling large numbers of complex SQL that need to be executed about a set of Entities?

Spring Data Projections is not really made for this kind of mapping as it will fetch entities and execute your "mappings" in the @Value annotation as SPEL(Spring Expression Language) code. I suppose you would rather like to use more efficient SQL?

This is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(Entity1.class)
public interface Entity1Dto {
    @IdMapping
    Long getId();
    @Mapping("COUNT(entity2.entity3.entity4)")
    Long getCount();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

Entity1Dto a = entityViewManager.find(entityManager, Entity1Dto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features