Skip to main content

Many to Many bidirectional relationship

10 replies [Last post]
ptm
Offline
Joined: 2007-03-08

I’m trying to set up a Many-To-Many relationship between two entities. I believe I have set it up correctly and they persist to the database fine, where by I get two entity tables and a relationship table mapping them together via there PK. However if I want to access a collection in one of the entities with a getCollectionEG method it always returns an empty collection. My code is as follows:

Owning side entity Story.java contains a Collection and annotations:

private Collection testTables = new ArrayList();

@ManyToMany(cascade=CascadeType.ALL,fetch=FetchType.EAGER)
@JoinTable(name="STORY_TESTTABLE",
joinColumns=@JoinColumn(name="STORY_ID",
referencedColumnName="STORY_ID"),
inverseJoinColumns=@JoinColumn(name="TABLE_ID",
referencedColumnName="TABLE_ID"))
public Collection getTestTables(){
return testTables;
}

The other entity has a Collection and the annotation mapping:

private Collection stories = new ArrayList();

@ManyToMany(mappedBy="testTables",cascade=CascadeType.ALL, fetch=FetchType.EAGER)
public Collection getStories(){
return stories;
}

I have a TestTableFacade which contains a create method:

public void create(TestTable testTable) throws PageDoesNotExistException, TableCouldNotBeAddedToHTMLFileException, TestTableDoesNotExistException {
Page pageFound = null;
try {
pageFound = page.findByName(testTable.getP().getPageName());
testTable.setP(pageFound);
} catch (QueryDidNotReturnResultException ex) {
throw new PageDoesNotExistException();
}

int position = -1;

Collection storiesToBeAdded = new ArrayList();

ArrayList theStories = (ArrayList) testTable.getStories();
if((theStories.size() > 0) && (!theStories.get(0).getStoryName().equals(""))){
Collection stories = new ArrayList(theStories);

for(Story s: stories){
try {
Story storyFound = story.findStoryByName(s.getStoryName());
storiesToBeAdded.add(storyFound);

if(position == -1){
position = storyFound.getId().intValue();
}
} catch (StoryNotFoundException ex) {
ex.printStackTrace();
}
}
}else{
position = -1;
}
testTable.setStories(storiesToBeAdded);

em.persist(testTable);
em.flush();

for(Story s: testTable.getStories()){
try {
if(s != null && !s.equals("")){
Story st = story.findStoryByName(s.getStoryName());

st.getTestTables().add(this.findTableByName(testTable.getTableName()));

em.merge(st);
}
} catch (StoryNotFoundException ex) {
ex.printStackTrace();
} catch (TestTableDoesNotExistException ex) {
ex.printStackTrace();
}
}

}

So as far as I can see I do everything correctly. The Database is created correctly, the data is stored correctly with the relationship shown. I even have a EJB QL statement which gets testTables. Which returns me the list of testTables inside a given story.

Query query = em.createQuery( "SELECT o.testTables FROM Story o WHERE o.id=?1");
query.setParameter(1, id);
return query.getResultList();

However if I find a Story entity and call getTestTables(), it always returns an empty collection. I would be very grateful for any suggestions.

Thanks

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Gordon Yorke

This seems strange are you performing the getTestTables() on a Story from a different Persistence Context? How is the Story retrieved? Can you provide the code where you are accessing the getTestTables() list?
--Gordon

-----Original Message-----
From: glassfish@javadesktop.org [mailto:glassfish@javadesktop.org]
Sent: Thursday, March 08, 2007 3:29 PM
To: users@glassfish.dev.java.net
Subject: Many to Many bidirectional relationship

[b]I’m trying to set up a Many-To-Many relationship between two entities. I believe I have set it up correctly and they persist to the database fine, where by I get two entity tables and a relationship table mapping them together via there PK. However if I want to access a collection in one of the entities with a getCollectionEG method it always returns an empty collection. My code is as follows:

Owning side entity Story.java contains a Collection and annotations:[/b]

private Collection testTables = new ArrayList();

@ManyToMany(cascade=CascadeType.ALL,fetch=FetchType.EAGER)
@JoinTable(name="STORY_TESTTABLE",
joinColumns=@JoinColumn(name="STORY_ID",
referencedColumnName="STORY_ID"),
inverseJoinColumns=@JoinColumn(name="TABLE_ID",
referencedColumnName="TABLE_ID"))
public Collection getTestTables(){
return testTables;
}

[b]The other entity has a Collection and the annotation mapping:[/b]

private Collection stories = new ArrayList();

@ManyToMany(mappedBy="testTables",cascade=CascadeType.ALL, fetch=FetchType.EAGER)
public Collection getStories(){
return stories;
}

[b]I have a TestTableFacade which contains a create method:[/b]

public void create(TestTable testTable) throws PageDoesNotExistException, TableCouldNotBeAddedToHTMLFileException, TestTableDoesNotExistException {
Page pageFound = null;
try {
pageFound = page.findByName(testTable.getP().getPageName());
testTable.setP(pageFound);
} catch (QueryDidNotReturnResultException ex) {
throw new PageDoesNotExistException();
}

int position = -1;

Collection storiesToBeAdded = new ArrayList();

ArrayList theStories = (ArrayList) testTable.getStories();
if((theStories.size() > 0) && (!theStories.get(0).getStoryName().equals(""))){
Collection stories = new ArrayList(theStories);

for(Story s: stories){
try {
Story storyFound = story.findStoryByName(s.getStoryName());
storiesToBeAdded.add(storyFound);

if(position == -1){
position = storyFound.getId().intValue();
}
} catch (StoryNotFoundException ex) {
ex.printStackTrace();
}
}
}else{
position = -1;
}
testTable.setStories(storiesToBeAdded);

em.persist(testTable);
em.flush();

for(Story s: testTable.getStories()){
try {
if(s != null && !s.equals("")){
Story st = story.findStoryByName(s.getStoryName());

st.getTestTables().add(this.findTableByName(testTable.getTableName()));

em.merge(st);
}
} catch (StoryNotFoundException ex) {
ex.printStackTrace();
} catch (TestTableDoesNotExistException ex) {
ex.printStackTrace();
}
}

}

[b]So as far as I can see I do everything correctly. The Database is created correctly, the data is stored correctly with the relationship shown. I even have a EJB QL statement which gets testTables. Which returns me the list of testTables inside a given story.[/b]

Query query = em.createQuery( "SELECT o.testTables FROM Story o WHERE o.id=?1");
query.setParameter(1, id);
return query.getResultList();

[b]However if I find a Story entity and call getTestTables(), it always returns an empty collection. I would be very grateful for any suggestions.[/b]

[b]Thanks[/b]
[Message sent by forum member 'ptm' (ptm)]

http://forums.java.net/jive/thread.jspa?messageID=206968

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
For additional commands, e-mail: users-help@glassfish.dev.java.net

ptm
Offline
Joined: 2007-03-08

[b]> This seems strange are you performing the
> getTestTables() on a Story from a different
> Persistence Context?

Nope, same persistence context.

> How is the Story retrieved?
> Can you provide the code where you are accessing the
> getTestTables() list?
> -Gordon

Here is my destory method:[/b]

public void destroy(TestTable testTable) {

Collection stories = testTable.getStories();
Collection testTables = new ArrayList();

for(Story s: stories){
// s = story.findStoryByName(s.getStoryName());
s = em.find(Story.class,s.getId());
testTables = s.getTestTables();
testTables.remove(testTable);
em.merge(s);
}

stories.clear();
testTable.setStories(stories);
testTable = em.merge(testTable);
em.remove(testTable);
}

[b]Both :
s = story.findStoryByName(s.getStoryName());
s = em.find(Story.class,s.getId());
retreive a Story.[/b]

ptm
Offline
Joined: 2007-03-08

[b]Sorry i forgot to add... when this method is called i get the following exception[/b]
Local Exception Stack:
Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2006.8 (Build 060830)): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: org.apache.derby.client.am.SqlException: DELETE on table 'TESTTABLE' caused a violation of foreign key constraint 'STRYTESTTABLETBLID' for key (20). The statement has been rolled back.Error Code: -1
Call:DELETE FROM TESTTABLE WHERE (TABLE_ID = ?)
bind => [20]
Query:DeleteObjectQuery(sem90.ptm2.fhmp.table.Table[id=20])

[b]Thanks[/b]

mf125085
Offline
Joined: 2005-03-29

Could you post the table definition used in your application? Does the TestTable class have any other relationships?

ptm
Offline
Joined: 2007-03-08

[b]Does the TestTable class have any other
> relationships?
Yes, has a ManyToOne with an entity called Page.

Here is the code for the TestTable class:[/b]

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collection;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.persistence.Transient;
import sem90.ptm2.fhmp.page.Page;
import sem90.ptm2.fhmp.story.Story;

/**
* Entity class Table
*
*/
@Entity
@Table(name="TESTTABLE")
public class TestTable implements Serializable {

private Long id;
private String tableName;
private int tableType;
private String positionBelow;
private Page p;
private Collection stories = new ArrayList();
private String htmlContent;

public TestTable() {
}
/** Creates a new instance of Table */
public TestTable(String tableName,int tableType, Page page, String positionBelow, String htmlContent) {
this.setTableName(tableName);
this.setTableType(tableType);
this.p =page;
this.setPositionBelow(positionBelow);
this.setHtmlContents(htmlContent);
}

public TestTable(String tableName,int tableType, Page page, String positionBelow, String htmlContent, Collection stories) {
this.setTableName(tableName);
this.setTableType(tableType);
this.p =page;
this.setPositionBelow(positionBelow);
this.setHtmlContents(htmlContent);
this.stories = stories;
}

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name="TABLE_ID")
public Long getId() {
return this.id;
}

/**
* Sets the id of this Table to the specified value.
* @param id the new id
*/
public void setId(Long id) {
this.id = id;
}

/**
* Returns a hash code value for the object. This implementation computes
* a hash code value based on the id fields in this object.
* @return a hash code value for this object.
*
*/
@Override
public int hashCode() {
int hash = 0;
hash += (this.getId() != null ? this.getId().hashCode() : 0);
return hash;
}

/**
* Determines whether another object is equal to this Table. The result is
* true if and only if the argument is not null and is a Table object that
* has the same id field values as this object.
* @param object the reference object with which to compare
* @return true if this object is the same as the argument;
* false otherwise.
*/
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof TestTable)) {
return false;
}
TestTable other = (TestTable)object;
if (this.getId() != other.getId() && (this.getId() == null || !this.getId().equals(other.getId()))) return false;
return true;
}

/**
* Returns a string representation of the object. This implementation constructs
* that representation based on the id fields.
* @return a string representation of the object.
*/
@Override
public String toString() {
return "sem90.ptm2.fhmp.table.Table[id=" + getId() + "]";
}

@Column(name="TABLENAME")
public String getTableName() {
return tableName;
}

public void setTableName(String tableName) {
this.tableName = tableName;
}

@Column(name="TABLETYPE")
public int getTableType() {
return tableType;
}

public void setTableType(int tableType) {
this.tableType = tableType;
}

@Column(name="POSITIONBELOW")
public String getPositionBelow() {
return positionBelow;
}

public void setPositionBelow(String positionBelow) {
this.positionBelow = positionBelow;
}

@ManyToOne
@JoinColumn(name="PAGE_ID")
public Page getP() {
return p;
}

public void setP(Page page){
this.p = page;
}

@ManyToMany(mappedBy="testTables",cascade=CascadeType.ALL, fetch=FetchType.EAGER)
public Collection getStories(){
return stories;
}

public void setStories(Collection stories){
this.stories = stories;
}

@Transient
public String getHtmlContents() {
return htmlContent;
}

public void setHtmlContents(String htmlContent){
this.htmlContent = htmlContent;
}
}

mf125085
Offline
Joined: 2005-03-29

Can you please turn on SQL logging to see, if entries in STORY_TESTTABLE are removed at all? In JavaSE, logging of SQL statements can be turned on by adding

to persistence.xml.

Is there any other (uni-directional) relationship to TestTable?

ptm
Offline
Joined: 2007-03-08

[b]> Can you please turn on SQL logging to see, if entries
> in STORY_TESTTABLE are removed at all? In JavaSE,
> logging of SQL statements can be turned on by adding
>
>
>
> value="FINE"/>
> to persistence.xml.[/b]

SELECT STORY_ID, CREATED, LASTEDITED, STORYNAME, PAGE_ID FROM STORY WHERE (STORYNAME = CAST (? AS VARCHAR(32672) ))
bind => [Create Story]
SELECT TABLE_ID, TABLETYPE, POSITIONBELOW, TABLENAME, PAGE_ID FROM TESTTABLE WHERE (TABLENAME = CAST (? AS VARCHAR(32672) ))
bind => [Table 4]
INSERT INTO STORY_TESTTABLE (TABLE_ID, STORY_ID) VALUES (?, ?)
bind => [20, 13]
SELECT TABLE_ID, TABLETYPE, POSITIONBELOW, TABLENAME, PAGE_ID FROM TESTTABLE WHERE (TABLENAME = CAST (? AS VARCHAR(32672) ))
bind => [Table 4]
SELECT STORY_ID, CREATED, LASTEDITED, STORYNAME, PAGE_ID FROM STORY WHERE (STORYNAME = CAST (? AS VARCHAR(32672) ))
bind => [Create Story]

...

SELECT TABLE_ID, TABLETYPE, POSITIONBELOW, TABLENAME, PAGE_ID FROM TESTTABLE
SELECT TABLE_ID, TABLETYPE, POSITIONBELOW, TABLENAME, PAGE_ID FROM TESTTABLE WHERE (TABLENAME = CAST (? AS VARCHAR(32672) ))
bind => [Table 4]
DELETE FROM TESTTABLE WHERE (TABLE_ID = ?)
bind => [20]
Local Exception Stack:
Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2006.8 (Build 060830)): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: org.apache.derby.client.am.SqlException: DELETE on table 'TESTTABLE' caused a violation of foreign key constraint 'STRYTESTTABLETBLID' for key (20). The statement has been rolled back.Error Code: -1
Call:DELETE FROM TESTTABLE WHERE (TABLE_ID = ?)
bind => [20]
Query:DeleteObjectQuery(sem90.ptm2.fhmp.table.Table[id=20])
at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:295)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:639)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:688)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:477)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:437)
at oracle.toplink.essentials.internal.sessions.AbstractSession.executeCall(AbstractSession.java:675)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:213)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:199)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.deleteObject(DatasourceCallQueryMechanism.java:190)
at oracle.topl

[b]>
> Is there any other (uni-directional) relationship to
> TestTable?

TestTable Has a Many to Many with Story and a bidirectional OneToMany with Page. And no other entities have a uniDirectional defined back to TestTable.

Inside the Page class i have:[/b]

@OneToMany(cascade={CascadeType.ALL}, mappedBy="p", fetch=FetchType.EAGER)
public List getTestTables(){
return testTables;
}

public void setTestTables(List tables){
if(tables != null){
this.testTables = tables;
}
}

gyorke
Offline
Joined: 2005-06-21

Have you inspected the getTestTables() results or do you feel the collection is empty because the TestTable is not removed?
--Gordon

ptm
Offline
Joined: 2007-03-08

I have debugged it using the Netbeans debugger... the 'watches' i had showed that the Collection was empty.
Thanks

gyorke
Offline
Joined: 2005-06-21

I recommend creating a simple testcase and filing an Issue uploading that test case.
--Gordon