Spring Boot 中使用log4jdbc记录SQL的运行时参数

Spring Boot 中使用log4jdbc记录SQL的运行时参数

• 关于log4jdbc

log4jdbc is a Java JDBC driver that can log SQL and/or JDBC calls (and optionally SQL timing information) for other JDBC drivers using the Simple Logging Facade For Java (SLF4J) logging system.
在github上可以找到log4jdbc git的代码。

• 项目文件 结构
  
• 在appliaction.properties中添加必要的配置

spring.datasource.url = jdbc:log4jdbc:mysql://localhost:3306/springdemo?useUnicode=true&characterEncoding=UTF-8

    # Username and password
    spring.datasource.username = test
    spring.datasource.password = ****
    spring.datasource.driver-class-name=net.sf.log4jdbc.DriverSpy

    # ===============================
    # = JPA / HIBERNATE
    # ===============================

    # Use spring.jpa.properties.* for Hibernate native properties (the prefix is
    # stripped before adding them to the entity manager).

    # Show or not log for each sql query
    spring.jpa.show-sql = true

    # Hibernate ddl auto (create, create-drop, update): with "update" the database
    # schema will be automatically updated accordingly to java entities found in
    # the project
    spring.jpa.hibernate.ddl-auto = update

    # Naming strategy
    spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy

    # Allows Hibernate to generate SQL optimized for a particular DBMS
    spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

• 添加logback配置就文件

    <?xml version="1.0" encoding="UTF-8"?>
    <configuration>
        <include resource="org/springframework/boot/logging/logback/base.xml"/>
        <logger name="jdbc.connection" additivity="false" level="FATAL">
            <appender-ref ref="CONSOLE" />
            <appender-ref ref="FILE" />
        </logger>
        <logger name="jdbc.resultset" additivity="false" level="INFO">
            <appender-ref ref="CONSOLE" />
            <appender-ref ref="FILE" />
        </logger>
        <logger name="jdbc.audit" additivity="false" level="INFO">
            <appender-ref ref="CONSOLE" />
            <appender-ref ref="FILE" />
        </logger>
        <logger name="jdbc.sqlonly" additivity="false" level="DEBUG">
            <appender-ref ref="CONSOLE" />
            <appender-ref ref="FILE" />
        </logger>
        <logger name="jdbc.sqltiming" additivity="false" level="DEBUG">
            <appender-ref ref="CONSOLE" />
            <appender-ref ref="FILE" />
        </logger>
    </configuration>

• 在Application中添加几条数据

public class SpringDemoApplication {
    @Autowired
    PlayerRepository playerRepository;

    @PostConstruct
    public void logSomething() {
        log.debug("Sample Debug Message");
        log.trace("Sample Trace Message");

        Player player = new Player("Lion", "Messi", 29);
        playerRepository.save(player);

        player = new Player("Luis", "Suárez Díaz", 29);
        playerRepository.save(player);

        player = new Player("Neymar", "Santos Júnior", 24);
        playerRepository.save(player);

    }

    public static void main(String[] args) {
        SpringApplication.run(SpringDemoApplication.class, args);
    }
}

• 程序启动后可以看到日志输出

    2016-05-14 13:16:38.127  INFO 2900 --- [           main] jdbc.audit                               : 10. Connection.setAutoCommit(false) returned 
    Hibernate: insert into player (age, first_name, last_name) values (?, ?, ?)
    2016-05-14 13:16:38.164  INFO 2900 --- [           main] jdbc.audit                               : 10. PreparedStatement.new PreparedStatement returned 
    2016-05-14 13:16:38.164  INFO 2900 --- [           main] jdbc.audit                               : 10. Connection.prepareStatement(insert into player (age, first_name, last_name) values (?, ?, ?), 1) returned net.sf.log4jdbc.PreparedStatementSpy@61bfc9bf
    2016-05-14 13:16:38.166  INFO 2900 --- [           main] jdbc.audit                               : 10. PreparedStatement.setInt(1, 29) returned 
    2016-05-14 13:16:38.166  INFO 2900 --- [           main] jdbc.audit                               : 10. PreparedStatement.setString(2, "Lion") returned 
    2016-05-14 13:16:38.166  INFO 2900 --- [           main] jdbc.audit                               : 10. PreparedStatement.setString(3, "Messi") returned 
    2016-05-14 13:16:38.168 DEBUG 2900 --- [           main] jdbc.sqlonly                             :  org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
    10.     insert into player (age, first_name, last_name) values (29, 'Lion', 'Messi')    

• 相关文件

  • PlayerRepository.java

    @RepositoryRestResource(collectionResourceRel = "players", path = "players")
    public interface PlayerRepository extends PagingAndSortingRepository<Player, Long> {
        List<Player> findFirst10ByLastNameOrderByFirstNameDesc(String lastname, Sort sort);
    
    }

  • Player.java

    @Entity
    @Data
    @RequiredArgsConstructor
    @NoArgsConstructor
    public class Player {
        @Id
        @GeneratedValue
        private Long id;
        @NonNull
        private String firstName;
    
        @NonNull
        private String lastName;
    
        @NonNull private Integer age;
    }
    

  • pom.xml

    <project>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>1.3.5.RELEASE</version>
            <relativePath/>
            <!-- lookup parent from repository -->
        </parent>
    
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-rest</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.16.6</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
            <!-- http://mvnrepository.com/artifact/com.googlecode.log4jdbc/log4jdbc -->
            <dependency>
                <groupId>com.googlecode.log4jdbc</groupId>
                <artifactId>log4jdbc</artifactId>
                <version>1.2</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    </project>