PostgreSQL创建HikariDataSource解决报错autoCommit
问题
项目是使用jhipster构建的,使用liquibase管理数据库连接 我们使用的PostgreSQL数据库,由于业务需要,需要新增一个ClickHouse数据库,但是这样两个DataSource
导致程序出错
解决
首先,想的是对PostgreSQL数据库连接,显式声明。在DatabaseConfiguration
里,显式声明@Bean("springDataSource")
并设置@Primary
,优先加载使用pg
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
@Configuration
@EnableJpaRepositories({ "com.xxx.analysis.repository" })
@EnableJpaAuditing(auditorAwareRef = "springSecurityAuditorAware")
@EnableTransactionManagement
public class DatabaseConfiguration {
private static final Logger logger = LoggerFactory.getLogger(DatabaseConfiguration.class);
/**
* PG数据源
* 由于手动注入了CK数据源,所以需要在这里显式声明PG数据源优先为springDataSource
* @param properties yml里的数据源配置
* @return dataSource
*/
@Primary //默认优先选择
@Bean("springDataSource") //注入springDataSource对象
@ConfigurationProperties(prefix = "spring.datasource") //注入dspringDataSource对象读取配置文件,spring.datasource为前缀,之后字段和实体类的属性一致进行匹配诸如内容
public DataSource dataSource(DataSourceProperties properties) {
logger.info("init master data source:{}", properties.determineDatabaseName());
return DataSourceBuilder.create().build();
}
}
这样程序启动没有报错了,但是使用起来有问题,报错org.springframework.orm.jpa.JpaSystemException: Unable to commit against JDBC Connection; nested exception is org.hibernate.TransactionException: Unable to commit against JDBC Connection
Caused by: org.hibernate.TransactionException: Unable to commit against JDBC Connection
at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:92)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:282)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:562)
... 155 common frames omitted
Caused by: org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled.
at org.postgresql.jdbc.PgConnection.commit(PgConnection.java:872)
at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:387)
at com.zaxxer.hikari.pool.HikariProxyConnection.commit(HikariProxyConnection.java)
at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:86)
... 158 common frames omitted
改为使用注解声明后,autoCommit
没生效,调试了下发现DataSourceProperties
只接收部分参数,而autoCommit
没在里面 没办法,只好自己接收参数,自己创建连接初始化HikariDataSource
了,最终配置和代码如下
配置文件
spring:
datasource:
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:postgresql://192.168.1.50:5432/june
username: postgres
password: postgres
hikari:
poolName: Hikari
autoCommit: false
maximum-pool-size: 100
connection-timeout: 60000
idle-timeout: 400000
minimum-idle: 10
max-lifetime: 480000
connection-test-query: SELECT 1
代码
package com. xxx. analysis. config ;
import com. zaxxer. hikari. HikariConfig ;
import com. zaxxer. hikari. HikariDataSource ;
import org. slf4j. Logger ;
import org. slf4j. LoggerFactory ;
import org. springframework. beans. factory. annotation. Value ;
import org. springframework. boot. autoconfigure. jdbc. DataSourceProperties ;
import org. springframework. boot. context. properties. ConfigurationProperties ;
import org. springframework. context. annotation. Bean ;
import org. springframework. context. annotation. Configuration ;
import org. springframework. context. annotation. Primary ;
import org. springframework. data. jpa. repository. config. EnableJpaAuditing ;
import org. springframework. data. jpa. repository. config. EnableJpaRepositories ;
import org. springframework. transaction. annotation. EnableTransactionManagement ;
import org. springframework. util. StringUtils ;
import javax. sql. DataSource ;
@Configuration
@EnableJpaRepositories ( { "com.xxx.analysis.repository" } )
@EnableJpaAuditing ( auditorAwareRef = "springSecurityAuditorAware" )
@EnableTransactionManagement
public class DatabaseConfiguration {
private static final Logger logger = LoggerFactory . getLogger ( DatabaseConfiguration . class ) ;
@Value ( "${spring.datasource.hikari.poolName}" )
private String poolName;
@Value ( "${spring.datasource.hikari.autoCommit}" )
private boolean autoCommit;
@Value ( "${spring.datasource.hikari.connection-timeout}" )
private Integer connectionTimeout;
@Value ( "${spring.datasource.hikari.idle-timeout}" )
private Integer idleTimeout;
@Value ( "${spring.datasource.hikari.max-lifetime}" )
private Integer maxLifetime;
@Value ( "${spring.datasource.hikari.minimum-idle}" )
private Integer minimumIdle;
@Value ( "${spring.datasource.hikari.maximum-pool-size}" )
private Integer maximumPoolSize;
@Value ( "${spring.datasource.hikari.connection-test-query}" )
private String connectionTestQuery;
@Primary
@Bean ( "springDataSource" )
@ConfigurationProperties ( prefix = "spring.datasource" )
public DataSource dataSource ( DataSourceProperties properties) {
HikariDataSource dataSource = new HikariDataSource ( getConfig ( properties) ) ;
if ( StringUtils . hasText ( properties. getName ( ) ) ) {
dataSource. setPoolName ( properties. getName ( ) ) ;
}
return dataSource;
}
private HikariConfig getConfig ( DataSourceProperties properties) {
HikariConfig hikariConfig = new HikariConfig ( ) ;
hikariConfig. setJdbcUrl ( properties. getUrl ( ) ) ;
hikariConfig. setUsername ( properties. getUsername ( ) ) ;
hikariConfig. setPassword ( properties. getPassword ( ) ) ;
hikariConfig. setMaximumPoolSize ( maximumPoolSize) ;
hikariConfig. setConnectionTestQuery ( connectionTestQuery) ;
hikariConfig. setPoolName ( poolName) ;
hikariConfig. setAutoCommit ( autoCommit) ;
hikariConfig. setConnectionTimeout ( connectionTimeout) ;
hikariConfig. setIdleTimeout ( idleTimeout) ;
hikariConfig. setMaxLifetime ( maxLifetime) ;
hikariConfig. setMinimumIdle ( minimumIdle) ;
hikariConfig. addDataSourceProperty ( "dataSource.cachePrepStmts" , "true" ) ;
hikariConfig. addDataSourceProperty ( "dataSource.prepStmtCacheSize" , "250" ) ;
hikariConfig. addDataSourceProperty ( "dataSource.prepStmtCacheSqlLimit" , "2048" ) ;
hikariConfig. addDataSourceProperty ( "dataSource.useServerPrepStmts" , "true" ) ;
return hikariConfig;
}
}