openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From catalina wei <catalina....@gmail.com>
Subject Re: how to count sub-elements without retrieving them?
Date Sat, 28 Mar 2009 07:00:49 GMT
Edoardo,
more info...

The SIZE function resulting in a SQL pushdown that should look like the
following:

   SELECT (SELECT COUNT(*) from Sub where Sub.fk = t0.id) from MAINOBJECT t0

There will be no Sub objects returned, and the count is calculated by DB
based on the predicate Sub.fk = t0.id
where fk is the foreign key.

Catalina
On Fri, Mar 27, 2009 at 11:46 PM, catalina wei <catalina.wei@gmail.com>wrote:

> Edoardo,
> JPQL has SIZE function that you can use to get the size of a ToMany
> relation field.
> Assuming your entity class is named MainObject that contains
> public List<Sub> getSub() { return sub; },
> the following query returns the size of the "sub" for the given primary key
> (id) of MainObject:
>
> String query = "SELECT SIZE(m.sub) FROM MainObject m where m.id = ?1 ";
> Query q = em.createQuery(query);
> Long subcount = q.setParameter(1, 123).getSingleResult();
>
> Hope this helps.
>
> Catalina
>   On Wed, Mar 25, 2009 at 12:04 AM, Edoardo Panfili <edoardo@aspix.it>wrote:
>
>> Hi,
>> I have an object that use a one-to many relation
>> --------------------------------
>> @OneToMany(cascade={CascadeType.ALL}, mappedBy="segnalazione",
>> fetch=FetchType.LAZY)
>> public List<Sub> getSub() { return sub; }
>> public void setSub(List<Sub> sub) { this.sub = sub;}
>>
>> @Transient
>> public int getCounter() {
>>   return this.getSub().size();
>> }
>> --------------------------------
>>
>> sometimes I need to know only the number of Sub but getCounter() retrieve
>> from DB a lot of not useful data.
>>
>> I found this fragment of code in openJpa manual
>> --------------------------------
>> EntityManager em = ...
>> Query q = em.createQuery("SELECT MAX(x.price) FROM Magazine x WHERE
>> x.title = 'JDJ'");
>> Number result = (Number) q.getSingleResult();
>> --------------------------------
>>
>> This is usefull in a java program but I need to use the counter in a JSP
>> (a servlet retrieves the data and a JSP write a summary of data retrieved
>> whit the counter), It is more useful to me to use a property of my object
>> (like getCounter() ).
>>
>> I can't figure how to write it, can someone help me?
>>
>> thank you
>> Edoardo
>>
>>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message