How to pass parameter to Couchbase query with @Query annotation?

Question

I am trying to pass a parameter to custom query but it is not working.

My class is like this:

@Repository public interface CarRepository extends CouchbaseRepository<CarDocument, String> {
    @Query(
        value = "#{#n1ql.selectEntity} #{#n1ql.bucket} where cars.`:#{#carId}` is not missing; ")
    List<CarDocument> getCarDocumentsByCarId(@Param("carId")Integer carId);

}

The returned value is null. So it is not working. what am I doing wrong?

Answer 1

Here's an example of how you can pass parameters to a Couchbase query:

@Repository
public interface UserRepository extends CouchbaseRepository<User, String> {

    @Query("#{#n1ql.selectEntity} WHERE username = $1")
    User findByUsername(String username);
}

User user = userRepository.findByUsername("johndoe");

The placeholder $1 is used to represent the first parameter passed to the method.

When you call the findByUsername method and pass a username as an argument, it will be substituted in the query at the placeholder's position.

Answer 2

use the spel expression #{[<n>]}

@Query("SELECT META(#{#n1ql.bucket}).id AS __id, META(#{#n1ql.bucket}).cas AS __cas, meta().id as id FROM #{#n1ql.bucket} WHERE #{#n1ql.filter} #{[1]}")
@ScanConsistency(query = QueryScanConsistency.REQUEST_PLUS)
Flux<String> findIdByDynamicN1ql(String docType, String queryStatement);


List<String> all = reactiveAirportRepository.findIdByDynamicN1ql("ignored", "and 1=1").toStream().collect(Collectors.toList());

Gives the following. Notice how it replaces the #{[1]} with the second argument.

2023-06-23 16:21:29,302 DEBUG core.ReactiveFindByQueryOperationSupport: 184 - findByQuery scope: null collection: null options: null statement: SELECT META(9d4e29b1-6d3c-4c07-872d-241d232a3a0a).id AS __id, META(9d4e29b1-6d3c-4c07-872d-241d232a3a0a).cas AS __cas, meta().id as id FROM 9d4e29b1-6d3c-4c07-872d-241d232a3a0a WHERE t = "airport" and 1=1