Java 带有命名参数的本机查询失败，并显示“未设置所有命名参数”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28829818/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Native query with named parameter fails with "Not all named parameters have been set"
提问by membersound
I want to execute a simple native query, but it does not work:
我想执行一个简单的本机查询，但它不起作用：
@Autowired
private EntityManager em;
Query q = em.createNativeQuery("SELECT count(*) FROM mytable where username = :username");
em.setProperty("username", "test");
(int) q.getSingleResult();
Why am I getting this exception?
为什么我会收到此异常？
org.hibernate.QueryException: Not all named parameters have been set: [username]
采纳答案by Predrag Maric
Named parameters are not supported by JPA in native queries, only for JPQL. You must use positional parameters.
JPA 在本机查询中不支持命名参数，仅适用于 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.
命名参数遵循第 4.4.1 节中定义的标识符规则。命名参数的使用适用于 Java Persistence 查询语言，并没有为本地查询定义。只有位置参数绑定可以可移植地用于本机查询。
So, use this
所以，用这个
Query q = em.createNativeQuery("SELECT count(*) FROM mytable where username = ?1");
q.setParameter(1, "test");
While JPA specification doesn't support named parameters in native queries, some JPA implementations (like Hibernate) may support it
虽然 JPA 规范不支持本机查询中的命名参数，但一些 JPA 实现（如Hibernate）可能支持它
Native SQL queries support positional as well as named parameters
本机 SQL 查询支持位置和命名参数
However, this couples your application to specific JPA implementation, and thus makes it unportable.
但是，这会将您的应用程序耦合到特定的 JPA 实现，从而使其不可移植。
回答by pL4Gu33
Use set Parameter from query.
使用查询中的设置参数。
Query q = (Query) em.createNativeQuery("SELECT count(*) FROM mytable where username = ?1");
q.setParameter(1, "test");
回答by lekant
This was a bug fixed in version 4.3.11 https://hibernate.atlassian.net/browse/HHH-2851
这是 4.3.11 版本中修复的错误 https://hibernate.atlassian.net/browse/HHH-2851
EDIT:Best way to execute a native query is still to use NamedParameterJdbcTemplateIt allows you need to retrieve a result that is not a managed entity ; you can use a RowMapperand even a Map of named parameters!
编辑：执行本机查询的最佳方法仍然是使用NamedParameterJdbcTemplate它允许您需要检索不是托管实体的结果；您可以使用RowMapper甚至命名参数的Map！
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
@Autowired
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
final List<Long> resultList = namedParameterJdbcTemplate.query(query,
mapOfNamedParamters,
new RowMapper<Long>() {
@Override
public Long mapRow(ResultSet rs, int rowNum) throws SQLException {
return rs.getLong(1);
}
});
回答by Ahmed Salem
After many tries I found that you should use createNativeQueryAnd you can send parameters using #replacement
经过多次尝试，我发现您应该使用createNativeQuery并且您可以使用#替换发送参数
In my example
在我的例子中
String UPDATE_lOGIN_TABLE_QUERY = "UPDATE OMFX.USER_LOGIN SET LOGOUT_TIME = SYSDATE WHERE LOGIN_ID = #loginId AND USER_ID = #userId";
Query query = em.createNativeQuery(logQuery);
query.setParameter("userId", logDataDto.getUserId());
query.setParameter("loginId", logDataDto.getLoginId());
query.executeUpdate();
回答by Jeff_Alieffson
I use EclipseLink. This JPA allows the following way for the native queries:
我使用 EclipseLink。此 JPA 允许使用以下方式进行本机查询：
Query q = em.createNativeQuery("SELECT * FROM mytable where username = ?username");
q.setParameter("username", "test");
q.getResultList();
回答by oberlies
You are calling setPropertyinstead of setParameter. Change your code to
您正在调用setProperty而不是setParameter. 将您的代码更改为
Query q = em.createNativeQuery("SELECT count(*) FROM mytable where username = :username");
em.setParameter("username", "test");
(int) q.getSingleResult();
and it should work.
它应该工作。
