For custom query which is more feasible CouchbaseTemplate or Cluster or CouchbaseRepository?

[Vasavi]

We have the following use cases where we created a GSI index

1. Select only few fields from db using N1ql query (ex: select name, age from Person where dob=‘19-07-1990’)
2. Count the number of records using secondary index
   3.Get all fields from DB using secondary index
   4.Has to give query timeout options
   For the above use cases we are using Spring project where we can use CouchbaseTemplate or Cluster or CouchbaseRepsotory classes.Please help me which is the better option to implement

[deniswsrosa]

1- You can write custom queries with the repository pattern Spring Data Couchbase - Reference Documentation

2- You can’t specify the query timeouts using repositories, so either you use the Template (If the templates support that) or the standard SDK

In practice, you can use the Repository pattern for the simple queries and add an Impl for the ones that need to fall back to the standard SDK. Is not that hard to convert a result back to your object using the Java SDK:

cluster.bucket(bucket).scope(scope).query(query).rowsAs(MyObj.class)

[mreiche]

@Vasavi

We have the following use cases where we created a GSI index

spring data doesn’t have the concept of GSI but since you know what fields comprise the GSI, just use those fields.

@Vasavi

1. Select only few fields from db using N1ql query (ex: select name, age from Person where dob=‘19-07-1990’)

That can be accomplished by (a) template findByQuery() - using the as(entity) where entity has only the desired fields; (b) template findByQuery() - using the project(String fields) method; (c) repository - based on an entity that has only the desired fields. For the template, you would need to supply the query to the matching() method. For repository, the method to define would be findByDob(String dob)

@Vasavi

2. Count the number of records using secondary index

Probably easiest to define an repository @Query method with what you want.

@Vasavi

3. Get all fields from DB using secondary index

If the secondary index is a single field then it’s easy to define a repository method.
findByLastName(String lastname) // assuming the secondary index is lastname.

@Vasavi

4.Has to give query timeout options

For templates, use the withOptions() method. For repositories, define your repository to extend DynamicProxyable

public interface AirportRepository extends CouchbaseRepository<Airport, String>, DynamicProxyable<AirportRepository>{...}

And use the withOptions() method.

[Vasavi]

Thanks a lot Michael.
I see the option @Options(timeoutMs = 2) available. Is this configuration is query timeout?
EX:
@Repository
@ScanConsistency(query = QueryScanConsistency.REQUEST_PLUS)
@Options(timeoutMs = 2)
@Scope
@Collection
public interface UserRepository extends CouchbaseRepository<User, String> {

[Vasavi]

If so this option can mention at Class level that means for all queries that used i repository or for each method level.
@Query(“#{n1ql.selectEntity} WHERE user.state = $1”)
@Options(timeoutMs = 2)
long countUsersByIndex(String state);

[mreiche]

@Vasavi

@Options(timeoutMs = 2)

That is currently ignored. I’ve opened issue #1795 to address it (also note that the timeout does not appear in the logging of options).

Make your repository also implement DynamicProxyable and use the withOptions() method.

public interface UserRepository extends CouchbaseRepository<User, String>, DynamicProxyable<UserRepository> {

And use withOptions()
userRepository.withOptions(QueryOptions.queryOptions().timeout(Duration.ofMillis(1))).findByFirstname("Dave");