java - ORA_02201 此处不允许序列

问题描述

希望你们一切都好。当我尝试使用 JSON 将新条目发布到数据库时，我不断收到“ORA_02201 - 此处不允许序列”SQL 语法错误。我正在使用 Oracle 自治数据库在 Oracle 数据库中，人们使用 GenerationType.SEQUENCE 策略来增加 ID（主键）。

之前有尝试过，分享给大家。问题的根源似乎是 Sequence 无法与 JSON 一起使用，或者没有设置为可以使用。当我的 JSON 请求没有 id 时，它会给出“此处不允许序列”，因此当序列应该工作时。请注意，我将此应用程序作为 MVC Web 应用程序，通过从 jsp 页面中的表单输入获取数据，我将实体保存到数据库中。它可以完美运行并正确生成 ID。我相信问题出在 JSON 上。

{
    "firstName" : "Mark",
    "lastName" : "Heiberg",
    "email" : "heibergmark@dot.com"
}

我通过在 JSON 中指定数据库中存在的 ID 来发出请求，然后它更新了条目。所以我确信原因的根源是 id 生成，当它应该创建新的 id 时，它不起作用。我在 Internet 上找不到任何关于 JSON-Oracle DB 关系的信息。我想在这里问一下，也许你知道我可以或应该做些什么来在 JSON 请求的那一刻启用对序列生成类型的支持。提前致谢。

@PostMapping("/customers")
    Customer addCustomer(@RequestBody Customer customer){
        customer.setId(0); //set id to 0 to force it to  add customer to DB, instead of updating current.
        customerService.saveCustomer(customer);
        return customer;
    }

这是表定义 SQL：（我使用 Oracle APEX 环境构建它，不要介意 9999999999999999，默认情况下）

CREATE TABLE  "CUSTOMER" 
   (    "ID" NUMBER(3,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "FIRST_NAME" VARCHAR2(15) COLLATE "USING_NLS_COMP", 
    "LAST_NAME" VARCHAR2(15) COLLATE "USING_NLS_COMP", 
    "EMAIL" VARCHAR2(30) COLLATE "USING_NLS_COMP", 
     CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   )  DEFAULT COLLATION "USING_NLS_COMP"
/

CREATE OR REPLACE EDITIONABLE TRIGGER  "BI_CUSTOMER" 
  before insert on "CUSTOMER"               
  for each row  
begin   
  if :NEW."ID" is null then 
    select "CUSTOMER_SEQ".nextval into :NEW."ID" from sys.dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_CUSTOMER" ENABLE
/

这是我实现数据库注释的实体类：

package com.customer_tracker.entity;

import javax.persistence.*;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;

@Entity
@Table(name = "CUSTOMER")
public class Customer {

    public Customer() {
    }

    public Customer(String firstName, String lastName, String email) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "Customer_SEQ")
    @SequenceGenerator(name = "Customer_SEQ", sequenceName = "CUSTOMER_SEQ", allocationSize = 1)
    @Column(name = "ID")
    private int id;

    @NotNull(message = "This field is required!")
    @Size(min = 3, max = 15, message = "Write something!")
    @Column(name = "FIRST_NAME")
    private String firstName;

    @NotNull(message = "This field is required!")
    @Size(min = 3, max = 15, message = "Write something!")
    @Column(name = "LAST_NAME")
    private String lastName;

    @NotNull(message = "This field is required!")
    @Size(min = 3, max = 30, message = "Write something!")
    @Column(name = "EMAIL")
    private String email;

    public int getId() {
        return id;
    }

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

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName.trim().replaceAll("\\s", "");
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName.trim().replaceAll("\\s", "");
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email.trim().replaceAll("\\s", "");
    }

    @Override
    public String toString() {
        return "id = " + id +
                ", firstName = " + firstName + ", lastName = " + lastName + ", email = " + email;
    }
}

这是 Hibernate 将条目保存到数据库的代码片段：

package com.customer_tracker.dao;

import com.customer_tracker.entity.Customer;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.query.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class CustomerList implements CustomerDAO{

    @Autowired
    private SessionFactory sessionFactory;

    @Override
    public void saveCustomer(Customer customer) {
        Session session = sessionFactory.getCurrentSession();
        session.saveOrUpdate(customer);
    }

}

... GET, PUT,DELETE方法按需要工作，只是POST因为数据库应该在幕后生成 id 才不起作用。

标签： javajsonspringoracle

java - ORA_02201 此处不允许序列

问题描述

解决方案

推荐阅读