Columns forming composite key in one table are actually in other table as foreign key-write entity class

Question

Industry_Codes Table

Industry_Code(Primary key) | Industry_name
1| Reliance
2| TaTa

Technology_codes Table

Technology_code(Primary Key) | technology name
81| java
81|cpp

carrier_codes Table

Industry_Code(Primary key)(Foreign Key to Industry_Codes table) | technology_code(pk)(Foreign key to Technology_Code table) | other fields

1 | 81 |
2| 81|
1| 82

Register

Mobile Number(Pk)|Industry_code(Fk to carrier_codes) |Technology_Code(Fk to carrier_codes)

12345|1|83
78913|1|88

for the given table structure the entity class are written as-

@Entity
@Table(name = "Industry_Codes")
public class IndustryCodes implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="TELECOM_INDUSTRY_CODE")
    private String telecomIndustryCode;

    @Column(name="INdustry_Name")
    private String Industry_Name;

}

Table 2

@Entity
@Table(name="Technology_Codes")
public class TechnologyCodes {

    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "Technology_code")
    private int targetTechnologyCode;

    @Column(name="Technology_Name")
    private String TechnologyName;
}

and

@Entity
@Table(name = "CARRIER_CODES")
public class CarrierCodes {
    private static final long serialVersionUID = 1L;

    @Id
    @ManyToOne
    @JoinColumn(name ="CARRIER_CODE",referencedColumnName="INDUSTRY_CODE")
    private IndustryCodes carrierCode;

    @Id
    @ManyToOne
    @JoinColumn(name ="TECHNOLOGY_CODE",referencedColumnName="TECHNOLOGY_CODE")
    private TechnologyCodes TechnologyCode;

}

Now I am all lost on how to write entity for Register table which has column like carrier_code and technology_code both of these are foreign key to carrier_codes table adn both of these together form composite key for carrier_codes table also there are many occurance of both field in both table thus a many to many association between both columns of both table.

Any help on writing the register entity is appreciated. I am completely new to associations in JPA.

Answer 1

First, be sure you understand the relationships. UML and images are your friend.

Notice that IndustryCode and TechCode is a ManyToMany relationship. In DDL a many to many relationship is done with join table and a composite key. In JPA this is normally just mapped with a ManyToMany annotation but since you also want to use it as a foreign key for the Registry table you must define the entity yourself. This is done with CarrierCodes. In JPA an EmbeddableId is generally the easiest way to make a composite key for an entity that will be used in this manner. So the JPA can be done like this:

@Entity
public class IndustryCode {
    @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;
    private String industryName;

    @OneToMany(mappedBy="industryCode")
    private Set<CarrierCodes> industryCodes;

@Entity
public class TechCode {
    @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;
    private String techName;

    @OneToMany(mappedBy="techCode")
    private Set<CarrierCodes> techCodes;

@Entity
public class CarrierCodes {
    @EmbeddedId
    private CarrierCodesId id = new CarrierCodesId(); 

    @ManyToOne
    @MapsId("techCodeId")
    private TechCode techCode;

    @ManyToOne
    @MapsId("industryCodeId")
    private IndustryCode industryCode;

@SuppressWarnings("serial")
@Embeddable
public class CarrierCodesId implements Serializable {
    private Long industryCodeId;
    private Long techCodeId;

@Entity
public class Register {
    @Id
    private Long mobileNumber;
    @ManyToOne
    // optional but nice to have consistent names
    @JoinColumns({
        @JoinColumn(name="industryCode_id", referencedColumnName="industryCode_id"),
        @JoinColumn(name="techCode_id", referencedColumnName="techCode_id")
        })
    private CarrierCodes carrierCodes;

And to use it similar to your example is like this.

tx.begin();
IndustryCode ic1 = new IndustryCode("Reliance");
IndustryCode ic2 = new IndustryCode("TaTa");
TechCode tc1 = new TechCode("java");
TechCode tc2 = new TechCode("cpp");
CarrierCodes cc1 = new CarrierCodes(tc1, ic1);
CarrierCodes cc2 = new CarrierCodes(tc1, ic2);
CarrierCodes cc3 = new CarrierCodes(tc2, ic1);
Register r1 = new Register(12345L, cc1);
Register r2 = new Register(78913L, cc2);

em.persist(ic1);
em.persist(ic2);
em.persist(tc1);
em.persist(tc2);
em.persist(cc1);
em.persist(cc2);
em.persist(cc3);
em.persist(r1);
em.persist(r2);
tx.commit();

em.clear();
List<Register> rs = em.createQuery("select r from Register r left outer join fetch r.carrierCodes cc where cc.techCode.techName = 'java'", Register.class).getResultList();
rs.stream().forEach(r->System.out.println(r.getMobileNumber() + " " + r.getCarrierCodes().getTechCode().getTechName()));

List<Register> rs2 = em.createQuery("select r from Register r left outer join fetch r.carrierCodes cc where cc.industryCode.industryName = 'TaTa'", Register.class).getResultList();
rs2.stream().forEach(r->System.out.println(r.getMobileNumber() + " " + r.getCarrierCodes().getIndustryCode().getIndustryName()));

That gives me the following output.

Hibernate: create table CarrierCodes (industryCode_id bigint not null, techCode_id bigint not null, primary key (industryCode_id, techCode_id))
Hibernate: create table IndustryCode (id bigint generated by default as identity (start with 1), industryName varchar(255), primary key (id))
Hibernate: create table Register (mobileNumber bigint not null, industryCode_id bigint, techCode_id bigint, primary key (mobileNumber))
Hibernate: create table TechCode (id bigint generated by default as identity (start with 1), techName varchar(255), primary key (id))
Hibernate: alter table CarrierCodes add constraint FKfq42ix66txvd15crq2pey3dcp foreign key (industryCode_id) references IndustryCode
Hibernate: alter table CarrierCodes add constraint FK9os97pd53ijerp2mibllknovn foreign key (techCode_id) references TechCode
Hibernate: alter table Register add constraint FK2k626ouo1ajsccqlpb5y3xa8u foreign key (industryCode_id, techCode_id) references CarrierCodes
Hibernate: insert into IndustryCode (id, industryName) values (default, ?)
Hibernate: insert into IndustryCode (id, industryName) values (default, ?)
Hibernate: insert into TechCode (id, techName) values (default, ?)
Hibernate: insert into TechCode (id, techName) values (default, ?)
Hibernate: insert into CarrierCodes (industryCode_id, techCode_id) values (?, ?)
Hibernate: insert into CarrierCodes (industryCode_id, techCode_id) values (?, ?)
Hibernate: insert into CarrierCodes (industryCode_id, techCode_id) values (?, ?)
Hibernate: insert into Register (industryCode_id, techCode_id, mobileNumber) values (?, ?, ?)
Hibernate: insert into Register (industryCode_id, techCode_id, mobileNumber) values (?, ?, ?)
Hibernate: select register0_.mobileNumber as mobileNu1_2_0_, carriercod1_.industryCode_id as industry1_0_1_, carriercod1_.techCode_id as techCode2_0_1_, register0_.industryCode_id as industry2_2_0_, register0_.techCode_id as techCode3_2_0_ from Register register0_ left outer join CarrierCodes carriercod1_ on register0_.industryCode_id=carriercod1_.industryCode_id and register0_.techCode_id=carriercod1_.techCode_id cross join TechCode techcode2_ where carriercod1_.techCode_id=techcode2_.id and techcode2_.techName='java'
Hibernate: select industryco0_.id as id1_1_0_, industryco0_.industryName as industry2_1_0_ from IndustryCode industryco0_ where industryco0_.id=?
Hibernate: select techcode0_.id as id1_3_0_, techcode0_.techName as techName2_3_0_ from TechCode techcode0_ where techcode0_.id=?
Hibernate: select industryco0_.id as id1_1_0_, industryco0_.industryName as industry2_1_0_ from IndustryCode industryco0_ where industryco0_.id=?
12345 java
78913 java
Hibernate: select register0_.mobileNumber as mobileNu1_2_0_, carriercod1_.industryCode_id as industry1_0_1_, carriercod1_.techCode_id as techCode2_0_1_, register0_.industryCode_id as industry2_2_0_, register0_.techCode_id as techCode3_2_0_ from Register register0_ left outer join CarrierCodes carriercod1_ on register0_.industryCode_id=carriercod1_.industryCode_id and register0_.techCode_id=carriercod1_.techCode_id cross join IndustryCode industryco2_ where carriercod1_.industryCode_id=industryco2_.id and industryco2_.industryName='TaTa'
78913 TaTa