我如何获得 Spring 数据查询以满足投影中的某些关系(即将集合传递给投影)? [英] How do i get a Spring data query to satisfy some relations in a projection (ie pass a Collection to a projections)?

问题描述

所以我试图在 spring 数据中进行投影查询.这是我的模型(为了不发表一篇包含大量类、错误省略构造函数和一些注释的大帖子):

So im trying to make a projection query in spring data. This is my model (in order not to make a huge post with a spam of classes, ill omit constructors, and some annotations):

public class TutorialDAO implements Serializable {
    private UUID id;
    private LocalDateTime created;
    private String createdBy;
    private LocalDateTime lastModified;
    private String lastModifiedBy;
    private int version;
    private boolean exclusive;

    @ManyToMany(mappedBy = "tutorials", cascade = CascadeType.PERSIST, fetch = FetchType.EAGER)
    private Set<TopicDAO> topics = new HashSet<>();

    @ElementCollection(fetch = FetchType.EAGER)
    private Set<SectionDAO> sections = new HashSet<>();

    @ManyToMany(cascade = CascadeType.PERSIST, fetch = FetchType.LAZY)
    @JoinTable(name = "tutorial_courses", joinColumns = @JoinColumn(name = "tutorial_id"), inverseJoinColumns = @JoinColumn(name = "course_id"))
    private Set<CourseDAO> courses = new HashSet<>();

    @OneToMany(mappedBy = "tutorial", cascade = { CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST,
            CascadeType.REFRESH }, orphanRemoval = true, fetch = FetchType.EAGER)
    @MapKey(name = "localizedId.locale")
    private Map<String, LocalizableTutorial> localizations = new HashMap<>();

public class TopicDAO implements Serializable {
    private UUID id;
    private String topic;

    @ManyToMany(cascade = CascadeType.PERSIST, fetch = FetchType.LAZY)
    private Set<TutorialDAO> tutorials = new HashSet<>();
}

public class LocalizedTutorialDAO {
    private UUID id;
    private LocalDateTime created;
    private String createdBy;
    private LocalDateTime lastModified;
    private String lastModifiedBy;
    private int version;
    private boolean exclusive;
    private String name, description;
    private Set<TopicDAO> topics = new HashSet<>();
    private Set<SectionDAO> sections = new HashSet<>();

    public LocalizedTutorialDAO(UUID id, LocalDateTime created, String createdBy, LocalDateTime lastModified,
            String lastModifiedBy, int version, boolean exclusive, String name, String description,
            Set<TopicDAO> topics) {
        super(id, created, createdBy, lastModified, lastModifiedBy, version);
        System.out.println(topics);
        System.out.println();
        this.name = name;
        this.description = description;
        this.topics = topics;
    }
}

我正在尝试进行填充"投影查询LocalizedTutorialDAO 类，这意味着我想要例如主题关系(节也是如此，但因为它有类似的病态，只是提及主题).我有这个@Query 注释:

Im trying to make a projection query that "fills" the LocalizedTutorialDAO class, which means i want the topics relation for example (same happens with sections, but since its a similar ill just mention topics). I have this @Query annotation:

select new LocalizedTutorialDAO(t.id, t.created, t.createdBy, t.lastModified, t.lastModifiedBy, t.version, t.exclusive, (VALUE(l)).name, (VALUE(l)).description, topics) from tutorial t join t.localizations l join t.topics as topics where (VALUE(l)).name like %:name% and (KEY(l)) = :lang

澄清一下，TutorialDAO 对象具有多语言支持，我的查询得到正常"TutorialDAO 中的变量以及主题和部分关系.然后它从给定的语言中获取名称和描述，例如我想看一个教程，它的名字有煮鱼".在葡萄牙语中 - pt".现在，关于名称和描述的这部分工作正常，我在没有获取主题和部分的情况下对其进行了测试，并且实体被正确填充.我在这里的问题是我尝试过原生 sql 和 spring 数据非原生查询，但我永远无法让它工作.像这样，当前查询，它说:

To clear up, the TutorialDAO object has multi-language support, and my query gets the "normal" variables in TutorialDAO as well as the topics and sections relations. Then it gets the name and description from a given language for example i want to see a tutorial which name has "cooking fish" in portuguese - "pt". Now this part, concerning name and description is working, ive tested it without getting topics and sections and the entity gets populated correctly. My issue here has been ive tried native sql, and spring data non native query and i can never get it to work. Like this, the current query, it says:

org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate appropriate constructor on class [com.fullstack.daos.projections.LocalizedTutorialDAO]. Expected arguments are: java.util.UUID, java.time.LocalDateTime, java.lang.String, java.time.LocalDateTime, java.lang.String, int, boolean, java.lang.String, java.lang.String, com.fullstack.daos.TopicDAO [select new com.fullstack.daos.projections.LocalizedTutorialDAO(t.id, t.created, t.createdBy, t.lastModified, t.lastModifiedBy, t.version, t.exclusive, (VALUE(l)).name, (VALUE(l)).description, topics) from com.fullstack.daos.TutorialDAO t join t.localizations l join t.topics as topics where (VALUE(l)).name like :name and (KEY(l)) = :lang]

如果我尝试使用本机而不是将主题放在构造函数上，而是将主题.id，主题.主题放在那里，它永远不会起作用，因为它说它不能将 TopicDAO 转换为 Set.有没有办法告诉休眠如何做到这一点?比如spring自动生成的findById()查询，生成这个sql查询:

And if i try the native where instead of putting topic on the constructor i put topic.id, topic.topic it never works always because it says it cant convert TopicDAO to Set. Is there any way to tell hibernate how to do this? For example, the findById() query auto generated by spring works, generating this sql query:

select
        tutorialda0_.id as id1_33_0_,
        tutorialda0_.created as created2_33_0_,
        tutorialda0_.created_by as created_3_33_0_,
        tutorialda0_.last_modified as last_mod4_33_0_,
        tutorialda0_.last_modified_by as last_mod5_33_0_,
        tutorialda0_.version as version6_33_0_,
        tutorialda0_.exclusive as exclusiv7_33_0_,
        localizati1_.id as id1_8_1_,
        localizati1_.locale as locale2_8_1_,
        localizati1_.locale as formula413_1_,
        localizati1_.id as id1_8_2_,
        localizati1_.locale as locale2_8_2_,
        localizati1_.description as descript3_8_2_,
        localizati1_.name as name4_8_2_,
        sections2_.tutorial_id as tutorial1_34_3_,
        sectiondao3_.id as sections2_34_3_,
        sectiondao3_.id as id1_22_4_,
        sectiondao3_.parent_id as parent_i2_22_4_,
        children4_.parent_id as parent_i2_22_5_,
        children4_.id as id1_22_5_,
        children4_.id as id1_22_6_,
        children4_.parent_id as parent_i2_22_6_,
        topics5_.tutorials_id as tutorial2_31_7_,
        topicdao6_.id as topics_i1_31_7_,
        topicdao6_.id as id1_27_8_,
        topicdao6_.topic as topic2_27_8_
    from
        tutorials tutorialda0_
    left outer join
        localized_tutorial localizati1_
            on tutorialda0_.id=localizati1_.id
    left outer join
        tutorials_sections sections2_
            on tutorialda0_.id=sections2_.tutorial_id
    left outer join
        sections sectiondao3_
            on sections2_.sections_id=sectiondao3_.id
    left outer join
        sections children4_
            on sectiondao3_.id=children4_.parent_id
    left outer join
        topics_tutorials topics5_
            on tutorialda0_.id=topics5_.tutorials_id
    left outer join
        topics topicdao6_
            on topics5_.topics_id=topicdao6_.id
    where
        tutorialda0_.id=?

推荐答案

这对于 JPQL/HQL 构造函数表达式或 Spring Data Projections 是不可能的，因为这些概念不支持集合.

That's not possible with JPQL/HQL constructor expressions or Spring Data Projections as these concepts do not support collections.

我认为这是Blaze-Persistence Entity Views的完美用例.

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

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(Tutorial.class)
public interface LocalizedTutorialDAO {
    @IdMapping
    UUID getId();
    LocalDateTime getCreated();
    String getCreatedBy();
    LocalDateTime getLastModified();
    String getLastModifiedBy();
    int getVersion();
    boolean isExclusive();
    @Mapping("localizations[:lang].name")
    @AttributeFilter(ContainsFilter.class)
    String getName();
    @Mapping("localizations[:lang].description")
    String getDescription();
    Set<TopicDAO> getTopics();

    @EntityView(Topic.class)
    interface TopicDAO {
        @IdMapping
        UUID getId();
        String getTopic();
    }
}

查询是将实体视图应用于查询的问题，最简单的就是通过 id 查询.

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

LocalizedTutorialDAO a = entityViewManager.find(entityManager, LocalizedTutorialDAO.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

Page<LocalizedTutorialDAO> findAll(Pageable pageable, EntityViewSettingProcessor<LocalizedTutorialDAO> processor);

并像这样使用它:

Page<LocalizedTutorialDAO> p = repo.findAll(pageable, setting -> setting.addAttributeFilter("name", nameParameter));

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

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

这篇关于我如何获得 Spring 数据查询以满足投影中的某些关系(即将集合传递给投影)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！