[FIXED] use a non-entity class as a result class for native query

Issue

every example/tutorials on the net deals with entity classes, when it comes to stored procedures (functions), where the SP collects data only from this entity. While it is quite common, that we have a complex query involving multiple tables and we want to have back some columns. For this we have can use a function in postgresql with or without any parameters.

I have this piece of code for calling such a function:

@Component
public class CapPlanningItemPerCapGroupImpl implements CapPlanningItemPerCapGroupRepository {
    private final EntityManager em;

    public CapPlanningItemPerCapGroupImpl(EntityManager em) {
        this.em = em;
    }

    @Override
    public List<CapPlanningItemPerCapGroup> getCapPlanningItems(Long companyId, Long plantId, int year, Long costAccTypeId) {
        Session session = em.unwrap(Session.class);
        NativeQuery<CapPlanningItemPerCapGroup> query = session.createNativeQuery(
                "SELECT * FROM get_cap_planning_items(:year, :companyId, :plantId, :costAccTypeId)", CapPlanningItemPerCapGroup.class);
        query.setParameter("year", year);
        query.setParameter("companyId", companyId);
        query.setParameter("plantId", plantId);
        query.setParameter("costAccTypeId", costAccTypeId);

        return query.getResultList();
    }
}

The CapPlanningItemPerCapGroup class isn't annotated with @Entity, since I don't want to store it as a datatable in the db. It's like a custom type. But with this setting, the above query fails, saying, that CapPlanningItemPerCapGroup is an unknown entity.

If I remove this CapPlanningItemPerCapGroup.class from the query call, I get the result in postman, but of course without any field names, just the raw data.

So, my question is: do I really need to convert the result list manually to the appr. class type, or is there any mapping automatism out of the box, where I don't have to list all the returned column names and types?

Thanks.

Solution

You can use an @SqlResultSetMapping to describe a constructor call that Hibernate will perform for every record in the result set. I explain that in detail in Result Set Mapping: Constructor Result Mappings on my blog.

The general idea is simple:

Your CapPlanningItemPerCapGroup requires a constructor that sets all attributes.

In the @SqlResultSetMapping, you describe the constructor call by referencing the class and listing the columns in your result set in the order in which you want to provide them to the constructor, e.g.:

@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")}))

After you defined your mapping, you can provide its name as the 2nd parameter to the createNativeQuery method.

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();

Answered By - Thorben Janssen
Answer Checked By - David Goodson (JavaFixing Volunteer)