Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Spring Data JPA: How can Query return Non- Entities Objects or List of Objects?

I am using spring data JPA in my project. I am playing with millions of records. I have a requirement where I have to fetch data for various tables and build a object and then paint it on a UI. Now how to achieve this my Spring data Repositories. I have read that it can be achieved by Named native queries.

If the named native query does not return an entity or a list of entities, we can map the query result to a correct return type by using the @SqlResultSetMapping annotation.

But when I am trying to use @SqlResultSetMapping it is taking another entityResult. Mean what I understand is that it is just transformation some query result to entity result set only, but I want a result set of non - entities objects.

@SqlResultSetMapping(
    name="studentPercentile",
    entities={
        @EntityResult(
           entityClass=CustomStudent.class,
              fields={
                  @FieldResult(name="id", column="ID"),
                  @FieldResult(name="firstName", column="FIRST_NAME"),
                   @FieldResult(name="lastName", column="LAST_NAME")
              }         
        )
   }
) 
@NamedNativeQuery(
    name="findStudentPercentile", 
    query="SELECT * FROM STUDENT", 
    resultSetMapping="studentPercentile")

In above example I am just trying to get a results from student Entity into another pojo 'CustomStudent' which is not a entity. (This example I am trying to execute just for POC purpose, actual usecase is much complicated, with complicated query returning different resultset).

How to achieve above usecase? Is there any other way besides using name query that my repository method returning Non - Entities objects?

like image 822
Dhruv Bansal Avatar asked May 07 '13 13:05

Dhruv Bansal


4 Answers

You can do something like

@NamedQuery(name="findWhatever", query="SELECT new path.to.dto.MyDto(e.id, e.otherProperty) FROM Student e WHERE e.id = ?1")

Then the MyDto object would just need a constructor defined with the correct fields i.e.

public MyDto(String id, String otherProperty) { this.id = id; this.otherProperty = otherProperty; }
like image 169
tlavarea Avatar answered Sep 30 '22 21:09

tlavarea


I was deeply surprised when I came accross this for the first time but, yes, you can map query results using @SqlResultSetMapping only to scalars and managed entities.

The best you can do, I guess, is to skip automatic mapping. Query without mapping would return List<Object[]> and you can map it the way you need.

Another approach would be to use @MappedSuperclass. The class denoted as @MappedSuperclass (CustomStudent in your case) can be (not sure 100%, though) used in @SqlResultSetMapping. but you need to introduce inheritance hierarchy, that is your Student entity must extend CustomStudent. That would suck most of the time from the proper OO design, because inheritance would be a little bit artificial...

like image 25
Piotr Kochański Avatar answered Sep 29 '22 21:09

Piotr Kochański


How about JPA 2.1 ConstructorResult ?

@SqlResultSetMapping(
    name="studentPercentile",
    classes={
        @ConstructorResult(
            targetClass=CustomStudent.class,
            columns={
                @ColumnResult(name="ID"),
                @ColumnResult(name="FIRST_NAME"),
                @ColumnResult(name="LAST_NAME")
            }
        )
    }
)

@NamedNativeQuery(name="findStudentPercentile", query="SELECT * FROM STUDENT", resultSetMapping="studentPercentile")
like image 38
Daimon Avatar answered Oct 02 '22 21:10

Daimon


We can also parse using JSON help.

Class level declaration.

@Autowired
private EntityManager em;
private ObjectMapper mapper = new ObjectMapper(); 

Main Code.

Query query = em.createNativeQuery(argQueryString);
NativeQueryImpl nativeQuery = (NativeQueryImpl) query;
nativeQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
List<Map<String,Object>> result = nativeQuery.getResultList();
List<CustomStudent> resultList = result.stream()
   .map(o -> {
         try {
              return 
            mapper.readValue(mapper.writeValueAsString(o),CustomStudent.class);
       } catch (Exception e) {
           ApplicationLogger.logger.error(e.getMessage(),e);
       }
     return null;
    }).collect(Collectors.toList());
like image 26
Kapil Thakkar Avatar answered Oct 02 '22 21:10

Kapil Thakkar