Saying position beyond number of declared ordinal parameters

Question

I want to perform a native/raw mysql query using hibernate, I have this:

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

I'm getting the error:

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's wrong here?

Answer 1

Use index as 0 since the parameter index start from 0.

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

Since you are using Hibernate, you can use the named parameter as well i.e.

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

Answer 2

The 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 specify that setPosition relies on zero based 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 out the parameters 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 has been some discussion regarding whether the setParameter() method is zero based or one based. This confusion is due to the exception received by the poster noting that parameters are 1 based, while the JavaDoc states that they are zero based. I analyzed the Hibernate source code and believe that they are in fact zero based. Assuming that I checked the right class, the underlying code uses a list to store the parameter bind values, which would imply that the setParameter method is in fact zero based. Checkout the source code for yourself: https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/internal/AbstractQueryImpl.java