代码之家 › 专栏 › 技术社区 › Alferd Nobel

Hibernate查询,用于筛选嵌套对象列表中的结果

hql spring-data hibernate java

Alferd Nobel · 技术社区 · 5 年前

作为后续行动 answer (关于方法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 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);

关联类实体

阶级关系 父母实体<1-oneToMany-x>儿童实体<1-oneToMany-x>孙子女实体

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

0 回复 | 直到 5 年前

Christian Beikov 5 年前

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

我认为这是一个完美的用例 Blaze-Persistence Entity Views .

我创建了这个库,以便在JPA模型和自定义接口或抽象类定义的模型之间进行轻松映射,就像Spring Data Projections一样。其想法是,您可以按照自己喜欢的方式定义目标结构(域模型),并通过JPQL表达式将属性(getter)映射到实体模型。

使用Blaze Persistence实体视图时,用例的DTO模型可能如下:

@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查询。

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