使用postgres表序列而不是共享hibernate

本文介绍了使用postgres表序列而不是共享hibernate_sequence的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

当我对表格执行任何操作时，它总是显示错误:

Hibernate: select nextval ('hibernate_sequence') 2019-07-20 16:15:44.877 WARN 58376 --- [nio-9000-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42P01 2019-07-20 16:15:44.877 ERROR 58376 --- [nio-9000-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: relation "hibernate_sequence" does not exist

我不想使用hibernate_sequence在表之间共享ID序列，但想要为每个表定义ID seq并分别使用它们.

我使用Spring Boot 2.1.6.RELEASE，Spring Data JPA(Hibernate 5.3.10.Final)和Postgres 11.2，并使用BigSerial类型定义id字段，并希望在各自的实体中使用每个表的id序列课.

演示仓库在这里: github/Redogame/share_hibernate_sequence 创建用户表(将身份用作表名，因为用户是Postgres保留关键字). 通过使用bigserial类型定义id，Postgres将自动创建一个identity_id_seq，并且我验证了identity_id_seq已成功创建.

create table identity ( id bigserial not null constraint identity_pkey primary key, name varchar(255) not null constraint identity_name_key unique constraint identity_name_check check ((name)::text <> ''::text), created_date timestamp not null, created_by_id bigint not null constraint identity_identity_id_fk references identity, last_modified_date timestamp not null, last_modified_by_id bigint not null constraint identity_identity_id_fk_2 references identity, version bigint not null );

指定一个序列生成器以使用此id序列:

@Table(name = "identity") public class UserEntity extends Auditable<Long> { @Id @SequenceGenerator(name="identity_id_seq", sequenceName = "identity_id_seq", initialValue=1, allocationSize=1) @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="identity_id_seq") private Long id;

但是它不起作用.我也尝试配置spring.jpa.hibernate.use-new-id-generator-mappings和spring.jpa.properties.hibernate.id.new_generator_mappings，但仍然无法正常工作.

spring: jpa: hibernate: use-new-id-generator-mappings: false properties: hibernate: id: new_generator_mappings: false

我希望不要使用hibernate_sequence，即:不要在任何SQL语句之前/之后执行select nextval('hibernate_sequence').

解决方案

尝试以下步骤

如果不存在，则创建序列manual_seq;

更改创建表脚本

create table identity ( id integer NOT NULL DEFAULT nextval('manual_seq'::regclass), name varchar(255) not null constraint identity_name_key unique constraint identity_name_check check ((name)::text <> ''::text), created_date timestamp not null, created_by_id bigint not null, last_modified_date timestamp not null, last_modified_by_id bigint not null, version bigint not null, CONSTRAINT manual_seq_pkey PRIMARY KEY (id) );

出于测试目的，我删除了外键约束.

更新实体映射

@Entity @Table(name = "identity") @JsonIgnoreProperties(ignoreUnknown = true) public class UserEntity extends Auditable<Long> { @Id @SequenceGenerator(name="manual-seq", sequenceName = "manual_seq",allocationSize = 1) @GeneratedValue(generator="manual-seq") private Long id; @Basic @Column(name = "name", nullable = false) private String name;

@MappedSuperclass @JsonIgnoreProperties({"new", "createdDate", "createdById", "lastModifiedDate", "lastModifiedById", "version"}) abstract class Auditable<PK extends Serializable>{ @NotAudited @CreatedDate @Temporal(TemporalType.TIMESTAMP) private Date createdDate; @NotAudited @CreatedBy private Long createdById; @LastModifiedDate @Temporal(TemporalType.TIMESTAMP) private Date lastModifiedDate; @LastModifiedBy private Long lastModifiedById; @NotAudited @Version private Long version;

还原spring.jpa.hibernate.use-new-id-generator-mappings

该问题正在扩展 AbstractPersistable ，因为未使用哪个数据库序列.另外，请注意，出于测试目的，我已删除了审核.

When I do anything with a table, it always show the error:

Hibernate: select nextval ('hibernate_sequence') 2019-07-20 16:15:44.877 WARN 58376 --- [nio-9000-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42P01 2019-07-20 16:15:44.877 ERROR 58376 --- [nio-9000-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: relation "hibernate_sequence" does not exist

I DON'T want to use hibernate_sequence to share id sequence between tables, but want to define id seq for each table and use them respectively.

I use Spring Boot 2.1.6.RELEASE, Spring Data JPA (Hibernate 5.3.10.Final), and Postgres 11.2, and define the id field with BigSerial type and hope to use the id sequence of each table in respective entity class.

The demo repo is here: github/Redogame/share_hibernate_sequence

Create user table (use identity as table name because user is a Postgres reserved keyword). By defining id with bigserial type, Postgres will create a identity_id_seq automatically, and I verified that identity_id_seq has been created successfully.

create table identity ( id bigserial not null constraint identity_pkey primary key, name varchar(255) not null constraint identity_name_key unique constraint identity_name_check check ((name)::text <> ''::text), created_date timestamp not null, created_by_id bigint not null constraint identity_identity_id_fk references identity, last_modified_date timestamp not null, last_modified_by_id bigint not null constraint identity_identity_id_fk_2 references identity, version bigint not null );

Specify a sequence generator to use this id sequence:

@Table(name = "identity") public class UserEntity extends Auditable<Long> { @Id @SequenceGenerator(name="identity_id_seq", sequenceName = "identity_id_seq", initialValue=1, allocationSize=1) @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="identity_id_seq") private Long id;

But it doesn't work. I also tried to config spring.jpa.hibernate.use-new-id-generator-mappings and spring.jpa.properties.hibernate.id.new_generator_mappings, but still not work.

spring: jpa: hibernate: use-new-id-generator-mappings: false properties: hibernate: id: new_generator_mappings: false

I expect not to use hibernate_sequence, that is: don't execute select nextval ('hibernate_sequence') before/after any SQL statement.

解决方案

Try the below steps

CREATE SEQUENCE IF NOT EXISTS manual_seq;

Change create table script

create table identity ( id integer NOT NULL DEFAULT nextval('manual_seq'::regclass), name varchar(255) not null constraint identity_name_key unique constraint identity_name_check check ((name)::text <> ''::text), created_date timestamp not null, created_by_id bigint not null, last_modified_date timestamp not null, last_modified_by_id bigint not null, version bigint not null, CONSTRAINT manual_seq_pkey PRIMARY KEY (id) );

I removed the foreign key constraint for testing purposes.

Update the entity mapping

@Entity @Table(name = "identity") @JsonIgnoreProperties(ignoreUnknown = true) public class UserEntity extends Auditable<Long> { @Id @SequenceGenerator(name="manual-seq", sequenceName = "manual_seq",allocationSize = 1) @GeneratedValue(generator="manual-seq") private Long id; @Basic @Column(name = "name", nullable = false) private String name;

@MappedSuperclass @JsonIgnoreProperties({"new", "createdDate", "createdById", "lastModifiedDate", "lastModifiedById", "version"}) abstract class Auditable<PK extends Serializable>{ @NotAudited @CreatedDate @Temporal(TemporalType.TIMESTAMP) private Date createdDate; @NotAudited @CreatedBy private Long createdById; @LastModifiedDate @Temporal(TemporalType.TIMESTAMP) private Date lastModifiedDate; @LastModifiedBy private Long lastModifiedById; @NotAudited @Version private Long version;

Revert the spring.jpa.hibernate.use-new-id-generator-mappings

The issue was extending AbstractPersistable because of which database sequence was not getting used. Also, note I have removed the audit for testing purposes.