Custom N1QL query with Couchbase and Spring Boot


#1

I have written a custom repository to get the results using couchbase’s N1QL. Here is my code,

@Override
    public List<Person> findAll(int age) {
        String statement = "select * from bucket_name where _class = 'com.mine.test.model.Person' and age>"+age;
        SimpleN1qlQuery query = N1qlQuery.simple(statement);
        List<Person> list=couchbaseTemplate.findByN1QL(query, Person.class);
        return list;
    }

But I am getting below error,

Unable to retrieve enough metadata for N1QL to entity mapping, have you selected _ID and _CAS?

Any help ?


#2

Try this

String statement = “select *, META().id AS _ID, META().cas AS _CAS from bucket_name where _class = ‘com.mine.test.model.Person’ and age>”+age;


#3

Thank you. it’s working.
But Why should I use these two values? Can’t we get rid of them?
Any Idea?


#4

may be due to person.class . cc @ingenthr


#5

I believe the requirement for the ID and CAS is owing to the fact that you may mutate the object, so we need a way to reference it back to the underlying item. When you build through query derivation and Spring Data repositories, that’s done pretty transparently to you, but when you implement a custom repository you’ll need to add that directly.

Is that correct @subhashni?


#6

Yes as @ingenthr mentioned, those values are required for mapping the values to key and version fields. Those fields are translated from the entity fields to id and cas when mutating the entity’s couchbase json document. For the custom repository, you can also do

@Query("#{#n1ql.selectEntity} WHERE #{#n1ql.filter}")
List<Person> findAllUsingN1ql();