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
}

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

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

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

Subscribe to our newsletter for more code resources and news

Adrian Matei (aka adixchen)

Adrian Matei (aka adixchen)
Life force expressing itself as a coding capable human being

Mongoose schema field from list of strings (enum)

Mongoose schema field from list of strings (enum) code snippet Continue reading

Set value of input field in angular template

Published on December 14, 2022

How to use jest test.each function

Published on December 14, 2022