Java Hibernate Query By Example 和 Projections
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/84644/
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 Query By Example and Projections
提问by
To make it short: hibernate doesn't support projections and query by example? I found this post:
简而言之：休眠不支持投影和示例查询？我找到了这个帖子：
The code is this:
代码是这样的：
User usr = new User();
usr.setCity = 'TEST';
getCurrentSession().createCriteria(User.class)
.setProjection( Projections.distinct( Projections.projectionList()
.add( Projections.property("name"), "name")
.add( Projections.property("city"), "city")))
.add( Example.create(usr))
Like the other poster said, The generated sql keeps having a where class refering to just y0_= ? instead of this_.city.
就像另一张海报说的那样，生成的 sql 一直有一个 where 类只引用 y0_= ？而不是 this_.city。
I already tried several approaches, and searched the issue tracker but found nothing about this.
我已经尝试了几种方法，并搜索了问题跟踪器，但一无所获。
I even tried to use Projection alias and Transformers, but it does not work:
我什至尝试使用投影别名和变压器，但它不起作用：
User usr = new User();
usr.setCity = 'TEST';
getCurrentSession().createCriteria(User.class)
.setProjection( Projections.distinct( Projections.projectionList()
.add( Projections.property("name"), "name")
.add( Projections.property("city"), "city")))
.add( Example.create(usr)).setResultTransformer(Transformers.aliasToBean(User.class));
Has anyone used projections and query by example ?
有没有人使用过投影和示例查询？
采纳答案by Arthur Thomas
Can I see your User class? This is just using restrictions below. I don't see why Restrictions would be really any different than Examples (I think null fields get ignored by default in examples though).
我可以看看你的 User 类吗？这只是使用下面的限制。我不明白为什么 Restrictions 与 Example 有任何不同（尽管我认为在示例中默认情况下会忽略空字段）。
getCurrentSession().createCriteria(User.class)
.setProjection( Projections.distinct( Projections.projectionList()
.add( Projections.property("name"), "name")
.add( Projections.property("city"), "city")))
.add( Restrictions.eq("city", "TEST")))
.setResultTransformer(Transformers.aliasToBean(User.class))
.list();
I've never used the alaistToBean, but I just read about it. You could also just loop over the results..
我从未使用过 alaistToBean，但我只是阅读了它。你也可以只循环结果..
List<Object> rows = criteria.list();
for(Object r: rows){
Object[] row = (Object[]) r;
Type t = ((<Type>) row[0]);
}
If you have to you can manually populate User yourself that way.
如果您必须这样做，您可以自己手动填充 User 。
Its sort of hard to look into the issue without some more information to diagnose the issue.
如果没有更多信息来诊断问题，就很难调查问题。
回答by Ryan Cook
The problem seems to happen when you have an alias the same name as the objects property. Hibernate seems to pick up the alias and use it in the sql. I found this documented hereand here, and I believe it to be a bug in Hibernate, although I am not sure that the Hibernate team agrees.
当您有一个与 objects 属性同名的别名时，问题似乎就会发生。Hibernate 似乎拿起别名并在 sql 中使用它。我在这里和这里找到了这个文档，我相信它是 Hibernate 中的一个错误，尽管我不确定 Hibernate 团队是否同意。
Either way, I have found a simple work around that works in my case. Your mileage may vary. The details are below, I tried to simplify the code for this sample so I apologize for any errors or typo's:
无论哪种方式，我都找到了一个简单的解决方法，适用于我的情况。你的旅费可能会改变。详细信息如下，我试图简化此示例的代码，因此对于任何错误或错字，我深表歉意：
Criteria criteria = session.createCriteria(MyClass.class)
.setProjection(Projections.projectionList()
.add(Projections.property("sectionHeader"), "sectionHeader")
.add(Projections.property("subSectionHeader"), "subSectionHeader")
.add(Projections.property("sectionNumber"), "sectionNumber"))
.add(Restrictions.ilike("sectionHeader", sectionHeaderVar)) // <- Problem!
.setResultTransformer(Transformers.aliasToBean(MyDTO.class));
Would produce this sql:
会产生这个sql：
select
this_.SECTION_HEADER as y1_,
this_.SUB_SECTION_HEADER as y2_,
this_.SECTION_NUMBER as y3_,
from
MY_TABLE this_
where
( lower(y1_) like ? )
Which was causing an error: java.sql.SQLException: ORA-00904: "Y1_": invalid identifier
这导致了错误：java.sql.SQLException: ORA-00904: "Y1_": invalid identifier
But, when I changed my restriction to use "this", like so:
但是，当我将限制更改为使用“this”时，如下所示：
Criteria criteria = session.createCriteria(MyClass.class)
.setProjection(Projections.projectionList()
.add(Projections.property("sectionHeader"), "sectionHeader")
.add(Projections.property("subSectionHeader"), "subSectionHeader")
.add(Projections.property("sectionNumber"), "sectionNumber"))
.add(Restrictions.ilike("this.sectionHeader", sectionHeaderVar)) // <- Problem Solved!
.setResultTransformer(Transformers.aliasToBean(MyDTO.class));
It produced the following sql and my problem was solved.
它产生了以下sql，我的问题就解决了。
select
this_.SECTION_HEADER as y1_,
this_.SUB_SECTION_HEADER as y2_,
this_.SECTION_NUMBER as y3_,
from
MY_TABLE this_
where
( lower(this_.SECTION_HEADER) like ? )
Thats, it! A pretty simple fix to a painful problem. I don't know how this fix would translate to the query by example problem, but it may get you closer.
就是这样！一个非常简单的解决一个痛苦的问题。我不知道此修复程序如何通过示例问题转换为查询，但它可能会让您更接近。
回答by VHristov
I'm facing a similar problem. I'm using Query by Example and I want to sort the results by a custom field. In SQL I would do something like:
我面临着类似的问题。我正在使用按示例查询，我想按自定义字段对结果进行排序。在 SQL 中，我会执行以下操作：
select pageNo, abs(pageNo - 434) as diff
from relA
where year = 2009
order by diff
It works fine without the order-by-clause. What I got is
它在没有 order-by-clause 的情况下工作正常。我得到的是
Criteria crit = getSession().createCriteria(Entity.class);
crit.add(exampleObject);
ProjectionList pl = Projections.projectionList();
pl.add( Projections.property("id") );
pl.add(Projections.sqlProjection("abs(`pageNo`-"+pageNo+") as diff", new String[] {"diff"}, types ));
crit.setProjection(pl);
But when I add
但是当我添加
crit.addOrder(Order.asc("diff"));
I get a org.hibernate.QueryException: could not resolve property: diffexception. Workaround with thisdoes not work either.
我得到一个org.hibernate.QueryException: could not resolve property: diff异常。解决方法与此也不管用。
PS: as I could not find any elaborate documentation on the use of QBE for Hibernate, all the stuff above is mainly trial-and-error approach
PS：由于我找不到任何关于在 Hibernate 中使用 QBE 的详细文档，因此上述所有内容主要是试错法
回答by Dobes Vandermeer
The real problem here is that there is a bug in hibernate where it uses select-list aliases in the where-clause:
这里真正的问题是 hibernate 中存在一个错误，它在 where-clause 中使用了 select-list 别名：
http://opensource.atlassian.com/projects/hibernate/browse/HHH-817
http://opensource.atlassian.com/projects/hibernate/browse/HHH-817
Just in case someone lands here looking for answers, go look at the ticket. It took 5 years to fix but in theory it'll be in one of the next releases and then I suspect your issue will go away.
以防万一有人来到这里寻找答案，去看看票吧。修复花了 5 年的时间，但理论上它会出现在下一个版本中，然后我怀疑您的问题会消失。
回答by mustafa
I do not really think so, what I can find is the word "this." causes the hibernate not to include any restrictions in its query, which means it got all the records lists. About the hibernate bug that was reported, I can see it's reported as fixed but I totally failed to download the Patch.
我真的不这么认为，我能找到的是“这个”这个词。导致休眠在其查询中不包含任何限制，这意味着它获得了所有记录列表。关于报告的休眠错误，我可以看到它被报告为已修复，但我完全无法下载补丁。
回答by singh
ProjectionList pl = Projections.projectionList();
pl.add(Projections.property("id"));
pl.add(Projections.sqlProjection("abs(`pageNo`-" + pageNo + ") as diff", new String[] {"diff"}, types ), diff); ---- solution
crit.addOrder(Order.asc("diff"));
crit.setProjection(pl);
