jcr query construct

Question

I am building a jcr query and receive data from repository. Here is my code:

    String queryString = "SELECT * FROM public:hours";

try {
  // get session
  Session session = requestContext.getSession();

  // create query from queryString constructed
  Query q = session.getWorkspace().getQueryManager().createQuery(queryString, Query.JCR_SQL2); 

  // execute query and retrieve result
  QueryResult result = q.execute();  


  // debug line
  log.error("query is", q.getStatement());
....

But this can not execute successfully. It gives me an error that

Repositorty Failed: 
[INFO] [talledLocalContainer] javax.jcr.query.InvalidQueryException: Query:
[INFO] [talledLocalContainer] SELECT * FROM public:(*)hours; expected: <end>

In the jcr-shell, it works if I type in query sql "select * from public:hours" and will give me proper results.
I searched many references but almost every example is the same as mine. so I am not sure where the problem is. Anyone have experience with this please help.

Answer 1

If you're using the JCR-SQL2 query language, then you should quote the selector name with square brackets:

SELECT * FROM [public:hours]

For details, see Section 6.7.4 of the JSR-283 (aka, JCR 2.0) specification, which is also available online. Note that the square bracket quote characters are not required if the name were to be a valid SQL92 identifier. The node type names containing namespace prefixes always need to be quoted, since the ':' character is not allowed in SQL92 identifiers.

Of course, this assumes that you have a node type named "public:hours", where "public" is the namespace prefix.