有 Java 编程相关的问题?

你可以在下面搜索框中键入要查询的问题!

HQL查询的java帮助(分组依据、订单依据)

对于表中的以下数据,我在编写HQL以使用最新的应用程序(最新的createDate)显示不同的applicationId时遇到问题

+---------------+-------------+----------+---------------------+
| applicationId | firstName   | lastName | createDate          |
+---------------+-------------+----------+---------------------+
|             1 | Mark        | Johnson  | 2010-05-03 00:00:00 |
|             3 | Henry       | Jordan   | 2010-05-03 00:00:00 |
|             5 | Cindy Spahn | Wilson   | 2010-05-03 00:00:00 | 
|             5 | Cindy Spahn | Wilson   | 2010-05-04 00:00:00 |
|             5 | Cindy Spahn | Wilson   | 2010-05-05 00:00:00 |
+---------------+-------------+----------+---------------------+
5 rows in set (0.00 sec)

以下是我正在寻找的结果:

+---------------+-------------+----------+---------------------+
| applicationId | firstName   | lastName | createDate          |
+---------------+-------------+----------+---------------------+
|             1 | Mark        | Johnson  | 2010-05-03 00:00:00 |
|             3 | Henry       | Jordan   | 2010-05-03 00:00:00 |
|             5 | Cindy Spahn | Wilson   | 2010-05-05 00:00:00 |
+---------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

实体如下:

@Entity
@Table(name = "application")
public class Application {
    private long applicationId;
    private String firstName;
    private String lastName;
    private List<ApplicationHistory> applicationHistoryList;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public long getApplicationId() {
        return applicationId;
    }

    @OneToMany(mappedBy = "application", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    public List<ApplicationHistory> getApplicationHistoryList() {
        return applicationHistoryList;
    }
    // getter() and setter()
}

以及:

@Entity
@Table(name = "applicationHistory")
public class ApplicationHistory {
    private Application application;
    private final Timestamp createDate = new Timestamp(System.currentTimeMillis());

    @ManyToOne
    @JoinColumn(name = "applicationId", insertable = false, updatable = false)
    public Application getApplication() {
        return application;
    }

    @Id
    @Column(columnDefinition = "timestamp default current_timestamp")
    public Timestamp getCreateDate() {
        return createDate;
    }
}

共 (2) 个答案

  1. # 1 楼答案

    尝试使用group by子句:

    select ah from ApplicationHistory ah group by ah.applicationId order by ah.createDate desc
    
  2. # 2 楼答案

    您可以使用以下查询:

    select a, h.createDate  from Application as a  join a.applicationHistoryList as h where (a.applicationId, h.createDate) in( SELECT application.applicationId, max(createDate) FROM ApplicationHistory  group by application.applicationId) 
    

    例如:

    Query q = em.createQuery("select a, h.createDate  from Application as a  join a.applicationHistoryList as h where (a.applicationId, h.createDate) in( SELECT application.applicationId, max(createDate) FROM ApplicationHistory  group by application.applicationId) ");
    
            List list = q.getResultList();
    
            for (Iterator iterator = list.iterator(); iterator.hasNext();) {
                Object obj[] = (Object[])iterator.next();
                Application a =  (Application) obj[0];
    
                System.out.println("ApplicationId="+a.getApplicationId() );
                System.out.println("CreateDate="+obj[1] );
    
            }