Help coderanch get a
new server
by contributing to the fundraiser
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Difference Between count() and size

 
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
One Student entity can have many Presentation entity, which is held as a Collection. What I need to know if there is any difference between:

select count(s.presentations) from Student s

select size(s.presentations) from Student s
 
Ranch Hand
Posts: 856
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
SIZE- Arithmetic Functions
The SIZE function returns an integer value, the number of elements of the collection. If the collection is
empty, the SIZE function evaluates to zero.

Syntax for Size-
SIZE(collection_valued_path_expression)

COUNT- Aggregate function
The following aggregate functions can be used in the SELECT clause of a query: AVG, COUNT, MAX,
MIN, SUM. the path expression that is the argument to the aggregate
function must terminate in a state-field. The path expression argument to COUNT may terminate in
either a state-field or a association-field, or the argument to COUNT may be an identification variable.

Syntax for Count-

COUNT ([DISTINCT] identification_variable | state_field_path_expression |
single_valued_association_path_expression)
 
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Personally, i would suggest the use of count() instead of size.
Because count() will get the count while querying the database,
on the other hand, size() will load the entities and then return their size, because there is no such thing as size in the db.
since you only need the number of rows, better get them from the db instead of getting from java.

 
Sheriff
Posts: 14691
16
Eclipse IDE VI Editor Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Rahul Babbar wrote:better get them from the db instead of getting from java.


The SIZE function is not used to get anything from java. The persistence provider will turn the JPQL in a native query, whether you use COUNT or SIZE. SIZE is a function used in conditions, so it's different from COUNT.
 
Rahul Babbar
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Chris,

What i meant was if we use size(), then the persistence provider 'may'(not sure) load all the records and then return their size. which will not be good, since it'll affect performance.
If the persistence provider converts it into appropriate count query and executes on the db(as you said), it should be fine.

Thanks for the clarification..
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE VI Editor Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think the misunderstanding came from the first post. There's no such JPQL like "select size(s.presentations) from Student s". The size function is used in conditions. It's a handy function which avoids to type in subqueries. For example the following :

could be written like :
 
Amandeep Singh
Ranch Hand
Posts: 856
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
the same thing in my explanation but without example. :idea:
 
It's weird that we cook bacon and bake cookies. Eat this tiny ad:
We need your help - Coderanch server fundraiser
https://coderanch.com/t/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic