-
1. Re: JbossQL and m.createQuery(.. OFFSET 5 LIMIT 23)
cjc Aug 25, 2006 2:34 PM (in response to cjc)Hi again,
the reselution is so easy and nice, and now i found the real documentation for that:
http://docs.jboss.org/ejb3/app-server/HibernateEntityManager/reference/en/html_single/index.html@PersistenceContext Entitymanager manager; public List<SomeObj> findAllBlub(Integer offset, Integer limit){ String order = "ORDER BY ....." Query q = manager.createQuery("SELECT o FROM SomeObj o "+ order ); q.setFirstResult(offset); q.setMaxResults(limit); return q.getResultList(); }
thx all
cjc -
2. Re: JbossQL and m.createQuery(.. OFFSET 5 LIMIT 23)
laszlo.fogas Sep 3, 2006 5:48 AM (in response to cjc)Hi,
I found the same solution, but when i examined a bit more i realized that the whole resultset is generated every time, and these methods only cut a part of that. It does not generate offset/limit like sql query. Am I right? Because it could cause some performance problems.
Lazlo -
3. Re: JbossQL and m.createQuery(.. OFFSET 5 LIMIT 23)
laszlo.fogas Sep 3, 2006 6:04 AM (in response to cjc)ok
I was wrong:
It generates the desired offset/limit queries unless you use join fetch in your query to initialize lazy collections.
In the docs: (http://docs.jboss.org/ejb3/app-server/HibernateEntityManager/reference/en/html_single/index.html#d0e751)
"Nor should fetch be used together with setMaxResults() or setFirstResult(). It is possible to create a cartesian product by join fetching more than one collection in a query (as in the example above), be careful the result of this product isn't bigger than you expect. Join fetching multiple collection roles also sometimes gives unexpected results for bag mappings, so be careful about how you formulate your queries in this case.
TODO: The last statement is useless and typical developer thinking, please elaborate. The word "sometimes" should never appear in any technical documentation."
funny :) So what is the status of this problem(?) now?
I know the collection.size() workaround, but i don't like it!! I think that solution is much slower. Is there other solution?
lazlo -
4. Re: JbossQL and m.createQuery(.. OFFSET 5 LIMIT 23)
laszlo.fogas Sep 10, 2006 9:31 AM (in response to cjc)ok, i try again :)
So, i have an object with lots of lazy onetomany relations. I have second level cache enabled(TreeCache). I would like to use pagination with the setfirstresult/maxresult methods.
If i initialize the lazy collections with calling the size method, and use pagination the entity manager generates the limit/offset into the sql query.
(i set the a show sql param to true.)
But this way the cache does not operate as good as i expect: It's slower than dbhitting.
Here comes the sopution:
http://www.jboss.com/index.html?module=bb&op=viewtopic&t=88317
Which says that i should use fetching joins to initialize the collections, because the implementation likes that much more.. OK i did this.
But than the pagination breaks.. there's no limit and offset in my queries.
The documentation has a funny comment on this:
"Note that the fetch construct may not be used in queries called using scroll() or iterate(). Nor should fetch be used together with setMaxResults() or setFirstResult(). "
What does this mean?
Noone faced the same problem before me?
I have tested the cache with the fetching joins: i changed my query (id<10) in order to simulate the case with limit 10 to test the speed.. As the link said the cache working fine.. the query speed decreased after the first loading.
So there are two bugs(?) which prevent me to use EJB3 efficently!!
Any slutions? Workarounds?
regards, Lazlo -
5. Re: JbossQL and m.createQuery(.. OFFSET 5 LIMIT 23)
laszlo.fogas Sep 14, 2006 10:03 AM (in response to cjc)nobody any idea? i think it's not a special need.. all i want to do is a select with joins and limit... If i'm asking stupid thing please let me know!
regards