How to use percent signs with LIKE in a custom Spring Boot N1QL query for use with a Couchbase DB

Question

I want to pass in a name to the method and have it add the percent signs for me inside the query.

If the Query is @Query("...file_name LIKE $1 ...") and I pass in %dogNames% it works perfectly. But what does the Query have to be for me to pass in dogNames (no %'s) and it works exactly the same?

I am able to do this with findByFileNameContains(). The query for findByFileNameContains() is auto-generated for me and works so that I can pass in a String like "dogNames.txt" and it will return File's with names like "/bin/dogNames.txt.9348393.tgz" and "/bigdogNames.txt". So it wrote something like this for me: @Query ("#{#n1ql.selectEntity} WHERE file_name LIKE %$1% ") The percent signs are included in the query and I just pass in the string I want the file name to contain.

How do I write a query myself that performs the same way? I tried what is shown for findByFileNamePatternAndFileDirectory() and I get this error:

{"msg":"syntax error - at %!(NOVERB)","code":3000} 

DAO class:

import com.mystuff.File;
import java.util.List;
import org.springframework.data.couchbase.core.query.Query;
import org.springframework.data.couchbase.repository.CouchbasePagingAndSortingRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface FileDao
    extends CouchbasePagingAndSortingRepository<File, String> {


  List<File> findByFileNameContains(String fileName);


  @Query(
      "#{#n1ql.selectEntity} WHERE #{#n1ql.filter} AND file_name LIKE %$1% AND  file_dir = $2")
  List<File> findByFileNamePatternAndFileDirectory(
      String fileName, String fileDir);
}

More Complete Error Logs:

{"msg":"syntax error - at %!(NOVERB)","code":3000}
    at org.springframework.data.couchbase.core.CouchbaseTemplate.findByN1QL(CouchbaseTemplate.java:470)
    at org.springframework.data.couchbase.repository.query.AbstractN1qlBasedQuery.executeCollection(AbstractN1qlBasedQuery.java:157)
    at org.springframework.data.couchbase.repository.query.AbstractN1qlBasedQuery.executeDependingOnType(AbstractN1qlBasedQuery.java:132)
    at org.springframework.data.couchbase.repository.query.AbstractN1qlBasedQuery.execute(AbstractN1qlBasedQuery.java:107)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:605)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$invoke$3(RepositoryFactorySupport.java:595)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:595)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.couchbase.repository.support.ViewPostProcessor$ViewInterceptor.invoke(ViewPostProcessor.java:87)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)

Answer 1

I wasn't able to find the query that would go in the @Query("...") however I did get it to work by using the "query builder mechanism built into Spring Data repository infrastructure" to auto-generate the query for me by naming the method in a specific way:

List<File> findByFileNameContainsAndFileDirectory(
      String fileName, String fileDir);
}

Here is where I found some good information on how to name the methods to get it to perform the Query you want: Spring-Data-Couchbase Docs

And this discusses the SpEL (Spring Expression Language, ex: #{#n1ql.filter}) in case you are curious how that works: SpEL