SQL Select with IN clause from list with JPA

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

SELECT * FROM PARTNER where PARTNER_NUMBER IN ('id1', 'idn').

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

@Stateless
public class PartnerDataRepository {

    @Inject private EntityManager em;

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

      return query.getResultList();
    }
}

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

@Entity
@Access(AccessType.FIELD)
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@Table(name = PartnerData.TABLE_NAME)
@NamedQueries({
  @NamedQuery(
      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 =
      "findPartnerDataWithPartnerNumbers";

  //... rest ignored for brevity
}

---

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