IgorekPotworek IgorekPotworek - 4 years ago 219
Java Question

Spring-data-couchbase - running non ad-hoc parametrized query

Is any possibility to execute parametrized N1QL query with turned off adhoc flag using annotation @Query?

I.e. for given query:

@Query("#{#n1ql.selectEntity} WHERE #{#n1ql.filter} and author = $author")
List<Comment> getCommentsByAuthor(@Param("author") String author);

If not, is any other way to force couchbase to use secondary index (in this example index on author field) in annotation queries?

Answer Source

You seem to mix a few things:

  • Parametrized Query will work with the @Query annotation as in your snippet. The documentation mentions it (below the 1st warning block in this section)

  • N1QL secondary indexes should be automatically picked up by N1QL, depending on the statement. The subtlety here is that n1ql.filter. SDC needs that to limit such queries to the correct set of documents in an heterogeneous bucket.

  • adhoc is something else: it is about prepared statements. SDC doesn't use that feature, and it will only produce N1QL query with the default value for adhoc (which is still true as far as I know).

If you've logged the query produced by this annotation and run an EXPLAIN on it to effectively see that the index is not picked up, maybe try inverting the two expressions in the WHERE clause?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download