How to map results of an untracked SQL view to a DTO/POJO using JPA EntityManager?

Question

I'm stuck with a special use case.

The requirement is simple to explain: I want to query against a SQL view, and the tables that are joined within the view are not tracked by any entity and should not be. I want to use the EntityManager and map results automatically to my DTO/POJO.

What I unsuccessfully tried so far:

1. using em.createNativeQuery("select .... from MyView",MyDto.class);

   Results in Caused by: org.hibernate.MappingException: Unknown entity: MyDto.class

2. using SqlResultSetMapping in addition

   @Data
   @SqlResultSetMapping(name = "MyDto", classes = {
           @ConstructorResult(targetClass = MyDto.class,
                   columns = {
                           @ColumnResult(name = "id")
                           })
   })
   
   @AllArgsConstructor
   public class MyDto implements Serializable {
   
   

   Does not work either

3. using unwrap together with createNativeQuery Transformers.aliasToBean

   em.createNativeQuery("")
     .unwrap(SQLQuery.class)
     .setResultTransformer(Transformers.aliasToBean(MyDto.class));
   

   Results in syntax error near "." for whatever reason.

4. using unwrap together with createNativeQuery and AliasToEntityMapResultTransformer

   em.createNativeQuery("")
     .unwrap(SQLQuery.class)
     .setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE)
   

   Results in syntax error near "." for whatever reason as well.

Besides that, the setResultTransformer approach seems to be deprecated.

My current approach for a smaller DTO is manual mapping, but how can you do that automatically?

 em.createNativeQuery("")
   .getResultStream()
   .map(o -> {
       Object[] cols = (Object[]) o;
       //Do some ugly error-prone mapping here and return a new DTO object
       return new MyDto();
      })
   .filter(Objects::nonNull);

Answer 1

One option is to map the view to an entity. Just make sure to never insert or update it; JPA does not support it, the JPA provider might support read-only entities with provider-specific annotations. You may be able to enforce the no insert nor update policy with tricks (like entity listeners, insert/update=false primary key, ...). I would try this because of its simplicity.

Second option is to go as you say and use the result mapping facilities of JPA. To summarize, you have to use native queries, because the tables/views are not known to JPA. Then map the columns of the result to the DTO with @SqlResultSetMapping.

I implemented it in a scratch project, showing with my model so that the example is concrete:

First the DTO:

public class UserProjectDto {
    private long userId;

    private long projectId;

    private String userName;

    private String email;

    private String projectName;

    // default constructor, probably optional
    public UserProjectDto() {}

    // all-args constructor, mandatory
    public UserProjectDto(long userId, long projectId, String userName, String email, String projectName) {
        ...
    }

    // getters, setters
}

Then the (tedious) result set mapping; this has to go to a class known to JPA (e.g. an entity):

@SqlResultSetMappings({
    @SqlResultSetMapping(
        name = "UserProjectDto", // <- the name is used to reference the mapping
        classes = {
            @ConstructorResult(
                targetClass = UserProjectDto.class,
                columns = {
                    // order of column results matches the arguments of the constructor
                    // name matches the column in the result set (I like using the property name, but that's my preference)
                    @ColumnResult(name = "userId", type = long.class),
                    @ColumnResult(name = "projectId", type = long.class),
                    @ColumnResult(name = "userName"),
                    @ColumnResult(name = "email"),
                    @ColumnResult(name = "projectName")
                }
            )
        }
    )
})
public class SomeClassKnownToJpa {
    ...
}

Finally, the code to run it:

EntityManager em = ...
Query query = em.createNativeQuery(
        // note the "AS" names match the names of the @ColumnResult
        "SELECT " +
        "  u.id AS userId," +
        "  p.id AS projectId," +
        "  u.name AS userName," +
        "  u.email AS email," +
        "  p.name AS projectName " +
        "FROM APP_USER u, APP_PROJECT p",
        // reference the name of the @SqlResultSetMapping
        "UserProjectDto"
);
for( Object o : query.getResultList() ) {
    UserProjectDto u = (UserProjectDto) o;
    System.out.println(u.getUserName() + " - " + u.getProjectName());
}