无法对JDBC连接进行提交

Question

我正在使用Spring Boot连接到MySQL数据库。请查看下面的配置：

spring.datasource.url=jdbc:<connection-url>
spring.datasource.username=<username>
spring.datasource.password=<password>
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.show-sql=true

spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.format_sql=true
spring.datasource.tomcat.max-active=50
spring.datasource.tomcat.max-idle=20
spring.datasource.tomcat.max-wait=20000
spring.datasource.tomcat.min-idle=15

API 代码

@CrossOrigin(origins = "*", allowedHeaders = "*")
@GetMapping(value = "/validateuser/{consumerName}")
@Transactional
public Boolean valiadateuser(@PathVariable String consumerName) {
    LOGGER.info("Inside validateuser -1");
    ConsumerName user = consumerRepository.findByName(consumerName);
    LOGGER.info("Inside validateuser -2 :::: " + user);
    if (user != null) {
        return Boolean.TRUE;
    }
    return Boolean.FALSE;
}

以下是我的异常

 org.springframework.orm.jpa.JpaSystemException: Unable to commit against JDBC Connection; nested exception is org.hibernate.TransactionException: Unable to commit against JDBC Connection
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:353) ~[spring-orm-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255) ~[spring-orm-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:538) ~[spring-orm-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:743) [spring-tx-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711) [spring-tx-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:665) [spring-tx-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:370) [spring-tx-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118) [spring-tx-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) [spring-aop-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749) [spring-aop-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691) [spring-aop-5.2.5.RELEASE.jar!/:5.2.5.RELEASE]
    at com.server.controller.SubscribeController$$EnhancerBySpringCGLIB$$14f090fd.subscribeTopic(<generated>) [classes!/:0.0.1-SNAPSHOT]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_251]

- Saurabh Jhunjhunwala

---

为什么需要添加@Transactional？ - user7294900

@Transactional并不保证自动提交和关闭连接。 - Saurabh Jhunjhunwala

@user7294900 我已经删除了注释，问题仍然存在。 - Saurabh Jhunjhunwala

Answer 1

得到了答案

更新了resource.properties文件

spring.datasource.url=jdbc:<connection-url>
spring.datasource.username=<username>
spring.datasource.password=<password>
#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.show-sql=true

spring.jpa.hibernate.ddl-auto=update
spring.datasource.hikari.connectionTimeout=20000
spring.datasource.hikari.maximumPoolSize=5

- Saurabh Jhunjhunwala

Answer 2

我在练习中使用了Spring、Hibernate和PostgreSQL，遇到了类似的异常：

org.springframework.transaction.TransactionSystemException: Could not commit Hibernate transaction; 
nested exception is org.hibernate.TransactionException: 
Unable to commit against JDBC Connection] with root cause
  org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled.

我的 hibernate.prop 文件中有以下一些配置

hibernate.dialect=org.hibernate.dialect.PostgreSQL10Dialect
hibernate.connection.handling_mode=DELAYED_ACQUISITION_AND_RELEASE_AFTER_STATEMENT

packages.to.scan=org.practice.dao.entity

很奇怪，因为在Hibernate 5.6中，默认的hibernate.connection.autocommit值是false。

经过整整一天的搜索，我在互联网上找不到相同的错误，最后我弄清楚了：不知何故，在我的配置文件中添加了hibernate.connection.handling_mode，删除它（默认值就行），应用程序就正常工作了。

所以也许检查配置并使用最简单的参数会帮助其他人。

- Jon Wang

Answer 3

我在使用Spring / Postgres堆栈时遇到了相同的异常。基本上，数据库无法及时返回/提交与您的查询匹配的所有行。

可以通过在特定查询中使用的列上创建索引来解决此问题。这会加速查询速度。

CREATE INDEX index_redflag_person
ON redflag_person (firstname, alias,lastname,address,birthplace);

- Daniel Xav De Oliveira