Hi there, I am attempting to have appian create a database schema from the XSD's I am creating, and unfortunately I need to work with a @ManyToMany relationship.
When using this code below (Which is up to JPA Spec for @ManyToMany relationships):
Employee
<xsd:element maxOccurs="unbounded" minOccurs="0" name="skills" type="tns:CMD_Skill"> <xsd:annotation> <xsd:appinfo source="appian.jpa"> @ManyToMany(cascade=CascadeType.ALL) @JoinTable( name="cmdEmployeeSkill", joinColumns = {@JoinColumn(name="employee_id")}, inverseJoinColumns = {@JoinColumn(name="skill_id")} ) </xsd:appinfo> </xsd:annotation> </xsd:element>
Skill
<xsd:element maxOccurs="unbounded" minOccurs="0" name="employees" type="tns:CMD_Employee"> <xsd:annotation> <xsd:appinfo source="appian.jpa"> @ManyToMany( mappedBy = "skills", fetch = FetchType.LAZY ) </xsd:appinfo> </xsd:annotation> </xsd:element>
My SQL DDL comes out like this:
create table `cmdEmployeeSkill` ( `skill_id` integer not null, `employee_id` integer, `cmdemployee_skills_idx` integer not null, primary key (`employee_id`, `cmdemployee_skills_idx`) ) ENGINE=InnoDB; create table `cmdEmployee` ( `id` integer not null auto_increment, -- Other Meta as neccessary primary key (`id`) ) ENGINE=InnoDB; create table `cmdSkill` ( `id` integer not null auto_increment, `name` varchar(255), primary key (`id`) ) ENGINE=InnoDB; create table `cmdemployee_cmdskill` ( id integer not null, elt integer not null, `cmdemployee_skills_idx` integer not null, primary key (id, `cmdemployee_skills_idx`) ) ENGINE=InnoDB;
Take note of the extra table cmdemployee_cmdskill
However, when I change the XML to this
<xsd:element maxOccurs="unbounded" minOccurs="0" name="employees" type="tns:CMD_Employee"> <xsd:annotation> <xsd:appinfo source="appian.jpa"> @ManyToMany( cascade=CascadeType.ALL, indexed=false, fetch = FetchType.LAZY ) @JoinTable( name="cmdEmployeeSkill", joinColumns=@JoinColumn(name="skill_id"), inverseJoinColumns=@JoinColumn(name="employee_id") ) </xsd:appinfo> </xsd:annotation> </xsd:element>
My DDL comes out properly
Does anyone have experience with this?
I attempted to use this related discussion on a similar problem with a @OneToMany, but was not able to resolve
Thanks!
Discussion posts and replies are publicly visible