Webnovel Test1108 73 Jyp-Wordcount-Test02

Courses ▼

Guides ▼

About ▼

JPA Query Parameters Usage

Last modified: September 7, 2019

by Erik Pragt

Persistence

JPA

I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:

>> CHECK OUT THE COURSE

1. Introduction

Building queries using JPA is not difficult; however, we sometimes forget simple things that make a huge difference.

One of these things is JPA query parameters, and this is what we are going to talk about.

2. What Are Query Parameters?

Let's start by explaining what query parameters are.

Query parameters are a way to build and execute parametrized queries. So, instead of:

SELECT * FROM employees e WHERE e.emp_number = '123';

We'd do:

SELECT * FROM employees e WHERE e.emp_number = ?;

By using a JDBC prepared statement, we need to set the parameter before executing the query:

pStatement.setString(1, 123);

3. Why Should We Use Query Parameters?

Instead of using query parameters we could have chosen to use literals, though, it's not the recommended way to do it, as we'll see now.

Let's rewrite the previous query to get employees by emp_number using the JPA API, but instead of using a parameter we'll use a literal so we can clearly ill.u.s.trate the situation:

String empNumber = "A123";

TypedQuery query = em.createQuery(

"SELECT e FROM Employee e WHERE e.empNumber = '" + empNumber + "'", Employee.cla.s.s);

Employee employee = query.getSingleResult();

This approach has some drawbacks:

Embedding parameters introduce a security risk making us vulnerable to JPQL injection attacks. Instead of the expected value, an attacker may inject any unexpected and possibly dangerous JPQL expression

Depending on the JPA implementation we use and the heuristics of our application, the query cache may get exhausted. A new query may get built, compiled and cached each time we do use it with each new value/parameter. At a minimum, it won't be efficient and it may also lead to an unexpected OutOfMemoryError

4. JPA Query Parameters

Similar to JDBC prepared statement parameters, JPA specifies two different ways to write parameterized queries by using:

Positional parameters

Named parameters

We may use either positional or named parameters but we must not mix them within the same query.

4.1. Positional Parameters

Using positional parameters is one way to avoid the aforementioned issues listed earlier.

Let's see how we would write such a query with the help of positional parameters:

TypedQuery query = em.createQuery(

"SELECT e FROM Employee e WHERE e.empNumber = ?1", Employee.cla.s.s);

String empNumber = "A123";

Employee employee = query.setParameter(1, empNumber).getSingleResult();

As we've seen within the previous example, we declare these parameters within the query by typing a question mark followed by a positive integer number. We'll start with 1 and move forward, incrementing it by one each time.

We may use the same parameter more than once within the same query which makes these parameters more similar to named parameters.

Parameter numbering is a very useful feature since it improves usability, readability, and maintenance.

However, it's worth mentioning that, as per the JPA specification, we cannot safely use this feature with native queries since the spec does not mandate it.  While some implementations may support it, it may impact the portability of our application.

4.2. Collection-Valued Positional Parameters

As previously stated, we may also use collection-valued parameters:

TypedQuery query = ent.i.tyManager.createQuery(

"SELECT e FROM Employee e WHERE e.empNumber IN (?1)" , Employee.cla.s.s);

List empNumbers = Arrays.asList("A123", "A124");

List employees = query.setParameter(1, empNumbers).getResultList();

4.3. Named Parameters

Named parameters are quite similar to positional parameters; however, by using them, we make the parameters more explicit and the query becomes more readable:

TypedQuery query = em.createQuery(

"SELECT e FROM Employee e WHERE e.empNumber = :number" , Employee.cla.s.s);

String empNumber = "A123";

Employee employee = query.setParameter("number", empNumber).getSingleResult();

The previous sample query is the same as the first one but we've used :number, a named parameter, instead of ?1.

We can see we declared the parameter with a colon followed by a string identifier (JPQL identifier) which is a placeholder for the actual value that will be set at runtime. Before executing the query, the parameter or parameters have to be set by issuing the setParameter method.

One interesting thing to remark is that the TypedQuery supports method chaining which becomes very useful when multiple parameters have to be set.

Let's go ahead and create a variation of the previous query using two named parameters to ill.u.s.trate the method chaining:

TypedQuery query = em.createQuery(

"SELECT e FROM Employee e WHERE e.name = :name AND e.age = :empAge" , Employee.cla.s.s);

String empName = "John Doe";

int empAge = 55;

List employees = query

.setParameter("name", empName)

.setParameter("empAge", empAge)

.getResultList();

Here, we're retrieving all employees with the given name and age. As we clearly see and one may expect, we can build queries with multiple parameters and as many occurrences of them as required.

If for some reason we do need to use the same parameter many times within the same query, we just need to set it once by issuing the "setParameter" method. At runtime, the specified values will replace each occurrence of the parameter.

Lastly, it's worth mentioning that the Java Persistence API specification does not mandate named parameters to be supported by native queries. Even when some implementations like Hibernate do support it, we need to take into account that if we do use it, the query will not be as portable.

4.4. Collection-Valued Named Parameters

For clarity, let's also demonstrate how this works with collection-valued parameters:

TypedQuery query = ent.i.tyManager.createQuery(

"SELECT e FROM Employee e WHERE e.empNumber IN (:numbers)" , Employee.cla.s.s);

List empNumbers = Arrays.asList("A123", "A124");

List employees = query.setParameter("numbers", empNumbers).getResultList();

As we can see, it works in a similar way to positional parameters.

5. Criteria Query Parameters

A JPA query may be built by using the JPA Criteria API, which Hibernate's official doc.u.mentation explains in great detail.

In this type of query, we represent parameters by using objects instead of names or indices.

Let's build the same query again but this time using the Criteria API to demonstrate how to handle query parameters when dealing with CriteriaQuery:

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery cQuery = cb.createQuery(Employee.cla.s.s);

Root c = cQuery.from(Employee.cla.s.s);

ParameterExpression paramEmpNumber = cb.parameter(String.cla.s.s);

cQuery.select(c).where(cb.equal(c.get(Employee_.empNumber), paramEmpNumber));

TypedQuery query = em.createQuery(cQuery);

String empNumber = "A123";

query.setParameter(paramEmpNumber, empNumber);

Employee employee = query.getResultList();

For this type of query, the parameter's mechanic is a little bit different since we use a parameter object but in essence, there's no difference.

Within the previous example, we can see the usage of the Employee_ cla.s.s. We generated this cla.s.s with the Hibernate metamodel generator. These components are part of the static JPA metamodel which allows criteria queries to be built in a strongly-typed manner.

6. Conclusion

In this article, we've focused on the mechanics of building queries by using JPA query parameters or input parameters.

We've learned that we have two types of query parameters, positional and named. It's up to us which one fits best our objectives.

It's also worth to take note that all query parameters must be single-valued except for in expressions. For inexpressions, we may use collection-valued input parameters, such as arrays or Lists as shown within the previous examples.

The source code of this tutorial, as usual, is available on GitHub.

I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:

>> CHECK OUT THE COURSE

Comments are closed on this article!

CATEGORIES

SPRING

REST

JAVA

SECURITY

PERSISTENCE

JACKSON

HTTP CLIENT-SIDE

KOTLIN

SERIES

JAVA "BACK TO BASICS" TUTORIAL

JACKSON JSON TUTORIAL

HTTPCLIENT 4 TUTORIAL

REST WITH SPRING TUTORIAL

SPRING PERSISTENCE TUTORIAL

SECURITY WITH SPRING

ABOUT

ABOUT BAELDUNG

THE COURSES

CONSULTING WORK

META BAELDUNG

THE FULL ARCHIVE

WRITE FOR BAELDUNG

EDITORS

OUR PARTNERS

ADVERTISE ON BAELDUNG

TERMS OF SERVICE

 PRIVACY POLICY

 COMPANY INFO

 CONTACT