java - 使用命名参数的 native 查询失败并显示 "Not all named parameters have been set"

Question

我想执行一个简单的原生查询，但它不起作用:

@Autowired
private EntityManager em;

Query q = em.createNativeQuery("SELECT count(*) FROM mytable where username = :username");
em.setProperty("username", "test");
(int) q.getSingleResult();

为什么会出现这个异常？

org.hibernate.QueryException: Not all named parameters have been set: [username]

Answer 1

JPA 在 native 查询中不支持命名参数，仅适用于 JPQL。您必须使用位置参数。

Named parameters follow the rules for identifiers defined in Section 4.4.1. The use of named parameters applies to the Java Persistence query language, and is not defined for native queries. Only positional parameter binding may be portably used for native queries.

所以，用这个

Query q = em.createNativeQuery("SELECT count(*) FROM mytable where username = ?1");
q.setParameter(1, "test");

虽然 JPA 规范不支持本地查询中的命名参数，但一些 JPA 实现(如 Hibernate)可能支持它

Native SQL queries support positional as well as named parameters

但是，这会将您的应用程序与特定的 JPA 实现结合起来，从而使其不可移植。