java用数据库结果对象映射视图

Question

我试图用resultset对象映射一个视图，我在Spring JPA中查询Postgres DB后得到了这个对象。 但我不知道该怎么做，任何帮助都将不胜感激

 @Query(nativeQuery = true, value = "select e.job_number, p.tag_number, e.drawing_number, p.part_number, "
            + "eoa.operation_order, peoa.name, peoa.status, peoa.comment, peoa.last_updated_by, "
            + "peoa.last_updated_date, peoa.revtype "
            + "from brs.events e "
            + "join brs.parts p on e.id = p.event_id "
            + "join brs.event_operations_audit eoa on eoa.event_id = e.id "
            + "join brs.part_event_operations_audit peoa on peoa.part_id = p.id and peoa.event_operation_id = eoa.id "
            + "where e.id = :eventId "
            + "and e.is_received = true "
            + "and e.is_workscope_reviewed = true "
            + "order by p.tag_number, eoa.operation_order, peoa.last_updated_date ")
    Set<Object> getJobPartRecentActivityData(@Param("eventId")Long eventId);

https://pastebin.com/iDZDhpce

Answer 1

这可以通过以下示例实现。 创建一个类，比如BookValueMappig

@SqlResultSetMapping(
        name = "BookValueMapping",
        classes = @ConstructorResult(
                targetClass = BookValue.class,
                columns = {
                    @ColumnResult(name = "id", type = Long.class),
                    @ColumnResult(name = "title"),
                    @ColumnResult(name = "version", type = Long.class),
                    @ColumnResult(name = "authorName")}))

然后在JPA中包括映射文件：

List<BookValue> results = this.em.createNativeQuery("SELECT b.id, b.title, b.version, a.firstName || a.lastName as authorName FROM Book b JOIN Author a ON b.author_id = a.id", "BookValueMapping").getResultList();