Jpa:How to Convert a Native Query Result Set to Pojo Class Collection

JPA : How to convert a native query result set to POJO class collection

JPA provides an SqlResultSetMapping that allows you to map whatever returns from your native query into an Entity or a custom class.

EDIT JPA 1.0 does not allow mapping to non-entity classes. Only in JPA 2.1 a ConstructorResult has been added to map return values a java class.

Also, for OP's problem with getting count it should be enough to define a result set mapping with a single ColumnResult

Spring JPA. How to map from @Query(nativeQuery = true) to a POJO

You can use DTO projection with native queries:

// Projection Interface
public interface UserProjection {
    String getName();
    String getEmail();
    Integer getId();
    String getComment();
}

public interface UserRepository extends CrudRepository<User, Integer> {
    @Query(value = "select  u.name, u.email, c.comment from User u join 
                    Comment c on u.id = c.user_id where u.id in :ids", nativeQuery = true)
        List<UserProjection> getUserInterface(List<Integer> ids);
    }

This is one example I recently tried with DTO projections. This will Simply map result of the native query to UserProjection.
For more information read: Spring Data JPA Projection support for native queries

How to map JPA query results to a POJO?

Try using the constructor:

@Query("SELECT new TestPojo(tt, at.field, at.anotherField) from TableTest tt LEFT JOIN AnotherTable at ON at.commonField = tt.commonField")
List<TestPojo> findAllPojo(List<TableTestDTO> TableTestDTOList);

Of course such constructor must exist and even better would be to place the fully qualified name instead of bare TestPojo.

How to map the result set of a JPA NativeQuery to a POJO using SqlResultSetMapping

@SqlResultSetMapping annotation should not be put on a POJO. Put it at (any) @Entity class. "Unknown SqlResultSetMapping [foo]" tells you, that JPA provider doesn't see any mapping under name 'foo'. Please see another answer of mine for the correct example

• JPA- Joining two tables in non-entity class

Spring Data JPA map the native query result to Non-Entity POJO

Assuming GroupDetails as in orid's answer have you tried JPA 2.1 @ConstructorResult?

@SqlResultSetMapping(
    name="groupDetailsMapping",
    classes={
        @ConstructorResult(
            targetClass=GroupDetails.class,
            columns={
                @ColumnResult(name="GROUP_ID"),
                @ColumnResult(name="USER_ID")
            }
        )
    }
)

@NamedNativeQuery(name="getGroupDetails", query="SELECT g.*, gm.* FROM group g LEFT JOIN group_members gm ON g.group_id = gm.group_id and gm.user_id = :userId WHERE g.group_id = :groupId", resultSetMapping="groupDetailsMapping")

and use following in repository interface:

GroupDetails getGroupDetails(@Param("userId") Integer userId, @Param("groupId") Integer groupId);

According to Spring Data JPA documentation, spring will first try to find named query matching your method name - so by using @NamedNativeQuery, @SqlResultSetMapping and @ConstructorResult you should be able to achieve that behaviour

How to map a Native Query to a POJO, when I do not have any Entity on my project?

Actually I found the answer I was looking for:

I can define @SqlResultSetMapping's behavior using XML in orm.xml, so this definition:

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

Would be defined in XML like this:

<sql-result-set-mapping name="BookValueMappingXml">
    <constructor-result target-class="org.thoughts.on.java.jpa.value.BookValue">
        <column name="id" class="java.lang.Long"/>
        <column name="title"/>
        <column name="version" class="java.lang.Long"/>
        <column name="authorName"/>
    </constructor-result>
</sql-result-set-mapping>

Allowing me to do define it without needing an entity.

use a non-entity class as a result class for native query

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

---

Related Topics