Hibernate Sequence get stuck at value 41

Question

I have the following sequence in my class -

@Entity
public class CustomerOrder {

   @Id
   @SequenceGenerator( name = "CUSTOMER_ORDER_INVOICE_NO_SEQ_NAME", sequenceName = "CUSTOMER_ORDER_INVOICE_NO_SEQ", allocationSize = 20 )
   @GeneratedValue( strategy = GenerationType.SEQUENCE, generator = "CUSTOMER_ORDER_INVOICE_NO_SEQ_NAME" )
   private int id;

   private long orderInvoiceNo;

   //.. getters and setters

}

This is spring data jpa repository class having method to get next_val

  @Repository
  public interface CustomerOrderRepository
    extends CrudRepository<CustomerOrder, Long>, JpaSpecificationExecutor<CustomerOrder> {

     @Query( value = "SELECT next_val FROM CUSTOMER_ORDER_INVOICE_NO_SEQ", nativeQuery = true )
     Long getNextOrderInvoiceNoSeq();
 }

I need to get the next_val from the sequence and pass that value to two different places. So I am calling the getNextOrderInvoiceNoSeq() method of my repository and storing the value as orderInvoiceNo -

@Autowired
private CustomerOrderRepository customerOrderRepository;

public void placeCustomerOrder( CustomerOrder customerOrder )
{

 long orderInvoiceNo = customerOrderRepository.getNextOrderInvoiceNoSeq();
 customerOrder.setOrderInvoiceNo(orderInvoiceNo);

 // using the same orderInvoiceNo to my other pojo
 // CustomerPurchase customerPurchase = new CustomerPurchase
 // customerPurchase.setOrderInvoiceNo(orderInvoiceNo);

 //....
 customerOrderRepository.save(customerOrder);
}


 @Entity
 public class CustomerPurchase {

   private String itemId;
   private long orderInvoiceNo;
   //.....     
 }

So what is happening now for first two iteration of placing customerOrder, I am getting orderInvoiceNo as 1 and 41, but after that each time I am getting 41 as next_val from the sequence, hence making each order having same invoice no which is 41.

Which database are you using? Can you add the DDL definition for the sequence to the question? What happens if you run `SELECT next_val FROM CUSTOMER_ORDER_INVOICE_NO_SEQ` directly on the database? Do you still get 41? Or is this value just being cached by Hibernate? — codemonkey, Jun 01 '18 at 06:13
I am using mysql 5.7 and the sequence table is automatically gets created. Yes i get always 41 if run the query directly on the database. — thedevd, Jun 06 '18 at 04:59

score 2 · Answer 1 · answered Jun 07 '18 at 17:58

MySQL does not have sequences so in this case Hibernate will create a table generator.

Hibernate will do a SELECT FOR UPDATE to get the latest next_val and an UPDATE to increment the value:

SELECT next_val as id_val 
FROM CUSTOMER_ORDER_INVOICE_NO_SEQ FOR UPDATE

UPDATE CUSTOMER_ORDER_INVOICE_NO_SEQ 
SET next_val= 2 where next_val=1

The getNextOrderInvoiceNoSeq() only does a SELECT for the latest value but does not increment it.

That said, I wouldn't recommend implementing that. It would be better to switch to a native sequence and have a foreign key CustomerOrder and CustomerPurchase.

Hibernate Sequence get stuck at value 41

1 Answers1