hibernate messes up my query

Question

Hi I wrote the following Criteria-API query that creates a broken sql-select statement, because of the multiselect. If I uncomment the multiselect the query works as expected but the thing is I do not want have all the data. There are several relationships in my portal-object and loading them all is absolutely not necessary in my current case.

The method looks like this:

  @Override
  public Optional<Portal> loadPortalData(long clientId)
  {
    log.trace("loading data for client with id '{}'", clientId);
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Portal> criteriaQuery = criteriaBuilder.createQuery(Portal.class);
    Root<Portal> root = criteriaQuery.from(Portal.class);
    criteriaQuery.multiselect(root.get(Portal_.codes),
                              root.get(Portal_.certificate))
                 .where(criteriaBuilder.equal(root.get(Portal_.id), clientId));
    try
    {
      return Optional.of(entityManager.createQuery(criteriaQuery).getSingleResult());
    }
    catch (NoResultException noResult)
    {
      return Optional.empty();
    }
  }

and the broken query looks like this:

30 Mai 2017 07:12:56,305 [main] TRACE mypackage.repository.PortalDaoImpl (PortalDaoImpl.java:39) - loading data for client with id '1'
Hibernate: 
select
    . as col_0_0_,
    portal0_.certificate as col_1_0_ 
from
    portal portal0_ 
inner join
    Code authorisat1_ 
        on portal0_.id=authorisat1_.client_id 
inner join
    certificate certificat2_ 
        on portal0_.certificate=certificat2_.id 
where
    portal0_.id=1

any suggestions why hibernate is messing up my query like this if I use multiselect?

EDIT:

@Entity
@Table(name = "portal")
public class Portal
{
   ...
   @Valid
   @OneToMany(cascade = {CascadeType.ALL}, fetch = FetchType.EAGER, mappedBy = "client")
   private Set<Code> codes = new HashSet<>();

   @Valid
   @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
   @JoinColumn(name = "certificate", referencedColumnName = "id")
   private Certificate certificate;
   ...
}

and the Code class

@Entity
public class Code
{

  @Id
  @GeneratedValue
  private long id;

  @NotNull
  @Column(nullable = false, unique = true)
  private String code;

  @NotNull
  @ManyToOne(fetch = FetchType.EAGER, targetEntity = Portal.class)
  @JoinColumn(name = "client_id", referencedColumnName = "id", nullable = false)
  private Portal client;

  @NotNull
  @Temporal(TemporalType.TIMESTAMP)
  @Column(nullable = false)
  private Date creation_time;

  @Column(nullable = false)
  private int expires_in;
  ...
}

Answer 1

You can't select a whole collection (codes).

Assuming you want each row of the result to be composed of a code and of a certificate, the JPQL query should be

select code, portal.certificate from Portal portal 
left join portal.codes as code
where portal.id = :id

That will of course return as many rows as there are codes in the given portal, and not just one.

Avoiding to load other columns of the portal entity is probably a premature, unnecessary optimization. It should be much easier to just do

em.find(Portal.class, id)

or, if you want to load the codes and the certificate in the same query

select distinct portal from Portal portal 
left join fetch portal.certificate
left join fetch portal.codes
where portal.id = :id

which would return a unique row containing the portal, with the pre-fetched set of codes.

If you really want your application to be fast, you should instead make associations lazy by default (especially toMany associations), and use fetch joins when needed.