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