Basic Hibernate/JPA Mapping question

Question

I have to tables I want to map to each other. I want to populate 2 drop down lists: code_r and code_l. When i choose a value from code_r, code_l should display only certain records. In my database I have 2 tables:

Table code_r
===================
CODE         INT
LIBELLE      VARCHAR

And

Table code_l
===================
ID           BIGINT
CODE_R_ID    INT
LIBELLE      VARCHAR

One code_r can have multiple code_l associated with it (based on the code_r_id (not a defined as a Foreign key in the code_l definition). Of course, a code_l can only be associated to one code_r.

The following SQL query works fine:

SELECT * 
FROM code_r r
 left join `code_l` l on l.code_r_id = r.code;

How should I implement that using using JPA/Hibernate-3.5 annotations in the CodeR and CodeL classes??

Any help would be appreciated. Thanks in advance.

Answer 1

in the CodeR class:

@OneToMany(mappedBy="code_r_id")

Collection elementsFromL;

---

in the CodeL class:

@ManyToOne

CodeR code_r_id;

Answer 2

With Hibernate (and now standardized in JPA 2.0), you can use a unidirectional one-to-many association without a join table using a JoinColumn annotation:

Annotate the CodeR like this:

@Entity
public class CodeR {
   @Id
   private Integer code;
   private String libelle;

   @OneToMany
   @JoinColumn(name="CODE_R_ID")
   Set<CodeL> codeLs = new HashSet<CodeL>():

   // getters, setters
}   

And CodeL

@Entity
public class CodeL {
   @Id
   private Integer id;
   private String libelle;

   // getters, setters, equals, hashCode
}   

And the JPQL query:

SELECT r FROM CodeR LEFT JOIN  r.codeLs