How to implement auto-increment field other than id field?

Question

I want to implement one auto increment field other than id field that starts with 1 and increase by 1 sequentially.

Code Sample :

@Id
@GeneratedValue(generator = "uuid")
@GenericGenerator(name = "uuid", strategy = "uuid2")
private String id; //Id primary key

@Column(name = "request_number", nullable = false, unique = true, updatable = false, insertable = false)
@GeneratedValue(generator = "sequence", strategy = GenerationType.AUTO)
private Long requestNumber; //Talking about this

So, here requestNumber should increase automatically every time when ever object create. And that should increase sequentially.

Example : First entry's requestNumber will start with 1 and next requestNumber will be assign with 2 and so on...

I know it is possible via java code but I am looking for JPA provide such flexibility.

Answer 1

IDK this solution is right or wrong but I dug up some code and implemented this. It seems to work correctly. Performed on Mysql.

Requirement:

Required a separate column other than Id that have unique value and would auto insert and should insert value on INSERT (not on UPDATE).

Context:

1. Lets say I have an Entity name Bug with code as follows:

public class Bug extends AbstractAuditingMappedEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    
    // want 👇 column as auto increment non id column
    
    @Column(unique = true, nullable = false, updatable = false)
    private Integer bugKey;
    
    // ... other things

}

To achieve Result I have done this:

Some Observations:

1. Generating random unique auto insert values are hard to generate.
2. So, only consistent way to generate is to make use of Primary key Id Generation strategy.
3. And from primary key values we can use any Injective Function f(x) = y to generate unique values.

Procedure:

1. Make a Custom Generator that extracts max value of Ids Inserted till now and with that we can use any f(x), I have used f(x) = x;

public class BugKeyValueGenerator implements BeforeExecutionGenerator {
    
    private IntegralDataTypeHolder previousValueHolder; 

    @Override
    public synchronized Object generate(SharedSessionContractImplementor session, Object obj, Object idk, EventType eventType) {

        String sql = String.format("select max( %s ) as id from %s",
                session.getEntityPersister(obj.getClass().getName(), obj)
                        .getIdentifierPropertyName(),
                obj.getClass().getSimpleName());
        
        // 👇 according to your Id type.
        previousValueHolder = new IdentifierGeneratorHelper.BasicHolder(Integer.class);

        try {
            PreparedStatement st = session.getJdbcCoordinator().getStatementPreparer().prepareStatement(sql);
            try {
                ResultSet rs = session.getJdbcCoordinator().getResultSetReturn().extract(st);
                try {
                    if (rs.next()) {
                        previousValueHolder.initialize(rs, 0L).increment();
                    } else {
                        previousValueHolder.initialize(1L);
                    }
                    sql = null;
                } finally {
                    session.getJdbcCoordinator().getLogicalConnection().getResourceRegistry().release(rs, st);
                }
            } finally {
                session.getJdbcCoordinator().getLogicalConnection().getResourceRegistry().release(st);
                session.getJdbcCoordinator().afterStatementExecution();
            }
        } catch (SQLException sqle) {
            throw session.getJdbcServices().getSqlExceptionHelper().convert(
                    sqle,
                    "could not fetch initial value for increment generator",
                    sql
            );
        }
        
        // you can use any `Injective function` 👇.
        // eg
        // `f(x) = x` => to get same values as Id
        // `f(x) = "some string" + x` => to get values as BDK1, BDK2 ... etc...
        // `f(x) = 1000+x` => to get values as 1001, 1002 ... etc...
        return previousValueHolder.makeValueThenIncrement();
    }

@Override
    public EnumSet<EventType> getEventTypes() {
        return INSERT_ONLY;
    }

}

2. Make a custom annotation for this.

@ValueGenerationType(generatedBy = BugKeyValueGenerator.class)
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD, ElementType.FIELD})
public @interface BugKeyGenerator {
}

3. Finally Update you Entity class by using this annotation.

public class Bug extends AbstractAuditingMappedEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    
    // update 👇 column with annotation
    
    @Column(unique = true, nullable = false, updatable = false)
    @BugKeyGenerator
    private Integer bugKey;
    
    // ... other things

}

Findings:

1. Can only be used when primary keys are of any number type.
2. Can be used with string type primary keys when you will not delete any record form table. In that case instead of finding max in sql query you can use count to generate new unique number.
3. You can also use this to auto insert values based on other value as 1 of the parameter (obj) received have values for record which is to be inserted.

p.s. My first answer on stackoverflow

Answer 2

@GeneratedValue is used only for simple primary keys, as per the javadoc:

The GeneratedValue annotation may be applied to a primary key property or field of an entity or mapped superclass in conjunction with the Id annotation. The use of the GeneratedValue annotation is only required to be supported for simple primary keys. Use of the GeneratedValue annotation is not supported for derived primary keys.

If you want to do it in JPA you can define a @PrePersist method like:

@PrePersist
void doPrePersist() {
    // Use EntityManager to create a native query
    // read next value from a sequence
    // set the field value
}

Another option would be to define the database column as IDENTITY but this will take care of auto incrementing outside of JPA e.g. the entity field won't be insertable and value won't be seen during entity persist operation.

Please note that SQL Server, and most databases, doesn't guarantee that there won't be gaps in the sequence. When a transaction that increments sequence is rolled back the value of the sequence is not, so you can end up with: 1, 2, 4, 5, 6, 10, ...

Answer 3

You have to declare a @SequenceGenerator annotation on your class (or field):

@SequenceGenerator(sequenceName = "MY_DB_SEQUENCE", name = "sequence")
public class MyClass {
    // keep as is
}

Note that the generator = "sequence" on @GeneratedValue points to the @SequenceGenerator with the name = "sequence"