12

I'm trying to get the following NamedQuery to work:

@NamedQuery(name="MyEntity.findByUser", query="SELECT m FROM MyEntity m WHERE m.owner = :user OFFSET :offset LIMIT :limit")

The problem is that this causes Hibernate to explode with the following stack-trace upon server startup:

[INFO] [talledLocalContainer] java.lang.NullPointerException
[INFO] [talledLocalContainer]   at org.hibernate.hql.ast.ParameterTranslationsImpl.getNamedParameterExpectedType(ParameterTranslationsImpl.java:63)
[INFO] [talledLocalContainer]   at org.hibernate.engine.query.HQLQueryPlan.buildParameterMetadata(HQLQueryPlan.java:296)
[INFO] [talledLocalContainer]   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:97)
[INFO] [talledLocalContainer]   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
[INFO] [talledLocalContainer]   at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
[INFO] [talledLocalContainer]   at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:400)
[INFO] [talledLocalContainer]   at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:351)
[INFO] [talledLocalContainer]   at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1291)
[INFO] [talledLocalContainer]   at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:713)
[INFO] [talledLocalContainer]   at org.hibernate.ejb.HibernatePersistence.createEntityManagerFactory(HibernatePersistence.java:121)
[INFO] [talledLocalContainer]   at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:83)
[INFO] [talledLocalContainer]   at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:60)
(...)

After some trial-and-error I found that replacing ":offset" and ":limit" with literal values (0 and 10, respectively) worked fine. Is there a reason for this, and is there a way to get the named parameters to work in my query?

I've seen some other examples that use positioned parameters to dynamically set the offset and limit values in a named query, but I'd rather not have my code degenerate into a bunch of unreadable query.setParameter(1, "someValue"); nonsense. Named parameters were supposed to get rid of that kind of garbage code.

aroth
  • 54,026
  • 20
  • 135
  • 176

1 Answers1

27

Hibernate has a special API for specifying these concepts at runtime. Try this:

@NamedQuery(name="MyEntity.findByUser", 
    query="SELECT m FROM MyEntity m WHERE m.owner = :user") // don't put OFFSET or LIMIT here

...

entityManager.createNamedQuery("MyEntity.findByUser") 
.setFirstResult(20) // equivalent to OFFSET
.setMaxResults(5) // equivalent to LIMIT
.getResultList();

I guess the reason it's done this way is that database vendors vary greatly as to how and where in the SQL query these concepts are specified, so it's not reasonable to pick one format over another, and too hard to try to convert between them.

This way, the dialect implementation knows clearly what needs to be done, and can then do it.

RAJ
  • 9,697
  • 1
  • 33
  • 63
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 5
    Thanks, that method works. Not sure I entirely agree about the reason why being obvious, however. The `OFFSET` and `LIMIT` keywords are part of the EJBQL spec. And if they're in the spec, they should be supported as such. Moreover, if support for them was to be dropped, it should have been dropped completely such that even using literal values in the query would fail. The current state of things where literal values work and parameterized values do not is a bug, in my opinion. – aroth Jul 04 '11 at 02:08
  • fair comment - removed "obvious" from post – Bohemian Jul 04 '11 at 07:34
  • @aroth: this happened to me as well, but this time it's a string param value. It works fine if I replace the parameter by hard value... I guess there's other error behind this fact – Hoàng Long Jul 17 '12 at 08:30