You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Artur Z <ar...@o2.pl> on 2004/09/02 09:53:41 UTC
doSelect with collections filled
hello
please help - I have a performance problem
there are two tables: Book and Author
I call AuthorPeer.doSelect(new Criteria()); - it returns all authors
now i want to print all authors and their books
on each author I must call author.getBooks();
every time I call this method a database is queried for books for this
author
and when there are 2000 authors, there will be 2000 calls to database, which
of course takes much time
the question is:
how to query author table to get in return list of authors with already
initialized books collections
thanks a lot
Artur
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: doSelect with collections filled
Posted by Artur Z <ar...@o2.pl>.
> If you take a close look at BaseBookPeer.doSelectJoinAuthor() you will
> find that this method not only sets the Author for each book but it also
> builds up the Authors' Book collections. In BaseAuthor.java there is a
> method called something like addBook(), which is called in the
> doSelectJoinXXX loop.
thank, this will help me, but not 100%
going further I'd like to make such call, which will select all authors AND
in the same db call retrieve books and authors addresses (and of course fill
collections in objects) :)
it looks like I'll have to write custom code
thanks for help
Artur
----- Original Message -----
From: "T E Schmitz" <ma...@numerixtechnology.de>
To: "Apache Torque Users List" <to...@db.apache.org>
Sent: Thursday, September 02, 2004 12:55 PM
Subject: Re: doSelect with collections filled
> Hello Artur,
>
> Artur Z wrote:
>
> >> Did you defined the foreign key relationship beetwen Author and
> >>Book in your schema file?
> >>If yes, there should be protected method in BaseAuthorPeer
> >>doSelectJoinBooks() (or something like that). Write this method as
> >>public in AuthorPeer
> >>and than you can use it.
>
> > yes, I defined, but there is no such method
> > torque generated only method BaseBookPeer.doSelectJoinAuthor(...)
> > as far as I know torque generates only methods for accessing
associations
> > (one), but not collections
>
> There is no one SQL statement that would return you an author tuple and
> n tuples for his related books.
>
> > if you want to access a collection you call author.getBooks(), and it
reads
> > books from db, caches it and returns - it couses perfirmance issues (one
db
> > call for each getXXXs() method)
> >
> > how to solve it ?
> > how to query author table to get in return list of authors with ALREADY
> > INITIALIZED books collections ?
>
> If you take a close look at BaseBookPeer.doSelectJoinAuthor() you will
> find that this method not only sets the Author for each book but it also
> builds up the Authors' Book collections. In BaseAuthor.java there is a
> method called something like addBook(), which is called in the
> doSelectJoinXXX loop.
>
> The only way you can select all authors and their books in one SQL
> statement is by selecting all books joined with author (possibly ordered
> by author). If using Torque that means
BaseBookPeer.doSelectJoinAuthor(...).
> If you don't want to select all books, just certain authors, you have to
> pre-set the criteria argument for doSelectJoinXXXX. If you add an
> OrderBy Author to Criteria, you will get a list of books sorted by
authors.
>
> I presume each book has an author, in which case the generated inner
> join will do you.
>
>
>
> > I found one solution in
> > http://www.mail-archive.com/torque-user@db.apache.org/msg01712.html
> > but is not handy and you operate on array of objects, not getXXXs()
methods
> > and collections
>
> This example is pretty much a copy of doSelectJoinXXXX. To translate
> this to your example, this would produce something like
> BaseBookPeer.doSelectJoinAuthor(...): read all books pre-fileld with
> their Authors.
>
>
> --
>
>
> Regards/Gruß,
>
> Tarlika Elisabeth Schmitz
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: doSelect with collections filled
Posted by T E Schmitz <ma...@numerixtechnology.de>.
Hello Artur,
Artur Z wrote:
>> Did you defined the foreign key relationship beetwen Author and
>>Book in your schema file?
>>If yes, there should be protected method in BaseAuthorPeer
>>doSelectJoinBooks() (or something like that). Write this method as
>>public in AuthorPeer
>>and than you can use it.
> yes, I defined, but there is no such method
> torque generated only method BaseBookPeer.doSelectJoinAuthor(...)
> as far as I know torque generates only methods for accessing associations
> (one), but not collections
There is no one SQL statement that would return you an author tuple and
n tuples for his related books.
> if you want to access a collection you call author.getBooks(), and it reads
> books from db, caches it and returns - it couses perfirmance issues (one db
> call for each getXXXs() method)
>
> how to solve it ?
> how to query author table to get in return list of authors with ALREADY
> INITIALIZED books collections ?
If you take a close look at BaseBookPeer.doSelectJoinAuthor() you will
find that this method not only sets the Author for each book but it also
builds up the Authors' Book collections. In BaseAuthor.java there is a
method called something like addBook(), which is called in the
doSelectJoinXXX loop.
The only way you can select all authors and their books in one SQL
statement is by selecting all books joined with author (possibly ordered
by author). If using Torque that means BaseBookPeer.doSelectJoinAuthor(...).
If you don't want to select all books, just certain authors, you have to
pre-set the criteria argument for doSelectJoinXXXX. If you add an
OrderBy Author to Criteria, you will get a list of books sorted by authors.
I presume each book has an author, in which case the generated inner
join will do you.
> I found one solution in
> http://www.mail-archive.com/torque-user@db.apache.org/msg01712.html
> but is not handy and you operate on array of objects, not getXXXs() methods
> and collections
This example is pretty much a copy of doSelectJoinXXXX. To translate
this to your example, this would produce something like
BaseBookPeer.doSelectJoinAuthor(...): read all books pre-fileld with
their Authors.
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: doSelect with collections filled
Posted by Artur Z <ar...@o2.pl>.
> Did you defined the foreign key relationship beetwen Author and
> Book in your schema file?
> If yes, there should be protected method in BaseAuthorPeer
> doSelectJoinBooks() (or something like that). Write this method as
> public in AuthorPeer
> and than you can use it.
yes, I defined, but there is no such method
torque generated only method BaseBookPeer.doSelectJoinAuthor(...)
as far as I know torque generates only methods for accessing associations
(one), but not collections
if you want to access a collection you call author.getBooks(), and it reads
books from db, caches it and returns - it couses perfirmance issues (one db
call for each getXXXs() method)
how to solve it ?
how to query author table to get in return list of authors with ALREADY
INITIALIZED books collections ?
I found one solution in
http://www.mail-archive.com/torque-user@db.apache.org/msg01712.html
but is not handy and you operate on array of objects, not getXXXs() methods
and collections
----- Original Message -----
From: "Andras Balogh" <an...@reea.net>
To: "Apache Torque Users List" <to...@db.apache.org>
Sent: Thursday, September 02, 2004 10:25 AM
Subject: Re: doSelect with collections filled
> Hello,
>
> Did you defined the foreign key relationship beetwen Author and
> Book in your schema file?
> If yes, there should be protected method in BaseAuthorPeer
> doSelectJoinBooks() (or something like that). Write this method as
> public in AuthorPeer
> and than you can use it.
> Notice that the join made by Torque when selecting authors and books
> is inner join
> so you won't have the authors who don't have at least a book.
> (can be changed i think to left outer join if you patch torque to
> support outer joins, see mail archive)
>
> Best regards,
> Andras.
>
> Artur Z wrote:
>
> >hello
> >
> >please help - I have a performance problem
> >there are two tables: Book and Author
> >I call AuthorPeer.doSelect(new Criteria()); - it returns all authors
> >now i want to print all authors and their books
> >on each author I must call author.getBooks();
> >every time I call this method a database is queried for books for this
> >author
> >and when there are 2000 authors, there will be 2000 calls to database,
which
> >of course takes much time
> >
> >the question is:
> >how to query author table to get in return list of authors with already
> >initialized books collections
> >
> >thanks a lot
> >
> >Artur
> >
> >
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> >For additional commands, e-mail: torque-user-help@db.apache.org
> >
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: doSelect with collections filled
Posted by Andras Balogh <an...@reea.net>.
Hello,
Did you defined the foreign key relationship beetwen Author and
Book in your schema file?
If yes, there should be protected method in BaseAuthorPeer
doSelectJoinBooks() (or something like that). Write this method as
public in AuthorPeer
and than you can use it.
Notice that the join made by Torque when selecting authors and books
is inner join
so you won't have the authors who don't have at least a book.
(can be changed i think to left outer join if you patch torque to
support outer joins, see mail archive)
Best regards,
Andras.
Artur Z wrote:
>hello
>
>please help - I have a performance problem
>there are two tables: Book and Author
>I call AuthorPeer.doSelect(new Criteria()); - it returns all authors
>now i want to print all authors and their books
>on each author I must call author.getBooks();
>every time I call this method a database is queried for books for this
>author
>and when there are 2000 authors, there will be 2000 calls to database, which
>of course takes much time
>
>the question is:
>how to query author table to get in return list of authors with already
>initialized books collections
>
>thanks a lot
>
>Artur
>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
>For additional commands, e-mail: torque-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
primary key of 3 columns
Posted by a....@free.fr.
Hi
I have a primary key of 3 columns, and one of these column is sequence
my problem is how to insert explicitly data in sequence without auto generation
it's no problem with insertion by auto generation
my schema :
<column javaName="col1" name="col1"
required="true" size="10" type="DECIMAL"/>
<column javaName="col2" name="col2" primaryKey="true"
required="true" size="10" type="DECIMAL"/>
<column javaName="col3" name="col3"
required="true" type="TIMESTAMP" />
<id-method-parameter name="SEQ" value="SEQ"/>
<unique name="PK">
<unique-column name="col1"/>
<unique-column name="col2"/>
<unique-column name="col3"/>
</unique>
thanks
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: doSelect with collections filled
Posted by T E Schmitz <ma...@numerixtechnology.de>.
Hello Artur,
Artur Z wrote:
> please help - I have a performance problem
> the question is:
> how to query author table to get in return list of authors with already
> initialized books collections
You will indeed run into severe performance problems if you don't use
the doSelectJoinXXX methods, which read the main object and [some of
its] FK related objects in a oner.
Beware though that Torque 3.1 generated templates will use an inner join
for the doSelectJoinXXXX methods.
I remember, at the end of July I gave you a long reply including some
code snippets for the Peer.vm template regarding the doSelectJoinXXXX
methods, self-references and left joins.
It would be nice [for the helpers and and those who later browse the
archive] if the original poster gave some feedback whether that sorted
the problem.
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org