Incrementing a sequence by 1000 each persist of a new Entity

Question

We have some jdbc legacy code we are converting to JPA, we used to get ids from oracle using SELECT business_object_seq.nextval FROM dual; with the sequence specified as CREATE SEQUENCE business_object_seq INCREMENT BY 1000 START WITH X; with X being an integer less than 1000 matching the node id of the system.

ie for node 3, calls to get the next id would be 1003, 2003, 3003, etc

My converted entity is as follows

@Entity
@Table(name="BUSINESS_OBJECT")
@SequenceGenerator(name="BUSINESS_OBJECT_SEQ", allocationSize = 1000)

public class BusinessObject implements Serializable {

    private int id;

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="BUSINESS_OBJECT_SEQ")
    public int getId() {
        return id;
    }

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

But now the Entities(on node 3) are created with ids 3, 4, 5.

Without creating a custom sequence generator ( link -> CustomSequence) is it possible to have SequenceGenerator behave this way?

Cheers Adam

Answer 1

If you specify the allocation size of 1000, you are telling JPA that it gets allocated 1000 numbers to use every time it gets a sequence values. So when it gets a value of 1003, it thinks it can use 1003-2002 before it needs to go to the database to get more values.

Setting the allocation size to 1 will cause it to go to the database each time it needs a number, and while less efficient as it doesn't allow preallocation, it would give you the behavior you want. You then would just need to ensure that each node accesses a sequence that starts at a different initial value.