CODEWITHSUNDEEP
javasqljpa
Andrej
Andrej

Reputation: 391

SQL query to JPA Joins

just started using JPA today, so i'm preety new to it.
How would one say
"SELECT login.ID, userID, logInTime, logOutTime, user.ID AS 'uID', surname FROM login, user WHERE login.userID = user.ID" in JPA?
I need to join two tables via the userID.
I looked through a couple of tutorials on the net, but couldn't quite find the anwser to this.
Thanx!

Upvotes: 2

Views: 1753

Answers (3)

Jorge
Jorge

Reputation: 18237

Assuming that login and user are now objects, and also assuming that relation between login and user are that one user have many logins will be somthing like this

select 
       L.ID,
       u.userID, 
       L.logInTime, 
       L.logOutTime,
       u.ID 
from 
       User U, 
       IN(u.logins) L /*note that the IN makes the inner join between Login and user*/

Renember i'm assuming the mapping between the entities

Upvotes: 0

amphied
amphied

Reputation: 160

Wildly speculating I would guess that your domain model includes at least two entities User and Login with the latter representing something like a session. (cardinality User-1:n-Session)

My imaginary entities:

@Entity
class User {
    @OneToMany(mappedBy="user")
    private List<Login> logins;
    ...
}

@Entity
class Login {
    @ManyToOne
    private User;
    ...
}

To retrieve all Logins:

jpql: "from Login"

(The associated User objects are fetched by default. At the @ManyToOne side the default fetch type is eager fetching.)

To retrieve all Users:

jpql: "select u from User u join fetch u.logins"

(The associated List<Login> objects are not fetched by default. At the @OneToMany side the default fetch type is lazy fetching.)

Getting started with JPA: openJPA documentation (Take a look at chapter 4 and 5 for entity design and chapter 10 for JPQL. The general JPA parts of the openJPA documentation fit all JPA implementations.)

P.S. It would be easier if you put a bit more detail into your questions.

Upvotes: 3

Affe
Affe

Reputation: 47954

JPQL queries are written against objects, not tables. It is impossible to write a query without knowing what the objects are and how the relationship between them is defined.

You need to first create Entity objects that represent a Login and a User. You map a relationship between them, then that becomes the join for the query.

Upvotes: 2

Related Questions