You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Edoardo Panfili <ed...@aspix.it> on 2009/03/25 08:04:32 UTC
how to count sub-elements without retrieving them?
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
Re: how to count sub-elements without retrieving them?
Posted by Edoardo Panfili <ed...@aspix.it>.
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<ca...@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<ed...@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
>>>
>>>
>
Re: how to count sub-elements without retrieving them?
Posted by catalina wei <ca...@gmail.com>.
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 <ca...@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 <ed...@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
>>
>>
>
Re: how to count sub-elements without retrieving them?
Posted by catalina wei <ca...@gmail.com>.
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 <ed...@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
>
>
Re: how to count sub-elements without retrieving them?
Posted by Edoardo Panfili <ed...@aspix.it>.
Paul Copeland ha scritto:
> Hi Edoardo -
>
> This is a bit off the topic of your question, but I am interested in
> your pattern for setting the EntityManager in a filter. That seems like
> a reasonable thing to do. I wonder if other servlet developers on the
> list do it any other way?
>
> One question I have for you is that doFilter() throws ServletException
> and IOException. Don't you have to declare those in the signature? And
> lastly, it would be a good practice to put entityManager.close() in a
> finally clause.
yes, the I cut down too much of the code.
You are right regarding try/finally block I am going to modify my code:
---------------------------------------------------
import java.io.IOException;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
public class ManagerJpa implements Filter{
EntityManagerFactory entityManagerFactory;
public void init(FilterConfig arg) throws ServletException {
entityManagerFactory =
Persistence.createEntityManagerFactory("openjpa-habitat");
}
public void destroy() {
}
public void doFilter(ServletRequest req, ServletResponse res,
FilterChain chain) throws IOException, ServletException {
EntityManager entityManager = null;
entityManager = entityManagerFactory.createEntityManager();
req.setAttribute("em", entityManager);
try {
chain.doFilter(req,res);
} finally{
entityManager.close();
}
}
}
---------------------------------------------------
Edoardo
Re: how to count sub-elements without retrieving them?
Posted by Paul Copeland <te...@jotobjects.com>.
Hi Edoardo -
This is a bit off the topic of your question, but I am interested in
your pattern for setting the EntityManager in a filter. That seems like
a reasonable thing to do. I wonder if other servlet developers on the
list do it any other way?
One question I have for you is that doFilter() throws ServletException
and IOException. Don't you have to declare those in the signature? And
lastly, it would be a good practice to put entityManager.close() in a
finally clause.
- Paul
On 3/25/2009 10:18 AM, Edoardo Panfili wrote:
> Kevin Sutter ha scritto:
>> What I meant is how are you maintaining the lifecycle of the
>> EntityManager?
>> Are you using application-managed persistence and creating your own
>> EMF and
>> associated EM's? Where are you doing this logic?
> I must re-read the open-jpa manual.
>
> My application runs inside tomcat 6.0.18
> I have a filter in my application that does all the work:
> -------------------------------------------------
> public class ManagerJpa implements Filter{
>
> EntityManagerFactory entityManagerFactory;
> public void init(FilterConfig fc) throws ServletException {
> entityManagerFactory =
> Persistence.createEntityManagerFactory("openjpa-habitat");
> }
>
> public void doFilter(ServletRequest req, ServletResponse res,
> FilterChain chain){
> EntityManager entityManager = null;
> entityManager = entityManagerFactory.createEntityManager();
> req.setAttribute("em", entityManager);
> chain.doFilter(req,res);
> entityManager.close();
> }
>
> }
> ---------------------------------------------------
> in the servlet I use
> entityManager = (EntityManager) req.getAttribute("em");
> I do all the work and at the end of servlet-jsp cycle the filter
> closes the manager.
>
>
>>> my solution (not so beautiful) based on your post
>>> -------------------------------------------
>>> @Entity
>>> @NamedQueries({
>>> @NamedQuery(name="counter",
>>> query="SELECT COUNT(x) FROM SubObject x WHERE
>>> x.mainObject.idMainObject =
>>> ?1")
>>> })
>>> public class MainObject {
>>> [.....]
>>> private int count;
>>> @Transient
>>> public int getCount() { return count; }
>>> public void setCount(int count) { this.count =count; }
>>>
>>> }
>>> -------------------------------------------
>>>
>>> And during object retrieve
>>> -------------------------------------------
>>> public class find extends HttpServlet{
>>>
>>> public void doPost(HttpServletRequest req, HttpServletResponse res){
>>> [.....]
>>> queryString = "SELECT * FROM MainObject WHERE ........";
>>> Query query = entityManager.createNativeQuery(queryString,
>>> MainObject.class);
>>> lista = query.getResultList();
>>> Query q = entityManager.createNamedQuery("counter");
>>> for(Object o: lista){
>>> MainObject k = ((MainObject)o);
>>> q.setParameter(1, k.getIdMainObject());
>>> Number result = (Number) q.getSingleResult();
>>> k.setOoo(result.intValue());
>>> }
>>> [.....]
>>> }
>>>
>>> }
>>> -------------------------------------------
>>>
>>> Now my application uses less memory, is faster (but less clean as
>>> you say).
>>
>>
>> Cool. So, we're making progress... Tell me this, what would your
>> doPost
>> method look like if you had your way? It looks confusing to do the
>> native
>> query just to get the set of MainObject id's and then rifle through the
>> named query invocations to count the number of SubObjects. I'm
>> trying to
>> figure out what your intended usage is. And, I'm trying to figure
>> out how
>> you are separating your data model from your business logic.
> The problem probably is: I am using OpenJPA in a wrong way.
>
> The idea is:
> (servlet)
> 1-parse the HttpServletRequest to build a MainObject as search pattern
> 2-retrieve a List<MainObject> using
> queryString = ("SELECT * FROM Segnalazione WHERE "+wherePart+" ORDER
> BY code USING ~<~ NULLS FIRST");
> 3-count the number of SubObject of each retieved MainObject, set this
> number as a property of MainObject.
> 4-bind the List<MainObject> to the request
> (JSP)
> 4-build an html page that views the List<MainObject>
>
>
> In (2) I am now using nativeQuery because I feel more comfortable with
> plain SQL :-(
>
> The main confusion is the "for(Object o: lista)" I know, but this is
> the part that speeds up my servlet and saves memory. Before that I
> simply retrieve the whole list of SubObject to use only the size of
> that list.
>
> Now the data model and business logic are mixed, this is not a clean
> to do the work :-(
>
>
> Thank you
> Edoardo
>
>
>
Re: how to count sub-elements without retrieving them?
Posted by Edoardo Panfili <ed...@aspix.it>.
Kevin Sutter ha scritto:
> What I meant is how are you maintaining the lifecycle of the EntityManager?
> Are you using application-managed persistence and creating your own EMF and
> associated EM's? Where are you doing this logic?
I must re-read the open-jpa manual.
My application runs inside tomcat 6.0.18
I have a filter in my application that does all the work:
-------------------------------------------------
public class ManagerJpa implements Filter{
EntityManagerFactory entityManagerFactory;
public void init(FilterConfig fc) throws ServletException {
entityManagerFactory =
Persistence.createEntityManagerFactory("openjpa-habitat");
}
public void doFilter(ServletRequest req, ServletResponse res,
FilterChain chain){
EntityManager entityManager = null;
entityManager = entityManagerFactory.createEntityManager();
req.setAttribute("em", entityManager);
chain.doFilter(req,res);
entityManager.close();
}
}
---------------------------------------------------
in the servlet I use
entityManager = (EntityManager) req.getAttribute("em");
I do all the work and at the end of servlet-jsp cycle the filter closes
the manager.
>> my solution (not so beautiful) based on your post
>> -------------------------------------------
>> @Entity
>> @NamedQueries({
>> @NamedQuery(name="counter",
>> query="SELECT COUNT(x) FROM SubObject x WHERE x.mainObject.idMainObject =
>> ?1")
>> })
>> public class MainObject {
>> [.....]
>> private int count;
>> @Transient
>> public int getCount() { return count; }
>> public void setCount(int count) { this.count =count; }
>>
>> }
>> -------------------------------------------
>>
>> And during object retrieve
>> -------------------------------------------
>> public class find extends HttpServlet{
>>
>> public void doPost(HttpServletRequest req, HttpServletResponse res){
>> [.....]
>> queryString = "SELECT * FROM MainObject WHERE ........";
>> Query query = entityManager.createNativeQuery(queryString,
>> MainObject.class);
>> lista = query.getResultList();
>> Query q = entityManager.createNamedQuery("counter");
>> for(Object o: lista){
>> MainObject k = ((MainObject)o);
>> q.setParameter(1, k.getIdMainObject());
>> Number result = (Number) q.getSingleResult();
>> k.setOoo(result.intValue());
>> }
>> [.....]
>> }
>>
>> }
>> -------------------------------------------
>>
>> Now my application uses less memory, is faster (but less clean as you say).
>
>
> Cool. So, we're making progress... Tell me this, what would your doPost
> method look like if you had your way? It looks confusing to do the native
> query just to get the set of MainObject id's and then rifle through the
> named query invocations to count the number of SubObjects. I'm trying to
> figure out what your intended usage is. And, I'm trying to figure out how
> you are separating your data model from your business logic.
The problem probably is: I am using OpenJPA in a wrong way.
The idea is:
(servlet)
1-parse the HttpServletRequest to build a MainObject as search pattern
2-retrieve a List<MainObject> using
queryString = ("SELECT * FROM Segnalazione WHERE "+wherePart+" ORDER BY
code USING ~<~ NULLS FIRST");
3-count the number of SubObject of each retieved MainObject, set this
number as a property of MainObject.
4-bind the List<MainObject> to the request
(JSP)
4-build an html page that views the List<MainObject>
In (2) I am now using nativeQuery because I feel more comfortable with
plain SQL :-(
The main confusion is the "for(Object o: lista)" I know, but this is the
part that speeds up my servlet and saves memory. Before that I simply
retrieve the whole list of SubObject to use only the size of that list.
Now the data model and business logic are mixed, this is not a clean to
do the work :-(
Thank you
Edoardo
Re: how to count sub-elements without retrieving them?
Posted by Kevin Sutter <kw...@gmail.com>.
Edoardo,
On Wed, Mar 25, 2009 at 10:20 AM, Edoardo Panfili <ed...@aspix.it> wrote:
> Kevin Sutter ha scritto:
>
>> Maybe what you should consider is defining a NamedQuery in your Entity
>> class
>> and then reference that for performing the desired "getCounter" behavior.
>> You would still need access to your EM, but you need that anyway for any
>> of
>> these JPA-related operations, don't you? How are you maintaining your
>> EntityManager/PersistenceContext?
>>
> I am writing by myself persistence.xml (no automatic tools)
What I meant is how are you maintaining the lifecycle of the EntityManager?
Are you using application-managed persistence and creating your own EMF and
associated EM's? Where are you doing this logic?
>
>
>
> my solution (not so beautiful) based on your post
> -------------------------------------------
> @Entity
> @NamedQueries({
> @NamedQuery(name="counter",
> query="SELECT COUNT(x) FROM SubObject x WHERE x.mainObject.idMainObject =
> ?1")
> })
> public class MainObject {
> [.....]
> private int count;
> @Transient
> public int getCount() { return count; }
> public void setCount(int count) { this.count =count; }
>
> }
> -------------------------------------------
>
> And during object retrieve
> -------------------------------------------
> public class find extends HttpServlet{
>
> public void doPost(HttpServletRequest req, HttpServletResponse res){
> [.....]
> queryString = "SELECT * FROM MainObject WHERE ........";
> Query query = entityManager.createNativeQuery(queryString,
> MainObject.class);
> lista = query.getResultList();
> Query q = entityManager.createNamedQuery("counter");
> for(Object o: lista){
> MainObject k = ((MainObject)o);
> q.setParameter(1, k.getIdMainObject());
> Number result = (Number) q.getSingleResult();
> k.setOoo(result.intValue());
> }
> [.....]
> }
>
> }
> -------------------------------------------
>
> Now my application uses less memory, is faster (but less clean as you say).
Cool. So, we're making progress... Tell me this, what would your doPost
method look like if you had your way? It looks confusing to do the native
query just to get the set of MainObject id's and then rifle through the
named query invocations to count the number of SubObjects. I'm trying to
figure out what your intended usage is. And, I'm trying to figure out how
you are separating your data model from your business logic.
>
> Can I map a method of my "MainObject" to a specific query? something like
> namedQuery but at method level.
No. The idea of named query is to be available for any usage of that
Entity. It's not meant to be fired due to a method request. Although the
idea sounds interesting... :-)
Thanks,
Kevin
>
>
>
> thank you
> Edoardo
>
>
Re: how to count sub-elements without retrieving them?
Posted by Edoardo Panfili <ed...@aspix.it>.
Kevin Sutter ha scritto:
> Edoardo,
> If I understand your desired scenario, you are hoping to define a "smart"
> getCounter() on your Entity that can count the number of Subs without
> actually retrieving and generating the complete List from the database. Is
> that what you are trying to do?
sorry for my English, you understood correctly.
> That might be easier said than done. You are mixing business logic with
> your data model. I would think that sticking with a Query of some kind
> would be the better way to go. The example you cited below mentions the MAX
> aggregate function, but wouldn't you be more interested in the COUNT
> function?
I am mixing, yes, but retrieve all the records is memory-consuming.
Yes, is the example from the manual, I nedd a count(*).
> Maybe what you should consider is defining a NamedQuery in your Entity class
> and then reference that for performing the desired "getCounter" behavior.
> You would still need access to your EM, but you need that anyway for any of
> these JPA-related operations, don't you? How are you maintaining your
> EntityManager/PersistenceContext?
I am writing by myself persistence.xml (no automatic tools)
my solution (not so beautiful) based on your post
-------------------------------------------
@Entity
@NamedQueries({
@NamedQuery(name="counter",
query="SELECT COUNT(x) FROM SubObject x WHERE x.mainObject.idMainObject
= ?1")
})
public class MainObject {
[.....]
private int count;
@Transient
public int getCount() { return count; }
public void setCount(int count) { this.count =count; }
}
-------------------------------------------
And during object retrieve
-------------------------------------------
public class find extends HttpServlet{
public void doPost(HttpServletRequest req, HttpServletResponse res){
[.....]
queryString = "SELECT * FROM MainObject WHERE ........";
Query query = entityManager.createNativeQuery(queryString,
MainObject.class);
lista = query.getResultList();
Query q = entityManager.createNamedQuery("counter");
for(Object o: lista){
MainObject k = ((MainObject)o);
q.setParameter(1, k.getIdMainObject());
Number result = (Number) q.getSingleResult();
k.setOoo(result.intValue());
}
[.....]
}
}
-------------------------------------------
Now my application uses less memory, is faster (but less clean as you say).
Can I map a method of my "MainObject" to a specific query? something
like namedQuery but at method level.
thank you
Edoardo
Re: how to count sub-elements without retrieving them?
Posted by Kevin Sutter <kw...@gmail.com>.
Edoardo,
If I understand your desired scenario, you are hoping to define a "smart"
getCounter() on your Entity that can count the number of Subs without
actually retrieving and generating the complete List from the database. Is
that what you are trying to do?
That might be easier said than done. You are mixing business logic with
your data model. I would think that sticking with a Query of some kind
would be the better way to go. The example you cited below mentions the MAX
aggregate function, but wouldn't you be more interested in the COUNT
function?
Maybe what you should consider is defining a NamedQuery in your Entity class
and then reference that for performing the desired "getCounter" behavior.
You would still need access to your EM, but you need that anyway for any of
these JPA-related operations, don't you? How are you maintaining your
EntityManager/PersistenceContext?
Let's start with that before diving in too deep...
Kevin
On Wed, Mar 25, 2009 at 2:04 AM, Edoardo Panfili <ed...@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
>
>