java 带有 SqlResultSetMapping 和本机查询的 JPA 数据存储库

JPA Data Repositories with SqlResultSetMapping and native queries

提问by LeoRado

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?

我的实体彼此相关，但我无法使用 JPQL。我被迫使用本机 SQL。现在我想将这些结果映射到一个 ValueObject。需要明确的是，我不想获得 Object 数组 ( List<Object[]>)的列表。我有 6 个实体，我只需要其中的一些列。谁能给我一个关于如何从本机查询实现这种映射的示例？

Tutorialthat 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:

在我的 jpa-repository 模块中：

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:

关键是我不知道把这些注释放在哪里，所以我可以使用这种映射。在本机查询中，我不能使用new rs.rado.leo.mypackage.MyVO(...). 我收到以下错误：

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!

提前致谢！

回答by Michelan Arendse

Add the missing resultClass

添加缺少的 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);

回答by HopeKing

You are almost there, but for the below parts

你快到了，但对于下面的部分

1. The whole @SqlResultSetMappingand @NamedNativeQueryhas 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).

1. 整体@SqlResultSetMapping和@NamedNativeQuery必须存在于实体中，而不是值对象中。在您的情况下，它应该在 MyEntity 类中，而 ** 不是 ** MyVO 类。这应该可以解决您的异常。

2. 那还是不行。完成上述操作后，更改以下内容

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

3. 最后，在某些情况下，您需要明确定义@ColumnResult(name = "userFirstName")。如果它是像 ZonedDateTime 或 Boolean 这样的复杂字段，您可能必须明确声明 @ColumnResult(name = "date_created", type = ZonedDateTime.class)。

Hope that helps.

希望有帮助。

回答by Urosh T.

You need to mark your query as a query :) And you need to use MyVOinstead of MyEntity, because that is the entity you have your resulsts mapped to

您需要将您的查询标记为查询 :) 并且您需要使用MyVO而不是MyEntity，因为这是您将结果映射到的实体

@Repository
public interface MyRepository extends JpaRepository<MyVO, Long> {

    @Query(nativeQuery = true)
    List<MyVO> findAllOfMyVO(@Param("param1") String param1, @Param("param2") String param2);
}