java 如何在Spring Data JPA @Query中使用带有@OneToMany关系的“select new Dto(...)...”构造?

v8wbuo2f  于 2023-02-02  发布在  Java
关注(0)|答案(1)|浏览(151)

我需要使用“select new ...“在我的存储库中构造DTO对象。
我有以下课程:
实体类Victim

@Entity
@Table(name = "victims")
@Getter
@Setter
@ToString
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Victim extends User {

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

  @Column(nullable = false, length = 13)
  private String phoneNumber;

  @Enumerated(EnumType.STRING)
  @Column(nullable = false)
  private UserStatus userStatus = UserStatus.ACTIVE;

  @JsonManagedReference
  @Setter(AccessLevel.PRIVATE)
  @BatchSize(size = 100)
  @OneToMany(mappedBy = "victim", cascade = CascadeType.ALL)
  @Exclude
  private List<Request> requests = new ArrayList<>();

  @JsonManagedReference
  @OneToOne(mappedBy = "victim", cascade = CascadeType.ALL)
  private LegalEntityVictim legalEntityVictim;

  @JsonManagedReference
  @OneToOne(mappedBy = "victim", cascade = CascadeType.ALL)
  private NaturalPersonVictim naturalPersonVictim;

}

还假设Request是对象,它与另一个对象有自己的关系,并且也包含该对象-这可能就是问题所在,因为如果不在DTO中使用该字段,一切都可以正常工作-因此可能的问题是:“如何构造具有内部一对多依赖关系的DTO(它也可以有自己的依赖关系)?”
DTO类VictimDTO

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class VictimDto {

  private Long id;

  private String email;

  private String password;

  private String name;

  private String surname;

  private String nameOfOrganization;

  private String phoneNumber;

  private UserStatus userStatus;

  private Collection<Request> requests = new ArrayList<>();
}

存储库VictimRepository

public interface VictimRepository extends JpaRepository<Victim, Long> {

  @Query("select new com.pavliuk.dto.VictimDto(v.id, v.email, v.password, v.naturalPersonVictim.name, v.naturalPersonVictim.surname, v.legalEntityVictim.nameOfOrganization, v.phoneNumber, v.userStatus, v.requests) from Victim v ")
  Page<VictimDto> findAllDto(Pageable pageable);

}

定义了以下SQL表层次结构:

因此,我得到了以下日志(我已经打开了show-sql功能,所以你也可以检查生成的查询):

Hibernate: select victim0_.id as col_0_0_, victim0_.email as col_1_0_, victim0_.password as col_2_0_, naturalper1_.name as col_3_0_, naturalper1_.surname as col_4_0_, legalentit3_.name_of_organization as col_5_0_, victim0_.phone_number as col_6_0_, victim0_.user_status as col_7_0_, . as col_8_0_ from victims victim0_ cross join natural_person_victims naturalper1_ cross join legal_entity_victims legalentit3_ inner join requests requests4_ on victim0_.id=requests4_.victim_id where victim0_.id=naturalper1_.victim_id and victim0_.id=legalentit3_.victim_id limit ?
2023-01-04 08:54:22.282  WARN 37428 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42601
2023-01-04 08:54:22.282 ERROR 37428 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near "."
  Position: 273
2023-01-04 08:54:22.293 ERROR 37428 --- [nio-8080-exec-1] c.p.controller.ErrorHandlingController   : handleException: message: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet, method: getAll
w3nuxt5m

w3nuxt5m1#

这是不可能的,因为JPQL中的DTO投影本质上是标量的。我认为这是Blaze-Persistence实体视图的完美用例。
我创建这个库是为了在JPA模型和定制接口或抽象类定义的模型之间进行简单的Map,就像Spring Data Projections一样,其思想是您可以按照自己喜欢的方式定义目标结构(域模型),并通过JPQL表达式将属性(getter)Map到实体模型。
使用Blaze-Persistence Entity-Views时,您的用例的DTO模型可能如下所示:

@EntityView(Victim.class)
public interface VictimDto {
    @IdMapping
    Long getId();
    String getEmail();
    String getPassword();
    String getName();
    String getSurname();
    String getNameOfOrganization();
    String getPhoneNumber();
    UserStatus getUserStatus();
    Collection<RequestDto> getRequests();

    @EntityView(Request.class)
    interface RequestDto {
        @IdMapping
        Long getId();
        String getName();
    }
}

查询就是将实体视图应用到查询中,最简单的查询就是按id查询。
VictimDto a = entityViewManager.find(entityManager, VictimDto.class, id);
Spring Data 集成允许您像使用Spring Data 投影一样使用它:https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<VictimDto> findAll(Pageable pageable);

最好的部分是,它只获取实际需要的状态!

相关问题