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 with from Codever. ๐Ÿ‘‰ Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.

Codever is open source on Github โญ๐Ÿ™

Subscribe to our newsletter for more code resources and news

routerLink with query params in Angular html template

routerLink with query params in Angular html template code snippet Continue reading