Menu
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.
423
In hibernate Query object we need to specify the first result and max results by calling the setFirstResult() and setMaxResults() methods to limit the query results.
This problem occurs when Hibernate performs 1 query to select n entities and then has to perform an additional query for each of them to initialize a lazily fetched association. Hibernate fetches lazy relationships transparently so that this kind of problem is hard to find in your code.
In some cases it can happen Hibernate does not generate the most efficient statements, so then native SQL can be faster - but with native SQL your application loses the portability from one database to another, so normally is better to tune the hibernate mapping and the HQL statement to generate more efficient SQL ...
Depending on the number of selected Order entities, Hibernate might need to execute a huge number of queries that significantly slow down your application. This issue is easy to find. Hibernate's session statistics and Retrace provide you with the number of queries that were executed within a given session.
Hibernate can be fast. Designing a good schema, tuning your queries, and getting good performance is kind of an artform. Remember, under the covers its all sql anyway, so anything you can do with sql you can do with hibernate.
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.
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
