Apache Cayenne: Mysql's json search functions problem

Question

my code is:

String sql = "SELECT data, JSON_EXTRACT(data, '$.symbol') FROM quote;";
List<String> symbols = SQLSelect.scalarQuery(sql, String.class).select(DbConn.context);

error is:

Caused by: org.apache.cayenne.CayenneRuntimeException: [v.4.2.RC1 Jun 03 2022 10:10:32] Error parsing template 'SELECT data, JSON_EXTRACT(data, "$.symbol") FROM quote;' : Encountered " "." ". "" at line 1, column 35.
Was expecting one of:
    <IDENTIFIER> ...
    <TEXT_OTHER> ...

Answer 1

This seems like a problem in Cayenne itself, as its templates parser doesn't support escaping syntax.

I could see several options to work around this:

1. You could add argument that in question via query parameter like this:

   scalarQuery("... JSON_EXTRACT(data, '$path') ...").param("path", "$.symbol").select(...)

2. You could use the latest Cayenne 4.2.RC1, that supports JSON type and function calls. It would be something like this: ObjectSelect.columnQuery(YourEntity.class, YourEntity.DATA.function("JSON_EXTRACT", String.class, "$.symbol")).select(...) See this demo as a reference.

3. And finally, for the completeness of the answer, you could use cayenne-velocity module, that allows to use full-featured template engine, Apache Velocity. It supports escaping, unlike the parser in the Cayenne core.