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