Posted by
Adam Heath-2 on
Sep 05, 2005; 7:27am
URL: http://ofbiz.116.s1.nabble.com/OFBiz-Dev-entity-engine-sql-using-javacc-tp165963p165964.html
On Sun, 4 Sep 2005, Adam Heath wrote:
> [snip]
Well, I have condition parsing working now. A snippet of a full sql query is
included at the end.
The code will parse any sql condition, and produce a real EntityCondition
object. Positional parameters are a new type of EntityConditionValue.
I am planning on making a cache of this, so that the reparse doesn't have to
happen all the time.
Here's how I think this might be used:
==
EntityCondition condition = EntityCondition.parse("((webSiteId = ?1 OR
(webSiteId = ?2 AND hostName LIKE '%.com')) OR foo BETWEEN ( 'a', 'b'
) OR foo IS NULL OR foo IS NOT NULL OR foo < '1' OR foo <= '1' OR foo > '1' OR
foo >= '1' OR foo = '1' OR foo <> '1' OR foo LIKE '%.com' OR L
OWER(foo) = LOWER('bar'))");
ArrayList params = new ArrayList(2);
params.add("first");
params.add("second");
PositionalParameter.setParams(params);
List values = delegator.findByConditionCache(entityName, condition, null,
null);
PositionalParameter.clearParams();
==
This makes use of a ThreadLocal in PositionalParameter, and the freeze feature
when the entity engine creates an actual sql query, to convert the positional
params into real values. You can set a positional parameter to null as well,
and that'll work fine.
At this point, I have all that I need to support a sql definition language,
but still have it play nice with the rest of the entity model. I may not get
it done tomorrow, but I don't forsee any hurdles at this point.
ps: the sql parser doesn't like the IN operator, and the output likes to quote
plain numbers. The latter shouldn't be hard to fixed(they are parsed as
strings or numbers, I'm just not checking). The former a bit more difficult,
but still easy. Also, I see that no one actually is using the BETWEEN
operator anywhere in ofbiz. The sql string it produces is buggy, as evident
by the code above.
==
WHERE
(
a.webSiteId = ?1
OR
a.webSiteId = ?2
AND
a.hostName LIKE '%.com'
)
OR
a.foo BETWEEN 'a' AND 'b'
OR
a.foo IS NULL
OR
a.foo IS NOT NULL
OR
a.foo < 1
OR
a.foo <= 1
OR
a.foo > 1
OR
a.foo >= 1
OR
a.foo = 1
OR
a.foo != 1
-- OR
-- a.foo IN ( 1, 2, 3 )
-- OR
-- a.foo NOT IN ( 1, 2, 3 )
OR
a.foo NOT LIKE '%.com'
OR
LOWER(a.foo) = LOWER('bar')
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev