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]
