-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
bKThanh
 Post subject: How to write a reporting SQL query using Criteria
PostPosted: Wed Nov 30, 2016 7:33 pm 
Newbie

Joined: Wed Nov 30, 2016 7:01 pm
Posts: 2
I am trying to write this query in Hibernate but don't know how to do it.
This query is giving me the correct result when it's ran.

Code:
select foo.package_id,
b.title,
round(b.expected_cost) expected_cost,
round(b.expected_cost) expected_cost,
from
(select package_id, round(sum(expected_cost)) total
from ocs_dbo.emr_req_casm_final
group by package_id
order by sum(expected_cost), package_id) as foo, ocs_dbo.emr_req_casm_final b
where b.package_id= foo.package_id
order by total, b.expected_cost


Basically, I am trying to generate a report that is sorting the sum of expected_cost group by package_id

Example:
Package_id expected_cost sum_expected_cost
5 10 60
5 20 60
5 30 60
1 10 80
1 15 80
1 25 80
1 30 80
2 100 100
3 10 120
3 110 120
4 20 200
4 80 200
4 100 200


Thanks
Top
 Profile  
 
vlad
 Post subject: Re: Complex Hibernate Query Using Criteria
PostPosted: Thu Dec 01, 2016 1:31 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
The simple answer is: You can't do it with Criteria or JPQL/HQL.

The longer answer is: You shouldn't even do it. Entity queries are meant for fetching entities that you plan on modifying, either in the currently running Persistence Context, or in a future Persistence Context so that you can benefit from:

- automatic dirty checking
- lost update prevention

For reporting and analytics queries, native SQL is the right tool. If you build it dynamically, then you should use jOOQ.
Top
 Profile  
 
christianbeikov
 Post subject: Re: How to write a reporting SQL query using Criteria
PostPosted: Thu Dec 01, 2016 8:22 am 
Beginner
Beginner

Joined: Sat May 21, 2011 7:40 am
Posts: 22
You might also want to take a look at Blaze-Persistence which allows you to do stuff like that with your JPA model.

Although I would rather recommend to implement this kind of stuff with a window function, the code for doing your query would look something like this

Code:
CriteriaBuilder<Tuple> cb = cbf.create(em, Tuple.class)
    .with(FooCte.class)
        .bind("packageId").select("packageId")
        .bind("total").select("FUNCTION('ROUND', SUM(expectedCost))")
        .from(ReportEntity.class)
        .groupBy("packageId")
    .end()
    .select("b.packageId")
    .select("b.title")
    .select("b.expectedCost")
    .select("foo.total")
    .from(FooCte.class, "foo")
    .from(ReportEntity.class, "b")
    .where("foo.packageId").eqExpression("b.packageId")
    .orderByAsc("foo.total")
    .orderByAsc("b.expectedCost");
Top
 Profile  
 
vlad
 Post subject: Re: How to write a reporting SQL query using Criteria
PostPosted: Thu Dec 01, 2016 8:45 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
Blaze Persistence is definitely worth a try. Thanks, Christian for the tip.
Top
 Profile  
 
bKThanh
 Post subject: Re: How to write a reporting SQL query using Criteria
PostPosted: Thu Dec 01, 2016 12:10 pm 
Newbie

Joined: Wed Nov 30, 2016 7:01 pm
Posts: 2
christianbeikov wrote:
You might also want to take a look at Blaze-Persistence which allows you to do stuff like that with your JPA model.

Although I would rather recommend to implement this kind of stuff with a window function, the code for doing your query would look something like this

Code:
CriteriaBuilder<Tuple> cb = cbf.create(em, Tuple.class)
    .with(FooCte.class)
        .bind("packageId").select("packageId")
        .bind("total").select("FUNCTION('ROUND', SUM(expectedCost))")
        .from(ReportEntity.class)
        .groupBy("packageId")
    .end()
    .select("b.packageId")
    .select("b.title")
    .select("b.expectedCost")
    .select("foo.total")
    .from(FooCte.class, "foo")
    .from(ReportEntity.class, "b")
    .where("foo.packageId").eqExpression("b.packageId")
    .orderByAsc("foo.total")
    .orderByAsc("b.expectedCost");

Thanks Christian,

I am not sure what is cbf?
Top
 Profile  
 
vlad
 Post subject: Re: How to write a reporting SQL query using Criteria
PostPosted: Thu Dec 01, 2016 12:34 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
Check out Blaze Persistence docs.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.