Saying a position outside the number of declared ordinal parameters

Question

I want to execute my own / raw mysql query using hibernate, I have this:

sessionFactory.getCurrentSession().createSQLQuery( "update table1 set someCounter = someCounter + 1 where id = ?") .setParameter(1, someId) .executeUpdate(); 

I get an error message:

 threw exception [Request processing failed; nested exception is org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 2] with root cause org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 2 

What is wrong here?

Answer 1

Use index as 0 , since the parameter index starts at 0 .

 sessionFactory.getCurrentSession() .createSQLQuery("update table1 set someCounter = someCounter + 1 where id = ?") .setParameter(0, someId) .executeUpdate(); 

Since you are using Hibernate, you can use a named parameter as well as ie

 sessionFactory.getCurrentSession() .createSQLQuery("update table1 set someCounter = someCounter + 1 where id = :id") .setParameter("id", someId) .executeUpdate(); 

Answer 2

Parameters use a zero-based index. Try:

  sessionFactory.getCurrentSession().createSQLQuery("update table1 set someCounter = someCounter + 1 where id = ?") .setParameter(0, someId) .executeUpdate(); 

The current Hibernate JavaDocs also states that setPosition relies on zero-position indexing for positional parameters. http://docs.jboss.org/hibernate/orm/4.1/javadocs/org/hibernate/Query.html#setParameter%28int,%20java.lang.Object%29

 setParameter Query setParameter(int position, Object val) throws HibernateException Bind a value to a JDBC-style query parameter. The Hibernate type of the parameter is first detected via the usage/position in the query and if not sufficient secondly guessed from the class of the given object. Parameters: position - the position of the parameter in the query string, numbered from 0. val - the non-null parameter value Throws: HibernateException - if no type could be determined 

Check the options section of this document: https://access.redhat.com/knowledge/docs/en-US/JBoss_Enterprise_Web_Server/1.0/html/Hibernate_Core_Reference_Guide/querysql.html#id3043464

There was some discussion as to whether the setParameter () method is based on zero or on the basis of one. This confusion is caused by the exception received by the poster, noting that the parameters are based on 1, while the JavaDoc claims that they are based on a zero value. I have analyzed the Hibernate source code and believe that they are based on a zero value. Assuming I checked the correct class, the base code uses a list to store parameter binding values, which implies that the setParameter method is actually based on a null value. Check the source code for yourself: https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/internal/AbstractQueryImpl.java

Answer 3

Positional parameters start at 0 not 1

Native SQL queries support positional as well as named parameters:

update your request by passing 0 instead of 1 to setParameter(1, someId)

 sessionFactory.getCurrentSession().createSQLQuery("update table1 set someCounter = someCounter + 1 where id = ?") .setParameter(0, someId) .executeUpdate(); 

resource parameters

Answer 4

For those of you who cannot solve the problem based on the above solution, sometimes it seems that hibernate (depending on the version I'm using 3) is actually throwing the wrong error, for example, the problem may be present if you make a syntax error in sleep q language:

 find("from Student s where s.id = :id") 

change this to:

 find("from Student s where s.id = ?") 

actually solved this problem. I assume that my main point is that you should also look at your syntax for the bc problem, which seems to be hibernate may incorrectly mark this exception.