wood burning stoves 2.0*
The moose likes Object Relational Mapping and the fly likes non-standard join in ManyToOne Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "non-standard join in ManyToOne" Watch "non-standard join in ManyToOne" New topic
Author

non-standard join in ManyToOne

Binu K Idicula
Ranch Hand

Joined: Jul 11, 2002
Posts: 99
Hi,

In a ManyToOne RelationShip, for example Employee(many) -> Company(One), is it always required that join ia allowed only based on a foreign key?

How can I make a join if there is no foreign key?


Thank you in advance
Binu K Idicula
Raf Szczypiorski
Ranch Hand

Joined: Aug 21, 2008
Posts: 383
If you want to join entities on properties that are not mapped as associations, you can do so using a JPQL query, and a theta-style join. In your example:

where Employee.fieldA and Company.fieldB are persistent properties of the entities. Of course, they must be of types that can be directly compared. It also has the limitation that is is inherently like an inner join, so that if null values for the properties used in the where clause exist, the entities will not be returned.

I am not sure if this is what you asked about, though... If not, please describe your problem more clearly.

Cheers.
[ November 28, 2008: Message edited by: Raf Szczypiorski ]
Binu K Idicula
Ranch Hand

Joined: Jul 11, 2002
Posts: 99
Hi Raf,

Thanks for the Quick Reply. Here is the situation in much detail

Class Person { int personId, String name, String street}
Class Address{ int addressId, String street, String location}

Now,

is it possible for me to define Address directly in Person using a @OneToOne annotation.

OR

is it possible for me to define Set<Person> in Address using a @ManyToOne annotation.

In Schema, street is NOT a foreign key.

regards
Binu K Idicula
Raf Szczypiorski
Ranch Hand

Joined: Aug 21, 2008
Posts: 383
Yes, you can (at least in Hibernate used as a JPA provider). Follow this code:


The tables are:
ADDRESS: ID, STREET, LOCATION
PERSON: ID, NAME, STREET - where street is not mapped as a persistent property, but rather as a key for the OneToOne association. Remeber that for OneToOne the STREET in ADDRESS table must be unique, Hibernate throws exceptions as it tries to join with address, but there are two (or more) addressses for a OneToOne relation.

The code to persist is standard:

You can do the same for OneToMany, in which case the ADDRESS.STREET doesn't have to be unique.

Hope this helps.
[ November 29, 2008: Message edited by: Raf Szczypiorski ]
Binu K Idicula
Ranch Hand

Joined: Jul 11, 2002
Posts: 99
Raf,
Thanks a lot for the detail with which you answered my doubt. It is extremely useful. I doubt why these examples are missing out from JPA examples and SUN Tutorials.
regards
Binu K Idicula
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: non-standard join in ManyToOne
 
Similar Threads
@ManyToOne & @JoinColumn
Game on SQL
make a entity class rows read-only in JPA
Hibernate join and key tag
difference between joinColumn and PrimaryKeyJoinColumni