How to use named parameters in JPA queries

JPA Queries (6 Part Series)

1 SQL Select with IN clause from list with JPA
2 How to do a Select clause comparison of LocalDateTime in JPA
… 2 more parts…
3 How to use named queries with JPA and JPQL
4 How to use named parameters in JPA queries
5 Pagination with JPA queries
6 How to execute delete statements with jpa query

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.

JPA Queries (6 Part Series)

1 SQL Select with IN clause from list with JPA
2 How to do a Select clause comparison of LocalDateTime in JPA
… 2 more parts…
3 How to use named queries with JPA and JPQL
4 How to use named parameters in JPA queries
5 Pagination with JPA queries
6 How to execute delete statements with jpa query

原文链接：How to use named parameters in JPA queries