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).

You can not use `addScalar` in this way. You can create constructor for `ResultDto` and then map results via `@SqlResultSetMapping` annotation. See for example [this](https://stackoverflow.com/a/64776376/6277104) — SternK, Dec 17 '20 at 09:33
I read your example, but my sql is dynamic. So do you have other options? — xmcx, Dec 17 '20 at 10:00
Maybe you can try to use `ResultTransformer` as it explained [here](https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#sql-dto-query). What exactly part of your query can be dynamically changed? Is result set the same for all possible combination? — SternK, Dec 17 '20 at 12:46
The other fields is referenced to dynamic columns(like size, color...). I tried `setResultTransformer( Transformers.aliasToBean( ResultDto.class ) )`, and get `org.hibernate.PropertyNotFoundException` due to `addScalar(xxx)`. — xmcx, Dec 18 '20 at 03:04
I read the source code of `AliasToBeanResultTransformer` and wrote a custom implementation based on his method. — xmcx, Dec 21 '20 at 10:16
@xmcx did you find any solution? I am facing similar issue: https://www.db-fiddle.com/f/vmAq1BzMfLVRW5enPrjoxo/1 — Silver Sky, Sep 27 '22 at 10:57
@SilverSky Try this.`Query nativeQuery = entityManager.createNativeQuery(sql); nativeQuery.unwrap(NativeQuery.class).addScalar("fieldName", fieldType).setResultTransformer(Transformers.aliasToBean(Bean.class))`. See [NativeQuery.addScalar(String columnAlias, Type type)](https://docs.jboss.org/hibernate/orm/5.3/javadocs/org/hibernate/query/NativeQuery.html) — xmcx, Oct 01 '22 at 03:07

score 0 · Answer 1 · answered Dec 21 '20 at 12:31

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

What should I do if `*Dto.class` does not has field `id` (or similar field)? — xmcx, Dec 23 '20 at 07:30
You just remove it. You don't require the id field, but if you have it, it's good to map it to be able to use all fetch strategies. You will get startup errors when you use an unsupported configuration though, so there is not much you can do wrong. — Christian Beikov, Dec 23 '20 at 08:52

score 0 · Answer 2 · answered Jun 21 '21 at 11:58

0

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

answered Jun 21 '21 at 11:58

Kanaris007

304
2
6

hibernate native query transformer columns to complex property in dto

2 Answers2