Sequence Generator using Generator Name rather than Sequence name

Question

`

ENTITIES:
@Entity
@Table(name = "ORDER_ITEM")
@Getter
@Setter
@NoArgsConstructor
public class Order {
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="ORDER_GEN")
    @SequenceGenerator(sequenceName = "ORDER_ID_SEQ", allocationSize = 1, name = "ORDER_GEN")
    @Column(name = "order_id")
    private Long orderId;
    @Column(name = "order_dt")
    private Date orderDt;
    @Column(length = 100, name = "first_name")
    private String firstName;
...
}
@Entity
@Table(name = "ORDER_ITEM")
@Getter
@Setter
@NoArgsConstructor
public class OrderItem {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ORDER_ITEM_ID_Gen")
    @SequenceGenerator(name = "ORDER_ITEM_ID_Gen", sequenceName = "ORDER_ITEM_ID_SEQ", allocationSize = 1)
    @Column(name = "order_Item_id")
    private Long orderItemId;
    @Column(name = "ORDER_ID")
    private Long orderId;
    @Column(length = 200, name = "content_id")
    private String contentId;
    @Column(length = 100, name = "business")
    private String business;
    @Column(length = 100, name = "media_type")
import java.util.Date;

import javax.persistence.SequenceGenerator;

import org.springframework.data.relational.core.mapping.Table;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
@Entity
@Table(name = "EMAN_ORDER_BY_BU")
@Getter
@Setter
@NoArgsConstructor
public class OrderByBu {
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="ORDER_SEQ")
    @SequenceGenerator(sequenceName = "ORDER_ID", allocationSize = 1, name = "ORDER_SEQ")
    private Long orderBuId;
    @Column(name="orderId")
    private Long orderId;
    @Column(length=100, name="business")
    private String business;
    private Date createDt;
    @Column(length=100, name="status")
    private String status;
....
}`

Service Object

@Transactional
        public Order saveOrder(Order order) {
            log.info("saving order");
            try {
                Order savedOrder = OrderRepository.saveAndFlush(order);
                savedOrder.setOrderbyBus(saveOrderByBu(order));
                savedOrder.setOrderItems(saveOrderItem(order));
                log.info("order saved");
                return savedOrder;
            } catch (Exception e) {
                log.error("Error while saving order: {}", e.getMessage());
                throw new RuntimeException("Error while saving order", e);
            }
        }

Repositories

public interface OrderRepository extends JpaRepository<Order,Long> {...
}
public interface OrderItemsRepository extends JpaRepository<OrderItem,Long> {...
}
public interface setOrderbyBusRepository extends JpaRepository<OrderByBu,Long> {...
}

If I use the same sequence on all 3 entitiy it works fine

@Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="ORDER_GEN")
    @SequenceGenerator(sequenceName = "ORDER_ID_SEQ", allocationSize = 1, name = "ORDER_GEN")

When I change it to use a different sequence I get this error

2023-07-28 15:31:10 DEBUG org.hibernate.SQL - select order_item_id_gen.nextval from dual
2023-07-28 15:31:10 ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-02289: sequence does not exist

So I tried using the Sequence name as the generator name

@Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ORDER_ITEM_ID_SEQ")
    @SequenceGenerator(name = "ORDER_ITEM_ID_SEQ", sequenceName = "ORDER_ITEM_ID_SEQ", allocationSize = 1)
    @Column(name = "order_Item_id")
    private Long orderItemId;

Now I get this error when booting up Spring boot

Caused by: org.hibernate.MappingException: The increment size of the [EMAN_ORDER_ITEM_ID_SEQ] sequence is set to [50] in the entity mapping while the associated database sequence increment size is [1].
    at org.hibernate.id.enhanced.SequenceStyleGenerator.configure(SequenceStyleGenerator.java:218) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
    at org.hibernate.id.factory.internal.StandardIdentifierGeneratorFactory.createIdentifierGenerator(StandardIdentifierGeneratorFactory.java:217) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]
    ... 43 common frames omitted

allocationSize = 1)

I expect to be able to use 3 different sequences I can use any Sequence I want as long as they are all the same I am using Springboot 3.0.8 JAVA 17

Answer 1

Few wrong things there:

• Sequence Does Not Exist – meaning that the ORDER_ITEM_ID_SEQ you're trying to use does not exist, please ensure it's really created at this step
• Mismatch between allocation size and increment size – it seems that the increment size set in your @SequenceGenerator (allocationSize) and the actual increment size in the database are different, please ensure they match. If the increment is 1 in the database, the allocationSize should be 1 in your annotation too