JPA Data Repositories with SqlResultSetMapping and native queries
Solution 1
Add the missing resultClass
@NamedNativeQuery(name = "findAllDataMapping", resultClass = Entity.class, query="sql")
Or
@NamedNativeQuery(name = "findAllDataMapping", resultClass = MyVO.class, resultSetMapping ="findAllDataMapping" query = "sql")
and lastly call the query in your repository
@Query(nativeQuery = true, name = "findAllDataMapping")
List<MyVO> findAllOfMyVO(@Param("param1") String param1, @Param("param2") String param2);
Solution 2
You are almost there, but for the below parts
- The whole
@SqlResultSetMapping
and@NamedNativeQuery
has to be present in the Entity and not the value Object. In your case it should be in the MyEntity class and **not ** the MyVO class. This should resolve your exception. That will still not do. After you do the above, change the below
@NamedNativeQuery(name = "findAllDataMapping", to
@NamedNativeQuery(name = "MyEntity.findAllDataMapping",Finally, in some cases you need to be explicit in your definition of @ColumnResult(name = "userFirstName"). If it is a complex field like ZonedDateTime or Boolean you may have to explicity state @ColumnResult(name = "date_created", type = ZonedDateTime.class).
Hope that helps.
Solution 3
You need to mark your query as a query :) And you need to use MyVO instead of MyEntity, because that is the entity you have your resulsts mapped to
@Repository
public interface MyRepository extends JpaRepository<MyVO, Long> {
@Query(nativeQuery = true)
List<MyVO> findAllOfMyVO(@Param("param1") String param1, @Param("param2") String param2);
}
LeoRado
Updated on July 05, 2022Comments
-
LeoRado almost 2 years
I was stuck with the following situation:
My entities are related to each other, but in such a way that i could not use JPQL. I was forced to use native SQL. Now I want to map these results to a ValueObject. To be clear, I don't want to get a list of Object array (
List<Object[]>
). I have 6 entities from which I need only some columns. Can anybody give me an example on how to implement such a mapping from a native query?Tutorial that I went through.
My code:
@SqlResultSetMapping( name = "findAllDataMapping", classes = @ConstructorResult( targetClass = MyVO.class, columns = { @ColumnResult(name = "userFirstName"), @ColumnResult(name = "userLastName"), @ColumnResult(name = "id"), @ColumnResult(name = "packageName") } ) ) @NamedNativeQuery(name = "findAllDataMapping", query = "SELECT " + " u.first_name as userFirstName, " + " u.last_name as userLastName, " + " i.id as id, " + " s.title as packageName, " + "FROM " + " invoice as i " + "JOIN user as u on i.user_id=u.id " + "LEFT JOIN subscription_package as s on i.subscription_package_id=s.id " + "where u.param1=:param1 and i.param2=:param2" + ) public class MyVO { private String userFirstName; private String userLastName; private Long id; private String packageName; public MyVO (String userFName, String userLName, Long id, String packageName) { this.userFirstName = userFName; this.userLastName = userLName; this.id = id; this.packageName = packageName; } // getters & setters }
In my jpa-repository module:
public interface MyRepository extends JpaRepository<MyEntity, Long> { List<MyVO> findAllOfMyVO(@Param("param1") String param1, @Param("param2") String param2); }
The point is that I don't know where to put these annotations so I can use this kind of mapping. In a native query I can't use
new rs.rado.leo.mypackage.MyVO(...)
. I got following error:Caused by:
org.springframework.data.mapping.PropertyReferenceException: No property findAllOfMyVO found for type MyEntity!
I suppose that my question is clear. If not, let me know so I can edit my question.
Thanks in advance!