One of the ways to improve your application performance in Couchbase is to create indexes for common queries. However, when you are working with Spring Data using its standard DSL, the generated query is not very clear, which makes a little bit more difficult to create the right index to it.
This problem can be easily solved by enabling DEBUG log level in the class AbstractN1qlBasedQuery, it will print the generated N1QL query in the log, a behavior very similar to enabling the attribute show_sql in Hibernate. Â Here is how you can set this configuration in your application.yml
|
1 2 3 |
logging: level: org.springframework.data.couchbase.repository.query: DEBUG |
You can also enable it via logback  by simply adding it as a dependency:
|
1 2 3 4 5 |
<dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.2.3</version> </dependency> |
And then, add the logback.xml configuration file in your resources folder:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<configuration> <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender"> <encoder> <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</pattern> </encoder> </appender> <logger name="org.springframework.data.couchbase.repository.query" level="debug" /> <root level="info"> <appender-ref ref="STDOUT" /> </root> </configuration> |
When you run your application again, the generated N1QL queries will be printed in the logs.
|
1 2 3 4 |
2018-11-29 14:31:25.999 DEBUG 33427 --- [ main] o.s.d.c.r.query.AbstractN1qlBasedQuery : Executing N1QL query: {"statement":"SELECT META(`test`).id AS _ID, META(`test`).cas AS _CAS, `test`.* FROM `test` WHERE (`username` = \"myuser\") AND `_class` = \"com.bc.test.standalone.model.User\"","scan_consistency":"statement_plus"} 2018-11-29 14:31:26.079 DEBUG 33427 --- [ main] o.s.d.c.r.query.AbstractN1qlBasedQuery : Executing N1QL query: {"args":["company--1","userId--1"],"statement":"SELECT META(`test`).id AS _ID, META(`test`).cas AS _CAS, `test`.* FROM `test` where `_class` = \"com.bc.test.standalone.model.SecurityGroup\" and companyId = $1 and removed = false AND ARRAY_CONTAINS(users, $2) ","scan_consistency":"statement_plus"} 2018-11-29 14:31:26.186 DEBUG 33427 --- [ main] o.s.d.c.r.query.AbstractN1qlBasedQuery : Executing N1QL query: {"args":["area--1","company--1"],"statement":"SELECT META(`test`).id AS _ID, META(`test`).cas AS _CAS, `test`.* FROM `test` where `_class` = \"com.bc.test.standalone.model.BusinessUnity\" and companyId = $2 and $1 within `test`","scan_consistency":"statement_plus"} 2018-11-29 14:31:26.827 DEBUG 33427 --- [ main] o.s.d.c.r.query.AbstractN1qlBasedQuery : Executing N1QL query: {"statement":"SELECT META(`test`).id AS _ID, META(`test`).cas AS _CAS, `test`.* FROM `test` WHERE (`companyId` = \"company--1\" AND `removed` = false AND `familyId` = \"famillyrsc--FamiliaTeste\") AND `_class` = \"com.bc.test.standalone.model.MaintenancePlan\"","scan_consistency":"statement_plus"} |
If you have any questions, ping me at @deniswsrosa
Does this work with SpringBoot 2.4 with springdata-couchbase 4.1.1?I couldn’t enable logging for repository queries with below logback-spring.xml?
%d{HH:mm:ss} [%thread] %-5level %logger{36} – %msg%n
%d{HH:mm:ss} [%thread] %-5level %logger{36} – %msg%n
Seems comment can neither be edited nor deleted.logback xml is distorted.But its the same as your example
https://github.com/spring-projects/spring-data-couchbase/issues/1045
Hi! please use the package org.springframework.data.couchbase.core.query from Spring Data 4 onwards
https://www.couchbase.com/how-to-log-queries-generated-by-spring-data/