Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Composite Key OneToOne Join
Composite Key OneToOne Join [message #841617] Wed, 11 April 2012 10:54 Go to next message
Ben Leov is currently offline Ben Leov
Messages: 3
Registered: April 2012
Junior Member
Hi Forum,

I'm having some problems using EclipseLink to do a join from one entity to another, both of which have composite primary keys.

I can't change the database structure. The table names and schemas are picked up correctly from an orm file which I haven't put here.

@Entity()
@Table()
public class Supplier implements Serializable {

        /** Contains the keys for the supplier, lets say sup_col1, sup_col2. */
 	@EmbeddedId()
	public SupplierPrimaryKey pk;
        
	@Column()
	public String someotherstuff, morestuff;

         /** I want to join the pk fields from this entity onto the pk fields
             of the account entity. */
        @OneToOne(targetEntity=Account.class)
	@JoinTable(joinColumns = { 
        @JoinColumn(name = "pk.account_col1", referencedColumnName = "pk.sup_col1"), 
        @JoinColumn(name = "pk.account_col2", referencedColumnName = "pk.sup_col2") })
	public Account account;



When I run this it tries to select on a non-existing supplier_account table. Can anybody point me in the right direction?

Thanks heaps for any help.


Re: Composite Key OneToOne Join [message #841652 is a reply to message #841617] Wed, 11 April 2012 11:37 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Remove the @JoinTable, you don't have one, this is not common in a OneToOne, normally just a @JoinColumn is used.

Try,
@JoinColumns({
@JoinColumn(name = "account_col1", referencedColumnName = "sup_col1"),
@JoinColumn(name = "account_col2", referencedColumnName = "sup_col2") })


James : Wiki : Book : Blog : Twitter
Re: Composite Key OneToOne Join [message #842269 is a reply to message #841652] Thu, 12 April 2012 02:19 Go to previous messageGo to next message
Ben Leov is currently offline Ben Leov
Messages: 3
Registered: April 2012
Junior Member
I just realized my (stupid) mistake. I have 3 fields in the Account's key, and only 2 in the Suppliers, so of course I get this error:

Exception Description: The @JoinColumns on the annotated element [field Account] from the entity class [class packages.Supplier] is incomplete. When the source entity class uses a composite primary key, a @JoinColumn must be specified for each join column using the @JoinColumns. Both the name and the referencedColumnName elements must be specified in each such @JoinColumn
.

If I change it to a OneToMany and use the JoinColumns as you have shown above, it works.

Thank you for your reply.
Re: Composite Key OneToOne Join [message #842373 is a reply to message #842269] Thu, 12 April 2012 04:31 Go to previous messageGo to next message
Ben Leov is currently offline Ben Leov
Messages: 3
Registered: April 2012
Junior Member
I'm not sure if I should create a new question for this. The above is working, but I cannot seem to select on the third pk column of the Account entity (The last one that I'm not joining on).

For example, I have this:

 
SELECT t FROM Supplier s 
JOIN t.account a 
WHERE s.pk.accountNumber = 1 
AND (s.stat = '20' OR s.stat= '90') 
AND a.pk.division='Something' 


The sql is executing, the suppliers are coming through and they are linked to their accounts, but the division is not limiting to 'Something'.

Is there something wrong with my JPQL or the entities?


[Updated on: Thu, 12 April 2012 04:33]

Report message to a moderator

Re: Composite Key OneToOne Join [message #846652 is a reply to message #842373] Mon, 16 April 2012 10:17 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Your Supplier has a OneToMany to Account, so there are many accounts for a Supplier.
You are querying all Suppliers who have "any" account with division='Something', so you will get these back.

The accounts of a Supplier are always the same for that Supplier object, it does not matter how you queried it. You cannot change an object just by a query.

Perhaps use,

SELECT s, a FROM Supplier s
JOIN s.account a 
WHERE s.pk.accountNumber = 1 
AND (s.stat = '20' OR s.stat= '90') 
AND a.pk.division='Something'


This will give you back Suppliers and Accounts.


James : Wiki : Book : Blog : Twitter
Previous Topic:Persisting new entity or changing an entity
Next Topic:Default Allocation size for sequence
Goto Forum:
  


Current Time: Wed Aug 20 10:49:30 EDT 2014

Powered by FUDForum. Page generated in 0.09341 seconds