CODEWITHSUNDEEP
javahibernate
user1865027
user1865027

Reputation: 3647

How to map result got from native query in hibernate?

I have a query that has more columns then what my entity class has.

In order to not let hibernate complaints, I have to add an annotation to the field like 

@Transient
private Integer count;

But by doing this makes hibernate not able to map count. Let's say my query is 

session.createSQLQuery("SELECT p.*, count(p.id), sqrt(1+2) as distance FROM post p group by p.id")

I know the query doesn't make any logical sense. This is just for example. The columns return from query above will have everything in post and two extra columns, count and distance. I wanted to map the result to my entity with count and distance are annotated with @Transient, or if there's a better way to map the result. I'm more than happy to do so. The goal is not to do this in an entity but a class with mapped result. I've tried calling addEntity() but doesn't seem to help.

Upvotes: 0

Views: 3638

Answers (2)

Simon Martinelli
Simon Martinelli

Reputation: 36103

I see that you are using Hibernate so Yathish answer works fine.

But if you want to do it with JPA spec then you can use Result Set Mapping

Query q = em.createNativeQuery(
    "SELECT c.id, c.name, COUNT(o) as orderCount, AVG(o.price) AS avgOrder " +
    "FROM Customer c " +
    "JOIN Orders o ON o.cid = c.id " +
    "GROUP BY c.id, c.name",
    "CustomerDetailsResult");

@SqlResultSetMapping(name="CustomerDetailsResult",
    classes={
        @ConstructorResult(targetClass=com.acme.CustomerDetails.class,
            columns={
                @ColumnResult(name="id"),
                @ColumnResult(name="name"),
                @ColumnResult(name="orderCount"),
                @ColumnResult(name="avgOrder", type=Double.class)})
    })

There you have to specifiy the mappin of the columns from the SQL result set to the DTO.

And if you think this is to complicated there is a open source project called QLRM (Query Lanaguage Result Mapper) that mapps any SQL statement to a POJO.

http://simasch.github.io/qlrm/

And last but not least if you will do extensive SQL processing why not have a look at jOOQ: https://www.jooq.org/

Upvotes: 1

Yathish Manjunath
Yathish Manjunath

Reputation: 2029

You can use Result Set Transformer to achieve this.

Step 1 ) Create a new DTO class with all the fields that you query going to return

Step 2 ) Add the below line 

setResultTransformer( Transformers.aliasToBean(DTO.class))

Example :

List resultWithAliasedBean = session.createQuery(
  "SELECT p.*, count(p.id), sqrt(1+2) as distance FROM post p group by p.id")
  .setResultTransformer(Transformers.aliasToBean(DTO.class))
  .list();

DTO dto = (DTO) resultWithAliasedBean.get(0);

Note : Make sure the field names in the DTO class match the column name which your query is returning.

Upvotes: 2

Related Questions