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

Question

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?

Haemophiliac

I came across the same issue recently and glad to see someone posted and got a solution for it too! – Folse

We had a similar problem at hand wherein we were supposed to fetch record count from three tables and we ended up using spring JdbcTemplate (though in general we are using Spring JPA Hibernate in our project). It seemed too much of work to write entities etc. simply for fetching record count. – Gad

The answer is the same as to this question – Blackcap

Answer 1

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...

Rosy

Do you mean Primitive types? – Rhiana

You can use ConstructorResult https://mcmap.net/q/409615/-return-custom-object-from-spring-data-with-native-query – Rhiana

Answer 2

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; }

Alcott

can i write something like SELECT new path.to.dto.MyDto(e.id, new path.to.dto.OtherDto) – Catlee

No, but in your MyDto you could have an OtherDto property and in the constructor of MyDto you could set the OtherDto property using the data passed in to the MyDto constructor. Or you could use the @SqlResultSetMapping annotation. – Alcott

When I write this, compilation fails with Validation failed for query for method public abstract.. – Bear

Answer 3

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")

Pascual

Hello, this sounds nice, but NamedQuery still have to be attached to and Entity to be part of the persistence unit ? – Rim

Answer 4

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

Factional