JPA Unknown SqlResultSetMapping

Question

I have

@SqlResultSetMapping(
        name = "OrderDetailsReportMapping",
        entities = @EntityResult(
                entityClass = OrderDetailsReportDto.class,
                fields = {
                    @FieldResult(name = "orderId", column = "col_0_0_"),
                    @FieldResult(name = "orgId", column = "col_1_0_")
}))
public class OrderDetailsReportMapping {

}

and OrderDetailsReportDto.class:

public class OrderDetailsReportDto {
    private Long orderId;
    private Long orgId;
}

and in my OrderManager.class:

StringBuilder queryView = new StringBuilder("SELECT * FROM order_details_report_view");
        Query dataQuery = em.createNativeQuery(queryView.toString(), "OrderDetailsReportMapping");

but I got error:

Unknown SqlResultSetMapping [OrderDetailsReportMapping]

Please help. Thanks.

Does this answer your question? [Mapping NativeQuery results into a POJO](https://stackoverflow.com/questions/25188939/mapping-nativequery-results-into-a-pojo) — kasptom, Mar 14 '20 at 09:53
See [5.1. Single Entity](https://www.baeldung.com/jpa-sql-resultset-mapping#1-single-entity). "EntityResult requires us to specify *the entity class*" — kasptom, Mar 14 '20 at 10:03
Are `col_0_0_` and `col_1_0_` the names of the *only two columns* in your (database) view? — kasptom, Mar 14 '20 at 13:23

kasptom · Answer 1 · 2020-03-14T21:36:47.837

The main problem, I guess, is the usage of entityClass = OrderDetailsReportDto.class in the @SqlResultSetMapping. From Baeldung's tutorial "A Guide to SqlResultSetMapping: 5.1 Single Entity"

EntityResult requires us to specify the entity class

You should use the class annotated with @Entity to store the result.

EDIT#1 for example:

@SqlResultSetMapping(
        name = "OrderDetailsReportMapping",
        entities = @EntityResult(
                entityClass = OrderDetailsView.class,
                fields = {
                        @FieldResult(name = "orderId", column = "first_db_view_col_name"),
                        @FieldResult(name = "orgId", column = "second_db_view_col_name")
                }))
@Entity
@Immutable
public class OrderDetailsView {
    @Id
    private Long orderId;

    private Long orgId;

    @Override
    public String toString() {
        return "OrderDetailsView{" +
                "orderId=" + orderId +
                ", orgId=" + orgId +
                '}';
    }
}

 Query query = em.createNativeQuery(
                "select * from order_details_report_view",
                "OrderDetailsReportMapping");
 List<OrderDetailsView> orderDetailsViews = query.getResultList();

EDIT#2 If "orderId" is not unique you can treat the rownumber as the id column: 1, 2

Tested on Postgres database:

        fields = {
added >>>               @FieldResult(name = "id", column = "fakeId"),
                        @FieldResult(name = "orderId", column = "first_db_view_col_name"),
                        @FieldResult(name = "orgId", column = "second_db_view_col_name")
        }

 em.createNativeQuery(
added >>>       "select row_number() OVER () as \"FakeId\", * "
                + "from order_details_report_view",
                "OrderDetailsReportMapping");

But when I use @Entity, I got this error: No identifier specified for entity: com.mycompany.OrderDetailsReportMapping. Please help. thanks. — Shazam, Mar 14 '20 at 11:49
You need to specify the `@Id` of your `@Entity`, see the updated answer — kasptom, Mar 14 '20 at 13:27

JPA Unknown SqlResultSetMapping

1 Answers1