How to log @Query in PagingAndSortingRepository?

Question

Is there a way to log some custom @Query method?

Here is example of my code:

@Query(value = "SELECT * FROM transfer WHERE char_length(internal_id) = 5 " +
        "AND internal_id REGEXP '^[0-9]+$' AND project_id = :projectId order by created_at desc limit 1", nativeQuery = true)
Transfer findLastWithDefaultOurIdForProject(@Param("projectId") String projectId);

It's written in interface that extends spring-data PagingAndSortingRepository.

I have tried to log it with adding these lines in property file: log4j.logger.org.hibernate.SQL=DEBUG log4j.logger.org.hibernate.type=TRACE

but I only get queries without real values passed from my service to repository interface?

score 3 · Accepted Answer · answered Sep 17 '16 at 10:23

3

Try this configuration:

application.properties

spring.jpa.properties.hibernate.format_sql=true
spring.jpa.database=h2

Add logback.xml file under src/main/resources to configure Hibernate to show parameters passed to the SQL Query:

logback.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <include resource="org/springframework/boot/logging/logback/base.xml"/>
    <logger name="org.springframework.web" level="DEBUG"/>

    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>%-4relative [%thread] %-5level %logger{35} - %msg %n</pattern>
        </encoder>
    </appender>

    <logger name="org.hibernate.SQL" additivity="false" >
        <level value="DEBUG" />
        <appender-ref ref="STDOUT" />
    </logger>

    <logger name="org.hibernate.type" additivity="false" >
        <level value="TRACE" />
        <appender-ref ref="STDOUT" />
    </logger>

</configuration>

You can find the working Demo Project in my GitHub repository.

answered Sep 17 '16 at 10:23

Sanjay Rawat

2,304
15
30

but is there a way to show real values passed to queries? – Ivan Aracki Sep 23 '16 at 09:17
@Raca See the log. This does show the real values paased to the sql query but in the lines below. It shows question mark in the query and real value passed is displayed in lines below. You can check the demo project and you'd see the real values after sql query. – Sanjay Rawat Sep 24 '16 at 16:36
thanks, it is true:) log is too much long but all infos are there :) – Ivan Aracki Sep 26 '16 at 12:14
Yes, unfortunately real values don't show up in sql query directly. – Sanjay Rawat Sep 27 '16 at 04:48

score 0 · Answer 2 · answered Sep 17 '16 at 21:15

0

The problem is your application properties. http://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-logging.html#boot-features-custom-log-levels

You have the wrong prefix on the properties.

logging.level.org.hibernate.SQL=DEBUG logging.level.org.hibernate.type=TRACE

answered Sep 17 '16 at 21:15

Shawn Clark

3,330
2
18
30

i changed properties... but is there a way to show real values passed to queries? – Ivan Aracki Sep 23 '16 at 09:02

How to log @Query in PagingAndSortingRepository?

2 Answers2

Linked