Thanks, David.
Your solution gives me a headache, although in terms of the original question it is
closest to
giving the database what I had in mind. I made the decision this morning to adhere to
the "80-20"
nature of this technology, so what I did instead of making a multi-call query to the
same table I
built the person table value objects sequentially using the various IDs stored in the
loan table.
The overhead on this kind of thing is absurd relative to a single query (mostly
because of the
setup costs of twelve separate Jdbc calls as opposed to one), but my time--and the
expected
maintenance cost--is worth far more than the cost of upgrading the box. In this
particular
application performance is not critical so it's not a show-stopper.
It occurred to me that this would be an issue in larger applications or if this was to
scale to a
user list of thousands. The simplest way to keep the "80-20" philosophy in this
situation and be
true to the object model here would be to
a) create a view in the database and custom-build the object model classes to access
it. This is
a good solution in a databsae like Oracle.
b) Call a stored procedure to do the work for you. I like this one a lot, too, but
I'm not sure
how to do it using torque.
In any case, I think that the key issue here is that the criteria are designed for
fairly rapid
code development that cover most situations. Clearly a compromise between normalized
database
models and the more abstracted object models in most business is necessary. One thing
that I
don't want to do is design crappy databases in order to accomodate my object model,
and I'm
thinking, with a little thought and planning, that I can have my normalization and
Criteria too ;)
Cheers,
Daniel
--- David Ramsey <[EMAIL PROTECTED]> wrote:
> Ah, you've touched on one of my hot buttons.
>
> Criteria are good for trivial queries but become ridiculously elaborate
> on anything too complex, and in many cases (as far as I can see) can't
> be used at all.
>
> And since the internals of criteria are hash tables, there's no
> guarantee that the final query that's actually sent to the database
> will have its terms constructed in the order you wish. This can have
> repercussions: for example, Oracle performance nose-dives in many cases
> if joins aren't performed in a particular order-- we're talking about
> the difference between a query returning in 2-3 seconds and a query
> returning in 4 minutes.
>
> Ahem. Anyway. Yes, you can bypass queries entirely and simply write raw
> JDBC code. It's easy and fun! Here's a code example:
>
> // Count all members with field data < jumpCharacter
> //
> // SELECT Count(*) FROM EpgClient where UPPER(<field>) <
> jumpCharacter
> //
> StringBuffer buf = new StringBuffer(100);
> buf.append( "SELECT COUNT(*) FROM " )
> .append( TABLE_NAME )
> .append( " WHERE UPPER(" )
> .append( field )
> .append( ") < '" )
> .append( jumpCharacter )
> .append( "'" );
> String query = buf.toString();
> Vector results = executeQuery( query );
> Record rec = (Record)results.get(0);
> return rec.getValue(1).asInt();
>
> As you can see, you get a vector of results from the executeQuery
> statement. I haven't moved to Torque yet but I think it works in a
> similar fashion-- you have objects and peers and such, right? This
> example just returns a "count(*)" number; for more complex queries you
> need to parse the data out of the results vector yourself. Here's an
> example:
>
> //
> // We've built the query string, now execute it
> //
> Vector qResults = BasePeer.executeQuery( query );
> int numResults = qResults.size();
> ArrayList results = new ArrayList( numResults );
>
> if ( numResults > 0 )
> {
> EpgBaseObject obj;
> Iterator iter = qResults.iterator();
> while( iter.hasNext() )
> {
> Record row = (Record)iter.next();
> obj = populateObject( row );
> results.add( obj );
> }
> }
> return results;
>
> The key here is the "populateObject" call. In the pre-Torque world,
> your peers normally have a "populateObject" method that's called
> automatically as part of the "making database calls with criteria"
> process. But since we're making the "executeQuery" call ourselves, we
> have to call it to get what we want: a list of populated objects.
>
> -- David Ramsey
>
> On Nov 20, 2003, at 6:08 AM, Daniel O'Neil wrote:
>
> > Greetings,
> >
> > I have a table in my database that has multiple references to a table
> > containing person data. The
> > references are foreign keys to the person table:
> > loan.loan_borrower_id
> > loan.loan_mortgagor_id
> > Both reference person.person_id.
> >
> > In SQL if I wanted to return a single row that contained information
> > about both the borrower and
> > the mortgagor, I would use the following sql statement:
> >
> > <1> select p1.pers_id,
> > <2> p2.pers_id,
> > <3> loan_id,
> > <4> p1.pers_first_name as borrower_name,
> > <5> p2.pers_first_name as mortgagor_name
> > <6> from loan, person p1, person p2
> > <7> where loan_borrower_id = 1 and loan_mortgagor_id = 2
> > <8> and p1.pers_id = loan_borrower_id
> > <9> and p2.pers_id = loan_mortgagor_id;
> >
> > I don't see an easy way to do this using criteria. The Criterion
> > inner class takes a stab at it,
> > but it doesn't seem to be able to do joins across different tables, so
> > it would be inadequate to
> > address the restriction clauses of lines <8> and <9> above.
> >
> > Is there a way to easily do these kinds of joins, and, if not, is
> > there a way to send a sql
> > statement directly to the data layer in the torque framework?
> >
> > Thanks for your help,
> > Daniel
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
