Hibernate native query with positional parameters
Solution 1
You call
session.createSQLQuery
but I believe you need to call
session.getNamedQuery
when using a named query.
Solution 2
Please check
.setParameter(1, subjectId)
.setParameter(2, userId)
as the exception trace says that ordinal parameters are 1-based!
Solution 3
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.
Please read Named SQL queries in : http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html#d0e13930
Solution 4
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.
Ray
Updated on July 05, 2020Comments
-
Ray almost 4 years
I wrote native sql query instead of using hql and faced with roblem
Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 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
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.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.
How I can decide this problem?