DTO Query with JPA CriteriaQuery and Hibernate using entity as a DTO constructor creates many selects

Question

hibernate 5.2.10.Final

jpa 2.1

I want to map a projection query to a DTO (Data Transfer Object) with JPA Criteria Query and Hibernate. I specify a constructor that will be applied to the results of the query execution.

If the constructor is for entire entity class, I have multiple of selects instead one(it is a long running process for thousands of records). If the constructor is for a set of params of the Entity then I see only one select in the console. I can't understand where I've mistaken or is it a bug?

public class ServiceDAO {

      public List<ServicesDTO> getAllServicesByFilter(ServicesFilter filter) {

          CriteriaBuilder cb = entityManager.getCriteriaBuilder();
          CriteriaQuery<ServicesDTO> criteria = cb.createQuery(ServicesDTO.class);
          Root<ServicesEntity> serviceEntity = criteria.from(ServicesEntity.class);

          // here is only one select to get list of services    
          criteria.select(cb.construct(ServicesDTO.class, serviceEntity.get("active"), serviceEntity.get("providerId"), serviceEntity.get("serviceId")));

          // in this case I have multiple selects
          //criteria.select(cb.construct(ServicesDTO.class, serviceEntity)); 
          if(filter != null) {
             List<Predicate> pcl = new ArrayList<Predicate>();

             if(filter.getActive() != null)
                pcl.add(cb.equal(serviceEntity.get("active"), filter.getActive()));
             if(filter.getProviderId() != null)
                pcl.add(cb.equal(serviceEntity.get("providerId"), filter.getProviderId()));
             if(filter.getServiceId() != null)
                pcl.add(cb.equal(serviceEntity.get("serviceId"), filter.getServiceId()));

             criteria.where(pcl.toArray(new Predicate[pcl.size()]));
          }

          return entityManager.createQuery(criteria).getResultList();
      }

}

-

public class ServicesDTO implements Serializable {

    private static final long serialVersionUID = 1L;

    private Boolean active;
    private Integer providerId;
    private Integer serviceId;

    public ServicesDTO() {}

    public ServicesDTO(Boolean active, String providerId, Integer serviceId) {

         this.active = active;
         this.providerId = Integer.parseInt(providerId);
         this.serviceId = serviceId;
    }


    public ServicesDTO(ServicesEntity service) {

       if(service != null) {
           this.active = service.isActive();
           this.providerId = Integer.parseInt(service.getProviderId());
           this.serviceId = service.getServiceId();
       }

    // getters & setters

}

-

@Entity
@Table
public class ServicesEntity {

   @Id
   @Column(name = "id", unique = true)
   @GeneratedValue(strategy=GenerationType.IDENTITY)
   private Long id;

   @Column(name = "serviceId", nullable = false)
   private int serviceId;

   @Column(nullable = false)
   private String providerId;

   @ManyToOne(fetch=FetchType.LAZY)
   @JoinColumn(name="categoryId")
   private Categories categoryId;

   private boolean active;

   @OneToMany(fetch = FetchType.LAZY, mappedBy = "service", cascade = CascadeType.ALL)
   private List<Service_Area_Ref> areas = new ArrayList<Service_Area_Ref>();

   @ManyToOne(fetch=FetchType.LAZY, optional = true)
   @JoinColumn(name="parentCatId")
   private Categories parentCatId;

   public ServicesEntity() {}

   public ServicesEntity(int serviceId) {
       this.serviceId = serviceId;
   }

   // getters & setters

   // equals & hashcode

}

Answer 1

I am using Hibernate 5.3 and also encounter this behaviour. But I found that if using JPA Tuple as a DTO container and multiselect, this problem will not happen. So my final solution is use Tuple to query the result set first and then convert it to DTO manually , something likes:

CriteriaQuery<Tuple> criteria = cb.createTupleQuery();

.......

criteria.multiselect(serviceEntity);

List<ServicesDTO>  result = entityManager.createQuery(criteria).getResultList().stream()
.map(t->new ServicesDTO(t.get(0,ServicesEntity.class)))
.collect(toList());         

Answer 2

Yea, so it does. There is probably not much of a use case for that. Given

@Entity
public class A {
    @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;
    private Integer value;

public class ADto {
    private Integer va;

    public ADto(A a) {
        this.va = a.getValue();
    }
    public ADto(Integer va) {
        this.va = va;
    }

Then

tx.begin();

A a1 = new A();
a1.setValue(1);
A a2 = new A();
a1.setValue(2);
em.persist(a1);
em.persist(a2);

tx.commit();

em.clear();

System.out.println("As usual");
em.createQuery("select new dto.ADto(a.value) from A a where a.value <= 2", ADto.class).getResultList();

System.out.println("As A");
em.createQuery("select new dto.ADto(a) from A a where a.value <= 2", ADto.class).getResultList();

gives you

create table A (id integer generated by default as identity (start with 1), value integer, primary key (id))
create table B (id integer generated by default as identity (start with 1), value integer, primary key (id))
insert into A (id, value) values (default, ?)
insert into A (id, value) values (default, ?)
As usual
select a0_.value as col_0_0_ from A a0_ where a0_.value<=2
As A
select a0_.id as col_0_0_ from A a0_ where a0_.value<=2
select a0_.id as id1_0_0_, a0_.value as value2_0_0_ from A a0_ where a0_.id=?
select a0_.id as id1_0_0_, a0_.value as value2_0_0_ from A a0_ where a0_.id=?

And you don't like the fact that entity A is selected each time for a new ADto instance. It's probably done that way because you could have created a DTO with multiple entities, not just A, like A, B, and C and so how would JPA/Hibernate do that conveniently in a single select statement? While it could select all the attributes and then keep track of which attributes belong to which entities and then construct them and pass them to your DTO so you can deconstruct them that seems like a lot of work for a rare thing. It's probably more efficient and better all around if you select the attributes you want and make a constructor out of whatever that is, as in the first case.