Criteria join with projection

Question

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]

score 4 · Accepted Answer · answered May 04 '15 at 07:34

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();

score 2 · Answer 2 · answered Dec 24 '16 at 15:57

I needed to receive the result list as named keys & values other than index & value. I used following way.

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"), "quantity");
proList.add(Projections.property("book.title"), "title");
proList.add(Projections.property("or.id"), "id");
c.setProjection(proList);

c.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

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

Criteria join with projection

2 Answers2