Expand Authors Top

If you have a few years of experience in the Java ecosystem and you’d like to share that with the community, have a look at our Contribution Guidelines.

Expanded Audience – Frontegg – Security (partner)
announcement - icon User management is very complex, when implemented properly. No surprise here.

Not having to roll all of that out manually, but instead integrating a mature, fully-fledged solution - yeah, that makes a lot of sense.
That's basically what Frontegg is - User Management for your application. It's focused on making your app scalable, secure and enjoyable for your users.
From signup to authentication, it supports simple scenarios all the way to complex and custom application logic.

Have a look:

>> Elegant User Management, Tailor-made for B2B SaaS

NPI – JPA Buddy – JPA (partner)
announcement - icon

JPA is huge! It covers nearly every aspect of communication between relational databases and the Java application and is deeply integrated into all major frameworks.

If you're using IntelliJ, JPA Buddy is super helpful. The plugin gently guides you through the subtleties of the most popular JPA implementations, visually reminds you of JPA features, generates code that follows best practices, and integrates intelligent inspections to improve your existing persistence code.

More concretely, it provides powerful tooling to generate Spring Data JPA repositories and methods, Flyway Versioned Migrations, Liquibase Differential Changelogs, DDL and SQL statements, DTO objects, and MapStruct interfaces.

Oh, and it actually generates JPA entities from an existing database and gradually update the data model as the database evolves! Yeah.

>> Become a lot more productive with JPA Buddy

1. Introduction

Building queries using JPA isn't difficult; however, we sometimes forget simple things that make a huge difference.

One of these things is JPA query parameters, and that's what we'll focus on in this tutorial.

2. What Are Query Parameters?

Let's start by explaining what query parameters are.

Query parameters are a way to build and execute parameterized 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've used literals, although that'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 illustrate the situation:

String empNumber = "A123";
TypedQuery<Employee> query = em.createQuery(
  "SELECT e FROM Employee e WHERE e.empNumber = '" + empNumber + "'", Employee.class);
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 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<Employee> query = em.createQuery(
  "SELECT e FROM Employee e WHERE e.empNumber = ?1", Employee.class);
String empNumber = "A123";
Employee employee = query.setParameter(1, empNumber).getSingleResult();

As we've seen with 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.

It's worth mentioning that native SQL queries support positional parameter binding, as well.

4.2. Collection-Valued Positional Parameters

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

TypedQuery<Employee> query = entityManager.createQuery(
  "SELECT e FROM Employee e WHERE e.empNumber IN (?1)" , Employee.class);
List<String> empNumbers = Arrays.asList("A123", "A124");
List<Employee> 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<Employee> query = em.createQuery(
  "SELECT e FROM Employee e WHERE e.empNumber = :number" , Employee.class);
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 that we declared the parameter with a colon, followed by a string identifier (JPQL identifier), which is a placeholder for the actual value that we'll set at runtime. Before executing the query, we have to set the parameter or parameters by issuing the setParameter method.

One interesting thing to remark is that 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 illustrate the method chaining:

TypedQuery<Employee> query = em.createQuery(
  "SELECT e FROM Employee e WHERE e.name = :name AND e.age = :empAge" , Employee.class);
String empName = "John Doe";
int empAge = 55;
List<Employee> employees = query
  .setParameter("name", empName)
  .setParameter("empAge", empAge)
  .getResultList();

Here we're retrieving all employees with a 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.

Finally, it's worth mentioning that the Java Persistence API specification doesn't mandate that native queries support named parameters. Even when some implementations, like Hibernate, do support it, we need to take into account that if we do use it, the query won't be as portable.

4.4. Collection-Valued Named Parameters

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

TypedQuery<Employee> query = entityManager.createQuery(
  "SELECT e FROM Employee e WHERE e.empNumber IN (:numbers)" , Employee.class);
List<String> empNumbers = Arrays.asList("A123", "A124");
List<Employee> 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 documentation 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<Employee> cQuery = cb.createQuery(Employee.class);
Root<Employee> c = cQuery.from(Employee.class);
ParameterExpression<String> paramEmpNumber = cb.parameter(String.class);
cQuery.select(c).where(cb.equal(c.get(Employee_.empNumber), paramEmpNumber));

TypedQuery<Employee> 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_ class. We generated this class 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 focused on the mechanics of building queries by using JPA query parameters or input parameters.

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

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

As usual, the source code for this article is available over on GitHub.

Persistence bottom

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

>> CHECK OUT THE COURSE
Persistence footer banner
Comments are closed on this article!