Sequence Generator without @Id annotation in java with hibernate in Postgres within a range

Question

I have a REST API which has somebody, now I have a range of number (40000 - 99999) which I have to populate at my end and save it to the DB, I won't be getting it in the body of the request. However I still have to persist this number sequentially

e.g.: 40001, 40002, 40003, etc in Postgres and as it's not an @Id field then I am unable to find a way to persist it in DB, is there any way to do this with Java, JPA?

CREATE SEQUENCE public.certificate
    INCREMENT 1
    START 40000
    MINVALUE 40000
    MAXVALUE 99999999
    CACHE 1;

create table CertificateNumber (c_number integer default nextval(‘certificate’));

@Generated(value = GenerationTime.INSERT)
@Column(name = "c_number", insertable = false,updatable = false)
Integer certificateNumber;

 Resolved [org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [certificate_number]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement]
2020-03-06 16:33:13.167  INFO 864 --- [nio-8080-exec-2] i.StatisticalLoggingSessionEventListener : Session Metrics {
    714213 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    3037131 nanoseconds spent preparing 1 JDBC statements;
    18543560 nanoseconds spent executing 1 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    67929213 nanoseconds spent executing 1 flushes (flushing a total of 1 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

In response of the API, what i am getting is also mentioned below.

{
    "apierror": {
        "status": "INTERNAL_SERVER_ERROR",
        "timestamp": "06-03-2020 04:33:13",
        "message": "Unexpected error",
        "debugMessage": "could not execute statement; SQL [n/a]; constraint [certificate_number]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement",
        "subErrors": null
    }
}

Answer 1

I suggest to use triggers in your Database, first of all you create your sequence:

CREATE SEQUENCE certificateNumber_table_seq
INCREMENT 1
START 1; 

then create this function:

CREATE OR REPLACE FUNCTION trigger_function()
RETURNS trigger AS
$BODY$
BEGIN
New.c_number:=nextval('certificateNumber_table_seq');
Return NEW;
END;
$BODY$

and finally your trigger which will execute the function you created

DROP TRIGGER IF EXISTS trigg_auto_increment ON table_name;
CREATE TRIGGER trigg_auto_increment
BEFORE INSERT
ON table_name //the name of your table
FOR EACH ROW
EXECUTE PROCEDURE trigger_function();