Eclipse Community Forums: EclipseLink » DTYPE Generated on ManyToMany Join?

Home » Eclipse Projects » EclipseLink » DTYPE Generated on ManyToMany Join?(DTYPE generated when not using inheritance)

Show: Today's Messages :: Show Polls :: Message Navigator

DTYPE Generated on ManyToMany Join? [message #642102]

Mon, 29 November 2010 20:24

No real name

Messages: 4
Registered: November 2010

Junior Member

We have two entities, Client and User in tables D001 and D002 respectively. Neither are using InheritanceType.JOINED; they are the only objects in their respective hierarchies.

Each entity can join with the other via a @JoinTable:

In our User :
/** The clients. */
@ManyToMany
@JoinTable( name = "D003", joinColumns = { @JoinColumn( name = "SYSASSGN_USER_ID", referencedColumnName = "SYSASSGN_USER_ID" ) }, inverseJoinColumns = { @JoinColumn( name = "CLIENT_ID", referencedColumnName = "CLIENT_ID" ) } )
protected List<Client> clients;

In our Client:
/** The users. */
@ManyToMany
@JoinTable( name = "D003", joinColumns = { @JoinColumn( name = "CLIENT_ID", referencedColumnName = "CLIENT_ID" ) }, inverseJoinColumns = { @JoinColumn( name = "SYSASSGN_USER_ID", referencedColumnName = "SYSASSGN_USER_ID" ) } )
private List<User> users;

The tables are quite simple:

The D003 only has two columns: CLIENT_ID and SYSASSGN_USER_ID

Client table (D001) only has CLIENT_ID and CLIENT_NAME

User table (D002) has SYSASSGN_USER_ID, USER_ID, USER_NAME, TITLE

The SQL generated includes a column called DTYPE in the query:
SELECT t1.CLIENT_ID, t1.DTYPE, t1.CLIENT_NAME FROM D003 t0, D001 t1 WHERE ((t0.SYSASSGN_USER_ID = ?) AND (t1.CLIENT_ID = t0.CLIENT_ID))

It is looking for a value in this column on the D001 - however since @Inheritance is neither JOINED nor SINGLE_TABLE, DTYPE should not be required as a column on the table.

Any assistance is appreciated; we have tried a number of different approaches but none have worked to date.

Thanks,
Mike

Report message to a moderator

Re: DTYPE Generated on ManyToMany Join? [message #642111 is a reply to message #642102]

Mon, 29 November 2010 21:20

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

Hello Mike,

I am not sure on the DTYPE column or where it is coming from, but I do not think it is coming from these two mappings. They are using the Join column incorrectly though - they will result in duplicate rows being added since both are writable and control the join table. Instead, one side should define the join table and join columns, and the other should specify it is mappedby the other. Using the mappedby indicates that the other side owns/controls the relationship and that this side is effectively read-only (changes on this side are merged to the cache, but not the database).

As for why the DTYPE is used - what happens if you do a find or simple "Select c from Client"? Can you post the full Client entity?

Regards
Chris

Report message to a moderator

Re: DTYPE Generated on ManyToMany Join? [message #642239 is a reply to message #642111]

Tue, 30 November 2010 14:13

No real name

Messages: 4
Registered: November 2010

Junior Member

Chris, thank you for your input. We agree that our join needs to be corrected.

When we do a simple "Select c from Client", DTYPE is included within the select. Any idea why this is occuring?

SELECT CLIENT_ID, DTYPE, CLIENT_NAME FROM D001

This creates the following error message:
javax.ws.rs.WebApplicationException: javax.ejb.EJBTransactionRolledbackException: EJB Exception: ; nested exception is: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.2.v20100323-r6872): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00904: "DTYPE": invalid identifier Error Code: 904 Call: SELECT CLIENT_ID, DTYPE, CLIENT_NAME FROM D001 Query: ReadAllQuery(name="Client.findAll" referenceClass=Client sql="SELECT CLIENT_ID, DTYPE, CLIENT_NAME FROM D001");

Report message to a moderator