从一个API中的多个表(SpringBOOT + JPA)获取数据的查询

Question

下面是我的DB模式的ERD。

​

​

我需要在API中传递所有具有不同参数的电影。

1. 需要获得以语言为基础的电影数据(英语和印地语，体裁是动作和幻想)

实体：

categorystudio

@Entity
@Table(name = "categorystudio")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class CategoryOfStudio implements Serializable {

    private static final long serialVersionUID = 1L;

    public CategoryOfStudio() {
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="categorystudio_id")
    private Long categorystudio_id;

    @NotNull
    @Column(name = "categorystudio_title")
    private String categorystudio_title;

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "categorystudio", cascade = CascadeType.ALL)
    @JsonIgnore
    private Set<Title> categorystudio = new HashSet<>();

标题

@Entity
@Table(name = "title")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Title implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "title_id")
    private Long title_id;

    @NotNull
    @Column(name = "title_name")
    private String title_name;

    @ManyToMany(fetch = FetchType.LAZY,
        cascade = {
            CascadeType.PERSIST,
            CascadeType.MERGE
        })
    @JoinTable(name = "title_genre",
        joinColumns = { @JoinColumn(name = "genresCategory_id") },
        inverseJoinColumns = { @JoinColumn(name = "title_id") })
    private Set<GenresCategory> genre_title = new HashSet<>();


    @ManyToMany(fetch = FetchType.LAZY,
        cascade = {
            CascadeType.PERSIST,
            CascadeType.MERGE
        })
    @JoinTable(name = "title_language",
        joinColumns = { @JoinColumn(name = "language_id") },
        inverseJoinColumns = { @JoinColumn(name = "title_id") })
    private Set<Language> title_language = new HashSet<>();

    @ManyToOne
    @JoinColumn(name="title_categorystudio_id")
    private CategoryOfStudio categorystudio;

    @OneToOne(fetch = FetchType.LAZY,
        cascade =  CascadeType.ALL,
        mappedBy = "movieTitle")
    private Movie movie;

    @OneToOne(fetch = FetchType.LAZY,
        cascade =  CascadeType.ALL,
        mappedBy = "tvShowTitle")
    private TvShow tvShowTitle;

    @OneToOne(fetch = FetchType.LAZY,
        cascade =  CascadeType.ALL,
        mappedBy = "kidTitle")
    private Kid kidTitle;

    public Title() {
    }

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "comicMaindata", cascade = CascadeType.ALL)
    @JsonIgnore
    private Set<ComicData> comicMaindata = new HashSet<>();

}

语言

    @Entity
    @Table(name = "language")
    @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    public class Language implements Serializable {

        private static final long serialVersionUID = 1L;

        public Language() {
        }

        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name="language_id")
        private Long id;

        @NotNull
        @Column(name = "language_name")
        private String languageName;

        @OneToMany(fetch = FetchType.EAGER, mappedBy = "comicDataLanguage", cascade = CascadeType.ALL)
        @JsonIgnore
        private Set<ComicDetailedData> comicDataLanguage = new HashSet<>();

        @ManyToMany(fetch = FetchType.LAZY,
            cascade = {
                CascadeType.PERSIST,
                CascadeType.MERGE
            },
            mappedBy = "title_language")
        private Set<Title> title_language = new HashSet<>();

        @OneToMany(fetch = FetchType.EAGER, mappedBy = "comicTitleLanguage", cascade = CascadeType.ALL)
        @JsonIgnore
        private Set<ComicData> comicTitleLanguage = new HashSet<>();

        @OneToMany(fetch = FetchType.EAGER, mappedBy = "comicMainLanguage", cascade = CascadeType.ALL)
        @JsonIgnore
        private Set<ComicData> comicMainLanguage = new HashSet<>();

genresCategory

@Entity
    @Table(name = "genresCategory")
    @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    public class GenresCategory implements Serializable {

        private static final long serialVersionUID = 1L;

        public GenresCategory() {
        }

        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name="genresCategory_id")
        private Long id;

        @NotNull
        @Column(name = "genresCategory_name")
        private String name;



        @ManyToMany(fetch = FetchType.LAZY,
            cascade = {
                CascadeType.PERSIST,
                CascadeType.MERGE
            },
            mappedBy = "genre_title")
        private Set<Title> genre_title = new HashSet<>();

控制器

  @GetMapping("/categoryByName/{name}")
@Timed
public Title getAllComicsByName(@RequestParam List<String> genres,
                                @RequestParam List<String> languages){
    return titlesRepository.findByStudiosGenresLanguages(genres, languages);
}

Respository

@Repository
public interface TitleRepository extends JpaRepository<Title, Long> {

        @Query("SELECT T FROM TITLE T WHERE T.LANGUAGE.NAME IN (:languages) AND T.GENRE.NAME IN (:genres)")
Title findByStudiosGenresLanguages(@Param("genres") List<String> genres, @Param("languages") List<String> languages);


}

错误：

    org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'movieController': Unsatisfied dependency expressed through field 'titlesRepository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'titleRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract com.comicflix.library.domain.Title com.comicflix.library.repository.TitleRepository.findByStudiosGenresLanguages(java.util.List,java.util.List)!
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:587)
    at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:91)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:373)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1350)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:580)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:503)
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:317)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:315)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:760)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:869)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550)
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:140)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759)
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:395)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:327)
    at com.comicflix.library.ComicFlixLibraryApp.main(ComicFlixLibraryApp.java:61)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'titleRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract com.comicflix.library.domain.Title com.comicflix.library.repository.TitleRepository.findByStudiosGenresLanguages(java.util.List,java.util.List)!
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1708)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:581)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:503)
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:317)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:315)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
    at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:251)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1138)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1065)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:584)
    ... 22 common frames omitted
Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract com.comicflix.library.domain.Title com.comicflix.library.repository.TitleRepository.findByStudiosGenresLanguages(java.util.List,java.util.List)!
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:93)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:63)
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:76)
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromQueryAnnotation(JpaQueryFactory.java:56)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:139)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:206)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:79)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lookupQuery(RepositoryFactorySupport.java:553)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$mapMethodsToQuery$1(RepositoryFactorySupport.java:546)
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
    at java.util.Iterator.forEachRemaining(Iterator.java:116)
    at java.util.Collections$UnmodifiableCollection$1.forEachRemaining(Collections.java:1049)
    at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:499)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.mapMethodsToQuery(RepositoryFactorySupport.java:548)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$new$0(RepositoryFactorySupport.java:538)
    at java.util.Optional.map(Optional.java:215)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:538)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:317)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.lambda$afterPropertiesSet$3(RepositoryFactoryBeanSupport.java:287)
    at org.springframework.data.util.Lazy.getNullable(Lazy.java:141)
    at org.springframework.data.util.Lazy.get(Lazy.java:63)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:290)
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:102)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1767)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1704)
    ... 32 common frames omitted
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: TITLE is not mapped [SELECT T FROM TITLE T WHERE T.LANGUAGE.NAME IN (:languages) AND T.GENRE.NAME IN (:genres)]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:133)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:164)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:670)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:350)
    at com.sun.proxy.$Proxy175.createQuery(Unknown Source)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:87)
    ... 61 common frames omitted
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: TITLE is not mapped [SELECT T FROM TITLE T WHERE T.LANGUAGE.NAME IN (:languages) AND T.GENRE.NAME IN (:genres)]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.generateQueryException(QuerySyntaxException.java:79)
    at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:217)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:141)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153)
    at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:553)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:662)
    ... 69 common frames omitted
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: TITLE is not mapped
    at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:169)
    at org.hibernate.hql.internal.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:91)
    at org.hibernate.hql.internal.ast.tree.FromClause.addFromElement(FromClause.java:79)
    at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromElement(HqlSqlWalker.java:326)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3706)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3595)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:720)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:576)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:313)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:261)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:266)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189)
    ... 75 common frames omitted


Process finished with exit code 0

请告诉我如何根据传递给API的输入来获得响应。

提前谢谢

Answer 1

到目前为止我们还没有足够的信息来帮助你。但大写实体/字段名的使用是可疑的。这些应该与Java命名相对应。

编辑:更新您的问题后，您的JPQL命名显然与@所指出的Java命名+不正确的连接不一致。

Answer 2

你的问题是错的。

JPQL不是SQL！JPQL在实体模型上运行，名称必须在相同的情况下。

此外，如果要查询ToMany关系，则必须加入这些关系。

这个查询应该可以工作(我还没有测试它)：

 @Query("SELECT t FROM Titel t join t.title_language l join t.genre_title g where l.name IN (:languages) AND g.name IN (:genres)")