Hibernate executes additional query when fetching the parent-side of a one-to-one entity association

Question

I have these to entities, where FSAK_Nachricht has an @OneToOne relationship to FNACHRICHTAKTSTATUS.

@Entity
@Table(name = "FSAK_NACHRICHT")
public class FsakNachricht implements Serializable {
  private static final long serialVersionUID = 1L;

  @Id
  @SequenceGenerator(name = "FSAK_NACHRICHT_FNNR_GENERATOR", sequenceName = "SEQ_FSAK_NACHRICHT", allocationSize = 1)
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FSAK_NACHRICHT_FNNR_GENERATOR")
  @Column(name = "FN_NR", unique = true, nullable = false, updatable = false)
  private long fnNr;

  // one-to-one association to FNachrichtAktStatus
  @OneToOne
  @JoinColumn(name = "FN_NR")
  private FNachrichtAktStatus fnachrichtAktStatus;

  // Other attributes, Getter / Setter
}  

@Entity
@Table(name = "FNACHRICHTAKTSTATUS")
public class FNachrichtAktStatus implements Serializable {
  private static final long serialVersionUID = 1L;

  @OneToOne
  @Id
  @JoinColumn(name = "FN_NR", referencedColumnName = "FN_NR")
  private FsakNachricht fsakNachricht;

  @Column(name = "FN_AKTSTATUS", length = 30)
  private String fnAktStatus;

  //  Getter / Setter

}

What I want to do is loading a record from FSAK_Nachricht and fetch the FNACHRICHTAKTSTATUS with one query. So I wrote this criteria statement:

CriteriaBuilder builder = this.getCriteriaBuilder();
CriteriaQuery<FsakNachricht> criteriaQuery = builder.createQuery(FsakNachricht.class);
Root<FsakNachricht> rootFsakNachricht = criteriaQuery.from(FsakNachricht.class);

Join<FsakNachricht, FNachrichtAktStatus> joinFNachrichtAktStatus = (Join<FsakNachricht, FNachrichtAktStatus>) rootFsakNachricht.fetch(
    FsakNachricht_.fnachrichtAktStatus);

// I also join some other tables    

Predicate wherePrimaryKey = builder.equal(rootFsakNachricht.get(FsakNachricht_.fnNr), primaryKey);

criteriaQuery.where(wherePrimaryKey);

List<FsakNachricht> result = this.findByCriteriaQuery(criteriaQuery);

This results in the following statement, which looks perfect for me - except the projection as the attribut of the @OneToOne table is not listed

2020-01-22T15:00:15,358 DEBUG [AThreadPool : 2] o.h.SQL:92 - 
    select
        fsaknachri0_.FN_NR as FN_NR1_11_0_,
        // Other attributes of fsaknachri0_
        // Attributs of nachrichta2_
        // attributes of user tables
        // 
        // !!!
        // But none of fnachricht1_
from
    FSAK_NACHRICHT fsaknachri0_ 
inner join
    FNACHRICHTAKTSTATUS fnachricht1_ 
        on fsaknachri0_.FN_NR=fnachricht1_.FN_NR 
inner join
    NACHRICHTART nachrichta2_ 
        on fsaknachri0_.NRART_NR=nachrichta2_.NRART_NR 
inner join
    USER user3_ 
        on fsaknachri0_.FN_U1=user3_.U_NR
left outer join
    USER user4_ 
        on fsaknachri0_.FN_U2=user4_.U_NR
left outer join
    USER user5_ 
        on fsaknachri0_.FN_U3=user5_.U_NR 
where
    fsaknachri0_.FN_NR=<KEY>

Therefore another query is made

2020-01-22T15:22:44,244 DEBUG [AThreadPool : 1] o.h.SQL:92 - 
    select
        fnachricht0_.FN_NR as FN_NR2_7_0_,
        fnachricht0_.FN_AKTSTATUS as FN_AKTSTATUS1_7_0_ 
    from
        FNACHRICHTAKTSTATUS fnachricht0_ 
    where
        fnachricht0_.FN_NR=?            

How can I avoid this additional query and fetch the data (column fnAktStatus) with the wanted single statement? Further question: Are the additional queries the case why I cant use this.findByCriteriaQuerySingelResult(criteriaQuery); (when used Hibernate doesn't find any row, even if the generated query produces exact one row, when executed in SQLDeveloper)

Hibernate 5.2.18 (Last supported for JPA 2.1)

Answer 1

The fix this issue, you need to do two things.

Adding @MapsId to the child-side table

@MapsId is the only way you can map a true one-to-one table relationship:

@Entity
@Table(name = "FNACHRICHTAKTSTATUS")
public class FNachrichtAktStatus implements Serializable {
  private static final long serialVersionUID = 1L;

  @OneToOne
  @MapsId
  @JoinColumn(name = "FN_NR", referencedColumnName = "FN_NR")
  private FsakNachricht fsakNachricht;

  @Column(name = "FN_AKTSTATUS", length = 30)
  private String fnAktStatus;

  //  Getter / Setter

}

Without @MapsId, you'd use a one-to-many table relationship where the FK column has a unique constraint. This is not desirable since you'd waste one extra column.

Avoiding the parent-side association

First of all, the parent-side needs to use mappedBy, like this:

@Entity
@Table(name = "FSAK_NACHRICHT")
public class FsakNachricht implements Serializable {
  private static final long serialVersionUID = 1L;

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FSAK_NACHRICHT_FNNR_GENERATOR")
  @SequenceGenerator(name = "FSAK_NACHRICHT_FNNR_GENERATOR", sequenceName = "SEQ_FSAK_NACHRICHT", allocationSize = 1)
  @Column(name = "FN_NR", unique = true, nullable = false, updatable = false)
  private long fnNr;

  // one-to-one association to FNachrichtAktStatus
  @OneToOne(mappedBy = "fsakNachricht")
  private FNachrichtAktStatus fnachrichtAktStatus;

  // Other attributes, Getter / Setter
}  

This parent-side @OneToOne association generates the extra query, as it needs to fetch the association eagerly to know whether to assign the attribute to null or to a Proxy.

So, it's better if you remove this parent-side association:

@Entity
@Table(name = "FSAK_NACHRICHT")
public class FsakNachricht implements Serializable {
  private static final long serialVersionUID = 1L;

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FSAK_NACHRICHT_FNNR_GENERATOR")
  @SequenceGenerator(name = "FSAK_NACHRICHT_FNNR_GENERATOR", sequenceName = "SEQ_FSAK_NACHRICHT", allocationSize = 1)
  @Column(name = "FN_NR", unique = true, nullable = false, updatable = false)
  private long fnNr;

  // Other attributes, Getter / Setter
}  

You can always fetch the child entity via the parent entity identifier:

FNachrichtAktStatus fnachrichtAktStatus = entityManager.find(
    FNachrichtAktStatus.class, 
    fsakNachricht.getFnNr()
);

This way, you'll only fetch this association when needed, therefore avoiding N+1 query issues.

Answer 2

Your query looks fine, but there are a few issues with your mappings:

1. When you're modeling a bi-directional one-to-one association, you need to define the association with its join column on one entity (the owning side of the association) and reference that attribute on the other entity (the referencing side). The owning side maps the table that contains the foreign key column.
2. If you want to use the same primary key value for both entities, the entity that shall reuse the primary key value needs to own the association (map the foreign key column). You also need to annotate it with @MapsId and model a primary key attribute.

I adjusted your mappings based on the assumptions that FsakNachricht defines the primary key value and that the table FNACHRICHTAKTSTATUS shall reuse that value as the primary key and the foreign key reference. The primary key column in both tables is called FN_NR.

@Entity
@Table(name = "FSAK_NACHRICHT")
public class FsakNachricht implements Serializable {
  private static final long serialVersionUID = 1L;

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FSAK_NACHRICHT_FNNR_GENERATOR")
  @SequenceGenerator(name = "FSAK_NACHRICHT_FNNR_GENERATOR", sequenceName = "SEQ_FSAK_NACHRICHT", allocationSize = 1)
  @Column(name = "FN_NR", unique = true, nullable = false, updatable = false)
  private long fnNr;

  // one-to-one association to FNachrichtAktStatus
  @OneToOne(mappedBy = "fsakNachricht")
  private FNachrichtAktStatus fnachrichtAktStatus;

  // Other attributes, Getter / Setter
}  

@Entity
@Table(name = "FNACHRICHTAKTSTATUS")
public class FNachrichtAktStatus implements Serializable {
  private static final long serialVersionUID = 1L;

  @Id
  @Column(name = "FN_NR")
  private long fnNr;

  @OneToOne(fetch = FetchType.LAZY)
  @MapsId
  @JoinColumn(name = "FN_NR")
  private FsakNachricht fsakNachricht;

  @Column(name = "FN_AKTSTATUS", length = 30)
  private String fnAktStatus;

  //  Getter / Setter

}

Before you use this mapping, please be aware that you can't use lazy loading for the fnachrichtAktStatus attribute on the FsakNachricht with Hibernate because the foreign key is mapped by the other entity. I, therefore, recommend using a unidirectional mapping with an additional query.

Answer 3

It turned out, that my mapping was wrong. Changing it to the following solved the problem:

@Id
private Long id;

@OneToOne(fetch = FetchType.LAZY)
@MapsId
@JoinColumn(name = "FN_NR", referencedColumnName = "FN_NR")
private FsakNachricht fsakNachricht;

Solution provided by Vlad Mihalcea in his blog