添加第二个数据库源后Hibernate序列不存在错误

Question

在我的项目中添加第二个数据源后，我将面临以下错误：

Table 'portal-titan.hibernate_sequence' doesn't exist; error performing isolated work; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: error performing isolated work

当我尝试插入具有类型的对象(包括GenerationType.AUTO. )时，它会出现。我有点困惑，因为有很多关于这个话题的问题和讨论，我试了很多，但我没有得到想要的结果。当我将GenerationType更改为标识时，它就开始工作了，但是我看到这会导致性能问题，这不是期望的结果。更重要的是，我在yml文件中的hibernate属性中有use-new-id-generator-mappings: false，但这也无助于解决问题。

这里是我的yml文件：

management:
  security:
    roles: ADMIN
  context-path: /management

spring:
  messages:
    basename: i18n/messages
  mvc:
    favicon:
      enabled: false
  thymeleaf:
    mode: XHTML
  jpa:
    hibernate:
      ddl-auto: validate
      use-new-id-generator-mappings: false
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQLDialect
        format-sql: true
        physical_naming_strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
        implicit_naming_strategy: org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy
 
caching:
  specs:
    products:
      timeout: 60
    orders:
      timeout: 60
      max: 500
    deliveries:
      timeout: 120
    tracking:
      timeout: 1
    admins:
      timeout: 120
    balance:
      timeout: 120

application:

  async:
    core-pool-size: 2
    max-pool-size: 50
    queue-capacity: 1000

  jwt:
    token-secret: secret-key
    token-validity: PT6H
    token-remember-me-validity: P7D

  default-language-tag: bg

  upload:
    allowed-content-types:
      - image/jpg
      - image/jpeg
      - image/png

  static-resource:
    path: /static/

  jobs:
    batch-size: 20

activity:
  purge:
    ttl-value: 90
    ttl-unit: days
    job-run-interval-value: 1
    job-run-interval-unit: days

下面是现在要插入的实体的外观：

@Getter
@Setter
@Entity
@Table(name = "comments")
public class Comment implements Serializable {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Integer id;

  @Column(nullable = false, unique = true)
  private String uuid;

  @Column(nullable = false)
  private String content;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "delivery_id")
  private Delivery delivery;

  @CreatedDate
  @Column(name = "created_at", nullable = false)
  private Instant createdAt = Instant.now();

  @LastModifiedDate
  @Column(name = "updated_at", nullable = false)
  private Instant updatedAt = Instant.now();
}

这是在控制器部分中插入的方法：

  @PostMapping("/{deliveryUuid}")
  @ApiOperation(value = "Submit a comment")
  @ApiResponses(
      value = {
        @ApiResponse(code = 201, message = "Comment created"),
        @ApiResponse(code = 400, message = "Validation failed")
      })
  @PreAuthorize("hasRole('ROLE_CUSTOMER')")
  @ResponseStatus(value = HttpStatus.CREATED)
  public void submitComment(
      @PathVariable("deliveryUuid") String deliveryUuid,
      @Valid @RequestBody CommentDto commentDto,
      @CurrentUser AuthUser principal) {
    commentService.submitComment(commentDto, deliveryUuid, principal);
  }

因为在我配置了第二个数据库之后将出现错误，所以我也在添加它们的代码。注释实体位于主数据库中。

初级

@Configuration
@EnableTransactionManagement
@EnableJpaAuditing
@EntityScan(basePackageClasses = {TitanClientApp.class})
@EnableJpaRepositories(
        entityManagerFactoryRef = "clientEntityManagerFactory",
        transactionManagerRef = "clientTransactionManager",
        basePackages = { "titan.client" }
)
public class DbConfiguration {

    @Primary
    @Bean(name="clientDataSource")
    @ConfigurationProperties(prefix="spring.datasource.primary")
    public DataSource clientDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "clientEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean clientEntityManagerFactory(EntityManagerFactoryBuilder builder,
                                                                              @Qualifier("clientDataSource") DataSource clientDataSource) {
        return builder
                .dataSource(clientDataSource)
                .packages("titan.client")
                .build();
    }

    @Primary
    @Bean(name = "clientTransactionManager")
    public PlatformTransactionManager clientTransactionManager(
            @Qualifier("clientEntityManagerFactory") EntityManagerFactory clientEntityManagerFactory) {
        return new JpaTransactionManager(clientEntityManagerFactory);
    }
}

二级

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "gpsEntityManagerFactory",
        transactionManagerRef = "gpsTransactionManager",
        basePackages = {"titan.gps"}
)
public class SecondaryDbConfiguration {

    @Bean(name = "gpsDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource gpsDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "gpsEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean gpsEntityManagerFactory(EntityManagerFactoryBuilder builder,
                                                                          @Qualifier("gpsDataSource") DataSource gpsDataSource) {
        return builder
                .dataSource(gpsDataSource)
                .packages("titan.gps")
                .build();
    }

    @Bean(name = "gpsTransactionManager")
    public PlatformTransactionManager gpsTransactionManager(
            @Qualifier("gpsEntityManagerFactory") EntityManagerFactory gpsEntityManagerFactory) {
        return new JpaTransactionManager(gpsEntityManagerFactory);
    }
}

Answer 1

第二个数据库只是缺少一个Hibernate需要正确工作的表。如果要使用基于表的序列，则必须创建该表，这在某种程度上是默认的。

使用IDENTITY是完全可以的，但是只要您不每秒插入数千条记录。