| Composite Key OneToOne Join [message #841617] |
Wed, 11 April 2012 10:54  |
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   |
James Sutherland Messages: 1834 Registered: July 2009 |
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
|
|
|
|
| Re: Composite Key OneToOne Join [message #842373 is a reply to message #842269] |
Thu, 12 April 2012 04:31   |
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  |
James Sutherland Messages: 1834 Registered: July 2009 |
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
|
|
|
Powered by
FUDForum. Page generated in 0.12117 seconds