hibernate native query transformer columns to complex property in dto

Question

I have a native query, and I want to transformer columns (b_id, b_code, b_desc) to complexProperty (a custom object) in ResultDto.

SQL

select a.id   as id,
       a.name as name,
       b.id   as b_id,
       b.code as b_code,
       b.desc as b_desc
  from a
  left join b
    on a.id = b.a_id;

ResultDto.class

public class ResultDto {

    private String id;

    private String name;

    private ComplexPropertyDto complexProperty;

    // other fields and getter|setter

}

ComplexPropertyDto.class

public class ComplexPropertyDto {

    private String id;

    private String code;

    private String desc;

    // other fields and getter|setter

}

I try use column alias like complexProperty.code and use addScalar("complexProperty.code", StringType.INSTANCE) transformer. But I got org.hibernate.PropertyNotFoundException: Could not resolve PropertyAccess for complexProperty.code on class xxx.ResultDto.

Update

How to transformer columns in table b to property complexProperty in ResultDto.class(work as annotation Embedded).

Answer 1

Maybe it helps somebody. In my case "Could not resolve PropertyAcces" was because for set without get.

Answer 2

I don't know how you can make your particular example work, but I think this is a perfect use case for Blaze-Persistence Entity Views. Blaze-Persistence Core is the basis which adds support for many advanced SQL concepts in the realm of the JPA model.

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(TableAEntity.class)
public interface ResultDto {
    @IdMapping
    Long getId();
    String getName();
    ComplexPropertyDto getComplexProperty();

    @EntityView(TableBEntity.class)
    interface ComplexPropertyDto {
        @IdMapping
        Long getId();
        String getCode();
        String getDesc();
    }
}

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

ResultDto a = entityViewManager.find(entityManager, ResultDto.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