A little preview of the main feature of upcoming 1.1M6 (and likely the
last major feature before 1.1 goes Beta).
O/R mapping was supposed to free developers from writing SQL. At the
same time SQL is too powerful and too "different" from normal OO
thinking to be completely ignored. Something I had to do at one of my
recent projects looked like this:
SELECT * FROM
(SELECT NAME, CITY FROM TABLE1 WHERE ... ) AS T1,
(SELECT NAME, MAX(DOB) FROM TABLE2 WHERE ... GROUP BY NAME) AS T2,
TABLE3 AS T3
WHERE T1.NAME = T2.NAME AND T3.ID =* ...
UNION
SELECT
....
Try doing this in Java with ORM (or String concatenation). Well, it is
still possible, but takes *enormous* amount of code, in-memory sorting,
filtering, joining etc. Up till now Cayenne kind of solved it with
SqlModifyQuery and SqlSelectQuery. For anyone who haven't tried it, I
am telling you - those were a real pain to use.
So I was thinking how to finally make it usable. What I came up with is
a new query class - SQLTemplate. SQLTemplate combines both select or
modify query. Here is the main features:
1. Dynamic Template:
The core (and most exciting) feature of SQLTemplate is that its
internal SQL string is not static. It is a dynamic Velocity script that
is processed at runtime to generate PreparedStatement SQL. So it can be
parameterized and conditionally exclude/include pieces of SQL depending
on parameters. E.g. this includes a part of the WHERE clause only if
parameter "a" is not null:
WHERE C0 = 5 #if($a) AND C1 = $a #end
Raw power!
2. Describing Result:
Another piece that was extremely hard to configure was select result
format (data types, data row key names, etc). With scripting describing
a desired format is trivial (and actually optional):
SELECT #result('COLUMN_A', 'String'), #result('COLUMN_B', 'int',
'ALIAS') ...
3. True Cayenne Select
Unlike SqlSelectQuery, SQLTemplate is a full-featured select query - it
supports pagination, creating DataObjects (not just data rows), etc.,
etc.
4. Customizing Template:
And finally, template can be customized by adapter, so the same query
can be executed on Oracle and MySQL with a totally different SQL
syntax, all transparent to the user.
I don't think EOF or TopLink (?) come even close to that. SQLTemplate
is already implemented in CVS. I am working on the docs and Modeler
support. Will be released soon.
Andrus
This archive was generated by hypermail 2.0.0 : Wed Apr 21 2004 - 15:53:59 EDT