hibernate - 当孩子为空时，JPA LEFT JOIN FETCH 导致多个选择语句

Question

这是我的查询：

 @Query("SELECT DISTINCT f FROM FlowNlp f LEFT JOIN FETCH f.flowSelection fs JOIN FETCH f.chatConversations cc " +
            "WHERE f.initialFlowTriggered IN( SELECT DISTINCT fd.name FROM FlowDefinition fd, FlowPermission fp WHERE fp.flowName = fd.name " +
            ") AND cc.flowStep = 'Run NLP'" +
            "AND f.created BETWEEN :start AND :end")
    List<FlowNlp> getFlowNlpBetween(@Param("start") Date start, @Param("end") Date end);

这里是实体：

---

FlowNlp.java

@Entity
@Table(name = "flow_nlp")
@Data
@TypeDef(name = "json", typeClass = JsonStringType.class)
@NoArgsConstructor
public class FlowNlp implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "PK")
    private int id;

    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "FLOW_SELECTION_KEY", referencedColumnName = "FLOW_SELECTION_KEY")
    @NotFound(action = NotFoundAction.IGNORE)
    private List<ChatConversation> chatConversations;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "FLOW_SELECTION_KEY")
    @NotFound(action = NotFoundAction.IGNORE)
    private FlowSelection flowSelection;

    @Column(name = "NLP_TYPE")
    private String nlpType;

    @Column(name = "FLOW_MATCH_JSON")
    @Type(type = "json")
    private Object flowMatchJson;

    @Column(name = "MATCH_DESCRIPTION")
    private String matchDescription;

    @Column(name = "INITIAL_FLOW_TRIGGERED")
    private String initialFlowTriggered;

    @Column(name = "JOB_ID")
    private String jobId;

    @Column(name = "IS_USED")
    private Boolean isUsed;

    @Column(name = "IS_TAUGHT")
    private Boolean isTaught;

    @Column(name = "IS_PRIMARY")
    private Boolean isPrimary;

    @Column(name = "CREATETS")
    @Temporal(TemporalType.TIMESTAMP)
    private Date created;

    @Column(name = "MODIFYTS")
    @Temporal(TemporalType.TIMESTAMP)
    private Date modified;
}

聊天对话.java

@Entity
@Table(name = "chat_conversation")
@Data
public class ChatConversation implements Serializable{

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "CHAT_CONVERSATION_PK")
    private int id;

    @Column(name = "FLOW_SELECTION_KEY")
    private String flowSelectionKey;

    @Column(name = "CHATTING_USER")
    private String chattingUser;

    @Column(name = "CHATTING_USERTYPE")
    private String chattingUserType;

    @Column(name = "BOT")
    private String bot;

    @Column(name = "CHAT_CLIENT")
    private String chatClient;

    @Column(name = "PHRASE")
    private String phrase;

    @Column(name = "FLOW_STEP")
    private String flowStep;

    @Column(name = "JOB_ID")
    private String jobId;

    @Column(name = "INBOUND")
    private Boolean inbound;

    @Column(name = "UU_RESPONSE_DELTA")
    private String uuResponseDelta;

    @Column(name = "UB_RESPONSE_DELTA")
    private String ubResponseDelta;

    @Column(name = "THREAD_LOCK")
    private String threadLock;

    @Column(name = "CREATETS")
    @Temporal(TemporalType.TIMESTAMP)
    private Date created;

    @Column(name = "MODIFYTS")
    @Temporal(TemporalType.TIMESTAMP)
    private Date modified;
}

FlowSelection.java

@Entity
@Table(name = "flow_selection")
@Data
public class FlowSelection {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "FLOW_SELECTION_KEY")
    private String id;

    @Column(name = "FINAL_SELECTED_FLOW")
    private String finalSelectedFlow;

    @Column(name = "FINAL_SELECTED_DESCRIPTION")
    private String finalSelectedDescription;

    @Column(name = "CREATETS")
    @Temporal(TemporalType.TIMESTAMP)
    private Date created;

    @Column(name = "MODIFYTS")
    @Temporal(TemporalType.TIMESTAMP)
    private Date modified;
}

---

每当 FlowNlp 结果没有 FlowSelection 时，Hibernate 似乎对 FlowSelection 表执行了 5 个以上相同的查询：

2020-02-04 15:19:01.808 DEBUG 56820 --- [nio-8081-exec-1] org.hibernate.SQL                        : select flowselect0_.flow_selection_key as flow_sel1_12_0_, flowselect0_.createts as createts2_12_0_, flowselect0_.final_selected_description as final_se3_12_0_, flowselect0_.final_selected_flow as final_se4_12_0_, flowselect0_.modifyts as modifyts5_12_0_ from flow_selection flowselect0_ where flowselect0_.flow_selection_key=?
2020-02-04 15:19:01.810 TRACE 56820 --- [nio-8081-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [1580844097880_TK82506_e6b17201db3acc14eab2fcfaae961914]
2020-02-04 15:19:01.859 DEBUG 56820 --- [nio-8081-exec-1] org.hibernate.SQL                        : select flowselect0_.flow_selection_key as flow_sel1_12_0_, flowselect0_.createts as createts2_12_0_, flowselect0_.final_selected_description as final_se3_12_0_, flowselect0_.final_selected_flow as final_se4_12_0_, flowselect0_.modifyts as modifyts5_12_0_ from flow_selection flowselect0_ where flowselect0_.flow_selection_key=?
2020-02-04 15:19:01.859 TRACE 56820 --- [nio-8081-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [1580844097880_TK82506_e6b17201db3acc14eab2fcfaae961914]
2020-02-04 15:19:01.892 DEBUG 56820 --- [nio-8081-exec-1] org.hibernate.SQL                        : select flowselect0_.flow_selection_key as flow_sel1_12_0_, flowselect0_.createts as createts2_12_0_, flowselect0_.final_selected_description as final_se3_12_0_, flowselect0_.final_selected_flow as final_se4_12_0_, flowselect0_.modifyts as modifyts5_12_0_ from flow_selection flowselect0_ where flowselect0_.flow_selection_key=?
2020-02-04 15:19:01.893 TRACE 56820 --- [nio-8081-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [1580844097880_TK82506_e6b17201db3acc14eab2fcfaae961914]
2020-02-04 15:19:01.920 DEBUG 56820 --- [nio-8081-exec-1] org.hibernate.SQL                        : select flowselect0_.flow_selection_key as flow_sel1_12_0_, flowselect0_.createts as createts2_12_0_, flowselect0_.final_selected_description as final_se3_12_0_, flowselect0_.final_selected_flow as final_se4_12_0_, flowselect0_.modifyts as modifyts5_12_0_ from flow_selection flowselect0_ where flowselect0_.flow_selection_key=?
2020-02-04 15:19:01.920 TRACE 56820 --- [nio-8081-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [1580844097880_TK82506_e6b17201db3acc14eab2fcfaae961914]
2020-02-04 15:19:01.950 DEBUG 56820 --- [nio-8081-exec-1] org.hibernate.SQL                        : select flowselect0_.flow_selection_key as flow_sel1_12_0_, flowselect0_.createts as createts2_12_0_, flowselect0_.final_selected_description as final_se3_12_0_, flowselect0_.final_selected_flow as final_se4_12_0_, flowselect0_.modifyts as modifyts5_12_0_ from flow_selection flowselect0_ where flowselect0_.flow_selection_key=?
2020-02-04 15:19:01.950 TRACE 56820 --- [nio-8081-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [1580844097880_TK82506_e6b17201db3acc14eab2fcfaae961914]

当返回许多没有 FlowSelection 的行时，这会导致希望查询的时间更长。

我试过只是做一个正常的JOIN FETCH，但不再返回其中有一个空 FlowSelection 的行。我该怎么做才能得到与我得到的结果相同但没有所有额外（不必要的？）查询的结果？

标签： hibernatejpajoin