休眠复杂连接 [英] Hibernate Complex Join

问题描述

所以我们有2个对象，每个对象都映射到一个db表.

So we have 2 objects which each map to a db table.

public class Dispute {
  private Long disputeSeqNo;
  private List<StatusInfo> statusInfos;
}

public class StatusInfo {
  private Long statusSeqNo;
  private Date createdDt;
  private String statusCd;
  private Dispute dispute;
}

一个争议可以与0个或多个StatusInfo相关联.

A Dispute can have 0 or more StatusInfo's associated with it.

当前，休眠状态将为每个Dispute返回所有StatusInfo对象.在某些情况下，这是理想的.但是我有一个用例，其中:

Currently hibernate is returning all the StatusInfo objects for every Dispute. While in some cases this is desired. However I have a use case where:

• 我只需要为每个争议显示最新的StatusInfo.
• 此外，在同一显示上，我需要能够按最新的StatusInfo.statusCd值进行排序.由于我的显示是分页的(使用休眠分页功能)，因此需要在数据库中进行这种排序. (实际上，这不是选择对数据进行排序的一种选择，因为将有很多行数据.)

我知道仅使用普通的SQL就能做到这一点，努力将其转换为HQL.

I know using just plain SQL I can do this, struggling to translate this into HQL.

关于如何使用HQL解决此问题的任何想法?我仍然回到哪里:

Any thoughts on how to solve this with HQL? Where I still get back:

List<Dispute>

感谢您的反馈.

谢谢！

推荐答案

您需要做的第一件事是在下面定义一个Hibernate过滤器.您只需将此注释添加到Dispute类的顶部即可.

The first thing you need to do is define a Hibernate filter below. You can simply add this annotation at the top of your Dispute class.

@FilterDef( name = "dispute-with-latest-statusinfo" )

接下来，您需要按如下所示将此命名过滤器应用于您的StatusInfo集合:

Next you need to apply this named filter on your collection of StatusInfo as below:

@OneToMany( mappedBy = "dispute" )
@Filter( 
  name = "dispute-with-latest-statusinfo",
  condition = "statusinfo_id = (select max(si.statusinfo_id) from 
    StatusInfo si WHERE si.dispute_dispute_id = dispute_dispute_id")
private List<StatusInfo> statusInfos;

要满足您的第一个要求，可以通过在会话上启用该过滤器，然后执行查询，来获取具有最新StatusInfo的Dispute实例的列表:

To satisfy your first requirement, you can obtain a list of Dispute instances with their latest StatusInfo by enabling that filter on your session and then executing a query:

session.enableFilter( "dispute-with-latest-statusinfo" );
List<Dispute> disputes = session
  .createQuery( "FROM Dispute d JOIN FETCH d.statusInfos", Dispute.class )
  .getResultList()

为了按照您的要求对结果进行排序:

In order to sort your results as you asked:

session.enableFilter( "dispute-with-latest-statusinfo" );
List<Dispute> disputes = session
  .createQuery( "FROM Dispute d JOIN FETCH d.statusInfos si ORDER BY si.status DESC", Dispute.class )
  .getResultList()

现在，如果您不想在这种情况下使用包含单个元素的List<StatusInfo>，则可以添加一个临时方法来提供帮助，以便无需获取即可获取对象或获取null担心集合语义:

Now if you don't like having to work with a List<StatusInfo> that contains a single element for these cases, you can add a transient method to help so that you can get the object or get null without having to worry with the collection semantics:

@Transient
public StatusInfo getLatestStatusInfo() {
  if ( statusInfos.isEmpty() ) {
    return null;
  }
  else if ( statusInfos.size() == 1 ) {
    return statusInfos.get( 0 );
  }
  else {
    // you could add logic here to do the sort in-memory 
    // for cases where you may have the full collection
    // but still want to obtain the last entry ...
  }
}

这篇关于休眠复杂连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！