DEV Community

Adrian Matei for Codever

Posted on • Updated on • Originally published at

SQL Select with IN clause from list with JPA

The native SQL query we want to map in JPA is similar to the following:

Enter fullscreen mode Exit fullscreen mode

With JPA you can use a TypedQuery for example and set the expected list of the IN clause directly as query parameter

public class PartnerDataRepository {

    @Inject private EntityManager em;

    public List<PartnerData> findPartnerDataFromList(
        List<String> partnerNumbers) {
      TypedQuery<PartnerData> query =
              PartnerData.FIND_PARTNER_DATA_IN_LIST, PartnerData.class);
      query.setParameter(PartnerData.PARTNER_NUMBERS, partnerNumbers);

      return query.getResultList();
Enter fullscreen mode Exit fullscreen mode

In the named query itself you can pass the parameter with : as you would when setting a "normal" parameter:

@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@Table(name = PartnerData.TABLE_NAME)
      name = PartnerData.FIND_PARTNER_DATA_IN_LIST,
      query = "select m from PartnerData m where partnerNumber in :partnerNumbers")
public class PartnerData {
  public static final String TABLE_NAME = "PARTNER";

  public static final String PARTNER_NUMBERS = "partnerNumbers";
  public static final String FIND_PARTNER_DATA_IN_LIST =

  //... rest ignored for brevity

Enter fullscreen mode Exit fullscreen mode

Shared ❤️ from Codever. 👉 use the copy to mine functionality to add it to your personal snippets collection.

Top comments (0)