JPA SqlResultSetMapping to pojo with complex object in attribute

Question

I wonder if it's possible (and if it's the good method) to map sql result of native query in pojo with other pojos in its attributes. I'm searching something like that :

ResultPojo Class

public class ResultPojo {

    private MyPojo myPojo;
    private Integer firstSimpleAttribute;
    private Integer secondSimpleAttribute;
    private Double thirdSimpleAttribute;
    
    // Empty Constructor + Constructor with fields + Getter + Setter
    
}

MyPojo Class

public class MyPojo {

    private Long id;
    private String label;
    private MySubPojo mySubPojo;
    
    // Empty Constructor + Constructor with fields + Getter + Setter
    
}

MySubPojo Class

public class MySubPojo {

    private String text;
    
    // Empty Constructor + Constructor with fields + Getter + Setter
    
}

Mapping part

@SqlResultSetMapping(
    name = "CustomMapping",
    classes={
        @ConstructorResult(
            targetClass=MyPojo.class,
            columns={
                @ColumnResult(name="id", type=Long.class),
                @ColumnResult(name="label", type=String.class),
                @ColumnResult(name="mySubPojo", type=MySubPojo.class)
            }
        )
    },
    columns = {
        @ColumnResult(name = "firstSimpleAttribute", type = Integer.class),
        @ColumnResult(name = "secondSimpleAttribute", type = Integer.class),
        @ColumnResult(name = "thirdSimpleAttribute", type = Double.class)
    }
)

Native Query

Query query = this.entityManager.createNativeQuery(
                    "SELECT r.*, me.id, me.label, mse.*
                     FROM result r 
                         INNER JOIN my_entity me ON r.id_my_entity = me.id_my_entity 
                         INNER JOIN my_sub_entity mse ON me.id_my_sub_entity = mse.id_my_sub_entity",
                    "CustomMapping");

List<Object[]> result = query.getResultList();

// Convert Object[] to List<ResultPojo>

When I do that, I obtain a NullPointerException due to line @ColumnResult(name="mySubPojo", type=MySubPojo.class). Is there a way to manage this kind of mapping?

Mansoor · Answer 1 · 2021-10-04T12:42:57.270

0

You need to use @TypeDef and POJO implementsUserType - POJO as Custom Type https://www.baeldung.com/hibernate-custom-types

Like class MySubPojo implements UserType

edited Oct 04 '21 at 12:42

answered Oct 01 '21 at 16:16

Mansoor

1,157
10
29

If I understood correctly, the `@TypeDef` annotation is used for convert an entity attribute to specific type (like the PhoneNumberType in the example of your link). Here, my problem is the following : I have a sql query which return a POJO, which contains sub-POJO. I need to call a POJO's class in my `@SqlResultSetMapping` or anything like that. – Julien Oct 04 '21 at 12:36
Partially correct. `@TypeDef` is also used for custom types to map a database column to complex Java Type. You can implement `UserType` to do that. – Mansoor Oct 04 '21 at 12:41

score 0 · Answer 2 · answered Oct 14 '21 at 06:32

You can't really do that except with the UserType approach, but that is going away in this form in Hibernate 6.0 and is really cumbersome to use for this particular use case. I would suggest you map out the columns individually to constructor arguments of basic types and construct whatever type you need in the constructor.

If you can avoid the native query, you could make use of Blaze-Persistence Entity Views which I think is the perfect fit for your needs here.

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(Entity1.class)
public interface ResultPojo {
    @IdMapping
    Long getId();
    Integer getFirstSimpleAttribute();
    Integer getSecondSimpleAttribute();
    Double getThirdSimpleAttribute();
    @Mapping("myAssociation")
    MyPojo getMyPojo();

    @EntityView(Entity2.class)
    interface MyPojo {
        @IdMapping
        Long getId();
        String getLabel();
        @Mapping("subAssociation")
        MySubPojo getMySubPojo();
    }
    @EntityView(Entity3.class)
    interface MySubPojo {
        String getName();
    }
}

Assuming an entity model similar to the following:

@Entity
public class Entity1 {
    @Id
    Long id;
    Integer firstSimpleAttribute;
    Integer secondSimpleAttribute;
    Double thirdSimpleAttribute;
    @ManyToOne(fetch = LAZY)
    Entity2 myAssociation;
}
@Entity
public class Entity2 {
    @Id
    Long id;
    String label;
    @ManyToOne(fetch = LAZY)
    Entity3 subAssociation;
}
@Entity
public class Entity3 {
    @Id
    Long id;
    String label;
}

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

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

Page<ResultPojo> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary!

JPA SqlResultSetMapping to pojo with complex object in attribute

2 Answers2