Hibernate支持UNION ALL吗？

Question

我是一名部分UI开发人员，现在需要转向Hibernate开发。今天，当我尝试使用UNION ALL时，遇到了HQL的问题，以下是我的HQL代码：

SELECT COUNT(DISTINCT users.userId) AS totalSize FROM (SELECT DISTINCT 
d1.sponsor.id AS userId FROM Dating d1 WHERE d1.invitee.id = ? UNION ALL 
SELECT DISTINCT d2.invitee.id AS userId FROM Dating d2 WHERE d2.sponsor.id = ?) 
AS users 

它显示的错误信息如下：

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 55

然后我尝试将这个HQL翻译为原始的SQL：

SELECT COUNT(DISTINCT users.userId) AS totalSize FROM (SELECT DISTINCT 
d1.sponsorId AS userId FROM mmy_dating d1 WHERE d1.inviteeId = 6 UNION 
ALL SELECT DISTINCT d2.inviteeId AS userId FROM mmy_dating d2 WHERE 
d2.sponsorId = 6) AS users;

通常情况下，它会显示正确的结果，所以我在想是否Hibernate不支持UNION ALL语法？谢谢。

- Brady Zhu

Answer 1

仍有一个功能请求未解决，可以在https://hibernate.atlassian.net/browse/HHH-1050上查看。还可以查看Hibernate Union替代方案

- Sean Carroll

---

我使用纯 SQL 来实现它，而不是使用 HQL。 - Brady Zhu

@BradyChu 如果你正在使用纯SQL，那么你可以使用本地SQL而不是HQL。 - Jacob

Answer 2

从Hibernate 6开始，支持UNION ALL。特别是对于Postgres。

import com.example.domain.*;
import jakarta.persistence.EntityManager;
import jakarta.persistence.TypedQuery;
import jakarta.persistence.criteria.*;
import org.hibernate.query.criteria.JpaCriteriaQuery;
import org.hibernate.query.sqm.internal.SqmCriteriaNodeBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.stream.Collectors;

@Repository
public class UnionAllJpaRepository {

    @Autowired
    private EntityManager em;

    List<UnionItem> find() {
        CriteriaBuilder builder = em.getCriteriaBuilder();
        CriteriaQuery<UnionItem> queryUser = builder.createQuery(UnionItem.class);
        CriteriaQuery<UnionItem> queryCompany = builder.createQuery(UnionItem.class);


        {

            Root<User> rootUser = queryUser.from(User.class);
            queryUser
                    .select(
                            builder.construct(
                                    UnionItem.class,
                                    rootUser.get(User_.ID),
                            )
                    );
        }

        {
            Root<Company> rootCompany = queryCompany.from(Company.class);
            queryC
                    .select(
                            builder.construct(
                                    UnionItem.class,
                                    rootC.get(Company_.ID)
                            )
                    );
        }

        JpaCriteriaQuery<UnionItem> unionAllJpaCriteriaQuery = ((SqmCriteriaNodeBuilder) builder).unionAll(
                queryUser,
                queryCompany
        );

        TypedQuery<UnionItem> resultQuery = em.createQuery(unionAllJpaCriteriaQuery);


        return resultQuery.getResultList();
    }


    public record UnionItem(
            Long id
    ) {
    }

}

- Andriy

Answer 3

我想分享一下，在我的情况下，我发现了一个避开这种情况的方法。 唯一的规则是要有相同类型，即字符串，在返回列表时对应，可以添加任意数量的表：

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

希望我做出了一点贡献，祝大家好运...

- Lanmaster