how to generate sequence id in composite primary with @IdClass in springboot hibernate

Question

I am implementing a composite primary key with auto-generated annotation in spring boot hibernate. below is the scenario:

Account table has below columns: stateCode,branchCode,prodCode,subProdCode,accountNumber

whenever there is a change in stateCode,branchCode,prodCode,subProdCode , the table Account should have new accountNumber .

eg:

stateCode,branchCode,prodCode,subProdCode,accountNumber
11,01,20,1,00001 
11,01,30,1,00001  (there is a change in prodCode) 
11,01,30,2,00001  (there is a change in subprodcode)
11,01,20,2,00001  (there is a change in prod & subprodcode)
11,01,20,1,00002  (prodcode,subprod code has already account number 00001 , 
                       now it should be 00002)

the same question is already posted at Sequences with composite primary key
but i would like to know , if there is any new feature introduced in the latest version of spring boot hibernate.

Below is the code , which i have implemented:

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode(callSuper = false)
@IdClass(CompositeId.class)
public class CompositeAccountNumber {
    private String city;
    private String custName;
    @Id
    @GeneratedValue
    private Long accNumber;
    @Id
    private String stateCode;
    @Id
    private String branchCode;
    @Id
    private String prodCode;
    @Id
    private String subProdCode;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode(callSuper = false)
public class CompositeId  implements Serializable {
    private String stateCode;
    private String branchCode;
    private String prodCode;
    private String subProdCode;
    private Long accNumber;
}

I am getting below result with above code:

stateCode,branchCode,prodCode,subProdCode,accountNumber
11,01,20,1,00001 
11,01,30,1,00002  
11,01,30,2,00003  
11,01,20,2,00004 
11,01,20,1,00005

score 0 · Answer 1 · answered Sep 08 '19 at 21:09

Have a look at @GenericGenerator, it essentially allows you to access both the entity instance and the Hibernate's Session when generating sequence values. This way, you can query the table for the existence of the combination of the other (non-generated) keys you're about to insert and act accordingly.

Ajay Kumar · Answer 2 · 2020-05-05T14:50:34.263

I faced the same problem in the current project But after spending a couple of hours. It worked for me and this would be helpful as well.

Comment out the @GenericGenerator lines if it's not relevant for your scenario. ( in my case, GenericGenerator implementation class appending a string before inserting a Sequence in key field.)

ClientEntity contains a composite primary key ( manager_id + client_id )

@Entity(name = "clientMaster")
@Table(name = "clientMaster")
@IdClass(ClientId.class)
public class ClientEntity implements Serializable {

    /**
     * Generated serial version UID
     */
    private static final long serialVersionUID = 2716239938412051148L;


    @Id
    @Column(name = "client_id",length = 50,nullable = false)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "CLIENTMASTER_SEQ")
    @GenericGenerator(
            name = "CLIENTMASTER_SEQ",
            strategy = "com.abc.api.utils.StringPrefixedSequenceIdGenerator",
            parameters = {
                    @org.hibernate.annotations.Parameter(name = StringPrefixedSequenceIdGenerator.INCREMENT_PARAM, value = "50"),
                    @org.hibernate.annotations.Parameter(name = StringPrefixedSequenceIdGenerator.VALUE_PREFIX_PARAMETER, value = "CNT"),
                    @org.hibernate.annotations.Parameter(name = StringPrefixedSequenceIdGenerator.NUMBER_FORMAT_PARAMETER, value = "%06d") })
    private String clientId;


    @Id
    @Column(name = "manager_id")
    private String managerId;

    @ManyToOne
    @JoinColumn(name = "manager_id", insertable = false, updatable = false)
    protected ManagerEntity manager;

    // Getter & Setter

   }

ClientId - composite key entity.

    public class ClientId implements Serializable {

        private String clientId;

        private String managerId;

        // Getter and Setter
    }

And ManagerEntity -

    @Entity(name = "portfolioManager")
    @Table(name = "portfolio_manager")
    public class ManagerEntity implements Serializable {

        @Id
        @Column(name = "manager_id",length = 50,nullable = false)
        @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "PORTFOLIO_MANAGER_SEQ")
        @GenericGenerator(
                name = "PORTFOLIO_MANAGER_SEQ",
                strategy = "com.abc.api.utils.StringPrefixedSequenceIdGenerator",
                parameters = {
                        @org.hibernate.annotations.Parameter(name = StringPrefixedSequenceIdGenerator.INCREMENT_PARAM, value = "50"),
                        @org.hibernate.annotations.Parameter(name = StringPrefixedSequenceIdGenerator.VALUE_PREFIX_PARAMETER, value = "MNG"),
                        @org.hibernate.annotations.Parameter(name = StringPrefixedSequenceIdGenerator.NUMBER_FORMAT_PARAMETER, value = "%06d") })

        private String managerId;

@Column(name = "manager_name",length = 100)
private String managerName;

 @OneToMany(fetch = FetchType.LAZY, mappedBy = "manager")
 protected List<ClientEntity> clients = new ArrayList<>();

how to generate sequence id in composite primary with @IdClass in springboot hibernate

2 Answers2