如何在Hibernate中使用union执行查询？ [英] How to execute query with union in hibernate?

问题描述

Hibernate不支持union，所以我想单独运行sql。但最后如何组合这些值？ $ b from TABEL1 dd，TABEL2 DP
其中dd.id = 00002
和dd.PRODUCTFAMILY为空
联合
选择
dp.DIVNUMBER，dp.DIVDESCR
从TABEL1 dd，TABEL2 DP
其中dd.id = 00002
和dd.PRODUCT不为空且dd.PRODUCTFAMILY不为空;

公开列表< PRODUCT> findmethod（）{
return findAllByQuery（query）;
}

请告知如何分开执行两个sql，最后如何组合这些值？

解决方案

请注意，UNION中的每个SELECT语句必须具有相同的列数。这些列也必须具有相似的数据类型。另外，每个SELECT语句中的列必须是相同的顺序。

如果这是真的，请为您的查询添加别名：

  select 
 dp.PRODUCTFAMILY as PRODUCTFAMILY，dp.PRODUCTFAMILYDESCR as PRODUCTFAMILYDESCR 
 from TABEL1 dd，TABEL2 DP 
 where dd .id = 00002 
和dd.PRODUCTFAMILY为空
联合
选择
 dp.DIVNUMBER作为PRODUCTFAMILY，dp.DIVDESCR作为PRODUCTFAMILYDESCR 
从TABEL1 dd，TABEL2 DP 
其中dd.id = 00002 
且dd.PRODUCT不为空且dd.PRODUCTFAMILY不为空
  

您可以按照以下方式使用SQLQuery和AliasToBeanResultTransformer：

  session.createSQLQuery（上面的sql with union） .addScalar（PRODUCTFAMILY，StringType.INSTANCE）.addScalar（PRODUCTFAMILYDESCR，StringType.INSTANCE）.setResultTransformer（new AliasToBeanResultTransformer（PRODUCT.class））
  

产品必须具有一个无用的构造函数和字段访问器。

否则，如果这个联合意图提取不同类型的字段，你必须分别运行两个查询addAll（）第二个结果到第一个！

Hibernate doesn't support union ,so i would like to run sql separately. but finally how to combine those values ?

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 ?

解决方案

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.

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:

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.

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!

这篇关于如何在Hibernate中使用union执行查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！