user2138356 user2138356 - 2 years ago 1059
SQL Question

createNativeQuery mapping to POJO (non-entity)

I have an easy problem at the first glance:

.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


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

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 Source

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
                .addScalar("total", LongType.INSTANCE)
                .addScalar("amountOfSales", LongType.INSTANCE)
                .addScalar("amountOfProducts", LongType.INSTANCE)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download