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 ⭐🙏