如何在Spring JPA @Query中获取完全匹配的记录

问题描述

我有两个实体

1.请求

@Getter
@Setter
@Entity
@Table(name = "request")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class Request {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "status")
    private String status;

    @OneToMany(mappedBy = "requestId", fetch = FetchType.LAZY)
    private Collection<Service> services;
}

2.服务

@Getter
@Setter
@Entity
@Table(name = "service")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class Service{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "request_id")
    private Integer requestId;

    @Column(name = "status")
    private String status;
}

现在，要获取具有特定状态的服务的请求，我在 @Query 中写道 RequestDao 文件.

Now, to fetch requests having service(s) of specific status I wrote following @Query in RequestDao file.

@Query(value = "SELECT DISTINCT req FROM request req JOIN req.services srvs WHERE (srvs.status = :serviceStatus)")
List<Request> getAll(@Param("serviceStatus") String serviceStatus)

因此，如果有任何关联的服务符合"where"条件，我将在"request"对象中获取所有服务.而我希望"request"仅包括那些与条件在哪里.

As result, I'm getting all services inside the 'request' object if any of the associated service matches the 'where' criteria.. while I'm expecting 'request' to only include those services which are matching with 'where' criteria.

任何人都可以帮忙吗?预先感谢.

Can anyone please help?Thanks in advance.

推荐答案

这不是那么容易，因为Hibernate/JPA试图保证实体模型与数据库状态同步.您显然想要一个投影，该投影很可能不应该与数据库保持同步.如果确实必须执行此操作，则可以使用以下查询，但是请注意，这可能会导致删除集合中与条件不匹配的服务元素:

That's not so easy because Hibernate/JPA try to guarantee that the entity model is in sync with the database state. You apparently want a projection that most probably should not be kept in sync with the database. If you really must do this, you can use the following query, but beware that this might cause deletion of service elements in the collection that don't match the criteria:

@Query(value = "SELECT DISTINCT req FROM request req JOIN FETCH req.services srvs WHERE (srvs.status = :serviceStatus)")
List<Request> getAll(@Param("serviceStatus") String serviceStatus);

这通常是通过引入DTO来处理的，我认为这是 Blaze-Persistence实体视图.

This is usually handled by introducing DTOs and I think this is a perfect use case for Blaze-Persistence Entity Views.

我创建了该库，以允许在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(Request.class)
public interface RequestDto {
    @IdMapping
    Integer getId();
    String getStatus();
    @Mapping("services[status = :serviceStatus]")
    Set<ServiceDto> getServices();

    @EntityView(Service.class)
    interface ServiceDto {
        @IdMapping
        Integer getId();
        Integer getRequestId();
        String getStatus();
    }
}

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

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

RequestDto a = EntityViewManager.find(entityManager，RequestDto.class，id);

Spring Data集成使您可以像使用Spring Data Projections一样使用它: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

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

List<RequestDto> findAll(@OptionalParam("serviceStatus") String serviceStatus);

最好的是，它只会获取实际必要的状态！

The best part is, it will only fetch the state that is actually necessary!

这篇关于如何在Spring JPA @Query中获取完全匹配的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！