hibernatecriterianhibernate-criteria

Criteria join with projection

I am going to create this SQL query's Criteria equivalent:

SQL:

SELECT
  orders.id,
  book.title,
  orderitem.quantity

  FROM orderitem
    INNER JOIN book ON book.id = orderitem.book_id
    INNER JOIN orders ON orders.id = orderitem.orders_id

WHERE user_id = 1

Here is my Criteria query:

Criteria c = getSession().createCriteria(OrderItem.class, "oi");
c.setProjection(Projections.property("quantity"));

c.createAlias("oi.book", "book");
c.setProjection(Projections.property("title"));
c.createAlias("oi.orders", "or");
c.setProjection(Projections.property("id"));
c.createAlias("or.user", "usr");

c.add(Restrictions.eq("usr.id", "1"));
return c.list();

It has no exception, but it displays just Orders.id column:

[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 22]
Solution

  • Use ProjectionList . It will return Object array containing fields quantity , title , id .

    Criteria c = getSession().createCriteria(OrderItem.class, "oi");
c.createAlias("oi.book", "book");
c.createAlias("oi.orders", "or");
c.createAlias("or.user", "usr");

ProjectionList proList = Projections.projectionList();
proList.add(Projections.property("oi.quantity"));
proList.add(Projections.property("book.title"));
proList.add(Projections.property("or.id"));
c.setProjection(proList);
c.add(Restrictions.eq("usr.id", "1"));
return c.list();