RE: extending the life of a LRS query
Hi Patrick, Thanks for your detailed reply. Patrick Linskey wrote: If what you're looking for is to hold the cursor to the DB open across multiple requests, you'll need to create an extended-persistence-context EM (either via a stateful session bean or via using a non-JTA entity manager, obtained through an EMF), and then do the query in that EM and bind the query into session state somewhere. Of course, all the normal cautions about keeping database connections open across multiple requests apply. I guess that was the obvious solution. I subscribed to the one-pm-per-request pattern so long ago that I've even forgotten why. This works very well for me. I can bind my list to jsf dataTable and scroll through the results as they're loaded from the database. I think you just improved my quality of life :) If you're just looking to page through results for a search-result-style listing, you might be interested in the JPA Query.setFirstResult() and Query.setMaxResults() calls -- these allow for efficient (but non-isolated) paging through queries. Don't forget to add an ordering to your query when you do this, of course. Oh, and OpenJPA is smart about using range data in the query cache. I tried this out also and it seems to be quite efficient (not all orm products are as good). Not as convenient as the LRS, but good to know that OpenJPA is smart about ranges. Cheers, Roger -Original Message- From: Dain Sundstrom [mailto:[EMAIL PROTECTED] Sent: Saturday, December 09, 2006 9:24 AM To: open-jpa-dev@incubator.apache.org Subject: Re: extending the life of a LRS query I am by no means an OpenJPA expert, but I did see something related to this when reading the docs yesterday. OpenJPA has a QueryResultsCache where it keeps the results of a query so if you run the query again you get the same results (I'm not sure if that is what you want). Here is the code from the docs: OpenJPAEntityManagerFactory oemf = OpenJPAPersistence.cast(emf); QueryResultCache qcache = oemf.getQueryResultCache(); EntityManager em = emf.createEntityManager(); Query pinQuery = em.createQuery(...). setParameter(0, paramVal0). setParameter(1, paramVal1); qcache.pin(pinQuery); Query unpinQuery = em.createQuery(...). setParameter(0, paramVal0). setParameter(1, paramVal1); qcache.unpin(unpinQuery); Hope that helped. -dain On Dec 8, 2006, at 8:04 PM, roger.keays wrote: Hi, I'm trying to use OpenJPA's fetch plan extensions to have a query return a large result set. It seems to work okay, except that the LRS gets closed / detached with the EntityManager, which only makes it useful for one request. Is it possible to have the LRS stay open after the em has been disposed? Thanks, Roger -- View this message in context: http://www.nabble.com/extending-the- life-of-a-LRS-query-tf2784490.html#a7769269 Sent from the open-jpa-dev mailing list archive at Nabble.com. -- View this message in context: http://www.nabble.com/extending-the-life-of-a-LRS-query-tf2784490.html#a7825755 Sent from the open-jpa-dev mailing list archive at Nabble.com.
Re: extending the life of a LRS query
Hi, Note that not all databases support efficient FirstResult and MaxResults implementations. But even those that don't have built-in support for skipping n rows, the usual use case is to skip where n is small. The most frequent case is FirstResult == 0 followed in rapidly descending order n, 2*n, 3*n, and vanishingly small. And most databases will be reasonably efficient for these common cases. Craig On Dec 11, 2006, at 5:35 PM, roger.keays wrote: Hi Patrick, Thanks for your detailed reply. Patrick Linskey wrote: If what you're looking for is to hold the cursor to the DB open across multiple requests, you'll need to create an extended-persistence- context EM (either via a stateful session bean or via using a non-JTA entity manager, obtained through an EMF), and then do the query in that EM and bind the query into session state somewhere. Of course, all the normal cautions about keeping database connections open across multiple requests apply. I guess that was the obvious solution. I subscribed to the one-pm-per-request pattern so long ago that I've even forgotten why. This works very well for me. I can bind my list to jsf dataTable and scroll through the results as they're loaded from the database. I think you just improved my quality of life :) If you're just looking to page through results for a search-result- style listing, you might be interested in the JPA Query.setFirstResult() and Query.setMaxResults() calls -- these allow for efficient (but non-isolated) paging through queries. Don't forget to add an ordering to your query when you do this, of course. Oh, and OpenJPA is smart about using range data in the query cache. I tried this out also and it seems to be quite efficient (not all orm products are as good). Not as convenient as the LRS, but good to know that OpenJPA is smart about ranges. Cheers, Roger -Original Message- From: Dain Sundstrom [mailto:[EMAIL PROTECTED] Sent: Saturday, December 09, 2006 9:24 AM To: open-jpa-dev@incubator.apache.org Subject: Re: extending the life of a LRS query I am by no means an OpenJPA expert, but I did see something related to this when reading the docs yesterday. OpenJPA has a QueryResultsCache where it keeps the results of a query so if you run the query again you get the same results (I'm not sure if that is what you want). Here is the code from the docs: OpenJPAEntityManagerFactory oemf = OpenJPAPersistence.cast(emf); QueryResultCache qcache = oemf.getQueryResultCache(); EntityManager em = emf.createEntityManager(); Query pinQuery = em.createQuery(...). setParameter(0, paramVal0). setParameter(1, paramVal1); qcache.pin(pinQuery); Query unpinQuery = em.createQuery(...). setParameter(0, paramVal0). setParameter(1, paramVal1); qcache.unpin(unpinQuery); Hope that helped. -dain On Dec 8, 2006, at 8:04 PM, roger.keays wrote: Hi, I'm trying to use OpenJPA's fetch plan extensions to have a query return a large result set. It seems to work okay, except that the LRS gets closed / detached with the EntityManager, which only makes it useful for one request. Is it possible to have the LRS stay open after the em has been disposed? Thanks, Roger -- View this message in context: http://www.nabble.com/extending-the- life-of-a-LRS-query-tf2784490.html#a7769269 Sent from the open-jpa-dev mailing list archive at Nabble.com. -- View this message in context: http://www.nabble.com/extending-the- life-of-a-LRS-query-tf2784490.html#a7825755 Sent from the open-jpa-dev mailing list archive at Nabble.com. Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp! smime.p7s Description: S/MIME cryptographic signature
RE: extending the life of a LRS query
FWIW, OpenJPA uses SQL syntax (rather than JDBC) for ranges for all databases but JDataStore, to my knowledge. -Patrick -- Patrick Linskey BEA Systems, Inc. ___ Notice: This email message, together with any attachments, may contain information of BEA Systems, Inc., its subsidiaries and affiliated entities, that may be confidential, proprietary, copyrighted and/or legally privileged, and is intended solely for the use of the individual or entity named in this message. If you are not the intended recipient, and have received this message in error, please immediately return this by email and then delete it. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, December 11, 2006 6:11 PM To: open-jpa-dev@incubator.apache.org Subject: Re: extending the life of a LRS query Hi, Note that not all databases support efficient FirstResult and MaxResults implementations. But even those that don't have built-in support for skipping n rows, the usual use case is to skip where n is small. The most frequent case is FirstResult == 0 followed in rapidly descending order n, 2*n, 3*n, and vanishingly small. And most databases will be reasonably efficient for these common cases. Craig On Dec 11, 2006, at 5:35 PM, roger.keays wrote: Hi Patrick, Thanks for your detailed reply. Patrick Linskey wrote: If what you're looking for is to hold the cursor to the DB open across multiple requests, you'll need to create an extended-persistence- context EM (either via a stateful session bean or via using a non-JTA entity manager, obtained through an EMF), and then do the query in that EM and bind the query into session state somewhere. Of course, all the normal cautions about keeping database connections open across multiple requests apply. I guess that was the obvious solution. I subscribed to the one-pm-per-request pattern so long ago that I've even forgotten why. This works very well for me. I can bind my list to jsf dataTable and scroll through the results as they're loaded from the database. I think you just improved my quality of life :) If you're just looking to page through results for a search-result- style listing, you might be interested in the JPA Query.setFirstResult() and Query.setMaxResults() calls -- these allow for efficient (but non-isolated) paging through queries. Don't forget to add an ordering to your query when you do this, of course. Oh, and OpenJPA is smart about using range data in the query cache. I tried this out also and it seems to be quite efficient (not all orm products are as good). Not as convenient as the LRS, but good to know that OpenJPA is smart about ranges. Cheers, Roger -Original Message- From: Dain Sundstrom [mailto:[EMAIL PROTECTED] Sent: Saturday, December 09, 2006 9:24 AM To: open-jpa-dev@incubator.apache.org Subject: Re: extending the life of a LRS query I am by no means an OpenJPA expert, but I did see something related to this when reading the docs yesterday. OpenJPA has a QueryResultsCache where it keeps the results of a query so if you run the query again you get the same results (I'm not sure if that is what you want). Here is the code from the docs: OpenJPAEntityManagerFactory oemf = OpenJPAPersistence.cast(emf); QueryResultCache qcache = oemf.getQueryResultCache(); EntityManager em = emf.createEntityManager(); Query pinQuery = em.createQuery(...). setParameter(0, paramVal0). setParameter(1, paramVal1); qcache.pin(pinQuery); Query unpinQuery = em.createQuery(...). setParameter(0, paramVal0). setParameter(1, paramVal1); qcache.unpin(unpinQuery); Hope that helped. -dain On Dec 8, 2006, at 8:04 PM, roger.keays wrote: Hi, I'm trying to use OpenJPA's fetch plan extensions to have a query return a large result set. It seems to work okay, except that the LRS gets closed / detached with the EntityManager, which only makes it useful for one request. Is it possible to have the LRS stay open after the em has been disposed? Thanks, Roger -- View this message in context: http://www.nabble.com/extending-the- life-of-a-LRS-query-tf2784490.html#a7769269 Sent from the open-jpa-dev mailing list archive at Nabble.com. -- View this message in context: http://www.nabble.com/extending-the- life-of-a-LRS-query-tf2784490.html#a7825755 Sent from the open-jpa-dev mailing list archive at Nabble.com. Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp! smime.p7s Description: S/MIME cryptographic signature
Re: extending the life of a LRS query
I am by no means an OpenJPA expert, but I did see something related to this when reading the docs yesterday. OpenJPA has a QueryResultsCache where it keeps the results of a query so if you run the query again you get the same results (I'm not sure if that is what you want). Here is the code from the docs: OpenJPAEntityManagerFactory oemf = OpenJPAPersistence.cast(emf); QueryResultCache qcache = oemf.getQueryResultCache(); EntityManager em = emf.createEntityManager(); Query pinQuery = em.createQuery(...). setParameter(0, paramVal0). setParameter(1, paramVal1); qcache.pin(pinQuery); Query unpinQuery = em.createQuery(...). setParameter(0, paramVal0). setParameter(1, paramVal1); qcache.unpin(unpinQuery); Hope that helped. -dain On Dec 8, 2006, at 8:04 PM, roger.keays wrote: Hi, I'm trying to use OpenJPA's fetch plan extensions to have a query return a large result set. It seems to work okay, except that the LRS gets closed / detached with the EntityManager, which only makes it useful for one request. Is it possible to have the LRS stay open after the em has been disposed? Thanks, Roger -- View this message in context: http://www.nabble.com/extending-the- life-of-a-LRS-query-tf2784490.html#a7769269 Sent from the open-jpa-dev mailing list archive at Nabble.com.
