JPA & Criteria API - Select only specific columns

Question

I would like to select only specific columns (ex. SELECT a FROM b). I have a generic DAO and what I came up with is:

public List<T> getAll(boolean idAndVersionOnly) {
    CriteriaBuilder builder = manager.getCriteriaBuilder();
    CriteriaQuery<T> criteria = builder.createQuery(entityClazz);
    Root<T> root = criteria.from(entityClazz);
    if (idAndVersionOnly) {
        criteria.select(root.get("ID").get("VERSION")); // HERE IS ERROR
    } else {
        criteria.select(root);
    }
    return manager.createQuery(criteria).getResultList();
}

And the error is: The method select(Selection<? extends T>) in the type CriteriaQuery<T> is not applicable for the arguments (Path<Object>). How should I change that? I want to get a type T object that has only ID and VERSION fields, and all others are null.

Type T extends AbstractEntity which has those 2 fields.

entityClazz is T.class.

perissf · Accepted Answer · 2012-10-03T08:58:59.063

One of the JPA ways for getting only particular columns is to ask for a Tuple object.

In your case you would need to write something like this:

CriteriaQuery<Tuple> cq = builder.createTupleQuery();
// write the Root, Path elements as usual
Root<EntityClazz> root = cq.from(EntityClazz.class);
cq.multiselect(root.get(EntityClazz_.ID), root.get(EntityClazz_.VERSION));  //using metamodel
List<Tuple> tupleResult = em.createQuery(cq).getResultList();
for (Tuple t : tupleResult) {
    Long id = (Long) t.get(0);
    Long version = (Long) t.get(1);
}

Another approach is possible if you have a class representing the result, like T in your case. T doesn't need to be an Entity class. If T has a constructor like:

public T(Long id, Long version)

then you can use T directly in your CriteriaQuery constructor:

CriteriaQuery<T> cq = builder.createQuery(T.class);
// write the Root, Path elements as usual
Root<EntityClazz> root = cq.from(EntityClazz.class);
cq.multiselect(root.get(EntityClazz_.ID), root.get(EntityClazz_.VERSION));  //using metamodel
List<T> result = em.createQuery(cq).getResultList();

See this link for further reference.

How is EntityClazz_.VERSION accessed? Is it to suppose be some Enum? — Bablu, Sep 05 '22 at 14:47

score 16 · Answer 2 · answered Mar 26 '14 at 23:14

16

cq.select(cb.construct(entityClazz.class, root.get("ID"), root.get("VERSION")));  // HERE IS NO ERROR

https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Criteria#Constructors

answered Mar 26 '14 at 23:14

EduardoRamirez

161
1
2

score 3 · Answer 3 · edited May 23 '17 at 11:47

3

First of all, I don't really see why you would want an object having only ID and Version, and all other props to be nulls. However, here is some code which will do that for you (which doesn't use JPA Em, but normal Hibernate. I assume you can find the equivalence in JPA or simply obtain the Hibernate Session obj from the em delegate Accessing Hibernate Session from EJB using EntityManager ):

List<T> results = session.createCriteria(entityClazz)
    .setProjection( Projections.projectionList()
        .add( Property.forName("ID") )
        .add( Property.forName("VERSION") )
    )
    .setResultTransformer(Transformers.aliasToBean(entityClazz); 
    .list();

This will return a list of Objects having their ID and Version set and all other props to null, as the aliasToBean transformer won't be able to find them. Again, I am uncertain I can think of a situation where I would want to do that.

edited May 23 '17 at 11:47

Community

1
1

answered Sep 27 '12 at 10:59

Nikola Yovchev

9,498
4
46
72

Thank you. I want to use it in my webservice. Client requests list of "something" containing just IDs and Versions. Then he compares it to the cache, and requests full objects that changed. Sounds reasonable? – BartoszCichecki Sep 27 '12 at 11:52
Is it to save network bandwidth? It is data dependent, I assume, but if you expect your data to change often, you could be wasting more bandwidth due to tcp/ip overhead of creating two requests than saving from just sending 'hollow' copies. Maybe it is worth checking out? – Nikola Yovchev Sep 27 '12 at 11:59
2

Yes, I know. I am going to use it only for data that will not change so often. – BartoszCichecki Sep 27 '12 at 12:48
1

Well I could not find anything similar in JPA 2 :/ – BartoszCichecki Sep 28 '12 at 12:59

score -3 · Answer 4 · answered Dec 28 '18 at 18:00

You can do something like this

Session session = app.factory.openSession();
CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery query = builder.createQuery();
Root<Users> root = query.from(Users.class);
query.select(root.get("firstname"));
String name = session.createQuery(query).getSingleResult();

where you can change "firstname" with the name of the column you want.

JPA & Criteria API - Select only specific columns

4 Answers4

Linked

Related