How to implement two sequences in one table using jpa

Question

I have one table invoices in database which is mapped to my java class invoice.java as following.

@Entity
@Table(name = "invoices")
public class Invoice implements Serializable {

@Id
@GeneratedValue(generator = "my_seqinvoice")
@SequenceGenerator(name = "my_seqinvoice", sequenceName = "MY_SEQINVOIVE",     allocationSize = 1)
private Long invoiceId;

private String invoiceType;

private String receiptNumber;

private Long amount;

private Long totalAmount;

private Date invoiceDate;

private String paymentType;

@ManyToOne
@JoinColumn(name = "userId")
private User user;

}

I want to generate receipt number in sequence but i have two sequences.

Receipt sequence depends on user type. If user male then I want to user first sequence and if user is female then i want to use second sequence.

As Example...

in invoice table

First entry - user:James(male) then receipt num 1

Second entry - user:Andrew(male) then receipt num 2

Third entry - user:Sarah(female) then receipt num 1

Fourth entry - user:Slawomir(male) then receipt num 3

fifth entry - user:Casie(female) then receipt num 2

and for one user there are two type of recipt but receipt num for both receipt will be same

so howto implement this in mydatabase using JPA .I am using PostgresSQL.

Answer 1

Sir, this is good question and solution is used by many developers as I used it many times. for this you need to use Custom Generator for Id. Here is my implementation . Create two sequences named MALE_SEQ and FEMALE_SEQ .

 @Entity
 @Table(name = "invoices")
 public class Invoice implements Serializable {
    @Id
    @GenericGenerator(name="invoice_id",strategy="com.sunixi.InvoiceKeyGenerator")
    @GeneratedValue(generator = "invoice_id")
    private Long invoiceId;
    //------rest of variables
    //getter setter
 }

    //The custom generator
    public class InvoiceKeyGenerator implements IdentifierGenerator {
      public Serializable generate(SessionImplementor session,Object obj) throws HibernateException{
        Invoice invoice=(Invoice) obj;
        Connection con=session.conncetion();
        String seqName=""
        if(invoice.getUser().getGender().equals("M"){
          seqName="MALE_SEQ";
        }else{
          seqName="FEMALE_SEQ";
        }
      try{
          PreparedStatement ps=conn.preparedStatement("SELECT nextval('"+seqName+"')";
          ResultSet rs=ps.executeQuery();
       if(rs.next()){
          int id=rs.getInt("nextval");
          return id;
        }

       }catch(SQLException e){
         e.printStackTrace();
       }
      return null;
    }

}