21

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!

Improve this question
3
  • If you want your class to be a pojo why don't you fetch the result of the query manually into your pojo? It will be much simpler. And your query seems to be wrong, at least this part: "select count(*) as total, select sum(field) as total_sum ..." Commented Nov 9, 2014 at 19:13
  • do you mean iterate over objects? it is pretty ugly... but I will be forced to use it as a last effort Commented Nov 9, 2014 at 20:25
  • 1
    getting result set into DTO with native SQL Query in Hibernate Commented Nov 9, 2014 at 22:53

6 Answers 6

Reset to default
14

Put your @SqlResultSetMapping annotation in a class which is an actual entity, not in the DTO Class. Your entity manager can not discover your mapping when you annotate a SqlResultSetMapping in a non-entity.

@SqlResultSetMapping(name = "AggregateStatsResult", classes = { @ConstructorResult(targetClass = AggregateStatsDto.class, columns = { @ColumnResult(name = "total"), @ColumnResult(name = "totalSum") }) }) @Entity public class SomeOtherClassWhichIsAnEntity {
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I was need to retrieve plain POJO not entity. I tried your approach without success. Anyway I solved this, see answer :)
you misunderstood my answer. With my approach, you are still retrieving plain POJO (AggregateStatsDto), just that you need to put the SqlResultSetMapping annotation into some other class which is an Entity for the entity manager to recognize it.
It is really annoying that JPA expects us to create this intermeidate entity class to return an adhoc query thi sway...
This presumes that you have an entity for your queried table. But what if you query against a view, which is a join of multiple tables, that are not tracked by hibernate?
No. All we are doing is putting the SqlResultSetMapping annotation into some arbitrary Java class that is annotated as an Entity. Said entity is not part of your query, but just uses as the medium to make JPA aware of the defined mapping.
8

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();
Improve this answer

4 Comments

setResultTransformer is now deprecated.
@Tvaroh what to use now instead of setResultTransformer?
This is more cleaner than polluting with the SqlResultMapping where you store the mapping somewhere else, which is bad for maintenance.
@LosmiNCL org.hiberbate.type.NativeQuery
1

This is not the suggested way . The more efficient way is mentioned above with @SqlResultSetMapping but in case you want something more hardcore you can access the Entity Manager's result by the object it returns .

For instance :

 Query q = getEntityManager().createNativeQuery(queryString);

Trggering that with q.getSingleResult();brings back to u an Object of type java.util.HashMap which you can use to access your results row by row .

For instance:

 Query q = getEntityManager().createNativeQuery(queryString); java.util.HashMap res = (java.util.HashMap) q.getSingleResult(); for (Object key : res.keySet()) { System.out.println(key.toString()); System.out.println( res.get(key).toString()); }
Improve this answer

Comments

0

I was stuck for a while on this one. I found that the most common solution is below. Do not forget to add name of the mapping on the createNativeQuery method 2nd parameter. Your pojo should then have no annotations but will be get mapped from entity class.

//Place SqlResultSetMapping annotation in Entity class: @SqlResultSetMapping(name = "AggregateStatsResult", classes = { @ConstructorResult(targetClass = AggregateStatsDto.class, columns = { @ColumnResult(name = "total"), @ColumnResult(name = "totalSum") }) }) @Entity public EntityFromExistingTableInDB{ ...fields } //1st param is your query, 2nd param is the name of your mapping above Query query = entityManager.createNativeQuery("yourQuery", "AggregateStatsResult");
Improve this answer

1 Comment

as far as I understand the question, it was explicitly asked for a solution for non-entities. In my case, I need to extract DTOs from an SQL view for example, that has no entity.
-1

ANSWER MAY BE MISSING INFORMATION

Here is the most direct way to do it in my opinion.

Declare a POJO/DTO and label it with @NativeQueryResultEntity. Note I am using Lombok @Data to declare getters/setters here.

import com.pls.core.jpa.nativequery.NativeQueryResultEntity; import lombok.Data; @Data @NativeQueryResultEntity public class FieldsDTO{ @NativeQueryResultColumn(index = 0) private String fieldA; @NativeQueryResultColumn(index = 1) private String fieldB; @NativeQueryResultColumn(index = 2) private String fieldC; }

Then use NativeQueryResultsMapper

String queryStr = "select field_a, field_b, field_c from db.table" Query query = entityManager.createNativeQuery(queryStr); List result = NativeQueryResultsMapper.map(query.getResultList(), FieldsDTO.class);

That's all you should need to do. result will be a list of FieldsDTO objects.

Improve this answer

2 Comments

I like your approach, but I can't find com.pls.core.jpa package with NativeQueryResultEntity. I've searched in Maven repository. Whhich version of jpa is this and where I can find it?
I'm sorry, I think there may be custom code in my answer that I didn't realize was custom...
-2

Hibernate will fill any pojo if you privide a propper constructor.

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

will return a list of Pojo instances.

Improve this answer

4 Comments

Honestly not sure. I used this type of mapping alot with aggregations and when there is no hsql function mapping there is no replacement. So try using createQuery instead.
Unfortunately constructor expressions work only with JPQL and Criteria API.
I can't use anything except native SQL since have to use some postgres specific and window functions which is not supported by JPQL, HQL, Criteria.
It seems to be that you have to live with Object[].

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.