如何在Hibernate中使用UNION执行查询

Question

我正在通过 Hibernate 执行一个 SQL 查询，它看起来像这样：

@Query("(select category from Category category where category.isDelete=false and category.status='A' AND " +
        "category.id in (select cat.id from Category cat where cat.isDelete=false and cat.status='A' and cat.parentCategory IS NOT NULL))" +
        "UNION" +
        "(select category from Category category where category.isDelete=false and category.status='A' and category.parentCategory IS NOT NULL)")

但是它显示出错误信息。

Caused by: java.lang.IllegalArgumentException: node to traverse cannot be null!

- Mahaveer Saini

---

嗨，简斯， 这是Java中的Hibernate框架，类别指的是表的所有列。 - Mahaveer Saini

Answer 1

在SQL层面上，您的查询是正常的。但是，在使用Hibernate时，您将会遇到这个异常。

Caused by: java.lang.IllegalArgumentException: node to traverse cannot be null!

所以将这个查询转换为：

@Query("(select category from Category category where category.isDelete=false and category.status='A' AND " +
    "category.id in (select cat.id from Category cat where cat.isDelete=false and cat.status='A' and cat.parentCategory IS NOT NULL))" +
    "UNION" +
    "(select category from Category category where category.isDelete=false and category.status='A' and category.parentCategory IS NOT NULL)")

将其拆分为两个查询。

@Query("select category from Category category where category.isDelete=false and category.status='A' AND " +
    "category.id in (select cat.id from Category cat where cat.isDelete=false and cat.status='A' and cat.parentCategory IS NOT NULL)")

@Query("select category from Category category where category.isDelete=false and category.status='A' and category.parentCategory IS NOT NULL")

并使用不同的方法调用它们。

- Dev Sabby

Answer 2

你如何选择类别？有类似的字段吗？你已经将分类表称为“category”，请修复它，然后尝试其他方法也可以。*您还可以通过使用星号*来检查它，您查询的其余部分是正确的。*

- Ashiqur Rahman Emran