如何基于NOT条件加入2个实体 [英] How to join 2 entities based on NOT condition

问题描述

我有2个表 - 例如TableA和TableB，其中有一些数据在这篇文章中定义 - 如何根据没有条件加入2个表的结果

现在我打算创建表和实体的Hibernate实体并不相互关联。

现在我想根据我在之前的文章中给出的NOT条件得到结果，I

结果是使用SQL查询：

  SELECT id，name，partNumber，Aid，Aname，Apart 
 FROM TableB AS t 
 CROSS JOIN（SELECT id AS Aid，name AS Aname，partNumber AS Apart 
 FROM TableA as a 
 WHERE NOT EXISTS（SELECT 1 
 FROM TableB AS b 
 WHERE b.partNumber = a.partNumber））AS c 
 ORDER BY id 
  

现在如何为这种情况创建 HQL查询或条件查询。我已经通过HQL文档了解了HQL&条件查询，但我无法理解如何将此SQL查询转换为HQL和Criteria查询。您可以帮我解决这个问题。

更新1：

这是我写的代码：

< $ p> 列表与LT;对象[]> list = session.createQuery（
select a，b+from TableB b，TableA a
+where b.partNumber！= a.partNumber
+ORDER BY b .ID）列表（）; （对象[]对象：列表）{
for（Object object：objects）{
System.out.println（object）;
}
}


我得到了下面的输出：

  A：id = 2，name = a2，partNumber = 20 
 B：id = 5，name = b1，partNumber = 10 
 
 A：id = 3，name = a3，partNumber = 30 
 B：id = 5，name = b1，partNumber = 10 
 
 A：id = 4， name = a4，partNumber = 40 
 B：id = 5，name = b1，partNumber = 10 
 
 A：id = 1，name = a1，partNumber = 10 
 B ：id = 6，name = b2，partNumber = 20 
 
 A：id = 3，name = a3，partNumber = 30 
 B：id = 6，name = b2，partNumber = 20 
 
 A：id = 4，name = a4，partNumber = 40 
 B：id = 6，name = b2，partNumber = 20 
 
 A：id = 1，name = a1，partNumber = 10 
 B：id = 7，name = b3，partNumber = 60 
 
 A：id = 2，name = a2，partNumber = 20 
 B：id = 7，name = b3，partNumber = 60 
 
 A：id = 3，name = a3，partNumber = 30 
 B：id = 7，name = b3，partNumber = 60 
 
 A：id = 4，name = a4，partNumber = 40 
 B：id = 7，name = b3，partNumber = 60 
 
 A： id = 1，name = a1，partNumber = 10 
 B：id = 8，name = b4，partNumber = 70 
 
 A：id = 2，name = a2，partNumber = 20 
 B：id = 8，name = b4，partNumber = 70 
 
 A：id = 3，name = a3，partNumber = 30 
 B：id = 8，name = b4，partNumber = 70 
 
 A：id = 4，name = a4，partNumber = 40 
 B：id = 8，name = b4， partNumber = 70 
  

从输出中我得到 TableA 带有id's = 1,2,3,4 & for TableB id's = 5,6,7,8 。

但是，所需的输出应该具有 TableA 的id，如 3& 4 和 TableB id为 5,6,7,8 。详细信息在我的另一篇文章中给出：根据没有条件加入2个表的结果

由Hibernate生成的查询是：

  Hibernate：
 / *从
中选择
a，
b 
 TableB b，
 TableA a 
其中
 b.partNumber！= a.partNumber 
 ORDER BY 
 b.id * / 
 
选择
 tablea1_.id as id1_0_0_，
 tableb0_.id as id1_1_1_，
 tablea1_.name as name2_0_0_，
 tablea1_.partNumber as partNumber3_0_0_，
 tableb0_.name as name2_1_1_，
 tableb0_.partNumber作为partNumber3_1_1_ 
 from 
 TableB tableb0_ cross 
 join 
 TableA tablea1_ 
 where 
 tableb0_.partNumber<> tablea1_.partNumber 
 order by 
 tableb0_.id 
  

更新2：

现在我已经试过的代码：

 列表与LT;对象[]> list = session.createQuery（select b，ab $ b +from TableB b，TableA a
 +where not exists（
 +select 1
 + TableB a1，TableA a1
 +其中
 +b1.partNumber = a1.partNumber和
 +b1.id = b.id和
 +a1.id = a.id
 +）
 +order by b.id）。list（）; （Object [] objects：list）{
 for（Object object：objects）{
 System.out.println（object）; 
 
 
  

由Hibernate生成的查询：

  Hibernate：
 
选择
 tableb0_.id作为id1_1_0_，
 tablea1_.id作为id1_0_1_，
 tableb0_.name作为name2_1_0_，
作为partNumb3_1_0_作为tableb0_.partNumber，
作为name2_0_1_作为tablea1_.name作为名称，b $ b作为partaumba_0_1_作为作品partnumb3_0_1_ 
作品作为作品bb b_B表bb_b 
 join 
 TableA tablea1_ 
其中
不存在（存在（从
中选择
 1 
 TableB tableb2_ cross 
 join 
 TableA tablea3_ 
其中
 tableb2_.partNumber = tablea3_.partNumber 
和tableb2_.id = tableb0_.id 
和tablea3_.id = tablea1_.id））
 order由
 tableb0_.id 
  

此查询的输出：

  B：id = 5，name = b1，partNumber = 10 
 A：id = 4，name = a4，partNumber = 40 
 B：id = 5，name = b1，partNumber = 10 
 A：id = 2，name = a2，partNumber = 20 
 B：id = 5，name = b1，partNumber = 10 
 A：id = 3，name = a3， partNumber = 30 
 B：id = 6，name = b2，partNumber = 20 
 A：id = 1，name = a1，partNumber = 10 
 B：id = 6，name = b2 ，partNumber = 20 
 A：id = 4，name = a4，partNumber = 40 
 B：id = 6，name = b2，partNumber = 20 
 A：id = 3，name = a3，partNumber = 30 
 B：id = 7，name = b3，partNumber = 60 
 A：id = 3，name = a3，partNumber = 30 
 B：id = 7，name = b3，partNumber = 60 
 A：id = 1，name = a1，partNumber = 10 
 B：id = 7，name = b3，partNumber = 60 
 A：id = 4， name = a4，partNumber = 40 
 B：id = 7，name = b3，partNumber = 60 
 A：id = 2，name = a2，partNumber = 20 
 B：id = 8 ，name = b4，partNumber = 70 
 A：id = 3，name = a3，partNumber = 30 
 B：id = 8，name = b4，partNumber = 70 
 A：id = 1，name = a1，partNumber = 10 
 B：id = 8，name = b4，partNumber = 70 
 A：id = 4，name = a4，partNumber = 40 
 B：id = 8，name = b4，partNumber = 70 
 A：id = 2，name = a2，partNumber = 20 
  

解决方案

您需要使用theta样式的连接：

  select b，a Table b中的
 b，TableA a 
其中不存在（
从TableB b1中选择1 
，TableA a1 
其中
 b1.partNumber = a1.partNumber和
 b1.id = b.id和
 a1.id = a .id 
）
按b.id顺序排列
  

或者您可以使用SQL查询来获取实体：

  List result = session.createSQLQuery（SELECT b。*，c。* \ n+ 
FROM TableB b AS t'\
+ 
CROSS JOIN（SELECT id AS Aid，name AS Aname，partNumber AS Apart Apart \\\
+ 
FROM TableA AS a \ n+ 
WHERE NOT EXISTS（SELECT 1 \\\
+ 
FROM TableB AS b@$ 
WHERE b.partNumber = a.partNumber））AS c \ n+ 
ORDER BY b.id）
 .addEntity（b ，B.class）
 .addEntity（a，A.class）
 .list（）; 
  

I have 2 tables - TableA and TableB for example with some data in them as defined in this post - How to join results of 2 tables based on not condition

Now I am planning to create Hibernate entities for both the tables and the entities are not related to each other.

Now I want to get the results based on NOT condition as given in my earlier post which I have mentioned.

The result is to use the SQL query:

SELECT id, name, partNumber, Aid, Aname, Apart
FROM TableB AS t
CROSS JOIN (SELECT id AS Aid, name AS Aname, partNumber AS Apart
            FROM TableA AS a
            WHERE NOT EXISTS (SELECT 1
                              FROM TableB AS b
                              WHERE b.partNumber = a.partNumber)) AS c
ORDER BY id   

Now how to create HQL query or criteria queries for such scenario's. I have gone through the Hibernate documentation for HQL & Criteria queries but I was not able to understand how can I convert this SQL query to HQL and Criteria queries. Can you please help me on this.

Update 1:

Based on the answer given by Vlad I am not getting correct output.

Here is the Code I have written:

List<Object[]> list = session.createQuery(
                "select a, b " + "from TableB b, TableA a "
                        + "where b.partNumber != a.partNumber "
                        + "ORDER BY b.id").list();

        for (Object[] objects : list) {
            for (Object object : objects) {
                System.out.println(object);
            }
        }

I got below output:

A: id=2, name=a2, partNumber=20
B: id=5, name=b1, partNumber=10

A: id=3, name=a3, partNumber=30
B: id=5, name=b1, partNumber=10

A: id=4, name=a4, partNumber=40
B: id=5, name=b1, partNumber=10

A: id=1, name=a1, partNumber=10
B: id=6, name=b2, partNumber=20

A: id=3, name=a3, partNumber=30
B: id=6, name=b2, partNumber=20

A: id=4, name=a4, partNumber=40
B: id=6, name=b2, partNumber=20

A: id=1, name=a1, partNumber=10
B: id=7, name=b3, partNumber=60

A: id=2, name=a2, partNumber=20
B: id=7, name=b3, partNumber=60

A: id=3, name=a3, partNumber=30
B: id=7, name=b3, partNumber=60

A: id=4, name=a4, partNumber=40
B: id=7, name=b3, partNumber=60

A: id=1, name=a1, partNumber=10
B: id=8, name=b4, partNumber=70

A: id=2, name=a2, partNumber=20
B: id=8, name=b4, partNumber=70

A: id=3, name=a3, partNumber=30
B: id=8, name=b4, partNumber=70

A: id=4, name=a4, partNumber=40
B: id=8, name=b4, partNumber=70

From output I got records of TableA with id's = 1,2,3,4 & for TableBid's= 5,6,7,8.

But the desired output should have id's of TableA as 3&4 and for TableB id's as 5,6,7,8. The details are given in my other post : How to join results of 2 tables based on not condition

Query generated by Hibernate is:

Hibernate: 
    /* select
        a,
        b 
    from
        TableB b,
        TableA a 
    where
        b.partNumber != a.partNumber 
    ORDER BY
        b.id */ 

select
    tablea1_.id as id1_0_0_,
    tableb0_.id as id1_1_1_,
    tablea1_.name as name2_0_0_,
    tablea1_.partNumber as partNumber3_0_0_,
    tableb0_.name as name2_1_1_,
    tableb0_.partNumber as partNumber3_1_1_ 
from
    TableB tableb0_ cross 
join
    TableA tablea1_ 
where
    tableb0_.partNumber<>tablea1_.partNumber 
order by
    tableb0_.id

Update 2:

Code that I have tried now:

List<Object[]> list = session.createQuery("select b, a "
                + "from TableB b, TableA a "
                + "where not exists ( "
                + "select 1 "
                + "from TableB b1, TableA a1 "
                + "where "
                + "b1.partNumber = a1.partNumber and "
                + "b1.id = b.id and "
                + "a1.id = a.id " 
                + ") "
                + "order by b.id").list();
        for (Object[] objects : list) {
            for (Object object : objects) {
                System.out.println(object);
            }
        }

Query generated by Hibernate:

Hibernate: 

select
            tableb0_.id as id1_1_0_,
            tablea1_.id as id1_0_1_,
            tableb0_.name as name2_1_0_,
            tableb0_.partNumber as partNumb3_1_0_,
            tablea1_.name as name2_0_1_,
            tablea1_.partNumber as partNumb3_0_1_ 
        from
            TableB tableb0_ cross 
        join
            TableA tablea1_ 
        where
            not (exists (select
                1 
            from
                TableB tableb2_ cross 
            join
                TableA tablea3_ 
            where
                tableb2_.partNumber=tablea3_.partNumber 
                and tableb2_.id=tableb0_.id 
                and tablea3_.id=tablea1_.id)) 
        order by
            tableb0_.id

Output of this query:

B: id=5, name=b1, partNumber=10
A: id=4, name=a4, partNumber=40
B: id=5, name=b1, partNumber=10
A: id=2, name=a2, partNumber=20
B: id=5, name=b1, partNumber=10
A: id=3, name=a3, partNumber=30
B: id=6, name=b2, partNumber=20
A: id=1, name=a1, partNumber=10
B: id=6, name=b2, partNumber=20
A: id=4, name=a4, partNumber=40
B: id=6, name=b2, partNumber=20
A: id=3, name=a3, partNumber=30
B: id=7, name=b3, partNumber=60
A: id=3, name=a3, partNumber=30
B: id=7, name=b3, partNumber=60
A: id=1, name=a1, partNumber=10
B: id=7, name=b3, partNumber=60
A: id=4, name=a4, partNumber=40
B: id=7, name=b3, partNumber=60
A: id=2, name=a2, partNumber=20
B: id=8, name=b4, partNumber=70
A: id=3, name=a3, partNumber=30
B: id=8, name=b4, partNumber=70
A: id=1, name=a1, partNumber=10
B: id=8, name=b4, partNumber=70
A: id=4, name=a4, partNumber=40
B: id=8, name=b4, partNumber=70
A: id=2, name=a2, partNumber=20

解决方案

You need to use a theta-style join:

select b, a
from TableB b, TableA a 
where not exists (
    select 1
    from TableB b1, TableA a1
    where 
        b1.partNumber = a1.partNumber and
        b1.id = b.id and
        a1.id = a.id    
)   
order by b.id

or you can use an SQL query to fetch entities as well:

List result = session.createSQLQuery("SELECT b.*, c.* \n" +
        "FROM TableB b AS t\n" +
        "CROSS JOIN (SELECT id AS Aid, name AS Aname, partNumber AS Apart\n" +
        "            FROM TableA AS a\n" +
        "            WHERE NOT EXISTS (SELECT 1\n" +
        "                              FROM TableB AS b\n" +
        "                              WHERE b.partNumber = a.partNumber)) AS c\n" +
        "ORDER BY b.id ")
        .addEntity("b", B.class)
        .addEntity("a", A.class)
        .list();

这篇关于如何基于NOT条件加入2个实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！