non-standard join in ManyToOne

[Binu K Idicula]

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]

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:

select e, c
from Employee e, Company c
where e.fieldA = c.fieldB

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]

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]

Yes, you can (at least in Hibernate used as a JPA provider). Follow this code:

package model;
 
import java.io.Serializable;
 
@Entity
public class Address implements Serializable {
 
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ADDRESS_GENERATOR")
    @SequenceGenerator(allocationSize = 20, initialValue = 1, name = "ADDRESS_GENERATOR", sequenceName = "ADDRESS_SEQ")
private long id;
 
private String location;
 
private String street;
 
private static final long serialVersionUID = 1L;
 
public long getId() {
return this.id;
}
 
public void setId(long id) {
this.id = id;
}   
 
public String getLocation() {
return this.location;
}
 
public void setLocation(String location) {
this.location = location;
}   
 
public String getStreet() {
return this.street;
}
 
public void setStreet(String street) {
this.street = street;
}
   
}

package model;
 
import java.io.Serializable;
 
@Entity
public class Person implements Serializable {
 
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "PERSON_GENERATOR")
    @SequenceGenerator(allocationSize = 20, initialValue = 1, name = "PERSON_GENERATOR", sequenceName = "PERSON_SEQ")
private long id;
 
private String name;
 
@OneToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "STREET", referencedColumnName = "STREET")
private Address address;
 
    private static final long serialVersionUID = 1L;
 
public long getId() {
return this.id;
}
 
public void setId(long id) {
this.id = id;
}   
 
public String getName() {
return this.name;
}
 
public void setName(String name) {
this.name = name;
}
 
    public Address getAddress() {
        return address;
    }
 
    public void setAddress(Address address) {
        this.address = address;
    }
 
   
}

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:

EntityManagerFactory emf = Persistence.createEntityManagerFactory("TestJPA");
EntityManager em = emf.createEntityManager();
Address a = new Address();
a.setLocation("City");
a.setStreet("Street");
 
Person p = new Person();
p.setName("Raf");
p.setAddress(a);
 
EntityTransaction et = em.getTransaction();
et.begin();
em.persist(p);
et.commit();

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]

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