java 带有位置参数的 Hibernate 本机查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13733384/
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
Hibernate native query with positional parameters
提问by Ray
I wrote native sql query instead of using hql and faced with roblem
我写了原生 sql 查询而不是使用 hql 并面临 roblem
Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1
位置超出声明的序数参数数量。请记住，序数参数是从 1 开始的！职位：1
<sql-query name="GET_ARRAY_MAX_POINT_QUESTION">
<![CDATA[
select TEST.TEST_ID as testId, TEST.VERSION_ID as versionId,
PASSED_TEST.RESULT as userResult,
PASSED_TEST.TIME_COMPLITED as timeComplited,
sum(COMPLEXITY) as maxTestResult from QUESTION
JOIN TEST_QUESTION ON QUESTION.QUESTION_ID = TEST_QUESTION.QUESTION_ID
JOIN TEST ON TEST.TEST_ID=TEST_QUESTION.TEST_ID
JOIN PASSED_TEST ON TEST.TEST_ID=PASSED_TEST.TEST_ID
AND TEST.VERSION_ID=PASSED_TEST.VERSION_ID
WHERE TEST.SUBJECT_ID = ?
AND PASSED_TEST.USER_ID = ?
GROUP BY PASSED_TEST.TEST_EVENT_ID
]]>
</sql-query>
And DAO
和 DAO
return session
.createSQLQuery(GET_ARRAY_MAX_POINT_QUESTION_NAME_QUERY)
.addScalar(TEST_ID_RESULT_PARAM, StandardBasicTypes.LONG)
.addScalar(VERSION_ID_RESULT_PARAM, StandardBasicTypes.LONG)
.addScalar(USER_RESULT_PARAM, StandardBasicTypes.DOUBLE)
.addScalar(MAX_TEST_RESULT_PARAM, StandardBasicTypes.DOUBLE)
.addScalar(TIME_COMPLITED_RESULT_PARAM, StandardBasicTypes.DATE)
.setParameter(0, subjectId)
.setParameter(1, userId)
.setResultTransformer(
Transformers.aliasToBean(PassedTestStatistic.class))
.list();
I read that //JPA specification. Only positional parameter binding may be portably used for native queries.And that hibernate use 0 as first index.
我读到那个//JPA specification. Only positional parameter binding may be portably used for native queries.hibernate 使用 0 作为第一个索引。
stacktrace
堆栈跟踪
Caused by: org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1
at org.hibernate.engine.query.spi.ParameterMetadata.getOrdinalParameterDescriptor(ParameterMetadata.java:80)
at org.hibernate.engine.query.spi.ParameterMetadata.getOrdinalParameterExpectedType(ParameterMetadata.java:86)
at org.hibernate.internal.AbstractQueryImpl.determineType(AbstractQueryImpl.java:444)
at org.hibernate.internal.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:416)
at by.bsuir.testapp.database.hibernate.PassedTestHibernateDAO.getDataForPassedTestStatisticGraph(PassedTestHibernateDAO.java:73)
at by.bsuir.testapp.service.PassedTestServiceImpl.getDataForPassedTestStatisticGraph(PassedTestServiceImpl.java:58)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy28.getDataForPassedTestStatisticGraph(Unknown Source)
at by.bsuir.testapp.controller.StatisticPassedTest.createLinearModel(StatisticPassedTest.java:61)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.apache.el.parser.AstValue.invoke(AstValue.java:262)
at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:278)
//UPDATE
//更新
Interesting, when I set in query number values
有趣的是，当我设置查询数字值时
WHERE TEST.SUBJECT_ID = 1
AND PASSED_TEST.USER_ID = 1
I get
我得到
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GET_ARRAY_MAX_POINT_QUESTION' at line 1
But in MySQL I get sucessful result.
但是在 MySQL 中我得到了成功的结果。
How I can decide this problem?
我如何决定这个问题？
回答by grekier
You call
你打电话
session.createSQLQuery
but I believe you need to call
但我相信你需要打电话
session.getNamedQuery
when using a named query.
使用命名查询时。
回答by Rahul
Please check
请检查
.setParameter(1, subjectId)
.setParameter(2, userId)
as the exception trace says that ordinal parameters are 1-based!
正如异常跟踪所说，序数参数是基于 1 的！
回答by Rahul
Can you try using named parameter instead of providing positional parameter.
您可以尝试使用命名参数而不是提供位置参数。
For eg.
例如。
session
.createSQLQuery("update table1 set variable = variable + 1 where id = :id")
.setParameter("id", someId)
.executeUpdate();
Native SQL queries support positional as well as named parameters.
本机 SQL 查询支持位置和命名参数。
Please read Named SQL queries in : http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html#d0e13930
请阅读命名 SQL 查询：http: //docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html#d0e13930
回答by Ryan Maloney
Looking at your source
看你的来源
createSQLQuery(GET_ARRAY_MAX_POINT_QUESTION_NAME_QUERY)
you must have variable pointing to a string containing the actual name of the native query like so:
您必须让变量指向一个包含本机查询实际名称的字符串，如下所示：
GET_ARRAY_MAX_POINT_QUESTION_NAME_QUERY = "GET_ARRAY_MAX_POINT_QUESTION"
The error you get here:
你在这里得到的错误：
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GET_ARRAY_MAX_POINT_QUESTION' at line 1
Indicates that createSqlQuery isn't finding your native query by name but is instead trying to execute the name "GET_ARRAY_MAX_POINT_QUESTION" as raw SQL just as if you'd put in s.createSqlQuery("Select * from...") If you look at the documentation here, you will see that in order to look up a named query, even a native one, you still have to use getNamedQuery.
表示 createSqlQuery 没有按名称查找您的本机查询，而是尝试将名称“GET_ARRAY_MAX_POINT_QUESTION”作为原始 SQL 执行，就像您放入 s.createSqlQuery("Select * from...") 如果您看在此处的文档中，您将看到为了查找命名查询，即使是本机查询，您仍然必须使用 getNamedQuery。
回答by user3132194
For those who googled the error message: Got this error when I had no mapping for a class of interest at all. Mapping solved the problem.
对于那些在谷歌上搜索错误消息的人：当我根本没有感兴趣的类的映射时出现这个错误。映射解决了这个问题。
