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.

Sentry image

Hands-on debugging session: instrument, monitor, and fix

Join Lazar for a hands-on session where you’ll build it, break it, debug it, and fix it. You’ll set up Sentry, track errors, use Session Replay and Tracing, and leverage some good ol’ AI to find and fix issues fast.

RSVP here →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay