Il 28-03-2009 8:00, catalina wei ha scritto:
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.
Thank you! now I can use one of ----------------------- SELECT SIZE(s.subObject) FROM MainObject s where s = ?1 SELECT COUNT(x) FROM SubObject x WHERE x.mainObject = ?1 ----------------------- both works fine. Edoardo
Catalina On Fri, Mar 27, 2009 at 11:46 PM, catalina wei<catalina....@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<edoa...@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
