SpringBoot connection invalid when use spring-boot-starter-jpa and querydsl.

• Question When I use spring data JPA and querydsl, in the case of multi data sources, I always report the problem of connection reset. To illustrate, this is a pre environment, so the frequency of service access is very low, and the database connection is likely not to be used for a long time. If the connection is not accessed in 120 seconds set by our database agent, the database agent will break the connection.

My database configuration is as follows: spring: datasource: primary: url: jdbc:mysql://xxx/xxx?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&allowMultiQueries=true&autoReconnect=true username: xxx password: xxx driver-class-name: com.mysql.jdbc.Driver initial-size: 10 validation-query: SELECT 1 max-idle: 8 min-idle: 5 max-active: 20 test-while-idle: true secondary: url: jdbc:mysql://xxxx/xxx?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&allowMultiQueries=true&autoReconnect=true username: xxx password: xxxx driver-class-name: com.mysql.jdbc.Driver initial-size: 10 validation-query: SELECT 1 max-idle: 5 min-idle: 2 max-active: 10 test-while-idle: true

DatasourceConfig.class @Configuration public class DataSourcesConfig { @Bean(name = "primaryDataSource") @Primary @ConfigurationProperties(prefix = "spring.datasource.primary") public DataSource primaryDataSource() { System.out.println("primary db built"); return DataSourceBuilder.create().build(); }

@Bean(name = "secondaryDataSource")
@ConfigurationProperties(prefix="spring.datasource.secondary")
public DataSource secondaryDataSource() {
    System.out.println("secondary db built");
    return DataSourceBuilder.create().build();
}

} PrimaryConfig.class @Configuration @EnableTransactionManagement @EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryPrimary", transactionManagerRef = "transactionManagerPrimary", basePackages = {"com.xx.xx.repositories"}) public class PrimaryDataSourceConfig { @Autowired @Qualifier("primaryDataSource") private DataSource primaryDataSource;

@Primary
@Bean(name = "entityManagerPrimary")
public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
    return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
}

@Primary
@Bean(name = "entityManagerFactoryPrimary")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary (EntityManagerFactoryBuilder builder) {
    return builder
            .dataSource(primaryDataSource)
            .properties(getVendorProperties(primaryDataSource))
            .packages("com.xx.xx.entity") //设置实体类所在位置
            .persistenceUnit("primaryPersistenceUnit")
            .build();
}

@Autowired(required=false)
private JpaProperties jpaProperties;

private Map<String, String> getVendorProperties(DataSource dataSource) {
    return jpaProperties.getHibernateProperties(dataSource);
}

@Primary
@Bean(name = "transactionManagerPrimary")
public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
    return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
}

@Bean
public JPAQueryFactory jpaQueryFactory(@Qualifier("entityManagerPrimary") EntityManager entityManagerPrimary) {
    return new JPAQueryFactory(entityManagerPrimary);
}

} SecondaryConfig.class @Configuration @EnableTransactionManagement @EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactorySecondary", transactionManagerRef = "transactionManagerSecondary", basePackages = {"com.xx.xx.database.repositories"}) public class SecondaryDataSourceConfig { @Autowired @Qualifier("secondaryDataSource") private DataSource secondaryDataSource;

@Bean(name = "entityManagerSecondary")
public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
    return entityManagerFactorySecondary(builder).getObject().createEntityManager();
}

@Bean(name = "entityManagerFactorySecondary")
public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder) {
    return builder
            .dataSource(secondaryDataSource)
            .properties(getVendorProperties(secondaryDataSource))
            .packages("com.xx.xx.database.entity") //设置实体类所在位置
            .persistenceUnit("secondaryPersistenceUnit")
            .build();
}

@Autowired(required = false)
private JpaProperties jpaProperties;

private Map<String, String> getVendorProperties(DataSource dataSource) {
    return jpaProperties.getHibernateProperties(dataSource);
}

@Bean(name = "transactionManagerSecondary")
public PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
    return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
}

}

I've seen a lot of solutions to this problem. They all say to set test-while-idle, but it doesn't work.

javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: could not extract ResultSet at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423) at org.hibernate.query.Query.getResultList(Query.java:146) at com.querydsl.jpa.impl.AbstractJPAQuery.getResultList(AbstractJPAQuery.java:160) at com.querydsl.jpa.impl.AbstractJPAQuery.fetch(AbstractJPAQuery.java:202) some private code at com.alibaba.ttl.TtlRunnable.run(TtlRunnable.java:55) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: org.hibernate.exception.JDBCConnectionException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:115) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69) at org.hibernate.loader.Loader.getResultSet(Loader.java:2168) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1931) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893) at org.hibernate.loader.Loader.doQuery(Loader.java:938) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341) at org.hibernate.loader.Loader.doList(Loader.java:2692) at org.hibernate.loader.Loader.doList(Loader.java:2675) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507) at org.hibernate.loader.Loader.list(Loader.java:2502) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:392) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1490) at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1445) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414) ... 19 common frames omitted Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 188,336 milliseconds ago. The last packet sent successfully to the server was 188,337 milliseconds ago. at sun.reflect.GeneratedConstructorAccessor267.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3746) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2509) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966) at sun.reflect.GeneratedMethodAccessor558.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) at com.sun.proxy.$Proxy321.executeQuery(Unknown Source) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) ... 34 common frames omitted Caused by: java.net.SocketException: Connection reset at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:115) at java.net.SocketOutputStream.write(SocketOutputStream.java:155) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3728) ... 45 common frames omitted - Managed Dependency Upgrade querydsl 4.1.4 spring-data-jpa 1.11.23.RELEASE spring-boot 1.5.22.RELEASE

Comment From: xiaolongzuo

Only when I use queuydsl (that is, JPA queryfactory), I will report an error. It's normal to use ordinary repositories of JPA.

Comment From: snicoll

Thanks for getting in touch, but it feels like this is a question that would be better suited to Stack Overflow. As mentioned in the guidelines for contributing, we prefer to use GitHub issues only for bugs and enhancements. Feel free to update this issue with a link to the re-posted question (so that other people can find it) or add some more details if you feel this is a genuine bug.