CODEWITHSUNDEEP
javahibernate
jds
jds

Reputation: 8259

Hibernate - Cannot set named parameter on SQLQuery

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)

Upvotes: 1

Views: 3496

Answers (2)

jds
jds

Reputation: 8259

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

  1. 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.
  2. 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.

  3. 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.

Upvotes: 2

Stanislav
Stanislav

Reputation: 28096

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();

Upvotes: 1

Related Questions