DEV Community

Codever profile image Adrian Matei
Adrian Matei for Codever

Posted on • Edited on • Originally published at codever.land

SQL Select with IN clause from list with JPA

#java #jpa #codever #snippets

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

SELECT * FROM PARTNER where PARTNER_NUMBER IN ('id1', 'idn').
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

@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(); } }
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:

@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 }
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)