CODEWITHSUNDEEP
javasqldatabasehibernatehql
Brady Zhu
Brady Zhu

Reputation: 1405

Does Hibernate support UNION ALL?

I'm a partial UI developer, right now I need to turn into Hibernate development. Today I occurred a problem in HQL when I'm trying to use UNION ALL, here is the 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

It shows error like this:

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

And then I tried translate this HQL to raw 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;

It normally shows the correct result, so I was thinking if Hibernate doesn't support UNION ALL syntax?

Thanks in advance.

Upvotes: 5

Views: 36599

Answers (3)

Andriy
Andriy

Reputation: 259

Starting from Hibernate 6, UNION ALL is supported. For Postgres, in particular.

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

}

Upvotes: 1

Lanmaster
Lanmaster

Reputation: 29

would like to share, which in my case, I found a situation that skirted this situation. The only rule here is to have the same type, in this case String, corresponding to return the list, could add as many tables you want:

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

I hope I have contributed a bit, good luck to all...

Upvotes: 0

Sean Carroll
Sean Carroll

Reputation: 2711

There is a feature request still open for this https://hibernate.atlassian.net/browse/HHH-1050. Also take a look at Hibernate Union alternatives

Upvotes: 6

Related Questions