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.
* @param rootClass (Class<?>)
* @param exp (Expression)
* @return 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.
* @param SQLTemplateStatement
* @param exp Expression;
* @return 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>
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
Tel : +33 3 83 15 25 76
