DEV Community

Adrian Matei for Codever

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

6

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').
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.

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Heroku

Simplify your DevOps and maximize your time.

Since 2007, Heroku has been the go-to platform for developers as it monitors uptime, performance, and infrastructure concerns, allowing you to focus on writing code.

Learn More

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay