DEV Community

Cover image for How to use named parameters in JPA queries
Adrian Matei for Codever

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

2 2

How to use named parameters in JPA queries

Define a named parameter (:status) in a named query:

import javax.persistence.*;

@Entity
@Access(AccessType.FIELD)
@Table(name = PartnerInitialLoad.TABLE_NAME)
@NamedQueries({
  @NamedQuery(
      name = FIND_PARTNER_BY_STATUS ,
      query =
          "select p from PartnerInitialLoad p where p.status = :status order by p.partnernummer asc")
})
public class PartnerInitialLoad {
  public static final String TABLE_NAME = "T_PARTNER_INITIAL_LOAD";

  public static final String FIND_MAX_PARTNERNUMMER = "findMaxPartnernummer";
  public static final String FIND_PARTNER_BY_STATUS = "findPartnerByStatus";

  public static final String PARAM_STATUS = "status"; //the value here has to match the one in jpql, here "status"

 // further entity details emitted for brevity
}
Enter fullscreen mode Exit fullscreen mode

Then, set the named parameter in the created TypedQuery (em.createNamedQuery) with the setParameter method, which expects the name of the parameter (should match the one defined in the @NamedQuery) and its value:

@Stateless
public class PartnerInitialLoadRepository {

  @Inject private EntityManager em;

  public List<PartnerInitialLoad> getPartnersByStatus(Integer chunkSize, String status) {
    var query =
        em.createNamedQuery(PartnerInitialLoad.FIND_UNPROCESSED_PARTNER, PartnerInitialLoad.class);
    query.setParameter(PartnerInitialLoad.PARAM_STATUS, status);
    query.setMaxResults(chunkSize);

    return query.getResultList();
  }
}
Enter fullscreen mode Exit fullscreen mode

Same principle applies, if we create a collection-valued named parameters, to generate for example a SELECT IN clause sql as in the following snippet:

  public List<PartnerInitialLoad> getPartnersWithStatusInList(Integer chunkSize, List<String> statusList) {
   String sql="select p from PartnerInitialLoad p where p.status IN (:statusList) order by p.partnernummer asc"
    var query =
        em.createQuery(sql, PartnerInitialLoad.class);
    query.setParameter("statusList", status);
    query.setMaxResults(chunkSize);

    return query.getResultList();
  }
Enter fullscreen mode Exit fullscreen mode

Shared with ❤️ from Codever. Use 👉 copy to mine functionality to add it to your personal snippets collection.

Speedy emails, satisfied customers

Postmark Image

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

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