최근에 실행 된 자식 엔티티를 선택하는 Hibernate

최근에 실행 된 자식 엔티티를 선택하는 Hibernate

1. 질문(문제점):

나는 실행 된 가장 최근 싶어 ChildEntityA로부터 ParentEntity마지막을 기반으로 lastExecutionTimestamp필드를. 작동하지 않는 다음 쿼리를 시도했습니다.

클래스 관계 : ParentEntity <1-oneToMany-x> ChildEntity <1-oneToMany-x> GrandChildEntity

는 ChildEntity필드가 lastExecutionTimestamp나는 최근에 실행 된 childEntity에 사용하려는. 이 메서드는 List<ChildEntity>하나의 레코드 만 반환한다고 가정 합니다. childEntity는 연관된 모든 것을 포함해야합니다 grandChildEntities.

모든 입력?

@Query(value = "select pr.childEntities from ParentEntity pr " +
   "inner join pr.childEntities ch ON pr.id = ch.parentEntity " +
   "inner join ch.grandChildEntities gc ON ch.id = gc.childEntity " +
   "where pr.bumId = ?1 and ch.lastExecutionTimestamp = ( select max(lastExecutionTimestamp) from ChildEntity)"
)
List<ChildEntity> findLastExecutedChildFromBumId(@Param("bumId") String bumId);

연관된 클래스 엔티티

@Entity
@Getter
@Setter
@Table(name = "table_parent")
@RequiredArgsConstructor
@NoArgsConstructor
@AllArgsConstructor
public class ParentEntity implements Serializable {
    
   private static final long serialVersionUID = -271246L;
    
   @Id
   @SequenceGenerator(
      name="p_id",
      sequenceName = "p_sequence",
      initialValue = 1,
      allocationSize = 1)
   @GeneratedValue(generator="p_id")
   @Column(name="id", updatable=false, nullable=false)
   private Long id;
    
   @NonNull
   @Column(name ="bum_id", nullable = false, unique = true)
   private String bumId;
    
   @NonNull
   @Column(nullable = false, length = 31)
   private String f1;
    
   @NonNull
   @Column(nullable = false, length = 31)
   private String f2;
    
   @NonNull
   @Column( nullable = false, length = 255)
   @Convert(converter = JpaConverterJson.class)
   private List<String> f3;
    
   @NonNull
   @Column(nullable = false)
   private String f4;
    
   @NonNull
   @Column(name = "es_status", nullable = false, length = 255)
   @Enumerated(EnumType.STRING)
   private ExecutionStatus esStatus;
    
   @JsonManagedReference
   @OneToMany(mappedBy = "parentEntity", cascade = CascadeType.ALL,
       fetch = FetchType.EAGER)
   @Setter(AccessLevel.NONE)
   private List<ChildEntity> childEntities;
    
   public void setChildEntities(List<ChildEntity> childEntities) {
      this.childEntities = childEntities;
      childEntities.forEach(entity -> entity.setParentEntity(this));
   }
}


@Entity
@Getter
@Setter
@Table(name= "table_child")
@NoArgsConstructor
public class ChildEntity implements Serializable {
   private static final long serialVersionUID =  -925587271547L;
    
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;
    
   @JsonBackReference
   @ManyToOne(fetch = FetchType.EAGER )
   @JoinColumn(name = "parent_id")
   private ParentEntity parentEntity;
    
   @Column(name = "c1",nullable = false)
   @NonNull
   @Convert(converter = JpaConverterJson.class)
   private String c1;
    
   @Column(name = "last_exec_status",nullable = false)
   @NonNull
   @Enumerated(EnumType.STRING)
   private ExecutionStatus lastExecStatus;
    
   @Column(name = "c4",nullable = false)
   @NonNull
   private String  c4;
    
   @Column(name = "last_execution_timestamp",nullable = false)
   @NonNull
   private long lastExecutionTimestamp;
    
   @JsonManagedReference
   @NonNull
   @OneToMany(mappedBy = "childEntity", cascade = CascadeType.ALL,
      fetch = FetchType.EAGER)
   @Setter(AccessLevel.NONE)
   private List<GrandChildEntity> grandChildEntities;
    
   public void setGrandChildEntities(List<GrandChildEntity> grandChildEntities) {
      this.grandChildEntities = grandChildEntities;
      grandChildEntities.forEach(entity -> entity.setChildEntity(this));
   }
}


@Entity
@Getter
@Setter
@Table(name="table_grand_child")
@NoArgsConstructor
//@AllArgsConstructor
public class GrandChildEntity implements Serializable {
   private static final long serialVersionUID = -925567241248L;
    
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;
    
   @JsonBackReference
   @ManyToOne(fetch = FetchType.EAGER )
   @JoinColumn(name = "child_entity_id")
   private ChildEntity childEntity;
    
   @Column(name="gc1",nullable = false)
   private String gc1;
    
   @Column(name="gc2",nullable = false)
   private String gc2;
    
   @Column(name="gc3",nullable = false)
   private String gc3;
    
   @Column(name="gc3",nullable = true)
   private List<String> gc3;
}

2. 해결방안:

먼저 아래의 올바른 솔루션을 제공 한 "SternK"에게 감사드립니다.

접근-1

 @Query("select ch from ChildEntity ch "
      + " join ch.parentEntity pr "
      + " join fetch ch.grandChildEntities gc "
      + " where pr.bumId = :bumId and ch.lastExecutionTimestamp in ( select max(ch1.lastExecutionTimestamp) from ChildEntity ch1 
      join ch1.grandChildEntities gc where ch1.parentEntity = pr group by ch1.c1))")
 List<ChildEntity> findLastExecutedChildFromBumId(@Param("bumId") String bumId);

접근-2

내가 알아 낸 또 다른 접근 방식 (빠르게 실행)은 추출해야하는 필수 필드가 포함 된 사용자 지정 "모델"클래스를 만든 다음 new정의 된 사용자 지정 모델 의 클래스 를 만드는 것입니다.

    @Query(value = "select new com.project.package.api.models.CustomResultModel(" +
                            "cast(pr.bumId as java.lang.String),"+
                            "cast(pr.field1 as java.lang.String),"+
                            "cast(pr.field2 as java.lang.String),"+
                            "cast(ch.field1 as java.lang.String),"+
                            "cast(tr.field1 as java.lang.String),"+
                            "cast(tr.field2 as java.lang.String),"+
                            "cast(ch.field3 as java.lang.String),"+
                            "cast(ch.lastCompletedStaus as java.lang.String),"+
                            "cast(ch.lastExecutionTimestamp as java.lang.Long)) from ParentEntity pr" +
                        "inner join ChildEntity ch.ON pr.id = ch.parentEntity " +
                        "inner join GrandChildEntity tr ON ch.id = tr.childEntity " +
                        "where pr.bumId = ?1 " +
                        "and ch.lastExecutionTimestamp = ( select max(b.lastExecutionTimestamp) from ChildEntity b where "+
                        "b.parentEntity =  ch.parentEntity )")
    List<CustomResultModel> findLastExecutedChildFromBumId(@Param("bumId") String bumId);

65622441