How to select all distinct records as entities - hibernate - JPA?

Question

I want to select all distinct records not a specific one and as an entities.

I'm using Hibernate 5.6.15, JPA 2.2 and JDK 11 LTS

I tried to select transid as an Orders' entity but getting:
Caused by: java.lang.ClassCastException: class java.lang.String cannot be cast to class com.tailorfx.pojo.Orders

public List<Orders> custOrder() {
    try ( Session session = HibernateUtil.getSessionFactory().openSession()) {
        CriteriaBuilder cb = session.getCriteriaBuilder();
        CriteriaQuery<Orders> cq = cb.createQuery(Orders.class);
        Root<Orders> root = cq.from(Orders.class);

        cq.select(root.get("transid")).distinct(true);

        return session.createQuery(cq).getResultList();
    }
}

So I want to select all distinct transid(s) but as an entities not as String.
So how to do?

Answer 1

This makes no sense. Which entity of the ones that have the same transid do you want? The first, or the last one? Or some other one based on an additional criteria?

You can run a query like the following and specify your "criteria" in the subquery.

public List<Orders> custOrder() {
    try ( Session session = HibernateUtil.getSessionFactory().openSession()) {
        CriteriaBuilder cb = session.getCriteriaBuilder();
        CriteriaQuery<Orders> cq = cb.createQuery(Orders.class);
        Root<Orders> root = cq.from(Orders.class);

        Subquery<Long> subquery = cq.subquery(Long.class);
        Root<Orders> subRoot = subquery.from(Orders.class);
        subquery.select(cb.max(subRoot.get("id")));
        subquery.groupBy(subRoot.get("transid"));

        cq.where(cb.in(root.get("id")).value(subquery));

        return session.createQuery(cq).getResultList();
    }
}