Named query to find all entities based on list (instance variable) containing certain values

Question

is it possible using named queries to find all entities based on list containing a certain value.

I have an entity called User

public class User implements Serializable {

    private static final long serialVersionUID = -82485348776234092345L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @NotNull
    @Size(min = 5, max = 50)
    @Column(name = "email")
    private String email;
    @NotNull
    @Size(min = 5, max = 50)
    @Column(name = "password")
    private String password;
    @Column(name = "creationDate")
    @Temporal(TemporalType.TIMESTAMP)
    private Date creationDate;
    @OneToMany(fetch = FetchType.EAGER, mappedBy = "user", cascade = CascadeType.ALL)
    private List<Phone> phoneNumbers;


    /* Getters and Setters */
}

I am going to simplify the Phone entity for keeping this post clean:

public class Phone implements Serializable {

    private static final long serialVersionUID = -34672347534985L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @NotNull
    @Column(name = "number")
    private String number;
    @Column(name = "type")
    @Enumerated(EnumType.STRING)
    private EPhoneType phoneType;
    @Column(name = "creationDate")
    @Temporal(TemporalType.TIMESTAMP)
    private Date creationDate;
    @ManyToOne
    private User user;


    /* Getters and Setters */

}

Now I need to find all the users that have the phone number, lets say: 289647.... and Phone.type = 'MOBILE'

Phone type is an enum.

I am not sure how to acheive this using a named query. With regular native query I can get this done using a JOIN on the tables. Has anyone done something like this with a NamedQuery

Answer 1

First of all, you are probably confusing "named queries" with "JPQL" queries.

A JPA query can be either "native" and "JPQL" - the difference is the query language (SQL or OQL-like; the first is record-oriented, the second is object-oriented).

Both types of queries can be dynamic (built in runtime) or static (aka "named", defined as a static part of the persistence context, just like the entities). The latter can be optimized by creating and storing prepared queries to represent them.

Joining in OQL-like languages (HQL, JPQL) is done in a similar way as in SQL, but they don't represent joining relations (tables) but rather - "object attributes". When joining on an attribute, you don't define any criteria, because these are already part of the entity's definition. On the other hand, joining will create a row per association between entities, so you probably want to use distinct clause (to avoid having User repeated as many times, as a matching Phone appears on the phoneNumbers list).

Probably what you want is:

select u from User u left join u.phoneNumbers n where n.type = :type and n.number = :number

The query above can be used both as named or unnamed variety, to run it you have to provide named parameters type and number: an enum and a string.