Spring Data JPA map the native query result to Non-Entity POJO

by

I think the easiest way to do that is to use so called projection. It can map query results to interfaces. Using SqlResultSetMapping is inconvienient and makes your code ugly :).

An example right from spring data JPA source code:

public interface UserRepository extends JpaRepository<User, Integer> {

   @Query(value = "SELECT firstname, lastname FROM SD_User WHERE id = ?1", nativeQuery = true)
   NameOnly findByNativeQuery(Integer id);

   public static interface NameOnly {

     String getFirstname();

     String getLastname();

  }
}

You can also use this method to get a list of projections.

Check out this spring data JPA docs entry for more info about projections.

Note 1:

Remember to have your User entity defined as normal – the fields from projected interface must match fields in this entity. Otherwise field mapping might be broken (getFirstname() might return value of last name et cetera).

Note 2:

If you use SELECT table.column ... notation always define aliases matching names from entity. For example this code won’t work properly (projection will return nulls for each getter):

@Query(value = "SELECT user.firstname, user.lastname FROM SD_User user WHERE id = ?1", nativeQuery = true)
NameOnly findByNativeQuery(Integer id);

But this works fine:

@Query(value = "SELECT user.firstname AS firstname, user.lastname AS lastname FROM SD_User user WHERE id = ?1", nativeQuery = true)
NameOnly findByNativeQuery(Integer id);

In case of more complex queries I’d rather use JdbcTemplate with custom repository instead.

More Related Contents:

  1. How does spring.jpa.hibernate.ddl-auto property exactly work in Spring?
  2. How to fetch FetchType.LAZY associations with JPA and Hibernate in a Spring Controller
  3. Joining two table entities in Spring Data JPA
  4. Spring boot – Not a managed type
  5. Disable all Database related auto configuration in Spring Boot
  6. Spring data JPA query with parameter properties
  7. Spring Data JPA – Multiple EnableJpaRepositories
  8. Spring Data optional parameter in query method
  9. JPA / JTA / @Transactional Spring annotation
  10. Could not build ClassFile – ArchiveException
  11. Pagination in Spring Data JPA (limit and offset)
  12. Spring Boot Data JPA – Modifying update query – Refresh persistence context
  13. LazyInitializationException in JPA and Hibernate
  14. FetchMode join makes no difference for ManyToMany relations in spring JPA repositories
  15. Multiple data source and schema creation in Spring Boot
  16. setMaxResults for Spring-Data-JPA annotation?
  17. Implementing custom methods of Spring Data repository and exposing them through REST
  18. Why does Hibernate disable INSERT batching when using an IDENTITY identifier generator
  19. Can I combine a @Query definition with a Specification in a Spring Data JPA repository method?
  20. Spring-Data FETCH JOIN with Paging is not working
  21. Could not commit JPA transaction: Transaction marked as rollbackOnly
  22. Why use returned instance after save() on Spring Data JPA Repository?
  23. Spring JpaRepository – Detach and Attach entity
  24. How to Log HttpRequest and HttpResponse in a file?
  25. Spring Transactions and hibernate.current_session_context_class
  26. How do I properly do a background thread when using Spring Data and Hibernate?
  27. Delete Not Working with JpaRepository
  28. Mapping JPA or Hibernate projection query to DTO (Data Transfer Object)
  29. PostGIS Geometry saving: “Invalid endian flag value encountered.”
  30. TransactionRequiredException Executing an update/delete query

Leave a Comment