JPA alternative of Hibernate Projections.property

Question

Folks!

Trying to limit amount of columns fetched from DB and found this: Hibernate Criteria Query to get specific columns

Criteria cr = session.createCriteria(User.class)
    .setProjection(Projections.projectionList()
      .add(Projections.property("id"), "id")
      .add(Projections.property("Name"), "Name"))
    .setResultTransformer(Transformers.aliasToBean(User.class));

  List<User> list = cr.list();

This works awesome when you use Hibernate, but I am trying to do the same with JPA (JPA provider is Hibernate tho). Is it possible to do it with JPA? I mean limit columns with CriteriaBuilder and map to specific Object?

Also, I saw this: https://docs.jboss.org/hibernate/orm/5.0/userguide/html_single/chapters/query/criteria/Criteria.html

Hibernate offers an older, legacy org.hibernate.Criteria API which should be considered deprecated. No feature development will target those APIs. Eventually, Hibernate-specific criteria features will be ported as extensions to the JPA javax.persistence.criteria.CriteriaQuery. For details on the org.hibernate.Criteria API, see Legacy Hibernate Criteria Queries.

So seems like it is possible to do this via hibernate extensions for JPA?

@fg78nc I was also trying to find out solution but what if my selection columns are dynamic and above code is a data provider call for example findUser( Some paramweter) now if my user table does have 70 columsn and using same findUser() method I need 10 colum at one place of application while at another place i need to fetch 12 columns then how can I implement this — Atul Singh Rathore, Feb 21 '19 at 08:30

fg78nc · Answer 1 · 2017-07-25T17:48:21.983

There is no alternative syntax within JPA, Hibernate is superset of JPA, it provides features beyond JPA specifications. In JPA you will have to do projections individually explicitly with select statement (in your case multiselect statement).

Criteria Query

 // Create instance of CriteriaBuilder, where em is EntityManager
CriteriaBuilder cb = em.getCriteriaBuilder();      

  // Use CriteriaBuilder interface to create an instance 
  // of CriteriaQuery. For multiselect result set is Object [].
CriteriaQuery<Object[]> c = cb.createQuery(Object[].class);

   // Establish the root of the query by invoking from() to get back a Root object. 
Root<User> user = c.from(User.class); 

  // Establish the SELECT clause of the query by passing the root into the multiselect() method
criteriaQuery.multiselect(user.get("property1"), user.get("property2"));

alternative would be constructor call:

CriteriaQuery<User> c = cb.createQuery(User.class);
Root<User> user = c.from(User.class);
c.select(cb.construct(User.class,
                      user.get("property1"),
                      user.get("property2")));

TupleQuery

    CriteriaQuery<Tuple> c = cb.createTupleQuery();
    Root<User> user = c.from(User.class);
    c.select(cb.tuple(user.get("property1"), user.get("property2")));
    Query query = entityManager.createQuery(c);
    List<Tuple> results = query.getResultList();

There is also array() method (it returns Object[]) as well.

    c.select(cb.array(user.get("property1"), user.get("property2")));

Thank you for advise! I just tried your solution..but: in first case the result of execution (*getResultList*) is just an array of objects - so the result is not being mapped back to User class.. And second approach gives me *QuerySyntaxException: Unable to locate appropriate constructor on class * — max.kuzmentsov, Jul 25 '17 at 17:27
For second approach you need to have appropriate constructor. Multiselect either will return Object[] or Tuple. I will include tuple in example also. — fg78nc, Jul 25 '17 at 17:40
Thank you, but still the same - list which is being returned is not of User type. With Tuple I noticed interesting behavior. Each Tuple has a member property,- in our case it is `id` and `name`. But it is not being converted back to User type automatically. Should it be in such way? I still see that generated SQL is proper, but the result is not being mapped to User entity. Seems like I really misunderstanding something. — max.kuzmentsov, Jul 25 '17 at 18:25
Result set for multiselect is object array, single select will return persisted Entity. for example `CriteriaQuery c = cb.createQuery(User.class); Root user = c.from(User.class); c.select(user.get("property1"));` — fg78nc, Jul 25 '17 at 18:36

JPA alternative of Hibernate Projections.property

1 Answers1