How to map results of a Hibernate Query to a DTO object?

I have the following 3 Hibernate Entities within my Java Project:

CompanyStatus

@Entity(name = "company_status")
@Table(name = "company_status")
public class CompanyStatus implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @JsonProperty
    @Column(name = "company_status_id")
    private Integer companyStatusId;

    @JsonProperty
    @Column(name = "company_status_label")
    private String companyStatusLabel;

}

Employee Status

@Entity(name = "employee_status")
@Table(name = "employee_status")
public class EmployeeStatus implements Serializable {

	private static final long serialVersionUID = 1L;

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@JsonProperty
	@Column(name = "employee_status_id")
	private Integer employeeStatusId;

	@JsonProperty
	@Column(name = "employee_status_name")
	private String employeeStatusName;

	// many other fields
}

CompanyStatusEmployeeStatus (Entity linking the 2 entities- one to one relationship)

@Entity(name = "company_status_employee_status")
@Table(name = "company_status_employee_status")
public class CompanyStatusEmployeeStatus implements Serializable {

    // int(20)
    @Id
    @JsonProperty
    @Column(name = "company_status_id")
    private Integer companyStatusId;

    // int(20)
    @JsonProperty
    @Column(name = "employee_status_id")
    private Integer employeeStatusId;
}

I only want to return the necessary fields in my JSON response to the front end , so In order to do so I have created a smaller CompanyStatusDTO object that also has an EmployeeStatusDTO list nested

CompanyStatusDTO

public class CompanyStatusDTO {

    @JsonProperty
    private Integer companyStatusId;

    @JsonProperty
    private String companyStatusLabel;

    @JsonProperty
    private List <EmployeeStatusDTO> employeeStatusDTOs;

}

EmployeeStatusDTO

public class EmployeeStatusDTO {

    @JsonProperty
    private Integer employeeStatusId;

    @JsonProperty
    private String employeeStatusName;

}

However, I am relatively new to using Hibernate - is there a way that I can create a query that will map results directly from my MySQL DB to my CompanyStatusDTOobject?

If so, how can do I this?

you can directly map query result to you desired DTO using NativeQuery (datatype must match)

String q = "select ... from table"; // your sql query
Query query = getEntityManager().createNativeQuery(q, "EmployeeStatusDTO");
EmployeeStatusDTO data = (EmployeeStatusDTO) query.getSingleResult();
return data;

This is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

If you adapt the CompanyStatus and CompanyStatusEmployeeStatus entities a bit and add the following:

public class CompanyStatus implements Serializable {
  //...
  @OneToMany(mappedBy = "companyStatus")
  private Set<CompanyStatusEmployeeStatus> employeeStatuses;
}

public class CompanyStatusEmployeeStatus implements Serializable {
    //...
    @JsonProperty
    @ManyToOne(fetch = LAZY)
    @JoinColumn(name = "company_status_id", insertable = false, updatable = false)
    private CompanyStatus companyStatus;

    @JsonProperty
    @ManyToOne(fetch = LAZY)
    @JoinColumn(name = "employee_status_id", insertable = false, updatable = false)
    private EmployeeStatus employeeStatus;
}

Your model could look like the following:

@EntityView(CompanyStatus.class)
public interface CompanyStatusDTO {
    @IdMapping
    Integer getCompanyStatusId();
    String getCompanyStatusLabel();
    @Mapping("employeeStatuses.employeeStatus")
    List<EmployeeStatusDTO> getEmployeeStatusDTOs();
}
@EntityView(EmployeeStatus.class)
public interface EmployeeStatusDTO {
    @IdMapping
    Integer getEmployeeStatusId();
    String getEmployeeStatusName();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

CompanyStatusDTO c = entityViewManager.find(entityManager, CompanyStatusDTO.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

You can try this:

public class Dao{

	private SessionFactory sessionFactory;
    
    public Dao(SessionFactory sessionFactory) {
      this.sessionFactory = sessionFactory;
    }

	public <T> T save(final T o){
      return (T) sessionFactory.getCurrentSession().save(o);
	}


	public void delete(final Object object){
	  sessionFactory.getCurrentSession().delete(object);
	}

	public <T> T get(final Class<T> type, final Long id){
      return (T) sessionFactory.getCurrentSession().get(type, id);
	}

	public <T> List<T> getAll(final Class<T> type) {
	  final Session session = sessionFactory.getCurrentSession();
	  final Criteria crit = session.createCriteria(type);
  return crit.list();
	}
// and so on, you should get the idea

and you can then access like so in the service layer:

    private Dao dao;
   
    @Transactional(readOnly = true)
    public List<MyEntity> getAll() {
      return dao.getAll(MyEntity.class);
    }