Here's my reason for not liking Criteria (and where CrossDB falls down
as well): It doesn't handle joins.
Given tables/objects like this:
department (id, name, location)
employee (id, name, depId)
I want to be able to select all employees who are in San Francisco like
this:
SELECT e.*
FROM employee e, department d
WHERE e.depId = d.id AND d.location = 'San Francisco';
Criteria doesn't try, and CrossDB requires the FROM clause to be
explicitly defined - not good if Torque's doSelect should only take the
equivalent of a WhereClause.
I like the way RogueWave DBTools.h++ handles this. It also provides a
very elegant syntax for constructing the expressions; no annoying
constants like LESS_THAN_OR_EQUAL to work with.
The important characteristic of DBTools is that they have a Column
object which encapsulates the table it comes from and the column
identifier. This Column object is an Expr:
class Column extends Expr {...}
class Expr
{
public BooleanExpr greaterThan(Expr other)
public BooleanExpr equals(Expr other)
public BooleanExpr equals(String constant)
public BooleanExpr and(BooleanExpr other)
public Expr cast(int sqlType)
// etc...
}
Using this system the join query I mentioned above would look something
like this:
Expr emp_depId = EmployeePeer.getColumn("depId");
Expr dep_id = DepartmentPeer.getColumn("id");
Expr dep_location = DepartmentPeer.COL_LOCATION; // generated constant
List results = EmployeePeer.doSelect(
(emp_depId.equals(dep_id)).and(dep_location.equals("San
Francisco"))
);
I'll have to admit that this may be the first time I have actually
wished Java supported operator overloading :-)
To crank up the difficulty a bit more, I would also like to see a
mechanism that supports self-joins. For example, given the basic tree
structure in the table:
node (id, parentId, name)
I would like to be able to get all the grandchildren of a node:
SELECT n2.*
FROM node n1, node n2
WHERE n1.parentId = 5 AND n1.id = n2.parentId
This becomes more complicated. It could be done like this:
Table n2 = NodePeer.getJoinInstance();
// The normal getColumn() on NodePeer always refers to an
// implicit first join instance.
List results = NodePeer.doSelect(
NodePeer.getColumn("parentId").equals(5)
.and(NodePeer.getColumn("id").equals(n2.getColumn("parentId")))
);
One nice thing about this approach is that it's very easy to perform
joins with tables which are not maintained by Torque... a Table can be
created from any named table.
Does this make sense? Am I alone in thinking that joins are a
critically important part of any query system? Or did I just spend too
many years writing ROLAP code? (Actually, I already know the answer to
that last one :-)
If this is at all intriguing, the "cool chapter" from the RogueWave
documentation is this one:
http://www.roguewave.fr/support/docs/dbtug/5-2.cfm
Jeff Schnitzer
[EMAIL PROTECTED]
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
