spring-projects / spring-projects/spring-data-relational

Class based Projections - projection class doesn't optimizes the query execution

Open
#1,821 1 comment 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

status: blocked type: enhancement
Dominant language
Java
Stars
827
Forks
394
PR merge metrics
No merged PRs in 30d

Description

i have an aggregate that internally has two 1 to M relationship

@Getter
@Setter
@AllArgsConstructor
@Builder
@EqualsAndHashCode(of = {"id"})
@Table("TOOL")
public class AgrTool {

    private @Id Long id;

    private @Version Long version;

    private String status;

    //my flat parameters...
    private LocalDate creationDate;
    private LocalDate acquireDate;
    private String serialNo;
    ................
    ................

     //my two  1-to-M 
    private List<VoDefinitionCalibrationInternal> calibrationInternalDefinitions = new ArrayList<>();

    private List<VoDefinitionCalibrationExternal> calibrationExternalDefinitions = new ArrayList<>();

}

based on our business logic we need to inquery just the TOOL table returing a list of object.
Pratically we don't want to fetch the two relationship (calibrationInternalDefinitions and calibrationExternalDefinitions) we need to show a table wiht the miminal set of attribute of the TOOL table

as defined in the documentation we apply the projections expecting, that the query is limited to the field exposed by the consrtuctor of the Projection Class:
(official documentation) --> If the store optimizes the query execution by limiting the fields to be loaded, the fields to be loaded are determined from the parameter names of the constructor that is exposed.

then we implemented the minimal value object without the two relationships

  • calibrationInternalDefinitions
  • calibrationExternalDefinitions
@Getter
@Setter
@AllArgsConstructor
@Builder
public class PrjTool{

    private Long id;

    private String status;

    //my flat parameters...
    private LocalDate creationDate;
    private LocalDate acquireDate;
    private String serialNo;
    ................
    ................

}

and in our repository we add two methods:

public interface RepositoryTool extends ListCrudRepository<AgrTool,Long> {

    <T> Page<T> findAllByCustomerAndEnvironment(Long customer, Long environment, Pageable pageable , Class<T> type);

    <T> Collection<T> findAllByCustomerAndEnvironment(Long customer, Long environment, Class<T> type);

analisyng the result of our Unit test where we call this two methods:

        Page<PrjTool> result = null;
        result = repository.findAllByCustomerAndEnvironment(-160L, -160L, pageRequest, PrjTool.class);
        result.forEach(p -> log.info("element:" + p));
        assertThat(result.getNumberOfElements()).isEqualTo(2);

        Collection<PrjTool> result = null;
        result = repository.findAllByCustomerAndEnvironment(-160L, -160L, PrjTool.class);
        result.forEach(p -> log.info("element:" + p));
        assertThat(result.isEmpty()).isEqualTo(false);

activating the trace of the query from the log we see that the query generate continue to do select in the relasionsip also if no define in the projection object,

o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL query
.....
.....
o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [SELECT "TOOL"."ID" AS "ID", "TOOL"."AREA" AS "AREA"... 
.....
.....
o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [SELECT "DEFINITION_CALIBRATION_INTERNAL"."ID" AS... 
.....
......
o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [SELECT "DEFINITION_CALIBRATION_EXTERNAL"."ID" AS... 

Expectation
only the table TOOL table should be analyzed
if required I can create a test to show the behavior

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the PrjTool projection and the two RepositoryTool findAllByCustomerAndEnvironment methods, then inspect the SQL trace from the supplied unit-test calls. Verify whether class-based projections still trigger relationship queries and add a focused test if needed. Done means the projection query reads only TOOL fields and does not query either calibration relationship.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, spring
Domain
backend, database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.