spring-data-jpa报错，‘hibernate_sequence‘ doesn‘t exist

╰半夏微凉° 2022-10-15 14:59 155阅读 0赞

学习spring-data-jpa的时候，写了一个entity

import javax.persistence.Entity;
    import javax.persistence.GeneratedValue;
    import javax.persistence.GenerationType;
    import javax.persistence.Id;
    
    @Entity
    public class User {
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      private Long id;
      private String name;
      private String email;
    
      public Long getId() {
        return id;
      }
    
      public void setId(Long id) {
        this.id = id;
      }
    
      public String getName() {
        return name;
      }
    
      public void setName(String name) {
        this.name = name;
      }
    
      public String getEmail() {
        return email;
      }
    
      public void setEmail(String email) {
        this.email = email;
      }
    }

对应的UserRepository如下

import org.springframework.data.repository.CrudRepository;
    
    public interface UserRepository extends CrudRepository<User, Long> {
    
    }

测试的UserController如下

import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.ResponseBody;
    
    @Controller
    @RequestMapping("/demo")
    public class UserController {
    
      private final UserRepository userRepository;
    
      public UserController(UserRepository userRepository) {
        this.userRepository = userRepository;
      }
    
      @GetMapping("/add")
      public void addUser(@RequestParam String name, @RequestParam String email) {
        User user = new User();
        user.setName(name);
        user.setEmail(email);
        userRepository.save(user);
      }
    
      @GetMapping("/all")
      @ResponseBody
      public Iterable<User> getAllUsers() {
        return userRepository.findAll();
      }
    
    }

建表语句如下

CREATE TABLE `user` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` varchar(50) DEFAULT NULl,
     `email` varchar(200) DEFAULT NULL,
     PRIMARY KEY (`id`)
    );

在调用 /add方法的时候报错

java.sql.SQLSyntaxErrorException: Table 'spring-data-jpa-learning.hibernate_sequence' doesn't exist
    	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.21.jar:8.0.21]
    	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.21.jar:8.0.21]
    	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.21.jar:8.0.21]
    	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.21.jar:8.0.21]
    	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003) ~[mysql-connector-java-8.0.21.jar:8.0.21]
    	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.4.5.jar:na]
    	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.5.jar:na]
    	at org.hibernate.id.enhanced.TableStructure.executeQuery(TableStructure.java:216) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.id.enhanced.TableStructure.access$300(TableStructure.java:46) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.id.enhanced.TableStructure$1$1.execute(TableStructure.java:138) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.id.enhanced.TableStructure$1$1.execute(TableStructure.java:126) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.jdbc.WorkExecutor.executeReturningWork(WorkExecutor.java:55) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.jdbc.AbstractReturningWork.accept(AbstractReturningWork.java:34) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcIsolationDelegate.delegateWork(JdbcIsolationDelegate.java:57) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.id.enhanced.TableStructure$1.getNextValue(TableStructure.java:125) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.id.enhanced.NoopOptimizer.generate(NoopOptimizer.java:40) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.id.enhanced.SequenceStyleGenerator.generate(SequenceStyleGenerator.java:523) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:115) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:185) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:128) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:55) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:718) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:704) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
    	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    	at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
    	at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:366) ~[spring-orm-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    	at com.sun.proxy.$Proxy77.persist(Unknown Source) ~[na:na]
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
    	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    	at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
    	at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:314) ~[spring-orm-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    	at com.sun.proxy.$Proxy77.persist(Unknown Source) ~[na:na]
    	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.save(SimpleJpaRepository.java:554) ~[spring-data-jpa-2.2.9.RELEASE.jar:2.2.9.RELEASE]
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
    	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    	at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
    	at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:371) ~[spring-data-commons-2.2.9.RELEASE.jar:2.2.9.RELEASE]
    	at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:204) ~[spring-data-commons-2.2.9.RELEASE.jar:2.2.9.RELEASE]
    	at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:657) ~[spring-data-commons-2.2.9.RELEASE.jar:2.2.9.RELEASE]

实际上，需要将User类中的，Id的注解修改为如下

@GeneratedValue(strategy = GenerationType.IDENTITY)

原因如下：

@GeneratedValue:主键的产生策略，通过strategy属性指定。   
1、AUTO自动选择一个最适合底层数据库的主键生成策略。这个是默认选项，即如果只写@GeneratedValue，等价于@GeneratedValue(strategy=GenerationType.AUTO)。  该策略为主键序列化,而mysql是不支持的 ，oracle支持的。所以才会报错。

2、IDENTITY　表自增长字段，Mysql支持的 auto increment，所以修改为此。Oracle不支持这种方式。

3、SEQUENCE　通过序列产生主键，MySQL不支持这种方式。

4、TABLE　通过表产生主键，框架借由表模拟序列产生主键，使用该策略可以使应用更易于数据库移植。不同的JPA的实现生成的表名是不同的，Hibernate生成一个hibernate\_sequences表。