CODEWITHSUNDEEP
javahibernatespring-bootp6spy
Дима Годиков
Дима Годиков

Reputation: 327

p6spy outputs sql messages twice with spring boot

I want to show sql parameters via p6spy in spring boot, because hibernate is showing sql parameters quite bulky. But for some reason p6spy logger output sql message twice although actually queries to the db execute once. Usual spring application works with my p6spy configuration normally. Spring boot application works with hibernate output normally.

spy.properties:

driverlist=org.postgresql.Driver
appender=com.p6spy.engine.spy.appender.Slf4JLogger
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
customLogMessageFormat=time %(executionTime)|con %(connectionId)|%(sqlSingleLine)

log4j2.xml:

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="WARN">
    <Properties>
        <Property name="layoutPattern">
            %d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n
        </Property>
    </Properties>

<Appenders>
    <Console name="stdout">
        <PatternLayout pattern="${layoutPattern}"/>
    </Console>


</Appenders>

<Loggers>
    <Root level="info">
        <AppenderRef ref="stdout" />
    </Root>
</Loggers>

</Configuration>

HibernateConfig:

@Configuration
@EnableTransactionManagement
public class HibernateConfig {

@Bean
public LocalSessionFactoryBean getSessionFactory(){

    LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
    sessionFactory.setPackagesToScan("bel.rdigital.p6spy.boot.test.model");

    Properties hibernateProperties = new Properties();
    hibernateProperties.setProperty(DIALECT, "org.hibernate.dialect.PostgreSQLDialect");
    hibernateProperties.setProperty(HBM2DDL_AUTO, "create");
    hibernateProperties.setProperty(SHOW_SQL, "true");

    sessionFactory.setDataSource(dataSource());
    sessionFactory.setHibernateProperties(hibernateProperties);

    return sessionFactory;
}

@Bean
public DataSource dataSource() {
    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setDriverClassName("com.p6spy.engine.spy.P6SpyDriver");
    dataSource.setUrl("jdbc:p6spy:postgresql://localhost:5432/p6spy");
    dataSource.setUsername("postgres");
    dataSource.setPassword("postgres");
    return new P6DataSource(dataSource);
}

@Bean
public HibernateTransactionManager getTransactionManager() {
    HibernateTransactionManager transactionManager = new HibernateTransactionManager();
    transactionManager.setSessionFactory(getSessionFactory().getObject());
    return transactionManager;
}

}

I expected one sql output, but get two similar sql output with different p6spy connections:

1) p6spy - time 1|con 2|insert into serverstartups (applicationName, hostName, ip, startDate, stopDate, startupId) values ('', 'DIMON-LAPTOP', '192.168.88.244', '2019-02-11T12:00:46.989+0300', NULL, 1)

2) p6spy - time 1|con 5|insert into serverstartups (applicationName, hostName, ip, startDate, stopDate, startupId) values ('', 'DIMON-LAPTOP', '192.168.88.244', '2019-02-11T12:00:46.989+0300', NULL, 1)

As you can see these outputs are similar with exception of connection(2 and 5)

Upvotes: 3

Views: 3304

Answers (2)

agavlyukovskiy
agavlyukovskiy

Reputation: 231

Spring Boot doesn't automatically create wrapper for DataSource. But p6spy has 2 ways to intercept:

Wrap your DataSource with P6DataSource or modify your connection URL to add ‘p6spy:’.

you should not use both.

Upvotes: 6

Дима Годиков
Дима Годиков

Reputation: 327

It turns out that spring boot automatically creates wrapper for p6spy, so this problem is solved replacing the line 

return new P6DataSource(dataSource)

on line

return dataSourse;

Upvotes: 1

Related Questions