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

Issue

I have a Spring Data repository method with a native query

@Query(value = "SELECT g.*, gm.* FROM group g LEFT JOIN group_members gm ON g.group_id = gm.group_id and gm.user_id = :userId WHERE g.group_id = :groupId", nativeQuery = true)
GroupDetails getGroupDetails(@Param("userId") Integer userId, @Param("groupId") Integer groupId);

and I'd like to map the result to Non-Entity POJO GroupDetails.

Is it possible and if so, could you please provide an example ?

Solution

Assuming GroupDetails as in orid's answer have you tried JPA 2.1 @ConstructorResult?

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

@NamedNativeQuery(name="getGroupDetails", query="SELECT g.*, gm.* FROM group g LEFT JOIN group_members gm ON g.group_id = gm.group_id and gm.user_id = :userId WHERE g.group_id = :groupId", resultSetMapping="groupDetailsMapping")

and use following in repository interface:

GroupDetails getGroupDetails(@Param("userId") Integer userId, @Param("groupId") Integer groupId);

According to Spring Data JPA documentation, spring will first try to find named query matching your method name - so by using @NamedNativeQuery, @SqlResultSetMapping and @ConstructorResult you should be able to achieve that behaviour

Answered By - Daimon
Answer Checked By - Mildred Charles (JavaFixing Admin)