Why does JDOQL query using "matches" semantics only work with a literal?

Question

I'm trying to construct a JDOQL query (using datanucleus) that will search for matches of a parent class based on criteria in an owned one-to-many child class. The query looks like this:

    Query lQ = lPm.newQuery("select from "+Module.class.getName()+
            " where moduleMappings.contains(m)" +
            " && showNameParam.matches(m.criteria.trim())");
    lQ.declareVariables(ModuleMapping.class.getName()+" m");
    lQ.declareParameters("String showNameParam");

lRet = (List<Module>) lQ.execute("StarTrek");

My data set looks something like this:

Module[1]
- ModuleMapping[1]: criteria=".*"
Module[2]
- ModuleMapping[1]: criteria=".*StarTrek.*"
- ModuleMapping[2]: criteria=".*StarWars.*"

The query never matches on anything! However, if I replace the argument to the matches JDOQL method with a literal:

Query lQ = lPm.newQuery("select from "+Module.class.getName()+
            " where moduleMappings.contains(m)" +
            " && showNameParam.matches('.*StarTrek.*')");

Things will work for that single example, and my query will find Module[2]. What am I missing? Am I not allowed to use the contents of a mapped field as the argument to a JDOQL method? Do I need to escape things in some way?

Dave

score 1 · Accepted Answer · edited Aug 02 '12 at 13:37

1

So I figured this out, although to me it seems like a bug in either JDOQL or datanucleus. When using a mapped field as the argument to the matches method, the generated SQL does not translate the JDOQL syntax to the syntax of the data store (in my case, SQL). So in my example above, if I change the criteria fields to use SQL wildcard syntax rather than JDOQL syntax things will start to work.

Specifically, if in my example above I use criteria="%StarTrek%" rather than criteria=".\*StarTrek.\*" the JDOQL queries will start to match.

This doesn't seem right to me as different data stores could use different matching syntax, but this workaround gets me moving again...

edited Aug 02 '12 at 13:37

GDP

8,109
6
45
82

answered Sep 29 '10 at 04:09

HackySchmacky

79
10

If you think something is a bug then you provide a testcase and a statement of what feature of the spec is not correctly implemented (and the JDO TCK does have tests for such things, but maybe yours is some other situation ... we won't know til we see your testcase). Needless to say, looking at the SQL invoked (as I put in the reply) is the only way to debug things, and you present no evidence of this – DataNucleus Sep 29 '10 at 06:38
Fair enough, I'll look into filing a bug report, although I'm not 100% sure this is a bug. I'm not yet familiar enough with JDOQL spec to be sure. – HackySchmacky Sep 29 '10 at 16:33
As you can see from the very end of the SQL above, "WHERE 'Glee' LIKE B0.CRITERIA ESCAPE '\\'", there is no attempt to transform the contents of B0.CRITERIA from JDOQL syntax to native datastore syntax, thus the need to use SQL wildcard syntax in my CRITERIA field rather than JDOQL syntax. – HackySchmacky Sep 29 '10 at 16:38
Hence it obeys the JDO spec then. i.e when hardcoding a matches clause into the JDOQL it follows Java regexp syntax. The JDO spec says nothing about what is contained in a field when that field is used in the matches clause – DataNucleus Sep 30 '10 at 11:01
1

Can you suggest a portable way to accomplish this then? Is there a way that I could manually escape JDOQL wildcard to datastore and thus keep my implementation portable? – HackySchmacky Sep 30 '10 at 13:19

DataNucleus · Answer 2 · 2010-09-28T16:44:38.547

0

The log would obviously tell you what SQL is being invoked for your query. You could also address why you are mixing API JDOQL and single-string JDOQL ... definition of parameters/variables should be in the single-string if using single-string.

edited Sep 28 '10 at 16:44

answered Sep 28 '10 at 09:14

DataNucleus

15,497
3
32
37

Why does JDOQL query using "matches" semantics only work with a literal?

2 Answers2