JPA Query Parameters Usage – JPA查询参数的使用

最后修改: 2019年 8月 23日

中文/混合/英文(键盘快捷键：t)

1. Introduction

1.绪论

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

使用JPA构建查询并不困难；然而，我们有时会忘记一些简单的事情，而这些事情会带来巨大的变化。

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

其中之一是JPA查询参数，这就是我们在本教程中要关注的内容。

2. What Are Query Parameters?

2.什么是查询参数？

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:

通过使用JDBC准备的语句，我们需要在执行查询之前设置参数。

pStatement.setString(1, 123);

3. Why Should We Use Query Parameters?

3.我们为什么要使用查询参数？

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:

让我们重写前面的查询，通过emp_number使用JPA API来获取雇员，但我们不使用参数，而是使用一个字面，这样我们就可以清楚地说明情况。

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

JPA查询参数

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

与JDBC准备好的语句参数类似，JPA指定了两种不同的方式来编写参数化查询，即使用。

  • 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.

正如我们在前面的例子中所看到的，我们在查询中通过输入一个问号，然后是一个正整数来声明这些参数。我们将从1开始，然后向前推进，每次递增一个。

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.

值得一提的是，本地SQL查询也支持位置参数绑定。

4.2. Collection-Valued Positional Parameters

4.2.收集值的位置参数

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

4.3.命名的参数

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.

前面的示例查询与第一个相同，但我们使用了:number，一个命名参数，而不是?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.

我们可以看到，我们用冒号声明了参数，后面是一个字符串标识符（JPQL标识符），这是一个占位符，代表我们将在运行时设置的实际值。在执行查询之前，我们必须通过发布setParameter方法来设置参数或参数。

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

值得一提的是，TypedQuery支持方法链，这在需要设置多个参数时变得非常有用。

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.

如果由于某种原因，我们确实需要在同一个查询中多次使用同一个参数，我们只需要通过发布”setParameter“方法来设置一次。在运行时，指定的值将取代参数的每次出现。

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.

最后，值得一提的是，Java Persistence API规范并没有强制要求本地查询支持命名参数。即使一些实现，如Hibernate，确实支持它，我们也需要考虑到，如果我们真的使用它，查询将不那么容易移植。

4.4. Collection-Valued Named Parameters

4.4.集合值命名的参数

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

5.标准查询参数

A JPA query may be built by using the JPA Criteria API, which Hibernate’s official documentation explains in great detail.

可以通过使用JPA Criteria API来构建JPA查询，Hibernate的官方文档对此有非常详细的解释。

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:

让我们再次建立相同的查询，但这次使用 Criteria API 来演示在处理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.

在前面的例子中，我们可以看到Employee_类的用法。我们用Hibernate元模型生成器生成了这个类。这些组件是静态JPA元模型的一部分，它允许以强类型的方式构建标准查询。

6. Conclusion

6.结语

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

在这篇文章中，我们重点讨论了通过使用JPA查询参数或输入参数来构建查询的机制。

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.

还值得注意的是，除了in表达式，所有查询参数都必须是单值的。对于in表达式，我们可以使用集合值的输入参数，如数组或Lists，如前面的例子中所示。

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

像往常一样，本文的源代码可以在GitHub上找到。