在SpringBoot微服务中,我试图为每个mean_of_payment_id选择一个演员的最新记录。要实现此目的,请使用mean_of_payment_id上的group by子句为actor_id选择actor内容,其中created_date等于max(created_date)嵌套查询的子集。我正在使用JPQL。下面是表的结构和查询。
@Query("select ac from ActorContent ac "
+ "where (ac.actor.uuid=:actorUuid ) and "
+ "ac.createdDate IN ( SELECT MAX(aci.createdDate) "
+ "FROM ActorContent aci WHERE ac.actor.uuid=aci.actor.uuid "
+ "and aci.uuid = ac.uuid group by ac.meanOfPayment.id)"
)
不幸的是,执行查询后,我得到了所有记录,但我期望的是前三行。MeanOfPayment和Actor是ActorContent的引用表。
我认为在关系代数方面,您要ActorContent
减去的集合是ActorContent
受actor = actor和meanOfPayment = meanOfPayment和createDate <createDate约束的集合。因此,思考的方法是从ActorContent
with的叉积获得第二组ac1.meanOfPayment = ac2.meanOfPayment and ac1.actor = ac2.actor and ac1.createDate < ac2.createDate
。然后从中减去该集合ActorContent
。我没有看过它是否比使用MAX
和Group By
例如更有效:
@Query("select ac from ActorContent ac where ac.id not in (select ac1.id from ActorContent ac1, ActorContent ac2 where ac1.meanOfPayment = ac2.meanOfPayment and ac1.actor = ac2.actor and ac1.createDate < ac2.createDate)")
这给了我UPPER表中的前四行,分别代表第一个演员和他的唯一meanOfPayment,第二个演员和他对所有三个meanOfPayments的最新付款。
ActorContent [id=1, actor=Actor [id=1], meanOfPayment=MeanOfPayment [id=1], amount=10500.00, createDate=2018-10-09 00:00:00.887]
ActorContent [id=2, actor=Actor [id=2], meanOfPayment=MeanOfPayment [id=1], amount=-10400.00, createDate=2018-10-02 00:00:00.887]
ActorContent [id=3, actor=Actor [id=2], meanOfPayment=MeanOfPayment [id=3], amount=6000.00, createDate=2018-10-02 00:00:00.887]
ActorContent [id=4, actor=Actor [id=2], meanOfPayment=MeanOfPayment [id=2], amount=200.00, createDate=2018-09-30 00:00:00.887]
之后,您可能希望通过联接获取Actor
和MeanOfPayment
实例来优化查询。通过示例:
@Query("select ac from ActorContent ac left outer join fetch ac.actor left outer join fetch ac.meanOfPayment where ac.id not in (select ac1.id from ActorContent ac1, ActorContent ac2 where ac1.meanOfPayment = ac2.meanOfPayment and ac1.actor = ac2.actor and ac1.createDate < ac2.createDate)")
这将导致以下休眠生成的SQL查询:
select actorconte0_.id as id1_1_0_, actor1_.id as id1_0_1_, meanofpaym2_.id as id1_2_2_, actorconte0_.actor_id as actor_id4_1_0_, actorconte0_.amount as amount2_1_0_, actorconte0_.create_date as create_d3_1_0_, actorconte0_.mean_of_payment_id as mean_of_5_1_0_ from actor_content actorconte0_ left outer join actor actor1_ on actorconte0_.actor_id=actor1_.id left outer join mean_of_payment meanofpaym2_ on actorconte0_.mean_of_payment_id=meanofpaym2_.id where actorconte0_.id not in (select actorconte3_.id from actor_content actorconte3_ cross join actor_content actorconte4_ where actorconte3_.mean_of_payment_id=actorconte4_.mean_of_payment_id and actorconte3_.actor_id=actorconte4_.actor_id and actorconte3_.create_date<actorconte4_.create_date)
当然,如果您想要特定的内容,Actor
则只需将其添加到where子句中即可。
@Query("select ac from ActorContent ac left outer join fetch ac.actor left outer join fetch ac.meanOfPayment where ac.actor.id = :actorId and ac.id not in (select ac1.id from ActorContent ac1, ActorContent ac2 where ac1.meanOfPayment = ac2.meanOfPayment and ac1.actor = ac2.actor and ac1.createDate < ac2.createDate)")
public List<ActorContent> findLatestForActor(@Param("actorId") Integer actorId);
这给了我“前三行”
ActorContent [id=2, actor=Actor [id=2], meanOfPayment=MeanOfPayment [id=1], amount=-10400.00, createDate=2018-10-02 00:00:00.066]
ActorContent [id=3, actor=Actor [id=2], meanOfPayment=MeanOfPayment [id=3], amount=6000.00, createDate=2018-10-02 00:00:00.066]
ActorContent [id=4, actor=Actor [id=2], meanOfPayment=MeanOfPayment [id=2], amount=200.00, createDate=2018-09-30 00:00:00.066]
如果您对Actor和MeanOfPayment组合使用相同的createDate时遇到问题,则可以采用几种不同的方法来处理。首先,如果您具有逻辑约束以至于不想处理那些重复项,那么您可能还应该具有数据库约束,这样就不会得到它们并确保您首先不要创建它们。另一件事是,您可以手动检查结果列表并将其删除。最后,您可以在查询中使用一个ActorContent
非重复,但是您必须省略id字段,因为它不是唯一的。您可以使用DTO进行此操作,但JPA无法处理投影和join fetch
同时,因此您只会获得actor.id和meanOfPayment.id,否则您将进行多项选择。在此用例中,多重选择可能不是交易杀手,但您必须自己决定所有这一切。当然,您也可以将ActorContent
actor.id,meanOfPayment.id和createDate组合在一起作为主键,这将具有上述约束的好处。
这些是Entities
我合作过的。
@Entity
public class Actor {
@Id @GeneratedValue(strategy=GenerationType.IDENTITY)
private Integer id;
@Entity
public class MeanOfPayment {
@Id @GeneratedValue(strategy=GenerationType.IDENTITY)
private Integer id;
@Entity
public class ActorContent {
@Id @GeneratedValue(strategy=GenerationType.IDENTITY)
private Integer id;
@ManyToOne
private Actor actor;
@ManyToOne
private MeanOfPayment meanOfPayment;
private BigDecimal amount;
@Temporal(TemporalType.TIMESTAMP)
private Date createDate;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句