使用postgres表序列而不是共享hibernate_sequence [英] Use postgres table sequence instead of sharing 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序列课.
演示仓库在这里: https://github.com/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: https://github.com/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.
这篇关于使用postgres表序列而不是共享hibernate_sequence的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
