5

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;
}
Omid Monshizadeh
  • 1,514
  • 1
  • 10
  • 12
  • Use this link http://stackoverflow.com/questions/6562080/jpa-or-play-framework-list-from-query-joining-2-tables – vikas27 Oct 24 '11 at 08:59
  • @vikasTheJavaDeveloper: That'll work eventually, but JPA is quite a big technology stack. I find it frustrating and irritating that I always have to resort to native SQL, as if I were still using JDBC. I guess, the OP's inner join here can be achieved more elegantly, using JPA? – Lukas Eder Oct 24 '11 at 09:11
  • @user686563 and with what JPAQL did you came up to this moment? I don't see any reason why playframework is involved here, as it's plainly a JPA question – Piotr Nowicki Oct 24 '11 at 09:25

3 Answers3

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
JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
2

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".

jhice
  • 87
  • 5
  • Answered a little quickly. See this post for an alternative (similar) to the previous one : http://stackoverflow.com/questions/5127129/mapping-many-to-many-association-table-with-extra-columns – jhice Dec 15 '11 at 21:46
0

Example Jpa query try like this...

Query query = JPA.em().createQuery(" SELECT * FROM "+User.class.getName() +" AS a JOIN "+ 

Role.class.getName()+" AS b WHERE a.roleId=b.roleId ");
antyrat
  • 27,479
  • 9
  • 75
  • 76
user1252191
  • 49
  • 1
  • 7