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.

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

This site is powered by Heroku

Heroku was created by developers, for developers. Get started today and find out why Heroku has been the platform of choice for brands like DEV for over a decade.

Sign Up

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay