java – 如何基于NOT条件连接2个实体

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了java – 如何基于NOT条件连接2个实体，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4934字，纯文字阅读大概需要8分钟。

内容图文

我有2个表 – 例如TableA和TableB,其中包含一些数据,如本文中所定义 – How to join results of 2 tables based on not condition

现在我计划为这两个表创建Hibernate实体,并且实体彼此不相关.

现在我想根据我之前提到的帖子中给出的NOT条件得到结果.

结果是使用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的Hibernate文档. Criteria查询但我无法理解如何将此SQL查询转换为HQL和Criteria查询.你能帮我解决这个问题吗？

更新1：

根据弗拉德给出的答案我没有得到正确的输出.

这是我写的代码：

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);
            }
        }

我得到了以下输出：

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 = 1,2,3,4&对于TableBid = 5,6,7,8.

但是,所需的输出应该具有TableA的id为3& 4,而对于TableB id为5,6,7,8.详情见我的另一篇文章：How to join results of 2 tables based on not condition

Hibernate生成的查询是：

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

更新2：

我现在尝试过的代码：

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);
            }
        }

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

输出此查询：

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
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

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

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();

内容总结

以上是互联网集市为您收集整理的java – 如何基于NOT条件连接2个实体全部内容，希望文章能够帮你解决java – 如何基于NOT条件连接2个实体所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端