Join two tables with Play Framework and JPA

Question

How can I join two tables by using java play framework and jpa, I really have a hardtime converting my MySQL query to jpa query.

Here is the MySQL query that I used in my old Java code:

SELECT * FROM tbl_majors
INNER JOIN tbl_lookup_user_major
ON tbl_majors.id=tbl_lookup_user_major.majorId
WHERE tbl_lookup_user_major.userId=12

//Table 1:

@Entity
@Table(name="tbl_majors")
public class Major extends Model {
    public Major(){

    }
    @Column(name="major_name")
    private String name;
    @Column(name="major_desc")
    private String description;
}

//Table 2

@Entity
@Table(name="tbl_lookup_user_major")
public class LookupUserMajor extends Model {
    public LookupUserMajor(){

    }
    private int majorId;
    private int userId;
}

Answer 1

Dont know if I get the exact point here, but in the tutorial blog "YABE", this kind of join table is used and created automatically by Play :

http://www.playframework.org/documentation/1.2.4/guide6#tagging

The many-to-many relation is described in the Model (between "Post" and "Tag" here for the blog sample) :

@ManyToMany(cascade=CascadeType.PERSIST)
public Set<Tag> tags;

public Post(User author, String title, String content) {
    ...
    this.tags = new TreeSet<Tag>();
    ...
    this.title = title;
    this.content = content;
    ...
}

The YAML for the Posts data is :

Post(jeffPost):
    title:          The MVC application
    postedAt:       2009-06-06
    author:         jeff
    tags:           
                    - play
                    - architecture
                    - mvc

After running the app, I check the database and the table "post_tag" is automatically created and all the links between the two tables are done (post_ids and tags_ids are filled).

Retrieving data seems as easy as :

"select distinct p from Post p join p.tags as t"

Can someone confirm that ? Because new to Java and JPA and Play ^^

If this is correct, it looks easier than managing the join table "manually".

Answer 2

Every time you have a field names "xxxId" in an entity, and "xxxId" is the ID of another entity, you did something wrong. The point of JPA is to manipulate objects, and associations between objects using object references or object collections.

Your tbl_lookup_user_major looks like a join table to me. Such a join table means that you have a many-to-many (or one-to-many, is one of the IDs is unique) between Major and User. So, your Major entity should have the following field :

@ManyToMany
@JoinTable(...) // details omitted
private Set<User> users;

And your JPA query should look like

select m from Major m
inner join m.users user
where user.id = :userId