Hibernate: returning a DTO that is not an Entity?

Question

I have the following ContactDTO java POJO that is a trimmed down version of my Contact entity that contains over 100 fields/columns

ContactDTO:

public class ContactDTO {

    @JsonProperty
    private Integer contactId;

    @JsonProperty
    private String userName;

    @JsonProperty
    private String firstName;
    
    //getters and setters...
    
}

I am trying to return this in a hibernate query as follows, note that this query works as expected when I run it manually in MYSQL Workbench:

@Override
public ContactDTO getContactDTObyId(String client, Integer id) throws ATSException {

    EntityManager entityManager = null;
    try {

        entityManager = entityManagement.createEntityManager(client);

        String queryString = "select contact_id as contactId, username as userName, first_name as firstName from " + client + ".contact where "+" contact_id = " + id + "";

        Query query = entityManager.createNativeQuery(queryString, ContactDTO.class);

        return (ContactDTO) query.getSingleResult();

    } catch (Exception e) {
        log.error("An error is thrown in getContactDTObyId");
    } finally {
        entityManagement.closeEntityManager(client, entityManager);
    }
}

The above is giving me the following error at the getSingleResult() line:

org.hibernate.MappingException: Unknown entity: ContactDTO

How can I return this DTO object using Hibernate?

I am aware that this is not an Entity in the way that my Contact Entity maps to the Contact database table, but I thought I could still return it using Hibernate by populating the fields.

score 2 · Accepted Answer · answered Jan 15 '21 at 10:01

Specifying a class as the result of a native query only works with entity classes. To specify an DTO (and unmanaged class in the spec), you need to specify a constructor on your DTO and declare a SqlResultMapping

Assuming you declare a three argument constructor on ContactDTO you should add:

@SqlResultSetMapping(name = "contactDTOResult",
    classes = {
        @ConstructorResult(targetClass = ContactDTO.class,
            columns = {
                @ColumnResult(name = "contactId"),
                @ColumnResult(name = "userName"),
                @ColumnResult(name = "firstName")})
    })

And in your native query use the result set mapping name contactDTOResult:

Query query = entityManager.createNativeQuery(queryString, "contactDTOResult");

Hibernate: returning a DTO that is not an Entity?

1 Answers1