JPA Hibernate custom sequence generator for id

Question

I have two Entities named Agency and Pay. The Relationship between them is one-to-many(one Agency has many Pay). the agency table has a foreign key in Pay and Pay has a composite key: pay.id and agency.id I want to have a custom sequence generator for Pay.id where it's possible to have :

Agency.id || pay.id

---

1 || P00001

1 || P00002

1 || P00003

2 || P00001

2 || P00002

3 || P00001

4 || P00002

Thanks for your help

edit : i want to start the sequence for every new agency

Answer 1

Instead of implementing a generator I can recommend you another approach with using @OrderColumn and @Formula annotations:

@Entity
public class Agency {

   @Id
   @GeneratedValue
   private Integer id;

   @OneToMany(mapedBy = "agency", cascade = {PERSIST, MERGE})
   @OrderColumn(name = "order_num")
   @ListIndexBase(1)
   private final List<Payment> payments = new ArrayList<>();

   public Agency addPayment(Payment payment) {
      payment.setAgency(this);
      this.payments.add(payment);
      return this;
   }

   // ...
} 

@Entity    
public class Payment {
   @Id
   @GeneratedValue
   private Integer id;

   @OneToMany(optional = false)
   private Agency agency;

   @Formula(value = "CONCAT(agency_id, 'P', LPAD(order_num, 5, 0))")
   private String number;

   // ...
}

In this case the payment table will have additional column order_num with Agency relative order number of each its payment (1, 2, 3...).

With @Formula you can render this number on the number column. Be aware that the @Formula annotation takes a native SQL clause which can affect database portability (it's MySQL dialect in the example).

UPDATED

To make @OrderColumn work it's necessary to save Payment 'through' Agency and use helper method addPayment(Payment payment) since this 'one-to-many' association is bidirectional (more info is here):

final Payment payment1 = new Payment();
final Payment payment2 = new Payment();
final Agency agency = new Agency();
agency.addPayment(payment1);
agency.addPayment(payment2);

agencyRepo.save(agency);