Using Projections with Declared Queries in spring-data-jdbc

[VariabileAleatoria]

Summary

Interface-based projections with nested objects work perfectly with derived query methods but fail when using custom @Query annotations. The error indicates Spring Data JPA cannot instantiate the projection interface when using custom queries.

Database schema

CREATE TABLE IF NOT EXISTS country (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    code VARCHAR(10) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS country_dossier (
    id SERIAL PRIMARY KEY,
    country INTEGER NOT NULL,
    release_date DATE,
    score INTEGER,
    FOREIGN KEY (country) REFERENCES country(id)
);

Entity classes

@Data
@AllArgsConstructor
@Table("country")
public class Country {
    @Id
    private Long id;
    private String name;
    private String code;
    private CountryDossier dossier;
}

@Table("country_dossier")
@AllArgsConstructor
@Data
public class CountryDossier {
    @Id
    private Long id;
    private Long country;
    private Date releaseDate;
    private Integer score;
}

Projection interfaces

public interface CountryProjection {
    Long getId();
    String getName();
    CountryDossierProjection getDossier();
    
    interface CountryDossierProjection {
        Long getId();
        Integer getScore();
    }
}

Expected behavior

Both repository methods should return the same CountryProjection with properly populated nested CountryDossierProjection.

Actual behavior

✅ Working: Derived Query Method

CountryProjection findProjectionById(Long id);

This works perfectly and returns the projection with nested data populated correctly.

❌ Failing: Custom @Query

@Query("""
    SELECT
        c.ID AS ID,
        c.CODE AS CODE,
        c.NAME AS NAME,
        d.ID AS DOSSIER_ID,
        d.SCORE AS DOSSIER_SCORE,
        d.COUNTRY AS DOSSIER_COUNTRY,
        d.RELEASE_DATE AS DOSSIER_RELEASE_DATE
    FROM country c
    LEFT OUTER JOIN country_dossier d ON d.COUNTRY = c.ID
    WHERE c.ID = :id
""")
CountryProjection findProjectionCustom(Long id);

Error Message:

Failed to instantiate it.variabilealeatoria.springdata.demo.repository.CountryProjection using constructor NO_CONSTRUCTOR with arguments

Attempted solution

DTO Classes Instead of Interface Projections

@Data
@AllArgsConstructor
public class CountryProjection {
    Long id;
    String name;
    CountryDossierProjection dossier;
}

@Data
@AllArgsConstructor
public class CountryDossierProjection {
    Long id;
    Integer score;
}

Result: No error, but dossier field is null instead of being populated with the nested projection.

Questions

1. Why do interface projections work with derived queries but fail with custom @Query annotations?
2. Is there a specific alias naming convention required for nested projections in custom queries so that the default mapper is able to build the objects?
3. How can nested interface projections be properly used with custom @Query methods?
4. Is this a limitation of Spring Data JDBC, or is there a workaround?

While this example might seem contrived, there are legitimate use cases requiring both nested projections (and so Interface is the only option as stated in docs) and custom queries for complex business requirements.

Workarounds

My current workaround is to flatten the relationship into a flat projection object instead of using nested projections:

@Data
@AllArgsConstructor
public class CountryProjection {
    Long id;
    String name;
    CountryDossierProjection dossier;
}

but I'm not a big fan of it.

Another thing I discovered is that if I annotate the inner projection with @Id the conversion succed

@Data
@AllArgsConstructor
public class CountryDossierProjection {
    @Id
    Long id;
    Integer score;
}

This approach succeeds in populating the nested projection, but it's unclear why Spring Data JDBC requires an @Id annotations on what should be simple projection DTOs. This feels hacky and blurs the line between entities and projections.

[VariabileAleatoria]

apparently this is the reason why having @Id annotation in nested projection gives a different mapping result

[VariabileAleatoria]

Turns out that selecting the foreign key makes the mapping work

        @Query("""
                        SELECT
                            country.ID AS ID,
                            country.CODE AS CODE,
                            country.NAME AS NAME,
                            dossier.ID AS DOSSIER_ID,
                            dossier.SCORE AS DOSSIER_SCORE,
                            dossier.COUNTRY AS DOSSIER_COUNTRY_PROJECTION // added this selection
                        FROM country
                        LEFT OUTER JOIN country_dossier dossier ON dossier.COUNTRY = country.ID
                        WHERE country.ID = :id
                                    """)
        CountryProjection findProjectionCustom(Long id);

even though it's not clear to me why would it be needed

[mp911de]

The underlying issue is to some extend how much mapping shall be considered for projections. Customized field naming in an entity should be ideally applied to avoid repetitive aliasing in the declared query. So projecting a declared query would consider field naming and types and materialize directly the resulting DTO. Interface projections would directly materialize the interface projection.

Somewhat related: #2109