CreateNativeQuery POJO mapping (non-entity)

Question

I have a slight problem at a glance:

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

And I want to write the selection result in POJO, for example:

public class AggregateStatsDto {

    private int total;

    private long totalSum;

    // getters, setters, cosntructors
}

What is the best way to achieve this?

I can use JPA 2.1 and tried to use @SqlResultSetMapping in combination 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 like he wants @Entity anyway. But I just want a POJO.

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

Thanks in advance!

Answer 1

I solved my problem as follows: This is the request:

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

Then I turned to the internal Hibernate session inside the entity manager and application scalars / resultTransformer. That's 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();

Answer 2

@SqlResultSetMapping , , DTO. , SqlResultSetMapping -.

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

Answer 3

Hibernate pojo, propper.

select new Pojo(p1, p2, p3,...) from entity)

Pojo.

Answer 4

, , .

: , , , , .

Now I want to map the output of this source code that we get with the getResultList () method to the native POJO / non-JPA class. The result of getResultList () is a list of objects, that is, List, so we need to manually scroll and set the values ​​in our bean / pojo class. Below is a snippet below.

So my request is

// This is your POJO/bean class where you have settter/getter 
// methods of values you want to fetch from the database
List<YourPOJO> yourPOJOList = new ArrayList<YourPOJO>();
YourPOJO pojo = null;

StringBuffer SQL = new StringBuffer();
// I am creating some random SQL query
SQL.append("SELECT tb1.col1, ");
SQL.append(" tb1.col2, ");
SQL.append(" tb2.col3, ");
SQL.append("FROM Table1 tb1, Table2 tb2 ");
SQL.append("WHERE tb1.ID = tb2.ID       ");

qry = getEntityManager().createNativeQuery(SQL.toString());
List<Object[]> cdList = qry.getResultList();

for(Object[] object : cdList){
pojo = new YourPojo();

// Here you manually obtain value from object and map to your pojo setters
pojo.setCode(object[0]!=null ? object[0].toString() : "");

pojo.setProductName(object[1]!=null ? object[1].toString() : "");

pojo.setSomeCode(object[2]!=null ? object[2].toString() : "");

yourPOJOList.add(pojo);
}