I think the main problem with trying to use parameters the way you are
with EJBQLQuery is the meaning of "parameters": they're supposed to be
bound to SQL types.
At the same time, the "column names" are really object property names,
which means they have to be translated at the time the query is
compiled, rather than at the time the query is executed. That is,
parameters ultimately wind up calling into PreparedStatement's
"setParameter" method (more or less). They are the object-based
equivalent of the #bind directive in SQLTemplate. But what /might/ be
kind of entertaining is to perform 2-pass processing of the query
string, where the first pass is the /scripting/ portion, and the
second pass is the actual compilation. Scriptable EJBQLQuery?
EJBQLTemplateQuery? ;) Something like:
EJBQLTemplateQuery q = new EJBQLTemplateQuery("SELECT us.$skdid FROM
$ussched us WHERE us.$uid = :uid");
Map<String,Object> vars = new HashMap<String,Object>();
vars.put("skdid",UserSchedule.SCHEDULE_ID_PROPERTY);
vars.put("ussched",UserSchedule.class.getSimpleName());
vars.put("uid",USER_ID_PROPERTY);
q.setTemplateVariables(vars);
q.setParameter("uid",1);
objectContext.performQuery(q);
Now that I'm thinking about this more... even though EJBQLQuery
doesn't support the above scheme, there's no reason you can't tap into
velocity yourself to accomplish what you want.
Still, I'm intrigued by the idea of being able to directly script
EJBQLQuery. Anybody else think that's interesting? If there's a
favorable consensus, I'll add an issue and assign it to myself.
Robert
String queryString = "SELECT us.:SKDID FROM :USCHED us WHERE
us.:UID=1";
EJBQLQuery query = new EJBQLQuery(queryString);
query.setParameter("SKDID", 1);
query.setParameter("USCHED",
UserSchedule.class.getSimpleName());
query.setParameter("UID",
UserSchedule.USER_ID_PROPERTY);
On Apr 9, 2009, at 4/93:26 AM , Francois Eyl wrote:
I already use a lot of SelectQuery and even some SQLTemplate.
My problem with SelectQuery is the fact that it fetches all columns
information for a table and so, can be expensive for specific needs
where I
need high performances (here only need ids, so only 1 column to return
rather than 25 -> more efficient on remote connection). SelectQuery
is very
easy and friendly but limited to existing relationships, good point
with
EJBQL is the fact that you can select which column(s) you want to
fetch and
even merge your query with Expressions, so its object oriented, easy
and
powerful. But can be a nightmare to maintain when column/table's name
change.
Well, it seems there is no magic solution to my issue even if your
sugestions are appreciated :) Will check the JPA 1.0 spec just in
case...
Thanks again for your thoughts,
Francois
2009/4/8 Robert Zeigler <[email protected]>
Two thoughts.
1) You're not doing anything that /requires/ EJBQLQuery. So, if
you're
/really/ interested in using the cayenne-provided constants, switch
to
SelectQuery.
SelectQuery q = new SelectQuery(UserSchedule.class);//<-- see? Type
safety... :)
Expression e =
ExpressionFactory
.matchExp(UserSchedule.USER_ID_PROPERTY,theUserIdValue);
q.setQualifier(e);
UserSchedule us = (UserSchedule)
DataObjectUtils.objectForQuery(objectContext,q);
us.getSku();
2) Externalize your query.
This doesn't save you from having to change column names, but at
least
your query is external to the code, so
a) You can reuse it other places (so fewer places to change)
b) All queries in one place, if you externalize them all, so
you don't
have to hunt through your code to find them.
c) You can fix your query without a recompile.
3) Use SQLTemplate. SQLTemplate let's you script anything,
including table
and column names. BUT, you'll be writing direct sql, so you'll
have to use
the database names rather than the object names.
Note that EJBQLQuery parsing follows (or is supposed to follow) the
JPA 1.0
spec. So if you can find reference in the spec that says that
parameters
can be used anywhere (including column names), then you should file a
feature request.
Robert
On Apr 8, 2009, at 4/89:08 AM , Francois Eyl wrote:
oh, sorry it's my bad. I did a typing mistake I wanted to type :
query.setParameter("SKDID", UserSchedule.SCHEDULE_ID_PROPERTY);
Robert, you right, I'm trying to use paramters to specify Columns
and so
use
available constants coming from Cayenne. This way, even if columns
name
change, nothing to change into the query. But, parameters sounds
work only
for values :(
No working solution please ?
Thanks,
Francois
2009/4/8 Robert Zeigler <[email protected]>
Hm. I've never thought about specifying the columns and table via
parameters.
Event supposing that this works (not convinced it does/will), do
you
actually have a property named 1 in the UserSchedule entity
(select us.:SKDID...setParameter("SKDID",1) => select us.1)?
Try specifying the entity and property names directly.
Parameters are
generally used for parameterized values, like:
EJQLQuery query = new EJBQLQuery("select us.sku from UserSchedule
us
where
us.userId=:id");
query.setParameter("id",1);
Robert
On Apr 8, 2009, at 4/84:48 AM , Francois Eyl wrote:
Howdy guys,
I'm trying to use EJBQL query and parameters, but I can't get it
to
work.
Is
there something special for ?
Bellow is a quick sample of what I'm doing :
String queryString = "SELECT us.:SKDID FROM :USCHED
us WHERE
us.:UID=1";
EJBQLQuery query = new EJBQLQuery(queryString);
query.setParameter("SKDID", 1);
query.setParameter("USCHED",
UserSchedule.class.getSimpleName());
query.setParameter("UID",
UserSchedule.USER_ID_PROPERTY);
try {
ctx.getContext().performQuery(query);
} catch (CayenneRuntimeException e) {
System.err.println(e.getMessage());
}
I'm getting the following message : "[v.3.0M5 Dec 09 2008
00:42:23]
Error
parsing EJB QL statement".
Can somebody tell me where I'm wrong ?
Thanks,
Francois.
