0

I haven't been able to find any specific solutions:

I have a Native Query in Spring Data, and need to map the resultset to a custom data bean object.

@Query(query = "select id as activityId, activity_title as activityTitle,
                 activity_instructions as activityInstructions   from activities_t  " +
               "where case when activity_code ~ '^\\d+$' " +  // Digits only 
               "           then cast(activity_code as int) between :firstId and :lastActivityId end", 
                  resultSetMapping="myQueryResultSet",         
                  name = "myQuery")
public List<Activity> findActivities(@Param("firstId") int firstId, @Param("lastId") int lastId);

The problem is, this needs to return a custom object, Activity, rather than the @Entity object ActivitiesT.

Activity is a custom POJO that has a subset of ActivitiesT's fields, and some of the columns are slightly differently named:

Activity.java, 3 fields with getters/setters

activityId   (ACTIVITIES_T.ID)  <-- this is differently-named
activityTitle (ACTIVITIES_T.ACTIVITY_TITLE)  <-- same name
activityInstructions (ACTIVITIES_T.ACTIVITY_INSTRUCTIONS)  <-- same name

I found one solution here, but it doesn't work for me because I have a differently-named alias: Spring Data JPA map the native query result to Non-Entity POJO

Their solution is to use 

@SqlResultSetMapping(
    name="groupDetailsMapping",
    classes={
        @ConstructorResult(
            targetClass=GroupDetails.class,
            columns={
                @ColumnResult(name="GROUP_ID"),
                @ColumnResult(name="USER_ID")
            }
        )
    }
)

They assume in the answer that all the columns in the custom POJO are named the same as in the expected @Entity object. For me that's not the case. Also, I can't put a SqlResultSetMapping on my Spring Data JPA Repository method.

Any solutions to this?

gene b.
  • 10,512
  • 21
  • 115
  • 227
  • Maybe [this](https://stackoverflow.com/a/50103729/5380322) can help... – Cepr0 May 02 '18 at 16:21
  • @Cepr0 -- now I'm getting the error `Unknown alias` on some returned columns. They can sometimes be NULL. `java.lang.IllegalArgumentException: Unknown alias [activityinstructions]` – gene b. May 02 '18 at 17:34
  • Futher info on this: https://jira.spring.io/si/jira.issueviews:issue-html/DATAJPA-1264/DATAJPA-1264.html – gene b. May 02 '18 at 17:40
  • But anyway, what if I don't want to create a new Projection -- suppose I already have an existing data bean I'd like to use. I should be able to reuse it with a simple mapping. – gene b. May 02 '18 at 18:03
  • What makes you think Spring Data sees that as a native query? https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/Query.html#nativeQuery-- – Alan Hay May 02 '18 at 22:45

0 Answers0