Mixing
Columns forming composite key in one table are actually in other table as foreign key-write entity class
1
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.
java spring hibernate jpa model-view-controller
edited Nov 20 '18 at 6:12
K.Nicholas
5,27232338
asked Nov 19 '18 at 18:35
SarthakSarthak
153
add a comment |
1
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.
java spring hibernate jpa model-view-controller
edited Nov 20 '18 at 6:12
K.Nicholas
5,27232338
asked Nov 19 '18 at 18:35
SarthakSarthak
153
add a comment |
1
1
1
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.
java spring hibernate jpa model-view-controller
edited Nov 20 '18 at 6:12
K.Nicholas
5,27232338
asked Nov 19 '18 at 18:35
SarthakSarthak
153
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.
java spring hibernate jpa model-view-controller
java spring hibernate jpa model-view-controller
edited Nov 20 '18 at 6:12
K.Nicholas
5,27232338
asked Nov 19 '18 at 18:35
SarthakSarthak
153
edited Nov 20 '18 at 6:12
K.Nicholas
5,27232338
asked Nov 19 '18 at 18:35
SarthakSarthak
153
edited Nov 20 '18 at 6:12
K.Nicholas
5,27232338
edited Nov 20 '18 at 6:12
K.Nicholas
5,27232338
edited Nov 20 '18 at 6:12
K.Nicholas
5,27232338
5,27232338
asked Nov 19 '18 at 18:35
SarthakSarthak
153
asked Nov 19 '18 at 18:35
SarthakSarthak
153
asked Nov 19 '18 at 18:35
SarthakSarthak
153
153
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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
answered Nov 20 '18 at 5:54
K.NicholasK.Nicholas
5,27232338
Thank you,sir.! This works perfectly fine for me.
– Sarthak
Nov 21 '18 at 3:44
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53380731%2fcolumns-forming-composite-key-in-one-table-are-actually-in-other-table-as-foreig%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 20 '18 at 5:54
K.NicholasK.Nicholas
5,27232338
Thank you,sir.! This works perfectly fine for me.
– Sarthak
Nov 21 '18 at 3:44
add a comment |
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
answered Nov 20 '18 at 5:54
K.NicholasK.Nicholas
5,27232338
Thank you,sir.! This works perfectly fine for me.
– Sarthak
Nov 21 '18 at 3:44
add a comment |
1
1
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
answered Nov 20 '18 at 5:54
K.NicholasK.Nicholas
5,27232338
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
answered Nov 20 '18 at 5:54
K.NicholasK.Nicholas
5,27232338
edited Nov 20 '18 at 6:58
answered Nov 20 '18 at 5:54
K.NicholasK.Nicholas
5,27232338
answered Nov 20 '18 at 5:54
K.NicholasK.Nicholas
5,27232338
answered Nov 20 '18 at 5:54
K.NicholasK.Nicholas
5,27232338
5,27232338
Thank you,sir.! This works perfectly fine for me.
– Sarthak
Nov 21 '18 at 3:44
add a comment |
Thank you,sir.! This works perfectly fine for me.
– Sarthak
Nov 21 '18 at 3:44
Thank you,sir.! This works perfectly fine for me.
– Sarthak
Nov 21 '18 at 3:44
Thank you,sir.! This works perfectly fine for me.
– Sarthak
Nov 21 '18 at 3:44
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53380731%2fcolumns-forming-composite-key-in-one-table-are-actually-in-other-table-as-foreig%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
