CODEWITHSUNDEEP
javamysqljpaeclipselinkjpql
Ihsan Haikal
Ihsan Haikal

Reputation: 1215

JPQL for One to Many Relationship

I'm trying to create a JPQL for one to many relationship, namely user and device as 1 user can have one to many devices. I want to find the whole object of the device if it is owned by the user and the device name is correct.

If it is a SQL query then I can just do the query only for the device as follow:

select * from DEVICE where USER_ID = 2 and DEVICENAME = "mypc";

where USER_ID is the foreign key in DEVICE table. But how can I do the JPQL query for the user and device table? Here are some info for User and Device class

@Entity
public class User {
@Id
@GeneratedValue(strategy = GenerationType.TABLE)
public int id;
public String username;
public String firstname;
public String lastname;
public String hashPassword;

@OneToMany(mappedBy = "user")
public List<Device> devices = new ArrayList<Device>();


}

@Entity
public class Device {

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    public int id;
    public String deviceName;
    @ManyToOne
    public User user;
    String deviceOS;
    String deviceType;
    String attributes;
    boolean standard;
}

Upvotes: 4

Views: 27464

Answers (2)

Philipp Merkle
Philipp Merkle

Reputation: 2615

You can use JPQL path expressions in your query to navigate from one entity to another, or to refer to attributes of an entity.

More specifically, path expressions are useful to navigate along @ManyToOne or @OneToOnerelationships, e.g.:

SELECT d FROM Device d WHERE d.user.id = :userId AND d.deviceName = :deviceName

Here, d.user.id is a path expression.

For a general overview of JPQL, have a look at the great Wikibooks article.

Upvotes: 3

ooozguuur
ooozguuur

Reputation: 3476

Example JPA Query:Documention

Query query = entityManager.createQuery("SELECT di FROM Device di JOIN di.user u WHERE u.id=:userId and di.deviceName=:deviceName");
query.setParameter("userId",userId );
query.setParameter("deviceName",deviceName);
List<Device> result = query.getResultList();

Upvotes: 9

Related Questions