Hibernate - Cannot set named parameter on SQLQuery

Question

I am unable to set parameters on a native SQL query using Hibernate; here is the code:

Session sess = HibernateUtil.getCurrentSession();
SQLQuery query = sess.createSQLQuery("SELECT * FROM :table WHERE :field = ':value'");       
query
    .addEntity(klass)
    .setString("table", table)
    .setString("field", field)
    .setString("value", value)
    .uniqueResult();

I am receiving an IllegalArgumentException; here is the top of the trace:

java.lang.IllegalArgumentException: Parameter value does not exist as a named parameter in [SELECT * FROM :table WHERE :field = ':value']
    at org.hibernate.internal.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:409)
    at org.hibernate.internal.AbstractQueryImpl.setString(AbstractQueryImpl.java:696)

EDIT:

Trying to use positional parameters, I still get an error:

    SQLQuery query = sess.createSQLQuery("SELECT * FROM ? WHERE ? = '?'");

    query
        .addEntity(klass)
        .setParameter(1, table)
        .setParameter(2, field)
        .setParameter(3, value)
        .uniqueResult();

Error:

org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 3
at org.hibernate.engine.query.spi.ParameterMetadata.getOrdinalParameterDescriptor(ParameterMetadata.java:80)
at org.hibernate.engine.query.spi.ParameterMetadata.getOrdinalParameterExpectedType(ParameterMetadata.java:86)

Possible duplicate of [JPA/Hibernate Native Queries do not recognize Parameters](http://stackoverflow.com/questions/3144235/jpa-hibernate-native-queries-do-not-recognize-parameters) — jds, Oct 07 '15 at 14:43
Tried that; new error: `Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 4` — jds, Oct 07 '15 at 15:06

score 2 · Answer 1 · answered Oct 07 '15 at 15:36

I've accepted Stanislav's answer, but for thoroughness, I'd like to document a number of errors in my code:

Despite the documentation (I'm using Hibernate 4.1), native SQL queries do not appear to support named parameters. At least, I could not get them to work with the above code.
Despite the error log (Remember that ordinal parameters are 1-based!), positional parameters are 0 based. You can see this in the documentation's examples.
Parameters automatically place quotation marks around themselves, preventing you from using them for the names of tables, columns, etc. For example, in my above code—after removing all quotation marks from the query—, my output query was:

SELECT * FROM 'my_table' WHERE 'my_column' = 'my_value';

This caused a SQL error. I had to resort to a combination of String formatting and parameter setting.

score 1 · Accepted Answer · answered Oct 07 '15 at 15:07

1

Try to change the parameters numbers, the first one need to be 0, not 1:

SQLQuery query = sess.createSQLQuery("SELECT * FROM ? WHERE ? = '?'");

query
    .addEntity(klass)
    .setParameter(0, table)
    .setParameter(1, field)
    .setParameter(2, value)
    .uniqueResult();

answered Oct 07 '15 at 15:07

Stanislav

27,441
9
87
82

The error log explicitly states the parameters are 1-based... What am I missing? – jds Oct 07 '15 at 15:17
hm, may be you have to try assign indexes manually, like ?1, ?2 – Stanislav Oct 07 '15 at 15:27

Hibernate - Cannot set named parameter on SQLQuery

2 Answers2