JPQL for One to Many Relationship

Question

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;
}

Solution 1

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();

Solution 2

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.