-->
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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: Hibernate + JPA: usability issue w/ counts in Criteria API
PostPosted: Wed Feb 22, 2017 4:56 pm 
Newbie

Joined: Wed Feb 22, 2017 4:35 pm
Posts: 19
I'm using Hibernate 5.2.8 with JPA, no Spring or anything like that. As I'm sure you're familiar, in order to get a total count of objects, a CriteriaQuery<Long> needs to be created. Since I've already written an entire CrtieriaQuery with an existing root / joins / predicates, and since changing the type of an existing CriteriaQuery is a no-no, it'd be great if I could just create a new CriteriaQuery re-using the join chain and conditions that I've already created:

Code:
CriteriaQuery<Long> cntQuery = cb.createQuery(Long.class);
TypedQuery<Long> cntQ = em.createQuery(
   cntQuery.select(
      cb.count(root))
   .where(predicates));


Of course, this won't work because cntQ needs a .from() on it, pointing to a new root (since the root apparently can't be inferred simply from what's being counted?....)
But then, if I need to point cntQ to a new root, I would need to also create new joins on that root to satisfy all my predicates...
And then, if I create new joins, I have to create new predicates based on all those joins...

...and the result is just a big mess of code duplication could have been avoided by sticking with simple string splicing (i.e. just re-use a stringified FROM clause). I could break out the creation of those joins and their predicates into a separate method that is called when constructing both the count and list queries, but this would just bring things to a whole new apex of unintelligibility. So I would need to just duplicate code in order for other devs to be able to interpret this easily at all.

Unfortunately, that code duplication, not to mention the extra CPU used in translating from the criteria API to JPQL, means I'm better off going back to string splicing rather than using the criteria API. I really hope I'm wrong. What can be done about this?


Top
 Profile  
 
 Post subject: Re: Hibernate + JPA: usability issue w/ counts in Criteria API
PostPosted: Fri Feb 24, 2017 4:52 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
Quote:
Unfortunately, that code duplication, not to mention the extra CPU used in translating from the criteria API to JPQL, means I'm better off going back to string splicing rather than using the criteria API. I really hope I'm wrong. What can be done about this?


If you need to build dynamic queries, then you need to use either Criteria API or a tool like jOOQ. Just don't use string concatenation because you risk SQL injection attacks, as explained in this article.

Now back to your problem. Reusability is useful only when it makes sense. Reusing joins or where clauses across distinct queries is going to hurt readability, abnd create coupling between business use case data access methods. That's worse that duplicating a join or a where clause.


Top
 Profile  
 
 Post subject: Re: Hibernate + JPA: usability issue w/ counts in Criteria API
PostPosted: Mon Feb 27, 2017 2:50 pm 
Newbie

Joined: Wed Feb 22, 2017 4:35 pm
Posts: 19
We ultimately decided just to split out API calls for the list of items and the counts so that the same logic can be used for both lists and counts, rather than trying to fetch both in one go. The code is way cleaner this way, and more performant too.


Top
 Profile  
 
 Post subject: Re: Hibernate + JPA: usability issue w/ counts in Criteria API
PostPosted: Tue Feb 28, 2017 3:31 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
It's always a good idea to split queries instead of reusing one that would otherwise grow too complex. Simpler queries generate simple execution plans, so they are faster to execute.


Top
 Profile  
 
 Post subject: Re: Hibernate + JPA: usability issue w/ counts in Criteria API
PostPosted: Tue Feb 28, 2017 7:00 pm 
Newbie

Joined: Wed Feb 22, 2017 4:35 pm
Posts: 19
Re-using the query logic itself for both listing and counting was really straightforward. Pass in a "count" bool to a method that returns a typed query, then you only have to check the boolean twice:

Code:
      CriteriaQuery<Long> cq = cb.createQuery(Long.class);
      CriteriaQuery<Tuple> q = cb.createTupleQuery();
      Root<Stuff> stuff  = (count    ? cq : q)   .from(Stuff.class);


Then when the query is used
Code:
      return (count)    ? em.createQuery(cq.select(cb.count(stuff)).where(predicatess))
                       : em.createQuery( q.multiselect(...... etc))


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

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:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.