Monday, July 16, 2007


EntityWhereString simply allows you to add custom SQL syntax to the where clause for an entity operation. It extends EntityCondition so it can be used in the same way.

List pList = new ArrayList();
EntityWhereString conditions = new EntityWhereString("char_length(last_Name)>8");
EntityConditionList assocExprList = new EntityConditionList(UtilMisc.toList(conditions),EntityOperator.AND);

pList = delegator.findByCondition("Person",assocExprList,null,null);

you must use database names and not entity engine names. that is, if the column name in the database is "First_Name" then you can't use FirstName etc.
you can use database specific functions like char_length(), substr(), and the ever useful
EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
Custom functions could be used here as well.

Consider that this would allow SQL-injection if your application did not adequately scrub parameters coming from the client. This, I suppose, is the reason for this disclaimer in the source code:

Encapsulates SQL expressions used for where clause snippets.
NOTE: This is UNSAFE and BREAKS the idea behind the Entity Engine where
you avoid directly specifying SQL. So, KEEP IT MINIMAL and preferably replace
it when the feature you are getting at is implemented in a more automatic way for you.

conditions with EntityWhereString can be strung together with other normal sorts of EntityConditions in the EntityConditionList:

EntityConditionList assocExprList = new EntityConditionList(UtilMisc.toList(conditions,exp1),EntityOperator.AND);

where "conditions" is a EntityWhereString and exp1 is an EntityExpr

Does EntityWhereString work on entity-views??
Hell Yes! Even though the entity-view does not exist as far as the database is concerned, ofbiz passes the SQL where string back to the underlying table. Sweet.