Inserting parent and child entities into a third entity using selectOneMenu

[Jay Tai]

The company entity contains companyName, Sector and Segment columns. The mapping is 3 entities (Company, Sector, Segment)
where Sector and Segment are used to create a company record. Sector has a OneToMany relationship with Segment and with Company.
I put the Sector and Segment values into two select menus as use these to create a Sector and Segment reference for the Company table.
I'm getting the following exception:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row:
a foreign key constraint fails (`testdummy`.`company`, CONSTRAINT `FK_COMPANY_FK_COMPANY_SECTORID` FOREIGN KEY
(`FK_COMPANY_SECTORID`) REFERENCES `sectors` (`SECTORID`))
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

I'm thinking that the problem is that since the Segment entity is a child of Sector it must be entered through an instance
of Sector. Because it's being entered as a separate value I'm getting this error. The problem is Segment is defined as a Set
in the Sector entity and I can't figure out how to declare Segment as an instance using its parent entity (Sector).

If that is is not the problem I would surely appreciate some advice on where I'm going wrong. Thanks!

My code is as follows, starting with the Sector entity:

@Entity
@Table(name = "SECTORSNEW")
public class SectorsNew {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int sectorId;
private String sectorName;
@OneToMany(cascade = CascadeType.PERSIST)
@JoinColumn(name = "fk_company_sectorId")
private Set<Company> company;
@OneToMany(cascade = CascadeType.PERSIST)
@JoinColumn(name = "fk_sector_segmentId")
private Set<SegmentNew> segments;

Then the Segment:

@Entity
@Table(name = "SEGMENTNEW")
public class SegmentNew {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int segmentId;
private int fk_segment_sectorid;
private String segmentName;
@ManyToOne(optional = true)
@JoinColumn(name = "sectorId")
private SectorsNew sectors;
@OneToMany(cascade = CascadeType.PERSIST)
@JoinColumn(name = "fk_company_segmentId")
private Set<Company> company;

This is the company entity and the 'target' entity into which I'm trying to persist
Sector and Segment values:

@Entity
@Table(name = "COMPANY")
public class Company {
@Id
@GeneratedValue
private int companyId;
private String companyName;
private int fk_company_countryid;
private int fk_company_sectorId;
private int fk_company_segmentId;
@ManyToOne(optional = true)
@JoinColumn(name = "sectorId")
private SectorsNew sectors;
@ManyToOne(optional = true)
@JoinColumn(name = "segmentId")
private SegmentNew segmentObject;

Using this bean:

@ManagedBean
@SessionScoped
public class CompanyBean {
@EJB
private CompanyFacade companyFacade;
@EJB
private SectorsNewFacade sectorFacade;
@EJB
private SegmentNewFacade segmentFacade;
public String createCompany() {
newCompany = new Company(companyName, sectorId,
sector, segmentId, segmentObj, countryid, country);
country = countryServ.findById(countryid);
newCompany.setCountry(country);
sector = sectorFacade.find(sectorId);
newCompany.setSectors(sector);
segmentObj = segmentFacade.find(segmentId);
newCompany.setSegment(segmentObj);
companyFacade.save(newCompany);
return findCompany();
}

Finally the JSF page used to create the Company record:

<p><p:outputLabel value="Sector Name:"/>
<h:selectOneMenu value="#{companyBean.sectorId}">
<f:selectItems value="#{newSect.sectorsNewList}"
var="sec" itemLabel="#{sec.sectorName}"
itemValue="#{sec.sectorId}"/>
<f:ajax listener="#{companyBean.changeSegment}" render="segments">
</f:ajax>
</h:selectOneMenu>
</p>
<p><p:outputLabel value="Segment Name:"/>
<h:panelGroup id="segments">
<h:selectOneMenu value="#{companyBean.segmentId}"
rendered="#{not empty companyBean.segmentMenu}">
<f:selectItems value="#{companyBean.segmentMenu}"
var="seg" itemLabel="#{seg.segmentName}"
itemValue="#{seg.segmentId}"/>
</h:selectOneMenu>
</h:panelGroup>
</p>

[Jay Tai]

OK I solved this. The mapping of my tables was plain wrong. The way the company table was mapped created a physical column in the database and when there was no corresponding entity to populate that column it I got an integrity constraint violation exception. The solution was to re-write the company, sector and segment entities, using the 'mapped by' property in the @OneToMany relationship annotation. Mapped by clarifies the owning part of the relationship and prevents physical columns from being unnecessarily created by simply using the entity IDs (where my code before used foreign keys that were created as columns).

Please let me know if I need to re-post this in the JPA forum or if I should post the code of the solution here in case it helps anybody. Thanks, J

[Tim Holloway]

I've got tons of these things, so I've lots of practice. Worse yet, they have space-padded compound keys.

The thing that saved what little sanity I still have is a Apress book by Keith and Schincariol. Doesn't deal with ALL the insanity I've encountered, but it has been a good reference for a lot of JPA arcane problems.

[Jay Tai]

In case I have any sanity left after learning JSF, would you happen to know the neame of the book. I read one called 'Pro JPA' which was very well written and helpful (don't have the author's name off the top of my head). Thanks, J

[Tim Holloway]

Mine's "Pro JPA 2".

[Jay Tai]

:) Pardon the pun but then we're on the same page. It's written by Keith & Schincariol? One of the best books I've ever read on the subject :)

[Tim Holloway]

Pun accidental. I got started in JPA at version 1, but the extra features in JPA2 have been invaluable to me. And yes, they're the ones!