Uploaded image for project: 'Spring Data JPA'
  1. Spring Data JPA
  2. DATAJPA-980

Projections with native queries don't work as expected

    XMLWordPrintable

    Details

      Description

      The documentation doesn't mention anything about native queries in relation to the projection functionality so I don't know if it's even a supported use case. It does seem to work to some extent but there are issues.

      I have put together a demonstration project: https://github.com/roberthunt/spring-data-native-query-projection

      There is a test for each of these methods:

          List<PersonSummary> findAllProjectedBy();
      
          @Query(value = "SELECT name AS name, age AS age FROM Person")
          List<PersonSummary> findAllProjectedJpaQuery();
      
          @Query(value = "SELECT name AS name, age AS age FROM Person", nativeQuery = true)
          List<PersonSummary> findAllProjectedNativeQuery();
      
          PersonSummary findProjectedById(long id);
      
          @Query(value = "SELECT name AS name, age AS age FROM Person WHERE id = ?1")
          PersonSummary findProjectedByIdJpaQuery(long id);
      
          @Query(value = "SELECT name AS name, age AS age FROM Person WHERE id = ?1", nativeQuery = true)
          PersonSummary findProjectedByIdNativeQuery(long id);
      

      In the case of the native query which returns a list, it seems to work but the properties are in the wrong order - the name property contains the age value and the age property contains the name value. I think re-ordering the properties in the projection or query might resolve this but it seems rather fragile.

      In the case of the native query which returns a single projection a org.springframework.core.convert.ConversionFailedException exception is thrown.

      I know that a native query isn't necessary in this case but it's a demonstration of an issue I encountered whilst trying it on another project.

      Thanks!

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              olivergierke Oliver Drotbohm
              Reporter:
              roberthunt87@gmail.com Robert Hunt
              Last updater:
              Thomas Lang
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: