Positional Parameters with Hibernate Entity Manager

kakaroto · **Joined:** Tue May 29, 2007 12:33 am **Posts:** 13

Hibernate version: hibernate-3.2.2.GA, hibernate-entitymanager-3.3.0.GA, hibernate-annotations-3.3.0.GA

Hi all,

I'm using hibernate with entity manager and annotations. I have the following code (please note the positional parameter used):

Code:

Query query = getEntityManager().createQuery("SELECT f FROM Application a JOIN a.fields f WHERE a.id = ?1");
      query.setParameter(1, new Long(1));
      if (_logger.isInfoEnabled()) {
         _logger.info("About to execute query [ " + query + "]");
      }
      return query.getResultList();

The query is using one positional parameter

Code:

a.id = ?1

, following the syntax dictated by JSR 220 where positional parameters are formed by the '?' followed by an integer. When I execute it, I get the following exception:

Quote:

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query

And looking at the stack trace, I found this:

Quote:

Caused by: java.sql.SQLException: Unexpected token: 1 in statement [..]

If I remove the integer following the question mark (and hence, use positional parameters like plain jdbc prepared statement parameters), everything works OK. Am I missing something, because after reading hibernate docs and examples for hibernate entity manager, I see they use the JSR 220 syntax for positional parameters, and it is said to be supported.

As I see it, EJBQL query parameters are being parsed as normal HQL or JDBC prepared statement parameters.

Posting part of the stack trace here:

Code:

at org.hsqldb.jdbc.Util.throwError(Unknown Source)
    [junit]    at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
    [junit]    at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
    [junit]    at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:248)
    [junit]    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:302)
    [junit]    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [junit]    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    [junit]    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    [junit]    at java.lang.reflect.Method.invoke(Method.java:597)
    [junit]    at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.invoke(LazyConnectionDataSourceProxy.java:338)
    [junit]    at $Proxy23.prepareStatement(Unknown Source)
    [junit]    at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:497)
    [junit]    at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:415)
    [junit]    at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
    [junit]    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1538)
    [junit]    at org.hibernate.loader.Loader.doQuery(Loader.java:661)
    [junit]    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
    [junit]    at org.hibernate.loader.Loader.doList(Loader.java:2211)
    [junit]    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
    [junit]    at org.hibernate.loader.Loader.list(Loader.java:2090)
    [junit]    at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:912)
    [junit]    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
    [junit]    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
    [junit]    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
    [junit]    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:66)

Any help will be deeply appreciated, cheers!!!!

amila733 · **Posted:** Tue May 29, 2007 2:17 am

Hi

don't use 1 in sql query . hibernate will know parameter order.

Amila
(Don't forget to rate if helps)

kakaroto · **Joined:** Tue May 29, 2007 12:33 am **Posts:** 13

Hii, and thank you for your replyyyyy...

Yep, Hibernate works perfect with native and hql queries, but I was just curious to see if it supported the ejb (3.0) query syntax for positional parameters. I didn't want to use named parameters because then I'd have to hard code their names on my code..

Thanks anyway!!

max · **Posted:** Tue May 29, 2007 3:08 pm

it should work.

btw. what is better: hard coding random numbers or descriptive names in your code ?

kakaroto · **Joined:** Tue May 29, 2007 12:33 am **Posts:** 13

Hi,

Will test it again in a more isolated environment when I have the time, but as of right now it is not working with ?1 parameters.

I'm using positional parameters because, like it is said in the great book "Java Persistence with Hibernate" (referring to positional parameters):

Quote:

"They may be more conveient if you build complex queries programmatically, but the Criteria API is a much better alternative for that purpose."

Unfortunatelly, I'm using Hibernate as a persistence provider for jpa, and Criteria API is not available (hopefully something like that will be, in the near future).

Thanks for your reply.

Regards,

max · **Posted:** Wed May 30, 2007 11:23 am

so why don't you just use ? without the number ?

kakaroto · **Joined:** Tue May 29, 2007 12:33 am **Posts:** 13

That is what I'm doing right now ;)

Regards,