user2138356 user2138356 - 7 months ago 465
SQL Question

createNativeQuery mapping to POJO (non-entity)

I have an easy problem at the first glance:

entityManager()
.createNativeQuery("select count(*) as total, select sum(field) as total_sum ... blabla")


And I want to write select result into POJO, like this:

public class AggregateStatsDto {

private int total;

private long totalSum;

// getters, setters, cosntructors
}


What the best way to achieve this?

I can use JPA 2.1 and tried to use @SqlResultSetMapping in conjuction with @ConstructorResult:

@SqlResultSetMapping(name = "AggregateStatsResult", classes = {
@ConstructorResult(targetClass = AggregateStatsDto.class,
columns = {
@ColumnResult(name = "total"),
@ColumnResult(name = "totalSum")
})
})
public class AggregateStatsDto {

private long total;

private int totalSum;

// getters, setters, cosntructors
}


Query:

AggregateStatsDto result = (AggregateStatsDto) entityManager()
.createNativeQuery("select count(*) as total, select sum(field) as total_sum ... blabla", "AggregateStatsResult")
.getSingleResult();


But no luck. It seems that it wants @Entity anyway. But I want just a POJO.

org.hibernate.MappingException: Unknown SqlResultSetMapping [AggregateStatsResult]"


Thanks in advance!

Answer

I resolved my problem in following way: This is a query:

  final Query query = Sale.entityManager().createNativeQuery(...);

Then I accessed to internal Hibernate session inside entity manager and applied scalars/resultTransformer. Thats all!

 // access to internal Hibernate of EntityManager
        query.unwrap(SQLQuery.class)
                .addScalar("total", LongType.INSTANCE)
                .addScalar("amountOfSales", LongType.INSTANCE)
                .addScalar("amountOfProducts", LongType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(SaleStatsInfo.class));

        ...
        query.getSingleResult();