包含GROUPBY和HAVING的Hibernate自定义查询 [英] Hibernate custom query containing GROUPBY and HAVING

问题描述

所以我有两个具有一对多关系的实体. form_collection包含多个form. form包含一列version，该列跟踪最新形式.

So I have two entities with a one-to-many relationship. A form_collection contains multiple form. The form contains a column version which keeps track of the most current form.

form
+----+-----------------+---------+--------------------+
| id | description_key | version | form_collection_id |
+----+-----------------+---------+--------------------+
| 1  | desc1           | 1       | 1                  |
+----+-----------------+---------+--------------------+
| 2  | desc1           | 2       | 1                  |
+----+-----------------+---------+--------------------+
| 3  | desc2           | 1       | 1                  |
+----+-----------------+---------+--------------------+
| 4  | desc3           | 1       | 2                  |
+----+-----------------+---------+--------------------+

form_collection
+----+-------+
| id | name  |
+----+-------+
| 1  | coll1 |
+----+-------+
| 2  | coll2 |
+----+-------+

在我的Java代码中，我只希望一对多关系中仅包含具有相同描述键的每种表单的最新版本(类似于

In my java code I only want that the one-to-many relation ship only contains the most recent versions of each form with the same description key (similar to a soft delete as explained in this article). For retrieving the most recent forms I came up with this query which works as expected:

SELECT *
FROM form AS a
INNER JOIN (
    SELECT description_key, max(version) AS version
    FROM form
    GROUP BY description_key
) AS b
ON a.description_key = b.description_key AND a.version = b.version;

这仅返回第2、3、4行.

This only returns rows 2, 3, 4.

但是，在使用以下实体将其应用于我的Hibernate模式时，我遇到了问题.可以使用注释完成此过滤吗?

However, I have problems applying this to my Hibernate schema with the following entities. Can this filtering be done with annotations?

@Entity
@Table(name = "form", schema = "public")
public class FormDO extends BaseDO {
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "form_collection_id")
    private FormCollectionDO formCollection;
}

@Entity
@Table(name = "form_collection", schema = "public")
public class FormCollectionDO extends BaseDO {

    @OneToMany(mappedBy = "formCollection")
    // can I add an annotation here to filter for only the once that have the highest versions?
    private List<FormDO> forms;
    
}

可以用类似的方法吗?

@Where(clause = "version = SELECT max(version) FROM form GROUP BY description_key")

推荐答案

您可以尝试使用以下内容:

You could try to use the following:

@Entity
@Table(name = "form_collection", schema = "public")
public class FormCollectionDO extends BaseDO {

    @ManyToOne
    @JoinColumnsOrFormulas({
        @JoinColumnOrFormula(column = @JoinColumn(name = "id", referencedColumn = "form_collection_id")),
        @JoinColumnOrFormula(formula = @JoinFormula(value = "(select max(version) from public.form sub_f where sub_f.form_collection_id = {alias}.id)", referencedColumnName = "version"))
    })
    private FormDO latestForm;
    
}

但是通常，我建议您使用DTO方法，因为现在每次获取FormCollectionDO时都必须执行此max子查询.

But generally, I would recommend you to use a DTO approach instead as this max subquery now has to be executed every time you fetch a FormCollectionDO.

我认为这是 Blaze-Persistence实体视图.

我创建了该库，以允许在JPA模型与自定义接口或抽象类定义的模型之间轻松进行映射，例如类固醇上的Spring Data Projections.这个想法是，您可以按自己喜欢的方式定义目标结构(域模型)，并通过JPQL表达式将属性(获取器)映射到实体模型.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

针对您的用例的DTO模型可能与Blaze-Persistence Entity-Views相似，如下所示:

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(FormCollectionDO.class)
public interface FormCollectionDto {
    @IdMapping
    Long getId();
    String getName();
    @Limit(limit = "1", order = "version DESC")
    @Mapping("forms")
    FormDto getLatestForm();

    @EntityView(FormDO.class)
    interface FormDto {
        @IdMapping
        Long getId();
        String getDescription();
    }
}

查询是将实体视图应用于查询的问题，最简单的方法就是按ID查询.

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

FormCollectionDto a = entityViewManager.find(entityManager, FormCollectionDto.class, id);

Spring Data集成使您可以像使用Spring Data Projections一样使用它:

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

它只会获取您实际需要的数据.另外，如果可能，使用Blaze-Persistence Entity-Views的解决方案将使用横向联接，这比联接谓词中的聚合要有效得多.

It will only fetch the data that you actually need. Also, the solution with Blaze-Persistence Entity-Views will use a lateral join if possible, which is much more efficient than an aggregation in a join predicate.

这篇关于包含GROUPBY和HAVING的Hibernate自定义查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！