Hi,
You should send questions like these to the mailing list. I'll always try to
answer you as good as I can, but in the mailing list the pool of knowledgeable
people is much larger and your chances of getting helped is better. Also the
mailing list serves as a place where people can learn. By sharing questions and
answers we all learn.
> What is the proper way to send a SQL query to the
> DB backend? Or is this not advised within the Peer
> environment? Say I have my typical DB with three
> tables:
>
> Book Author BookAuthor
> ==== ====== ==========
> id id book
> name name author
> seq
>
> so that BookAuthor relates Books and Authors (seq is
> an integer field to keep authors in the order they
> are listed for each book). Now, say I want to retrieve
> a full list of all books, each with its respective
> authors. In SQL, I would do something like:
>
> select B.name, BA.seq, A.name
> from Book B, Author A, BookAuthor BA
> where B.id = BA.book and A.id = BA.author
> order by B.name, BA.seq
>
> I can see how I could, using Peer, iterate over all books,
> and for each of them retrieve its author information,
> but this looks suspiciously like replicating the DB
> functionality (very efficient) in Java code (very inefficient).
> Can this be done with pure SQL? Should it be done?
> Any recommendations?
>
I Peer you are allowed direct access to the underlying database. This is very
useful especially when you need to do very complex queries or transaction
management. I'll come to that in a moment. You can do Joins and Order By's and
other (see the api documentation) like this:
Criteria crit = new Criteria()
crit.addJoin (BookPeer.ID, BookAuthorPeer.BOOK);
crit.addJoin (AuthorPeer.ID, BookAuthorPeer.AUTHOR);
crit.addOrderByColumn (BookPeer.NAME)
crit.addOrderByColumn (BookAuthorPeer.SEQ)
Vector v = BookPeer.doSelect (crit);
The problem would be that you now have a vector of Book. You can alternatively
have a vector of author but I suspect you want both. To solve this you could edit
your generated BookPeer class by hand and add something like doSelectWithAuthor()
that returns a vector with both Book and Author or something .....
If you really like to get your hands dirty with raw sql queries you can do
something like:
DBConnection dbCon= BasePeer.beginTransaction (DBBroker.DEFAULT);
Connection con = dbCon.getConnection();
// Do queries with con
BasePeer.commitTransaction (dbCon);
Please *remember* that this only works with databases that supports transactions.
If your database does not support transactions you should use:
DBConnection dbCon= DBBroker.getInstance().getConnection(DBBroker.DEFAULT);
Connection con = dbCon.getConnection();
// Do queries with con
DBBroker.getInstance().releaseConnection(dbCon);
I would suggest to use the peer classes above raw SQL whenever you can. Even if
it means you should edit the peer classes a bit by hand - this will save you a lot
of pain in the future. However, raw SQL is at your disposal to give you the
freedom you need, but use it wisely.
At some stage I'd like to create a type of an "advanced generator" that does
things like I've mentioned above. Unfortunately this is still pretty much in my
imagination and nothing concrete at the moment.
You can also take a look at Castor or OPL. I've only had a little experience with
Castor. It's Database to Object mapping is a lot more powerful than Peer, but you
are denied access to the underlying database. Performance is also a problem.
~ Leon
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
Problems?: [EMAIL PROTECTED]
