解決springboot的JPA在Mysql8新增記錄失敗的問題

更新時(shí)間：2021年06月09日 14:53:34 作者：程序員欣宸

這篇文章主要介紹了解決springboot的JPA在Mysql8新增記錄失敗的問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

springboot的JPA在Mysql8新增記錄失敗

springboot版本是1.3.0.M1，連接的mysql版本為8，用spring-boot-starter-data-jpa操作數(shù)據(jù)庫(kù)，新增記錄時(shí)應(yīng)用拋出以下異常：

2018-02-21 12:52:59.471 DEBUG 6408 --- [nio-9090-exec-1] o.s.web.servlet.DispatcherServlet        : Could not complete request
org.springframework.orm.jpa.JpaSystemException: could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statement
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:310)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:221)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:121)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
	at com.sun.proxy.$Proxy74.save(Unknown Source)
	at com.business.backend.dao.impl.BaseStockDaoImpl.save(BaseStockDaoImpl.java:24)
	at com.business.backend.controller.BaseStockController.savePost(BaseStockController.java:78)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:111)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:799)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:728)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:969)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:871)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:845)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:85)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:668)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1521)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1478)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:211)
	at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:96)
	at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:58)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3032)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3558)
	at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:98)
	at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:492)
	at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:197)
	at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:181)
	at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:216)
	at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:334)
	at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:289)
	at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:195)
	at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:126)
	at org.hibernate.jpa.event.internal.core.JpaPersistEventListener.saveWithGeneratedId(JpaPersistEventListener.java:84)
	at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:206)
	at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:149)
	at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:75)
	at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:811)
	at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:784)
	at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:789)
	at org.hibernate.jpa.spi.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:1181)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:344)
	at com.sun.proxy.$Proxy64.persist(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:291)
	at com.sun.proxy.$Proxy64.persist(Unknown Source)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.save(SimpleJpaRepository.java:439)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:452)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:437)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:409)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
	... 54 common frames omitted
Caused by: java.sql.SQLException: Could not retrieve transation read-only status server
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:904)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:894)
	at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3613)
	at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3582)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2111)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2081)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2066)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
	... 103 common frames omitted
Caused by: java.sql.SQLException: Unknown system variable 'tx_read_only'
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2531)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2489)
	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446)
	at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3607)
	... 108 common frames omitted

這一句是關(guān)鍵信息：Unknown system variable ‘tx_read_only'，應(yīng)該是mysql-connector-java的jar包的版本和數(shù)據(jù)庫(kù)不匹配導(dǎo)致的；

當(dāng)前的springboot項(xiàng)目是個(gè)maven工程，打開pom.xml文件看一下mysql-connector-java庫(kù)的依賴：

<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
</dependency>

如上所示，pom.xml中沒有指定mysql-connector-java庫(kù)的版本，我們只能用mvn命令查看默認(rèn)版本是多少了，在pom.xml所在目錄下執(zhí)行以下命令，查看所有jar包的版本信息：

mvn dependency:tree

在輸出的信息中看到了mysql-connector-java的版本是5.1.35，如下：

[INFO] +- mysql:mysql-connector-java:jar:5.1.35:compile

5.1.35版本偏高了，我們還是在pom.xml中指定一個(gè)低版本吧，修改mysql-connector-java庫(kù)的依賴配置如下：

<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>5.1.15</version>
</dependency>

如果您用的IDE是IntelliJ IDEA，修改上面的配置可能不會(huì)立即生效，請(qǐng)?jiān)趐om.xml文件上點(diǎn)右鍵，選"Maven"->“Reimport”，如下圖：

再次啟動(dòng)springboot應(yīng)用并新增記錄到mysql，這次一切順利了。

spring data jpa + mysql 上踩過的坑

最近在做一個(gè)小項(xiàng)目，技術(shù)上用的是springboot+jpa(Java Persistence API)

spring data jpa讓我們解脫了DAO層的操作，基本上所有CRUD都可以依賴于它來實(shí)現(xiàn)，他是實(shí)現(xiàn)了orm思想的一套框架。

（1）先讓實(shí)體類和數(shù)據(jù)庫(kù)表對(duì)應(yīng)，再讓實(shí)體類屬性和表里面字段對(duì)應(yīng)

@Entity
@Table(name = "t_user")
public class User {
    @Id
    @GeneratedValue
    private Long id;
    private String nickname;
    private String username;
    private String password;
    getter and setter...
}

（2）不需要直接操作數(shù)據(jù)庫(kù)表，直接操作表對(duì)應(yīng)的實(shí)體類對(duì)象

public interface UserRepository extends JpaRepository<User,Long> {
    User findByUsernameAndPassword(String username, String password);
}

好了，問題就出在這第一步里的實(shí)體類屬性和表里面字段對(duì)應(yīng)

注意這個(gè)注解 @GeneratedValue ，他共有四種取值：（strategy = ***）

AUTO主鍵由程序控制, 是默認(rèn)選項(xiàng) ,不設(shè)置就是這個(gè)
IDENTITY 主鍵由數(shù)據(jù)庫(kù)生成, 采用數(shù)據(jù)庫(kù)自增長(zhǎng), Oracle不支持這種方式
SEQUENCE 通過數(shù)據(jù)庫(kù)的序列產(chǎn)生主鍵, MYSQL 不支持
Table 提供特定的數(shù)據(jù)庫(kù)產(chǎn)生主鍵, 該方式更有利于數(shù)據(jù)庫(kù)的移植

以下是我今天解決問題過程中的一些想法，不一定對(duì)，僅供參考

在對(duì)應(yīng)主鍵字段的屬性上要是只標(biāo)注了@GeneratedValue那就意味著把主鍵的生成策略交給了springboot，它將自動(dòng)的判斷你所使用的數(shù)據(jù)庫(kù)，若是Mysql則設(shè)置成IDENTITY，Oracle則設(shè)置成SEQUENCE。

但我今天是用jpa自動(dòng)生成的數(shù)據(jù)庫(kù)

在這里插入圖片描述

最上面舉的實(shí)體類的例子也正是我編寫實(shí)體類采用的注解方式，下面是我定義的實(shí)體類一共五個(gè)。

在這里插入圖片描述

由于其中兩個(gè)類之間的關(guān)系是多對(duì)多，所以按道理最終生成的表應(yīng)該是六張，也就是五張實(shí)體類對(duì)應(yīng)表和一張中間表。但實(shí)際情況確實(shí)這樣的：

在這里插入圖片描述

沒錯(cuò)，他多了一張表！多了一張貌似相當(dāng)于Oracle中的序列表（我也不太懂Oracle…就是覺得長(zhǎng)的像）這張表只有一個(gè)字段，并且其他幾張表的主鍵自增長(zhǎng)都沒有打開

在這里插入圖片描述

就是指定了你下一個(gè)自動(dòng)生成的主鍵的值，關(guān)鍵是所有表共用這個(gè)序列表，舉個(gè)例子：

A表插入一條數(shù)據(jù)后自動(dòng)生成的id=10，接下來你取B表插入一條數(shù)據(jù)，他生成的id就一定會(huì)接下去（=11）

如果你先在數(shù)據(jù)庫(kù)中手動(dòng)創(chuàng)建幾條數(shù)據(jù)，讓id的值超過序列表中的值

在這里插入圖片描述

然后在項(xiàng)目系統(tǒng)中使用增加數(shù)據(jù)功能時(shí)就會(huì)…

Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry ‘13' for key ‘PRIMARY'

破壞了完整性約束，在這里也就是說表中出現(xiàn)了重復(fù)的主鍵id=13,然后我不干任何事，在系統(tǒng)中再次執(zhí)行我的添加功能：

Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry ‘14' for key ‘PRIMARY'

那個(gè)序列表就會(huì)自增1然后再添加，發(fā)現(xiàn)id=14又已經(jīng)存在了。相信大家已經(jīng)看懂這序列表的機(jī)制了吧，就是說只要我在系統(tǒng)中在執(zhí)行一次添加功能，這條數(shù)據(jù)就能夠被加進(jìn)數(shù)據(jù)庫(kù)里了。

這樣也太麻煩了吧，解決它其實(shí)很簡(jiǎn)單，只要在實(shí)體類中手動(dòng)指定主鍵的生成策略，并且將表中對(duì)應(yīng)主鍵字段的 自動(dòng)遞增打開 就行了，springboot就會(huì)放棄那張序列表而按照你指定的方式去生成主鍵了：

@Entity
@Table(name = "t_user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String nickname;
    private String username;
    private String password;

在這里插入圖片描述