spring - Can I set min/max value of database sequence from Entity?

Question

Is there a way where I can the sequence max and min value from the entity itself

question from:https://stackoverflow.com/questions/65842189/can-i-set-min-max-value-of-database-sequence-from-entity

Answer 1

I don't think that it is possible to create a sequence with specific MINVALUE or MAXVALUE by using JPA or Hibernate.

The JPA specific annotation for this task is the SequenceGenerator which does not accept the MINVALUE or the MAXVALUE. It optionally accepts the initialValue which affects the "STARTS WITH" value of the sequence and probably implicitly sets the MINVALUE or the MAXVALUE.

For example the Oracle database says regarding the CREATE SEQUENCE statement:

START WITH

Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits.

There is the hibernate specific API GenericGenerator and SequenceStyleGenerator which can be used to create a sequence (see an example) but they too seem not to accept MINVLAUE or MAXVALUE as parameters.

Finally, a quick look into Hibernate's code for Oracle DB suggests that indeed the MINVALUE AND MAXVALUE is set implicitly based on the provided initialValue as the Oracle documentation above suggests. For example here is the relevant code:

protected String getCreateSequenceString(String sequenceName, int initialValue, int incrementSize) {
        if ( initialValue < 0 && incrementSize > 0 ) {
            return
                String.format(
                        "%s minvalue %d start with %d increment by %d",
                        getCreateSequenceString( sequenceName ),
                        initialValue,
                        initialValue,
                        incrementSize
                );
        }
        else if ( initialValue > 0 && incrementSize < 0 ) {
            return
                String.format(
                        "%s maxvalue %d start with %d increment by %d",
                        getCreateSequenceString( sequenceName ),
                        initialValue,
                        initialValue,
                        incrementSize
                );
        }
        else {
            return
                String.format(
                        "%s start with %d increment by  %d",
                        getCreateSequenceString( sequenceName ),
                        initialValue,
                        incrementSize
                );
        }
    }

I assume that this should be the case for other databases except Oracle that support sequences too.