Search code examples
javaoraclespring-data-r2dbcr2dbcoracle-r2dbc

Log SQL parameter values when using spring-boot-starter-data-r2dbc for Oracle database (oracle-r2dbc)

In my project I am using spring-data-r2bc which connects to an Oracle database. My dependencies are:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-webflux</artifactId>
</dependency>
<dependency>
    <groupId>com.oracle.database.r2dbc</groupId>
    <artifactId>oracle-r2dbc</artifactId>
    <version>1.2.0</version>
</dependency>

I am using ReactiveCrudRepository in order to retrieve the data:

@Query("SELECT DISTINCT * from PRODUCT prod WHERE  regexp_like (prod.number, (:number) )" )
Flux<Product> findByNumberContaining(String number);

I would like to see in the logs what exact SQL is being executed, along with the values of number. I have followed answers from Log values of query parameters in Spring Data R2DBC?, but nothing seems to work.

I am able to see the SQL, but without the parameter values:

o.s.d.r2dbc.core.NamedParameterExpander  : Expanding SQL statement [SELECT DISTINCT * from PRODUCT  prod WHERE  regexp_like (prod.number, (:number) )] to [SELECT DISTINCT * from PRODUCT prod WHERE  regexp_like (prod.number, (:P0_number) )]
o.s.r2dbc.core.DefaultDatabaseClient     : Executing SQL statement [SELECT DISTINCT * from PRODUCT prod WHERE  regexp_like (prod.number, (:P0_number) )  ]

My current properties are full of different settings which suppose to work for others but not for me:

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.springframework.http.codec.json=INFO
logging.level.org.springframework.web.HttpLogging=INFO
logging.level.org.springframework.r2dbc.core.DefaultDatabaseClient=TRACE
logging.level.org.springframework.r2dbc=TRACE
logging.level.io.r2dbc.postgresql.QUERY= DEBUG
logging.level.io.r2dbc.postgresql.PARAM= DEBUG
logging.level.io.r2dbc.oracle.QUERY= DEBUG
logging.level.io.r2dbc.oracle.PARAM= DEBUG

How can I log the value of the parameter: (:number)?

Solution

  • I have created a question for this topic in the orcale-r2dbc github page: https://github.com/oracle/oracle-r2dbc/issues/152 The answer from the team is:

    Oracle R2DBC doesn't include logging. While the logging.level.io.r2dbc.postgresql.PARAM property may be supported by Postgres R2DBC, but Oracle R2DBC doesn't have an equivalent.

    I can see this would be a desirable feature, so let's leave this issue open as a reminder to add it.

    Add a comment or upvote if you would like to see that improvement in the code.