java - 休眠选择最近执行的子实体
问题描述
我想ChildEntity
从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;
}
解决方案
首先非常感谢“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);
推荐阅读
- swift - 如何将数据添加到应用程序中的 coreML 文件
- codenameone - 代号一 - 字符串替换为空字符
- excel - 使用任务管理器关闭时 VBA Excel 2007 安全警告
- python - 从一列数字(字符串)中删除名称
- tsql - TSQL如何按月获取组的总百分比
- visual-studio - 在没有属性窗口的设计器中更改 .Text-property
- regex - 如何删除两个字符之间的所有空格?
- asp.net-core - 如何在 ASP NET 中将内存数据与磁盘同步
- r - 根据三列删除重复行
- firebase - 向 Firestore 中的特定用户 uid 发送通知