How to select specific fields and return as entity class instead of object in JPQL and Spring data JPA?

Question

I am trying to select specific fields using Spring Data JPA and JPQL like below:

String query = "Select exampleId, exampleNumber, exampleDate FROM  Example  Where exampleCost>'209879' And exampleDate BETWEEN '2010-11-17' AND '2018-01-10'";
Query q = entityManager.createQuery(query).setMaxResults(limit);
List<Example> permitList = q.getResultList();

Its returning List of Object Array instead of List of Example. Please note that select fields are differs as per the client needs.

When I checked in SO, I came to know that we need to create CustomObject and multiple constructors for all selected fields.

JPA Query selecting only specific columns without using Criteria Query?

Is there any alternative to achieve my requirement ?

What does this sentence "Its returning List instead of List." mean? — Tahir Hussain Mir, Mar 29 '18 at 09:09
@TahirHussainMir I have updated the question. SO formatter removed between the text of < >. its, "Its returning List of Object Array instead of List of Example" — Krish, Mar 29 '18 at 09:14
Yes you can do it. You can get the list of example but other fields will be null. Should I write the answer? — Tahir Hussain Mir, Mar 29 '18 at 09:20
Possible duplicate of [Spring data jpa - the best way to return object?](https://stackoverflow.com/questions/46878292/spring-data-jpa-the-best-way-to-return-object) — Cepr0, Mar 29 '18 at 09:35
@TahirHussainMir its more like hardcoding the properties. because in my case, as per the client requirement, select fields will differ. Like "exampleId, exampleNumber, exampleDate" OR "exampleNumber, exampleDate" OR "exampleId, exampleDate". I am looking for a solution which will set the properties automatically. i.e., List permitList = q.getResultList(); — Krish, Mar 29 '18 at 09:59
@Krish although people do use DTo's for that purpose but here is the answer, and it might be helpful to you. Link is https://stackoverflow.com/a/43513384/5435216 — Tahir Hussain Mir, Mar 29 '18 at 10:18

score 1 · Answer 1 · answered Mar 29 '18 at 10:04

You can´t make the query to return Entity instances loading only the values you want from the database. Your best options are to use a projection query and construct a class with the results of the query or return a List of Object[] and cast the objects to the appropriate type as described in your linked question.

There is an option to modify your Entity and set the fetch mode of the attribute to Lazy loading. This changes the behaviour of the entity, not just for the query you want to use. Also, setting the Lazy loading on your entity attributes is just a hint for hibernate, the attributes could still be loaded anyways.

To set the lazy loading, you can set the fetchType using the @Basic annotation:

public class Example {

    int id;
    int number;

    @Basic(fetchType=FetchType.LAZY)
    String otherField;
}

score 1 · Answer 2 · answered Mar 29 '18 at 13:10

1

Why not just use a constructor expression, and have a constructor on your entity taking those arguments. Then you stick to the JPA spec ...

String query = "SELECT new Example(e.exampleId, e.exampleNumber, e.exampleDate) FROM Example e WHERE e.exampleCost > 209879 AND e.exampleDate BETWEEN '2010-11-17' AND '2018-01-10'";
Query q = entityManager.createQuery(query).setMaxResults(limit);
List<Example> permitList = q.getResultList();

which, of course, has nothing to do with "Spring Data" and everything to do with the JPA API

answered Mar 29 '18 at 13:10

I tried this already. The problem is that select fields will differ for each request. I can't create multiple constructor those as my entity having more than 40 fields and not sure how client will request for the fields. – Krish Mar 29 '18 at 13:41
That is the ONLY way there is. JPA has no way of knowing what these randomly selected fields are relative to YOUR class. If you instead wanted to do it dynamically then you do the same thing but using JPA Criteria, and can then vary what fields are selected ... BUT you still need an appropriate constructor – Mar 29 '18 at 13:54

score 0 · Answer 3 · answered Mar 29 '18 at 09:35

I think You should know it. After you get the List, let's change it to

List<Object> permitList = q.getResultList();

Then cast each field to appropriate field of Example like this

List<Example> listEx = new ArrayList<Example>();
for(Object o : permitList){
    Example e = new Example();
    e.setExampleId(o[0]); //You might need casting, you can do that too like this
    // e.setExampleId((Long)o[0])
    e.setExampleNumber(o[1]);
    //and so on as per client needs
    listEx.add(e);
}
return listEx;

score 0 · Answer 4 · answered Mar 29 '18 at 09:59

0

You can use q.setResultTransformer(Transformers.aliasToBean(Yourclass.class));

Yourclass.class is the bean class to which you want to transform the result.

answered Mar 29 '18 at 09:59

Vipin CP

3,642
3
33
55

1

I am looking for javax.persistence.Query. Not hibernate specific. – Krish Mar 29 '18 at 10:02

score 0 · Answer 5 · answered Mar 29 '18 at 16:18

Another approach would be to change your query to the simple SELECT e FROM Example e WHERE ..., and use dynamic fetch graphs: https://docs.oracle.com/javaee/7/tutorial/persistence-entitygraphs001.htm

Note, however, that:

You might need to enable entity enhancement with your persistence provider for lazy fetching of basic attributes
Trying to access any attributes besides those specified in the graph will cause them to load (whether the above approach works for you depends on how you process the results)

How to select specific fields and return as entity class instead of object in JPQL and Spring data JPA?

5 Answers5