休眠查询以过滤嵌套对象列表中的结果

2022-01-18 00:00:00 java Hibernate spring-data hql

作为此答案(关于方法1)的后续行动，我想更进一步:

As a followup to this answer(on approach 1 ) I want to go a step further :

我想根据某些条件过滤大子对象.我尝试了以下查询，但它仍然没有过滤掉孙实体下的对象.

I want to Filter the grand child objects based on certain criteria's. I tried the following query, but it still does not filter out the objects under the grandchild entity.

  @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 ON ch1.id = gc.childEntity where "
      + "gc.field1 in ('"Criteria1"','"Criteria2"','"Criteria3"') and " 
      + "gc.field2 = '"soldout"'"
      + "ch1.parentEntity = pr group by ch1.c1))")
 List<ChildEntity> findLastExecutedChildFromBumId(@Param("bumId") String bumId);

关联的类实体

类关系 ParentEntity <1-oneToMany-x>ChildEntity<1-oneToMany-x>GrandChildEntity

Class Relation ParentEntity <1-oneToMany-x> ChildEntity<1-oneToMany-x>GrandChildEntity

@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;
}

推荐答案

过滤连接获取的集合是一个坏主意，因为这会改变持久状态".并可能因此导致实体被删除.我建议您改用 DTO 方法.

Filtering a collection that is join fetched is a bad idea as that alters the "persistent state" and might cause entities to be removed due to that. I suggest you use a DTO approach instead.

我认为这是 Blaze-Persistence 实体视图.

我创建了该库以允许在 JPA 模型和自定义接口或抽象类定义模型之间轻松映射，例如 Spring Data Projections on steroids.这个想法是您按照自己喜欢的方式定义目标结构(域模型)，并通过 JPQL 表达式将属性(getter)映射到实体模型.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

使用 Blaze-Persistence Entity-Views 的用例的 DTO 模型可能如下所示:

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(ChildEntity.class)
public interface ChildEntityDto {
    @IdMapping
    Long getId();
    String getC1();
    ParentEntityDto getParentEntity();
    @Mapping("grandChildEntities[field1 in ('"Criteria1"','"Criteria2"','"Criteria3"') and gc.field2 = '"soldout"']")
    Set<GrandChildEntityDto> getGrandChildEntities();

    @EntityView(ParentEntity.class)
    interface ParentEntityDto {
        @IdMapping
        Long getId();
        String getF1();
    }
    @EntityView(GrandChildEntity.class)
    interface GrandChildEntityDto {
        @IdMapping
        Long getId();
        String getGc1();
    }
}

查询是将实体视图应用于查询的问题，最简单的就是通过 id 进行查询.

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

UserDto a = entityViewManager.find(entityManager, UserDto.class, id);

Spring Data 集成让您可以像使用 Spring Data Projections 一样使用它:https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

相关文章