How to get field joined by third table in JPA.

Question

I have 3 tables:

object with object_id and field_name

object_form with object_id and form_id

and object_fields with form_id and field_desc

So I can get all the objects with field_names, but I need to get field_desc. In SQL it would be something like:

select o.object_id, f.field_desc 
from object o, object_form of, object_fields f
where o.object_id = of.object_id
and of.form_id = f.form_id

How can I do it in JPA?

Answer 1

Perhaps using a unidirectional one-to-many relationship mapping.

Hope this helps. RG

@Entity
@Table(name="OBJECT")
public class MyObject
{
    @Id
    @Column(name="OBJECT_ID")
    private int id;

    @Column(name="FIELD_NAME")
    private String fieldName;

    @OneToMany
    @JoinTable(name="OBJECT_FORM",
        joinColumns=@JoinColumn(name="OBJECT_ID"),
        inverseJoinColumns=@JoinColumn(name="FORM_ID"))
    private Collection<MyObjectField> objectFields;

    ...
}

@Entity
@Table(name="OBJECT_FIELDS")
public class MyObjectField
{
    @Id
    @Column(name="FORM_ID")
    private int id;

    @Column(name="FIELD_DESC")
    private String fieldDescription;

    ...
}

Answer 2

This is slightly counter-intuitive, but this is probably best mapped using @ManyToMany mapping.

Consider that your table structure accommodates the situation where a given form_id is associated with more than one object_id, and vice versa. Even if your business logic forbids this, this is the model of the relationship.

Check out this article and this code sample on how to do this. It's difficult to give a specific example since you've told us nothing about your class model.