2019-01-29

在控制台实时打印jdbc执行的sql

参考

经常需要查看程序最后提交到数据库的sql,可以使用Log4JDBC,实时记录所有执行的sql

添加pom引用

<!-- SLF4J API -->
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>1.6.4</version>
</dependency>

<!-- LOG4J -->
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-log4j12</artifactId>
    <version>1.6.4</version>
</dependency>

<!-- LOG4J for JDBC -->
<dependency>
    <groupId>org.lazyluke</groupId>
    <artifactId>log4jdbc-remix</artifactId>
    <version>0.2.7</version>
</dependency>

添加 resources/log4j.properties

添加 resources/log4j.properties

# Root logger option
log4j.rootLogger=INFO,stdout # level,输出方式

#定义一个输出方式
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n

#设置输出级别
log4j.logger.jdbc.sqlonly=ERROR
log4j.logger.jdbc.sqltiming=INFO
log4j.logger.jdbc.audit=OFF
log4j.logger.jdbc.resultset=ERROR
log4j.logger.jdbc.connection=ERROR
log4j.logger.jdbc.resultsettable=ON

修改Driver和连接字符串

@Configuration
@ComponentScan("com.weigram")
@PropertySource("classpath:/jdbc.properties")
public class Config01 {
    @Value("${db.user}")
    private String user;
    @Value("${db.password}")
    private String password;

    @Bean()
    public DataSource dataSource1() throws Exception {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setUser(user);
        dataSource.setPassword(password);
        // dataSource.setJdbcUrl("jdbc:mysql://118.89.189.78:3306/test");
        dataSource.setJdbcUrl("jdbc:log4jdbc:mysql://118.89.189.78:3306/test");
        // dataSource.setDriverClass("com.mysql.jdbc.Driver"); 
        dataSource.setDriverClass("net.sf.log4jdbc.DriverSpy");
        return dataSource;
    }


    @Bean
    public JdbcTemplate jdbcTemplate() throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource1());
        return jdbcTemplate;
    }
}

测试代码

public class TxTest {
    @Test
    public void testConfig01(){
        //创建一个applicationContext
        AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Config01.class);
        UserService userService = (UserService)applicationContext.getBean("userService");
        userService.insert();
        applicationContext.close();
    }
}

会打印出

2019-01-28 21:21:41 INFO  sqltiming:357 - INSERT INTO USER ( NAME, age) VALUES ( 'zhangdan', 20)  {executed in 30 msec}

完成的pom依赖

<dependencies>
  <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>compile</scope>
  </dependency>
  <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>4.3.12.RELEASE</version>
  </dependency>
  <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>4.3.12.RELEASE</version>
  </dependency>
  <dependency>
      <groupId>com.mchange</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.2</version>
  </dependency>

  <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.44</version>
  </dependency>

  <!-- SLF4J API -->
  <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.6.4</version>
  </dependency>

  <!-- LOG4J -->
  <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>1.6.4</version>
  </dependency>

  <!-- LOG4J for JDBC -->
  <dependency>
      <groupId>org.lazyluke</groupId>
      <artifactId>log4jdbc-remix</artifactId>
      <version>0.2.7</version>
  </dependency>

</dependencies>