javahibernatenamed-parametersejbql

Hibernate: Can't use a named parameter for OFFSET and LIMIT?

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.

Solution

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