19

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!

Balasubramanian
  • 700
  • 7
  • 26
LeoRado
  • 331
  • 1
  • 3
  • 10
  • The anser is here: https://stackoverflow.com/a/42942353/355438 – Ilya Serbis Oct 17 '17 at 09:57
  • Some of the annotations seems to be outdated. I looked at this page to make them work. https://github.com/eugenp/tutorials/blob/master/persistence-modules/java-jpa/src/main/java/com/baeldung/jpa/sqlresultsetmapping/Employee.java – al gh Oct 19 '20 at 07:45

4 Answers4

23

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);
Michelan Arendse
  • 270
  • 2
  • 6
6

You are almost there, but for the below parts

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

  2. That will still not do. After you do the above, change the below

    @NamedNativeQuery(name = "findAllDataMapping", to
    @NamedNativeQuery(name = "MyEntity.findAllDataMapping",

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

HopeKing
  • 3,317
  • 7
  • 39
  • 62
  • This did not work for me @NamedNativeQuery(name = "MyEntity.findAllDataMapping", Instead I did as Michelan Arendse suggested @NamedNativeQuery(name = "findAllDataMapping", resultClass = MyVO.class, resultSetMapping ="findAllDataMapping" query = "sql") – al gh Oct 19 '20 at 07:40
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);
}
Urosh T.
  • 3,336
  • 5
  • 34
  • 42
  • Sorry, @Urosh T. I know your answer is from 2012. I'm trying to do something like that but when the repository is initialize I obtain the error "Not a managed type" even if I declare no method in it. Do you know why? Thanks – elamas Nov 22 '17 at 15:04
  • @elamas, it is from September 12th this year :) `"Not a managed type"` probably means that Spring hasn't scanned your @Entity class or the package containing the class. Make sure you have in your `application.properties` line that says `entitymanager.packagesToScan=com.mypack.entity` – Urosh T. Nov 23 '17 at 12:21
  • Hi @Urosh T, You're right, "September 12th this year :)". I think that the problem is that MyVO class is not an entity. But don't worry, I have found another aproaches, thanks. – elamas Nov 24 '17 at 13:58
  • 2
    How did you solve the `Not a managed type` problem, @elamas? In my case, MyVO is indeed **not** an Entity, but I want to map the results of my query to MyVO nonetheless. – Hannon Queiroz Oct 16 '18 at 14:34
0

your @NamedNativeQuery are missing properties resultClass and resultSetMapping like below sample:

@NamedNativeQuery(name = "YourEntity.findAllDataMapping",
    resultClass =  MyVO.class ,
    resultSetMapping ="findAllDataMapping",
    query = "SELECT ..."
)
helvete
  • 2,455
  • 13
  • 33
  • 37
AlbertOu
  • 31
  • 4