Spring JPA DTO projection and handling the nested projection with null values

Question

I am using class based projection with constructor expressions. here is a sample code form my work

@Query("select new com.core.data.category.CategoryDto(c.id,c.code,c.externalCode,c.seoMeta, c.createdAt, c.updatedAt,c.parent.code) FROM Category c where c.code = :code")
CategoryDto findCategoryByCode(@Param("code") String code); 

This is how my CategoryDto look like:

public class CategoryDto implements Serializable {
 
private Long id;
private String code;
private String externalCode;
private SEOMeta seoMeta;
private CategoryDto parent;
private LocalDateTime createdAt;
private LocalDateTime updatedAt;
Map<String, LocCategoryDto> translation;

//constructor based on the requirement
}

This seems to be working fine except in one case where the property for the nested object is null. in my case the parent property can be null in case this is a root category but it seems using c.parent.code causing the issue and the entire object is coming null. Can someone help me with the following queries

1. Is there a way to handle this case using the same constructor expressions? I tried to look in to the doc but did not find the details.
2. I think other option might be using ResultTransformer (which will bind my code to specific JPA)but I did not find any information as how I can use it with Spring JPA.

Update I even tried the option to use the CASE option but seems this is also not working for me as I am still getting the null entity (while data is available in the DB). Here is the updated code I tried

@Query(value = "select new com.core.data.category.CategoryDto(c.id,c.code,c.externalCode,c.seoMeta, c.createdAt, c.updatedAt, " +
            "CASE " +
            "WHEN  c.parent is NULL " +
            "THEN NULL " +
            "ELSE c.parent.code " +
            "END ) " +
            "FROM Category c where c.code = :code")
    CategoryDto findCategoryByCode(@Param("code") String code);

Edit 2 I had also tried even with join but that also seems not working for.

Update: I did a silly mistake.Was using the simple join and not left join which caused this issue.

Answer 1

try using left join

@Query("select new com.core.data.category.CategoryDto(c.id,c.code,c.externalCode,c.seoMeta, c.createdAt, c.updatedAt,parent.code) FROM Category c left join c.parent as parent where c.code = :code")
CategoryDto findCategoryByCode(@Param("code") String code); 

Answer 2

I suspect your problem is something completely different, because using a left outer join solves the problem you describe.

Change your query to:

select new com.core.data.category.CategoryDto(c.id,c.code,c.externalCode,c.seoMeta, c.createdAt, c.updatedAt, p.code) 
FROM Category c
LEFT JOIN c.parent p
WHERE c.code = :code

I created a reproducer demonstrating that an outer join fixes the problem.

The relevant code:

@Entity
class SomeEntity {
    @Id
    @GeneratedValue
    Long id;

    String name;

    @ManyToOne
    SomeEntity parent;
}

public class Dto {

    final String name;
    final String parentName;

    public Dto(String name, String parentName) {
        this.name = name;
        this.parentName = parentName;
    }

    @Override
    public String toString() {
        return name + " - " + parentName;
    }
}

public interface SomeEntityRepository extends JpaRepository<SomeEntity, Long> {

    @Query("select new de.schauderhaft.de.constructorexpressionwithnestedreference.Dto(e.name, p.name) " +
            "from SomeEntity e " +
            "left join e.parent p")
    List<Dto> findDto();

    @Query("select new de.schauderhaft.de.constructorexpressionwithnestedreference.Dto(e.name, e.parent.name) " +
            "from SomeEntity e")
    List<Dto> findDtoInnerJoin();

    @Query("select e from SomeEntity e")
    List<SomeEntity> findEntities();
}

@SpringBootTest
class ConstructorExpressionWithNestedReferenceApplicationTests {

    @Autowired
    SomeEntityRepository ents;

    @Test
    @Transactional
    void testDtos() {

        createEnts();

        assertThat(ents.findDto()).extracting(Dto::toString).containsExactlyInAnyOrder("ents name - parents name", "parents name - null");

    }

    @Test
    @Transactional
    void testDtosInnerJoin() {

        createEnts();

        assertThat(ents.findDtoInnerJoin()).extracting(Dto::toString).containsExactly("ents name - parents name");

    }

    @Test
    @Transactional
    void testEntities() {

        createEnts();

        assertThat(ents.findEntities()).extracting(e -> e.name).containsExactlyInAnyOrder("ents name", "parents name");

    }

    private void createEnts() {

        SomeEntity ent = new SomeEntity();
        ent.name = "ents name";
        ent.parent = new SomeEntity();
        ent.parent.name = "parents name";


        ents.saveAll(asList(ent, ent.parent));
    }

}