You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Christian Defoy <ch...@gmail.com> on 2007/07/18 15:39:17 UTC
Getting a group of entities by ID
Hello,
I can't find an easy way of retrieving a group of entities by
specifying only their IDs. For example, if I want to get shapes with
IDs 1, 2, 4 and 6, do I have to do the following query or is there a
better way?
SELECT x FROM shape x WHERE x.id = 1 OR x.id = 2 OR x.id = 4 OR x.id = 6
I was thinking of something more along the lines of the IN SQL
statement ("WHERE x.id IN (1,2,4,6)") but I haven't found anything to
do this. Using SQL queries, I was able to retrieve my shapes but
OpenJPA does one select to retrieve the IDs (my SQL query with the IN
clause) and then one select per shape it retrieves. That is no
different than me doing a find for every shape myself...
Thanks in advance!
Christian
Re: Getting a group of entities by ID
Posted by Christian Defoy <ch...@gmail.com>.
Hi David,
Thanks for the response! I tried the findAll() method. It is
definitely simpler than creating a SQL query with an IN clause. When
an entity is not in the datacache, one SQL statement is generated per
object. But at least, the code is simpler...
Could there be an existing option to adjust this behavior? Something
that would allow me to say fetch X rows instead of just one...
Christian
On 7/18/07, David Ezzio <de...@bea.com> wrote:
> Hi Christian,
>
> You might consider using the OpenJPAEntityManager.findAll method. The
> expected advantage is that any objects in the datacache won't cause a
> hit to the database. If there is a high likelihood that all objects are
> in the datacache, then this is definitely the way to go. If some
> objects will very likely not be in the cache, then you might want to
> investigate. I'm not sure whether it will generate one SQL statement
> for all missing objects or one for each. You might want to turn on SQL
> logging to check,
>
> ((OpenJPAEntityManager) em).findAll(...)
>
> Hope this helps,
>
> David
>
> Christian Defoy wrote:
> > Hello,
> >
> > I can't find an easy way of retrieving a group of entities by
> > specifying only their IDs. For example, if I want to get shapes with
> > IDs 1, 2, 4 and 6, do I have to do the following query or is there a
> > better way?
> >
> > SELECT x FROM shape x WHERE x.id = 1 OR x.id = 2 OR x.id = 4 OR x.id = 6
> >
> > I was thinking of something more along the lines of the IN SQL
> > statement ("WHERE x.id IN (1,2,4,6)") but I haven't found anything to
> > do this. Using SQL queries, I was able to retrieve my shapes but
> > OpenJPA does one select to retrieve the IDs (my SQL query with the IN
> > clause) and then one select per shape it retrieves. That is no
> > different than me doing a find for every shape myself...
> >
> > Thanks in advance!
> >
> > Christian
> >
>
>
> 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.
>
Re: Getting a group of entities by ID
Posted by Marc Prud'hommeaux <mp...@apache.org>.
Christian-
Glad it works. Note that it is debatable that the spec actually
allows this. Technically, an IN clause is supposed to be of the form
(ob1, ob2, ob3), and the spec doesn't say anything about allowing
Collections to be used as parameters to an IN clause. However, in my
experience, all of the JPA implementations support passing in
Collection parameters to IN clauses.
On Jul 18, 2007, at 11:43 AM, Christian Defoy wrote:
> Hi Marc,
>
> Thanks for the tip. It works fine! That's the functionality I was
> looking for!
>
> I changed the query a bit because it looks like we have to enclose
> that specific parameter in ().
> em.createQuery("SELECT x FROM shape x WHERE x.id in (:ids)");
>
> Thank you both for your time!
>
> Christian
>
> On 7/18/07, Marc Prud'hommeaux <mp...@apache.org> wrote:
>> Christian-
>>
>> In additional to what David said, I think should be able to do:
>>
>> em.createQuery("SELECT x FROM shape x WHERE x.id in :ids").
>> setParameter("ids", Arrays.asList(new Integer[] { 1, 2, 3 })).
>> getResultlist();
>>
>>
>>
>>
>>
>>
>> On Jul 18, 2007, at 7:22 AM, David Ezzio wrote:
>>
>> > Hi Christian,
>> >
>> > You might consider using the OpenJPAEntityManager.findAll
>> method. The
>> > expected advantage is that any objects in the datacache won't
>> cause a
>> > hit to the database. If there is a high likelihood that all
>> > objects are
>> > in the datacache, then this is definitely the way to go. If some
>> > objects will very likely not be in the cache, then you might
>> want to
>> > investigate. I'm not sure whether it will generate one SQL
>> statement
>> > for all missing objects or one for each. You might want to turn on
>> > SQL
>> > logging to check,
>> >
>> > ((OpenJPAEntityManager) em).findAll(...)
>> >
>> > Hope this helps,
>> >
>> > David
>> >
>> > Christian Defoy wrote:
>> >> Hello,
>> >>
>> >> I can't find an easy way of retrieving a group of entities by
>> >> specifying only their IDs. For example, if I want to get shapes
>> with
>> >> IDs 1, 2, 4 and 6, do I have to do the following query or is
>> there a
>> >> better way?
>> >>
>> >> SELECT x FROM shape x WHERE x.id = 1 OR x.id = 2 OR x.id = 4 OR
>> >> x.id = 6
>> >>
>> >> I was thinking of something more along the lines of the IN SQL
>> >> statement ("WHERE x.id IN (1,2,4,6)") but I haven't found
>> anything to
>> >> do this. Using SQL queries, I was able to retrieve my shapes but
>> >> OpenJPA does one select to retrieve the IDs (my SQL query with
>> the IN
>> >> clause) and then one select per shape it retrieves. That is no
>> >> different than me doing a find for every shape myself...
>> >>
>> >> Thanks in advance!
>> >>
>> >> Christian
>> >>
>> >
>> >
>> > 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.
>>
>>
Re: Getting a group of entities by ID
Posted by Christian Defoy <ch...@gmail.com>.
Hi Marc,
Thanks for the tip. It works fine! That's the functionality I was looking for!
I changed the query a bit because it looks like we have to enclose
that specific parameter in ().
em.createQuery("SELECT x FROM shape x WHERE x.id in (:ids)");
Thank you both for your time!
Christian
On 7/18/07, Marc Prud'hommeaux <mp...@apache.org> wrote:
> Christian-
>
> In additional to what David said, I think should be able to do:
>
> em.createQuery("SELECT x FROM shape x WHERE x.id in :ids").
> setParameter("ids", Arrays.asList(new Integer[] { 1, 2, 3 })).
> getResultlist();
>
>
>
>
>
>
> On Jul 18, 2007, at 7:22 AM, David Ezzio wrote:
>
> > Hi Christian,
> >
> > You might consider using the OpenJPAEntityManager.findAll method. The
> > expected advantage is that any objects in the datacache won't cause a
> > hit to the database. If there is a high likelihood that all
> > objects are
> > in the datacache, then this is definitely the way to go. If some
> > objects will very likely not be in the cache, then you might want to
> > investigate. I'm not sure whether it will generate one SQL statement
> > for all missing objects or one for each. You might want to turn on
> > SQL
> > logging to check,
> >
> > ((OpenJPAEntityManager) em).findAll(...)
> >
> > Hope this helps,
> >
> > David
> >
> > Christian Defoy wrote:
> >> Hello,
> >>
> >> I can't find an easy way of retrieving a group of entities by
> >> specifying only their IDs. For example, if I want to get shapes with
> >> IDs 1, 2, 4 and 6, do I have to do the following query or is there a
> >> better way?
> >>
> >> SELECT x FROM shape x WHERE x.id = 1 OR x.id = 2 OR x.id = 4 OR
> >> x.id = 6
> >>
> >> I was thinking of something more along the lines of the IN SQL
> >> statement ("WHERE x.id IN (1,2,4,6)") but I haven't found anything to
> >> do this. Using SQL queries, I was able to retrieve my shapes but
> >> OpenJPA does one select to retrieve the IDs (my SQL query with the IN
> >> clause) and then one select per shape it retrieves. That is no
> >> different than me doing a find for every shape myself...
> >>
> >> Thanks in advance!
> >>
> >> Christian
> >>
> >
> >
> > 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.
>
>
Re: Getting a group of entities by ID
Posted by Marc Prud'hommeaux <mp...@apache.org>.
Christian-
In additional to what David said, I think should be able to do:
em.createQuery("SELECT x FROM shape x WHERE x.id in :ids").
setParameter("ids", Arrays.asList(new Integer[] { 1, 2, 3 })).
getResultlist();
On Jul 18, 2007, at 7:22 AM, David Ezzio wrote:
> Hi Christian,
>
> You might consider using the OpenJPAEntityManager.findAll method. The
> expected advantage is that any objects in the datacache won't cause a
> hit to the database. If there is a high likelihood that all
> objects are
> in the datacache, then this is definitely the way to go. If some
> objects will very likely not be in the cache, then you might want to
> investigate. I'm not sure whether it will generate one SQL statement
> for all missing objects or one for each. You might want to turn on
> SQL
> logging to check,
>
> ((OpenJPAEntityManager) em).findAll(...)
>
> Hope this helps,
>
> David
>
> Christian Defoy wrote:
>> Hello,
>>
>> I can't find an easy way of retrieving a group of entities by
>> specifying only their IDs. For example, if I want to get shapes with
>> IDs 1, 2, 4 and 6, do I have to do the following query or is there a
>> better way?
>>
>> SELECT x FROM shape x WHERE x.id = 1 OR x.id = 2 OR x.id = 4 OR
>> x.id = 6
>>
>> I was thinking of something more along the lines of the IN SQL
>> statement ("WHERE x.id IN (1,2,4,6)") but I haven't found anything to
>> do this. Using SQL queries, I was able to retrieve my shapes but
>> OpenJPA does one select to retrieve the IDs (my SQL query with the IN
>> clause) and then one select per shape it retrieves. That is no
>> different than me doing a find for every shape myself...
>>
>> Thanks in advance!
>>
>> Christian
>>
>
>
> 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.
Re: Getting a group of entities by ID
Posted by David Ezzio <de...@bea.com>.
Hi Christian,
You might consider using the OpenJPAEntityManager.findAll method. The
expected advantage is that any objects in the datacache won't cause a
hit to the database. If there is a high likelihood that all objects are
in the datacache, then this is definitely the way to go. If some
objects will very likely not be in the cache, then you might want to
investigate. I'm not sure whether it will generate one SQL statement
for all missing objects or one for each. You might want to turn on SQL
logging to check,
((OpenJPAEntityManager) em).findAll(...)
Hope this helps,
David
Christian Defoy wrote:
> Hello,
>
> I can't find an easy way of retrieving a group of entities by
> specifying only their IDs. For example, if I want to get shapes with
> IDs 1, 2, 4 and 6, do I have to do the following query or is there a
> better way?
>
> SELECT x FROM shape x WHERE x.id = 1 OR x.id = 2 OR x.id = 4 OR x.id = 6
>
> I was thinking of something more along the lines of the IN SQL
> statement ("WHERE x.id IN (1,2,4,6)") but I haven't found anything to
> do this. Using SQL queries, I was able to retrieve my shapes but
> OpenJPA does one select to retrieve the IDs (my SQL query with the IN
> clause) and then one select per shape it retrieves. That is no
> different than me doing a find for every shape myself...
>
> Thanks in advance!
>
> Christian
>
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.