求助大佬：多数据源下 postgres 重启引起 springboot 应用数据库连接断开
powinds · 2022-04-21 19:08:54 +08:00 · 1697 次点击这是一个创建于 934 天前的主题，其中的信息可能已经有所发展或是发生改变。
这是我的多数据源配置
spring:
servlet:
multipart:
max-file-size: 64MB
max-request-size: 64MB
jpa:
hibernate:
ddl-auto: update
show-sql: true
properties:
hibernate:
naming:
physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
primary-dialect: org.hibernate.dialect.PostgreSQLDialect
secondary-dialect: org.hibernate.dialect.PostgreSQLDialect
temp:
use_jdbc_metadata_defaults: false
database-platform: org.hibernate.dialect.PostgreSQL10Dialect
primary:
datasource:
driver-class-name: org.postgresql.Driver
jdbc-url: jdbc:postgresql://postgres-0.postgres.postgres:5432/platform
username: postgres
password: postgres
# JPA 重连
test-on-borrow: true
validation-query: SELECT 1
# 连接池名称
pool-name: HikariPool-1
## 最小空闲连接数
minimum-idle: 10
## 最大连接数
maximum-pool-size: 20
## 空闲连接存活最大时间
idle-timeout: 30000
## 最长生命周期
max-lefetime: 30000
## 连接超时时间
connection-timeout: 30000
secondary:
datasource:
driver-class-name: org.postgresql.Driver
jdbc-url: jdbc:postgresql://postgres-0.postgres.postgres:5432/keycloak
username: postgres
password: postgres
# JPA 重连
test-on-borrow: true
validation-query: SELECT 1
# 连接池名称
pool-name: HikariPool-1
## 最小空闲连接数
minimum-idle: 10
## 最大连接数
maximum-pool-size: 20
## 空闲连接存活最大时间
idle-timeout: 30000
## 最长生命周期
max-lefetime: 30000
## 连接超时时间
connection-timeout: 30000
这是我的 JAVA 多数据源
@Configuration
public class DataSourceConfig {
private static Logger logger = LoggerFactory.getLogger(DataSourceConfig.class);
@Bean(name = "primaryDataSource")
@Qualifier("primaryDataSource")
@ConfigurationProperties(prefix = "spring.primary.datasource")
@Primary
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "secondaryDataSource")
@Qualifier("secondaryDataSource")
@ConfigurationProperties(prefix = "spring.secondary.datasource")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
}
public class PrimaryConfig {
@Autowired
@Qualifier("primaryDataSource")
private DataSource primaryDataSource;
@Primary
@Bean(name = "entityManagerPrimary")
public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
return Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject()).createEntityManager();
}
@Primary
@Bean(name = "entityManagerFactoryPrimary")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
return builder.dataSource(primaryDataSource)
.properties(getVendorProperties(primaryDataSource))
.packages("net.skycloud.platform.licensemanager.model", "net.skycloud.platform.mail.model",
"net.skycloud.platform.perm.model", "net.skycloud.platform.model")
.persistenceUnit("primaryPersistenceUnit")
.build();
}
@Autowired
private JpaProperties jpaProperties;
@Autowired
private HibernateProperties hibernateProperties;
private Map<String, Object> getVendorProperties() {
return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
}
private Map<String, String> getVendorProperties(DataSource dataSource) {
Map<String, String> jpaProperties = new HashMap<>(16);
jpaProperties.put("hibernate.hbm2ddl.auto", "update");
jpaProperties.put("hibernate.show_sql", "true");
// jpaProperties.put("hibernate.format_sql", "true");
jpaProperties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
// jpaProperties.put("hibernate.dialect", "net.skycloud.platform.common.config.PgDialect");
// jpaProperties.put("hibernate.current_session_context_class",
// "org.springframework.orm.hibernate5.SpringSessionContext");
return jpaProperties;
}
@Primary
@Bean(name = "transactionManagerPrimary")
public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
}
}
public class SecondaryConfig {
@Autowired
@Qualifier("secondaryDataSource")
private DataSource secondaryDataSource;
@Bean(name = "entityManagerSecondary")
public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
return Objects.requireNonNull(entityManagerFactorySecondary(builder).getObject()).createEntityManager();
}
@Bean(name = "entityManagerFactorySecondary")
public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder) {
return builder.dataSource(secondaryDataSource)
.properties(getVendorProperties(secondaryDataSource))
.packages("net.skycloud.platform.user.keycloakmodel")
.persistenceUnit("secondaryPersistenceUnit")
.build();
}
@Autowired
private JpaProperties jpaProperties;
@Autowired
private HibernateProperties hibernateProperties;
@Autowired
private Environment env;
private Map<String, Object> getVendorProperties() {
return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
}
private Map<String, String> getVendorProperties(DataSource dataSource) {
Map<String, String> jpaProperties = new HashMap<>(16);
jpaProperties.put("hibernate.hbm2ddl.auto", "none");
jpaProperties.put("hibernate.show_sql", "true");
// jpaProperties.put("hibernate.format_sql", "true");
// jpaProperties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
jpaProperties.put("hibernate.dialect", "net.skycloud.platform.common.config.PgDialect");
// jpaProperties.put("hibernate.current_session_context_class",
// "org.springframework.orm.hibernate5.SpringSessionContext");
return jpaProperties;
}
//用来作为数据库事务回滚的限定词
//@Transactional(rollbackFor = OAPMException.class, value = "transactionManagerSecondary")
//事务管理器
@Bean(name = "transactionManagerSecondary")
PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
}
}
当我再 k8s 上重启 postgres 时，访问任意接口报错如下：
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1626)
at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1665)
at net.skycloud.platform.user.repository.UserDao.findOneByUsername(UserDao.java:121)
at net.skycloud.platform.user.service.UserService.getUserInfo(UserService.java:184)
at net.skycloud.platform.user.service.UserService$$FastClassBySpringCGLIB$$e1720440.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:783)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:123)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:698)
at net.skycloud.platform.user.service.UserService$$EnhancerBySpringCGLIB$$a191bfb.getUserInfo(<generated>)
at net.skycloud.platform.user.api.rest.UserController.getCurrentUser(UserController.java:358)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:645)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:889)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:151)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:2122)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2059)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037)
at org.hibernate.loader.Loader.doQuery(Loader.java:956)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357)
at org.hibernate.loader.Loader.doList(Loader.java:2868)
at org.hibernate.loader.Loader.doList(Loader.java:2850)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682)
at org.hibernate.loader.Loader.list(Loader.java:2677)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2186)
at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1204)
at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:177)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617)
... 61 common frames omitted
Caused by: java.sql.SQLException: Connection is closed
at com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection.lambda$getClosedConnection$0(ProxyConnection.java:515)
at com.sun.proxy.$Proxy155.prepareStatement(Unknown Source)
at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337)
at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)
... 76 common frames omitted
试了很多办法没有解决，各位大佬我该怎么解决这个问题？
3 条回复 • 2022-04-22 14:10:42 +08:00
 |
1
angryfish 2022-04-22 09:23:37 +08:00
把 HikariCP 换掉，用下 druid 之类的？
或者试试 connection-test-query: SELECT 1 ，每隔一段段时间会去检查数据库连接的可用性 |
 |
3
powinds OP #初始化连接
initial-size: 10 #最大空闲连接 max-idle: 20 #最小空闲连接 min-idle: 5 #最大连接数量 max-active: 50 #是否在自动回收超时连接的时候打印连接的超时错误 log-abandoned: true #是否自动回收超时连接 remove-abandoned: true #超时时间(以秒数为单位) remove-abandoned-timeout: 180 max-wait: 1000 test-while-idle: true #检测数据库的查询语句 validation-query: select 1 from dual test-on-borrow: true #每隔五分钟检测空闲超过 10 分钟的连接 min-evictable-idle-time-millis: 600000 time-between-eviction-runs-millis: 300000 又加了一大堆，但是没用，准备打几个连接数的日志看看 |
Select Language