java 如何将嵌套的 SQL 转换为 HQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5198636/
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
How to convert nested SQL to HQL
提问by Bhushan
I am new to the Hibernate and HQL. I want to write an update query in HQL, whose SQL equivalent is as follows:
我是 Hibernate 和 HQL 的新手。我想用HQL写一个更新查询，其SQL等价如下：
update patient set
`last_name` = "new_last",
`first_name` = "new_first"
where id = (select doctor_id from doctor
where clinic_id = 22 and city = 'abc_city');
doctor_idis PK for doctorand is FK and PK in patient. There is one-to-one mapping.
doctor_id是 PK for doctor并且是 FK 和 PK in patient。存在一对一映射。
The corresponding Java classes are Patient (with fields lastName, firstName, doctorId) and Doctor (with fields doctorId).
对应的 Java 类是 Patient（带有字段 lastName、firstName、doctorId）和 Doctor（带有字段 DoctorId）。
Can anyone please tell what will be the HQL equivalent of the above SQL query?
谁能告诉我上述 SQL 查询的 HQL 等价物是什么？
Thanks a lot.
非常感谢。
回答by mcyalcin
String update = "update Patient p set p.last_name = :new_last, p.first_name = :new_first where p.id = some (select doctor.id from Doctor doctor where doctor.clinic_id = 22 and city = 'abc_city')";
You can work out how to phrase hql queries if you check the specification. You can find a section about subqueriesthere.
回答by Abel
I don't think you need HQL (I know, you ask that explicitly, but since you say you're new to Hibernate, let me offer a Hibernate-style alternative). I am not a favor of HQL, because you are still dealing with strings, which can become hard to maintain, just like SQL, and you loose type safety.
我认为您不需要 HQL（我知道，您明确提出了这个问题，但是既然您说您是 Hibernate 的新手，那么让我提供一个 Hibernate 风格的替代方案）。我不赞成 HQL，因为您仍在处理字符串，这会变得难以维护，就像 SQL 一样，并且您失去了类型安全性。
Instead, use Hibernate criteria queries and methods to query your data. Depending on your class mapping, you could do something like this:
相反，使用Hibernate 标准查询和方法来查询您的数据。根据您的类映射，您可以执行以下操作：
List patients = session.CreateCriteria(typeof(Patient.class))
.createAlias("doctor", "dr")
.add(Restrictions.Eq("dr.clinic_id", 22))
.add(Restrictions.Eq("dr.city", "abc_city"))
.list();
// go through the patients and set the properties something like this:
for(Patient p : patients)
{
p.lastName = "new lastname";
p.firstName = "new firstname";
}
Some people argue that using CreateCriteria is difficult. It takes a little getting used to, true, but it has the advantage of type safety and complexities can easily be hidden behind generic classes. Google for "Hibernate java GetByProperty" and you see what I mean.
有些人认为使用 CreateCriteria 很困难。确实需要一点时间来习惯，但它具有类型安全的优点，并且复杂性很容易隐藏在泛型类之后。谷歌搜索“Hibernate java GetByProperty”，你明白我的意思。
回答by yogsma
update Patient set last_name = :new_last , first_name = :new_first where patient.id = some(select doctor_id from Doctor as doctor where clinic_id = 22 and city = abc_city)
回答by Krass
There is a significant difference between executing update with select and actually fetching the records to the client, updating them and posting them back:
使用 select 执行更新与实际将记录获取到客户端、更新它们并将它们发回之间存在显着差异：
UPDATE x SET a=:a WHERE b in (SELECT ...)
works in the database, no data is transferred to the client.
在数据库中工作，没有数据传输到客户端。
list=CreateCriteria().add(Restriction).list();
brings all the records to be updated to the client, updates them, then posts them back to the database, probably with one UPDATE per record.
将所有要更新的记录带到客户端，更新它们，然后将它们发布回数据库，可能每个记录一个 UPDATE。
Using UPDATE is much, much fasterthan using criteria (think thousands of times).
使用 UPDATE比使用标准快得多（想想数千次）。
