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 — jds
– jds, Commented Oct 7, 2015 at 14:43
Tried that; new error: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 4 — jds
– jds, Commented Oct 7, 2015 at 15:06

jds · Accepted Answer · 2015-10-07 15:36:13Z

2

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.

answered Oct 7, 2015 at 15:36

jds

8,31013 gold badges69 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stanislav · Accepted Answer · 2015-10-07 15:07:46Z

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 7, 2015 at 15:07

Stanislav

28.3k9 gold badges91 silver badges84 bronze badges

2 Comments

jds Over a year ago

The error log explicitly states the parameters are 1-based... What am I missing?

Stanislav Over a year ago

hm, may be you have to try assign indexes manually, like ?1, ?2

Collectives™ on Stack Overflow

Hibernate - Cannot set named parameter on SQLQuery

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related