4

I'm writing a query with CriteriaBuilder, but it has not been possible to add the order by clause, because an error it's thrown with the message ORDER BY expressions must appear in select list this are my entities.

public class A{
  Integer aId;
  @ManyToOne
  @JoinColumn(name = "bId", nullable = false)
  B       classB;
  //setter and getter.
}

public class B{
    Integer bId;
    String name;
    //setter and getter.

}

my query look like this.

CriteriaBuilder cb = super.getEntityManager().getCriteriaBuilder();
        CriteriaQuery<A> cq = cb.createQuery(A.class);
        Root<A> aRoot = cq.from(A.class);
        Join<A, B> joinB = aRoot.join("b", JoinType.INNER);

        cq.distinct( true );

        cq.orderBy( cb.asc( joinB.get("name") ));

        return super.getEntityManager().createQuery(cq).getResultList();

This is the sql tha hibernate generates. 

select
    distinct a0_.aId as aId,
    a0_.bId as bId,
from
    a a0_ 
inner join
    b b2_ 
        on a0_.aId=b2_.bId 
order by
    b2_.name asc //this is where the error comes

As you can see. B it is not in the select clause. so, how do i add B?

OJVM
  • 1,403
  • 1
  • 25
  • 37
  • 1
    [Here](http://docs.oracle.com/javaee/6/tutorial/doc/gjivm.html) is an example how it should works. – Jens Feb 04 '15 at 19:19
  • 5
    Thank you for your help, at the end the solution was to add a group by and delete the distinct. – OJVM Feb 04 '15 at 23:16
  • 2
    Thank you the Question and the comment. Although I think the above comment should also be posted as answer to the question. It seems to be still valid today. – EFreak Feb 26 '19 at 09:48

0 Answers0