java Hibernate：不能对 OFFSET 和 LIMIT 使用命名参数吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6566576/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate: Can't use a named parameter for OFFSET and LIMIT?
提问by aroth
I'm trying to get the following NamedQueryto work:
我正在尝试使以下内容NamedQuery起作用：
@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:
问题是这会导致 Hibernate 在服务器启动时出现以下堆栈跟踪：
[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?
经过一些反复试验，我发现用文字值（分别为 0 和 10）替换 ":offset" 和 ":limit" 效果很好。有没有原因，有没有办法让命名参数在我的查询中工作？
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.
我见过一些其他示例，它们使用定位参数在命名查询中动态设置偏移量和限制值，但我不想让我的代码退化为一堆不可读的query.setParameter(1, "someValue");废话。命名参数应该摆脱那种垃圾代码。
回答by Bohemian
Hibernate has a special API for specifying these concepts at runtime. Try this:
Hibernate 有一个特殊的 API 用于在运行时指定这些概念。试试这个：
@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.
我想这样做的原因是数据库供应商在 SQL 查询中指定这些概念的方式和位置方面差异很大，因此选择一种格式而不是另一种格式是不合理的，并且很难尝试在它们之间进行转换。
This way, the dialect implementation knows clearly what needs to be done, and can then do it.
这样，方言实现就清楚需要做什么，然后才能去做。
