Hi !
We are using Cayenne version 3.0 M4, and we had this problem too.
Our need was to fetch lighweight objects than persistent objects, we
called Descriptors.
So we created a complex SQLTemplate query with Outer/Left joins ...etc,
but we needed to append to this SQLTemplate string an Expression.
(To filter the query on user rights for example)
We tried EJBQL, but we found some JOINS limitations, and a bug : an
"inExpr" transformated to EJBQL that was not working.
http://www.nabble.com/Pb-to-cast-Expression-to-ejbql-string-td16083997.html
So we found a way to append Expressions to SQLTemplate :
/**
* Transform a given Expression to a string for SQLTemplate statement.
*..aram rootClass (Class<?>)
*..aram exp (Expression)
*..eturn String
*/
public static String transformExpressionToSQLTemplateString(ObjectContext context, Class<?> rootClass, Expression exp)
{
if (exp != null && rootClass != null)
{
//Transform expression
String expString = CayenneUtil.getObjEntity(context, rootClass).translateToDbPath(exp).toString();
String dbTableName = CayenneUtil.getDbEntityName(context, rootClass);
dbTableName += SystemConstants.DOT;
//remove all db: prefixes
expString = expString.replaceAll("db:", dbTableName);
expString = expString.replaceAll("\"", "'");
return expString;
}
return null;
}
/**
* This will add to the given SQLTemplate string its WHERE statement.
*..aram SQLTemplateStatement
*..aram exp Expression;
*..eturn String - the entire SQLTemplate statement string.
*/
public static String addExpressionToSQLTemplate(ObjectContext context, String sqlTemplateStatement, Expression exp, Class<?> classRootForExpression) {
String sqlStatement = sqlTemplateStatement;
//Transform expression
String expString = CayenneUtil.transformExpressionToSQLTemplateString(context, classRootForExpression, exp);
//Add the statement to the current sql template query model
if (expString != null) {
if (sqlStatement.contains(SQLTemplateConstants.WHERE)) {
sqlStatement += SQLTemplateConstants.AND + "(" + expString + ")";
} else {
sqlStatement += SQLTemplateConstants.WHERE + "(" + expString + ")";
}
}
return sqlStatement;
}
I know that it's not very clean, but we had hard deadlines in our
project, and we needed something which work quickly.
We use this system everywhere in our Descriptors Factories, i can post
some code if needed.
Regards.
Laurent Marchal.
Lachlan Deck wrote:
> Hi there,
>
> (question related to ROP)
>
> say I've got an Expression (whether complex or otherwise) and I want
> to utilise that Expression in a NamedQuery or SQLTemplate... what's
> the easiest way to transform the expression to an sql equivalent
> string? (i.e., such that, e.g.,, boolean values are transformed from
> say true to 1 (if using an int column), dates are formatted
> appropriately etc, comparison operators are transformed (e.g., != to
> <>) etc.
>
> I see that there's a QueryAssembler and QualifierTranslator etc, but
> what I'm looking for, I guess, is a something like
> expression.toSQLString(baseEntity).
>
> Any suggestions?
>
> Thanks.
>
> with regards,
> --
>
> Lachlan Deck
>
>
>
>
--Laurent Marchal
SMA Europe www.smaeur.com <http://www.smaeur.com> lmarcha..maeur.com <mailto:lmarchal@smaeur.com> Tel : +33 3 83 15 25 76
This archive was generated by hypermail 2.0.0 : Tue Jun 10 2008 - 04:53:00 EDT