Java 如何在休眠中使用联合执行查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18257757/
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 execute query with union in hibernate?
提问by user2444474
Hibernate doesn't support union ,so i would like to run sql separately. but finally how to combine those values ?
Hibernate 不支持 union ，所以我想单独运行 sql 。但最后如何结合这些值？
String query ="select
dp.PRODUCTFAMILY,dp.PRODUCTFAMILYDESCR
from TABEL1 dd, TABEL2 DP
where dd.id = 00002
and dd.PRODUCTFAMILY is null
union
select
dp.DIVNUMBER,dp.DIVDESCR
from TABEL1 dd, TABEL2 DP
where dd.id = 00002
and dd.PRODUCT is not null and dd.PRODUCTFAMILY is not null";
public List<PRODUCT> findmethod() {
return findAllByQuery(query);
}
Please advise how to execute two sql seperately and finally how to combine those values ?
请告知如何分别执行两个sql，最后如何组合这些值？
采纳答案by Luca Basso Ricci
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
请注意，UNION 中的每个 SELECT 语句必须具有相同的列数。列也必须具有相似的数据类型。此外，每个 SELECT 语句中的列必须具有相同的顺序。
If this is true add alias to your query:
如果这是真的，请为您的查询添加别名：
select
dp.PRODUCTFAMILY as PRODUCTFAMILY,dp.PRODUCTFAMILYDESCR as PRODUCTFAMILYDESCR
from TABEL1 dd, TABEL2 DP
where dd.id = 00002
and dd.PRODUCTFAMILY is null
union
select
dp.DIVNUMBER as PRODUCTFAMILY,dp.DIVDESCR as PRODUCTFAMILYDESCR
from TABEL1 dd, TABEL2 DP
where dd.id = 00002
and dd.PRODUCT is not null and dd.PRODUCTFAMILY is not null
You can use SQLQuery and a AliasToBeanResultTransformer in this manner:
您可以通过以下方式使用 SQLQuery 和 AliasToBeanResultTransformer：
session.createSQLQuery(above sql with union).addScalar("PRODUCTFAMILY",StringType.INSTANCE).addScalar("PRODUCTFAMILYDESCR",StringType.INSTANCE).setResultTransformer(new AliasToBeanResultTransformer(PRODUCT.class))
PRODUCT must have an emtpy constructor and field accessors.
PRODUCT 必须有一个空构造函数和字段访问器。
Else, if this union is intended to extract different fields with different types you have to run two queries separately the addAll() second result to first!
否则，如果此联合旨在提取不同类型的不同字段，您必须分别运行两个查询，将 addAll() 第二个结果改为第一个！
回答by user3632200
would like to share, which in my case, I found a situation that skirted this situation. The only rule here is to have the same type, in this case String, corresponding to return the list, could add as many tables you want:
想分享一下，就我而言，我发现了一种绕过这种情况的情况。这里唯一的规则是具有相同的类型，在本例中为 String，对应于返回列表，可以添加任意数量的表：
public List<String> findByCPForCNPJ(String query){
TypedQuery<String> ccpf = manager.createQuery("select cpf from PessoaFisica where cpf like :pCpf", String.class);
ccpf.setParameter("pCpf", "%" + query + "%");
List<String> lista1 = ccpf.getResultList();
TypedQuery<String> ccnpj = manager.createQuery("select cnpj from PessoaJuridica where cnpj like :pCnpj", String.class);
ccnpj.setParameter("pCnpj", "%" + query + "%");
lista1.addAll(ccnpj.getResultList());
return lista1;
}
}
I used a method in JAVA for this solution. I hope I have contributed a bit, good luck to all...
我在这个解决方案中使用了 JAVA 中的一种方法。我希望我有所贡献，祝大家好运...
