Postgres序列非空约束

Question

我有以下数据库表：

CREATE TABLE company
(
  id serial NOT NULL,
  name character varying(50),
  activestatus boolean,
  address bigint,
  remarks character varying(1024),
  revision integer
)

CREATE TABLE company_version
(
  id serial NOT NULL,
  company_id integer,
  name character varying(50),
  revision serial NOT NULL,
  revision_previous integer,
  revision_timestamp timestamp without time zone,
  revision_comment character varying(100),
  revision_userid character varying(50)
)

在公司表上，我有一个触发器，它会在插入后执行。

INSERT INTO company_version (company_id,name,revision_previous,revision_timestamp,revision_comment,revision_userid)
                                VALUES (NEW.id, NEW.name, (SELECT MAX(revision) FROM company_version),CURRENT_TIMESTAMP,'Inserted in application','The application')
                                RETURNING revision into revision_param;
            UPDATE company SET revision = revision_param WHERE id = NEW.id;

当我插入一个新的公司时，company_version表会像预期一样被添加。但是当我在Java中创建一个新的CompanyVersion对象，并通过JPA持久化它时，在“revision”列上它会给我一个非空约束错误。

org.postgresql.util.PSQLException: ERROR: null value in column "revision" violates not-null constraint

这是我尝试执行的代码：

final CompanyVersion cv = new CompanyVersion();
cv.setCompanyId(c);
cv.setName(c.getName());

cv.setRevisionTimestamp(new Date());
cv.setRevisionComment("Updated in application");
cv.setRevisionUserid("The application");
cv.setRevisionPrevious(this.findLatestVersion(c));

this.persist(cv);

这是我的 CompanyVersion 对象：

public class CompanyVersion {

    private static final long serialVersionUID = 1L;
    @Id
    @SequenceGenerator(name = "company_version_id_seq", sequenceName = "company_version_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "company_version_id_seq")
    @Basic(optional = false)
    @Column(name = "id")
    private Integer id;
    @Size(max = 50)
    @Column(name = "name")
    private String name;
    @OneToMany(mappedBy = "revisionPrevious", fetch = FetchType.LAZY)
    private List<CompanyVersion> previousRevisionList;
    @JoinColumn(name = "revision_previous", referencedColumnName = "revision")
    @ManyToOne(fetch = FetchType.LAZY)
    private CompanyVersion revisionPrevious;
    @JoinColumn(name = "company_id", referencedColumnName = "id")
    @ManyToOne(fetch = FetchType.LAZY)
    private Company companyId;
    @Column(name = "revision_timestamp")
    @Temporal(TemporalType.TIMESTAMP)
    private Date revisionTimestamp;
    @Size(max = 100)
    @Column(name = "revision_comment")
    private String revisionComment;
    @Size(max = 50)
    @Column(name = "revision_userid")
    private String revisionUserid;
    @Basic(optional = false)
    @Column(name = "revision")
    protected Integer revision;

    public CompanyVersion() {
    }

    public CompanyVersion(Integer id) {
        this.id = id;
    }

    public CompanyVersion(Integer id, int revision) {
        this.id = id;
        this.revision = revision;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<CompanyVersion> getPreviousRevisionList() {
        return previousRevisionList;
    }

    public void setPreviousRevisionList(List<CompanyVersion> previousRevisionList) {
        this.previousRevisionList = previousRevisionList;
    }

    public CompanyVersion getRevisionPrevious() {
        return revisionPrevious;
    }

    public void setRevisionPrevious(CompanyVersion revisionPrevious) {
        this.revisionPrevious = revisionPrevious;
    }

    public Company getCompanyId() {
        return companyId;
    }

    public void setCompanyId(Company companyId) {
        this.companyId = companyId;
    }

    public Date getRevisionTimestamp() {
        return revisionTimestamp;
    }

    public void setRevisionTimestamp(Date revisionTimestamp) {
        this.revisionTimestamp = revisionTimestamp;
    }

    public String getRevisionComment() {
        return revisionComment;
    }

    public void setRevisionComment(String revisionComment) {
        this.revisionComment = revisionComment;
    }

    public String getRevisionUserid() {
        return revisionUserid;
    }

    public void setRevisionUserid(String revisionUserid) {
        this.revisionUserid = revisionUserid;
    }

    public Integer getRevision() {
        return revision;
    }

    public void setRevision(Integer revision) {
        this.revision = revision;
    }
}

我已经尝试在修订版上添加@SequenceGenerator和@GeneratedValue注释，但迄今为止没有成功（由于某种原因，@GeneratedValue不允许在2个字段上）。

我认为我不应该选择最新的修订版本，添加一个并在我的后端bean中执行cv.setRevision()方法。但到目前为止，我认为这是唯一的解决方案。

那么，如何使revision字段自动插入？

- Erates

---

你需要找到一种方法来告诉你的混淆层（也称为ORM），通过不在“INSERT”语句中列出它来不提供revision列的任何值。或者，可以告诉它在生成的“insert”语句中使用“DEFAULT”而不是“NULL”。 - user330315

@a_horse_with_no_name 所以在我的情况下，我需要告诉EclipseLink在插入时省略revision字段？ - Erates

Answer 1

我把我的JPA实体类从以下这种形式改变了：

@Basic(optional = false)
@Column(name = "revision")
protected Integer revision;

为了

@Basic(optional = false)
@Column(name = "revision", insertable = false)
protected Integer revision;

做到了！

- Erates

---

使用Postgresql10，insertable = false 对我很有效。 - LMK IND