CODEWITHSUNDEEP
javaspring-data-jpa
Byron
Byron

Reputation: 13

Join query causes UnsatisfiedDependencyException

I'm working on a spring data jpa & thymeleaf project and I'm encountering a problem with a join query.

I have 3 tables ( many to many relationship ):
1. Lesson
2. Options
3. The join table : options_has_lesson

I would perform a join query to get the related lessons from an option id + active lessons not related to an option id.

My request below works and I have the records I want in phpMyAdmin, but eclipse shows an exception as soon as I save the request into my repository.

I already did other queries in spring data ( but without join ) and did not encounter this kind of issue. I guess it's because of the join and even the error message seems "clear", I'm little lost to solve it.

Here is my lesson model

@Entity
@Table(name="lesson")
@EntityListeners(AuditingEntityListener.class)
public class Lesson implements Serializable
{
private static final long serialVersionUID = 1L;

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

@Column(name="active")
@NotNull
private Boolean active = true;

@Column(name="name")
@NotNull
@Size(max=255)
private String name;

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

@OneToMany(mappedBy = "lesson")
@JsonBackReference
private Set<OptionsHasLesson> optionsHasLesson = 
    new HashSet<OptionsHasLesson>();

public Lesson() {}

/**the getters & setters**/
}

Here is my option model

@Entity
@Table(name = "options")
@EntityListeners(AuditingEntityListener.class)
public class Options implements Serializable
{
private static final long serialVersionUID = 1L;

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

@Column(name="name")
@NotNull
@Size(max = 255)
private String name;

@Column(name="active")
@NotNull
private Boolean active = true;

@OneToMany(mappedBy = "options")
@JsonBackReference
private Set<OptionsHasLesson> optionsHasLesson = 
    new HashSet<OptionsHasLesson>();

public Options()
{}
    /**the getters & setters**/
}

Here is my join table

@Entity
@Table(name="options_has_lesson")
@EntityListeners(AuditingEntityListener.class)
public class OptionsHasLesson implements Serializable
{

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue
@Column(name="id")
private long id;

@ManyToOne
@JoinColumn(name = "option_id")
@JsonManagedReference
private Options options;

@ManyToOne
@JoinColumn(name = "lesson_id")
@JsonManagedReference
private Lesson lesson;

@Column(name = "period_by_week")
@NotNull
private int periodByWeek;

/**the getters & setters**/

}

Here is my LessonRepository with the query 

@Repository
public interface LessonRepository extends JpaRepository<Lesson ,Long>
{
List<Lesson> findByActiveTrueOrderByNameAsc();

@Query("SELECT l.id as lesson_id, l.name as lesson_name, l.active as lesson_active,"
        + " ohl.id as ohl_id, ohl.lesson_id as ohl_lesson_id, ohl.option_id, ohl.period_by_week "
        + "from lesson l "
        + "left join options_has_lesson ohl "
        + "on ohl.lesson_id = l.id "
        + "where ohl.option_id = :id or ohl.option_id is NULL and l.active is TRUE")
List<Object> findCustomQuery(@Param("id") Long id);
}

Here is the exception

2019-01-03 10:41:25.176 ERROR Byron-PC --- [  restartedMain] o.s.b.SpringApplication                  : Application run failed
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'lessonController': Unsatisfied dependency expressed through field 'lessonRepository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'lessonRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List be.bschool.repository.LessonRepository.findCustomQuery(java.lang.Long)!
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:596)
    at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:90)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessProperties(AutowiredAnnotationBeanPostProcessor.java:374)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1378)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:575)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:498)
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:846)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:863)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:546)
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:140)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:775)
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:316)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1260)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1248)
    at be.bschool.BschoolSpringJpaThymeleafApplication.main(BschoolSpringJpaThymeleafApplication.java:14)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'lessonRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List be.bschool.repository.LessonRepository.findCustomQuery(java.lang.Long)!
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1745)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:576)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:498)
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
    at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:273)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1239)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1166)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:593)
    ... 24 more
Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List be.bschool.repository.LessonRepository.findCustomQuery(java.lang.Long)!
    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:566)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$mapMethodsToQuery$1(RepositoryFactorySupport.java:559)
    at java.util.stream.ReferencePipeline$3$1.accept(Unknown Source)
    at java.util.Iterator.forEachRemaining(Unknown Source)
    at java.util.Collections$UnmodifiableCollection$1.forEachRemaining(Unknown Source)
    at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Unknown Source)
    at java.util.stream.AbstractPipeline.copyInto(Unknown Source)
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(Unknown Source)
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(Unknown Source)
    at java.util.stream.AbstractPipeline.evaluate(Unknown Source)
    at java.util.stream.ReferencePipeline.collect(Unknown Source)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.mapMethodsToQuery(RepositoryFactorySupport.java:561)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$new$0(RepositoryFactorySupport.java:551)
    at java.util.Optional.map(Unknown Source)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:551)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:324)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.lambda$afterPropertiesSet$5(RepositoryFactoryBeanSupport.java:297)
    at org.springframework.data.util.Lazy.getNullable(Lazy.java:211)
    at org.springframework.data.util.Lazy.get(Lazy.java:94)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:300)
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:119)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1804)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1741)
    ... 34 more
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: lesson is not mapped [SELECT l.id as lesson_id, l.name as lesson_name, l.active as lesson_active, ohl.id as ohl_id, ohl.lesson_id as ohl_lesson_id, ohl.option_id, ohl.period_by_week from lesson l left join options_has_lesson ohl on ohl.lesson_id = l.id where ohl.option_id = :id or ohl.option_id is NULL and l.active is TRUE]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:713)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:350)
    at com.sun.proxy.$Proxy646.createQuery(Unknown Source)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:87)
    ... 63 more
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: lesson is not mapped [SELECT l.id as lesson_id, l.name as lesson_name, l.active as lesson_active, ohl.id as ohl_id, ohl.lesson_id as ohl_lesson_id, ohl.option_id, ohl.period_by_week from lesson l left join options_has_lesson ohl on ohl.lesson_id = l.id where ohl.option_id = :id or ohl.option_id is NULL and l.active is TRUE]
    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:219)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:143)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:119)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153)
    at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:595)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:704)
    ... 71 more
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: lesson 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:331)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3695)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3584)
    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:271)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:191)
    ... 77 more

Upvotes: 0

Views: 342

Answers (1)

user9420984
user9420984

Reputation:

As it is currently stated, your query is written in SQL. The default language Spring uses is HQL. This is an object oriented language and therefore throws an exception when SQL syntax is used.

In order to use SQL within Spring, the query should be built the following way:

@Query(value=<SQL Query String>, nativeQuery=true)

This allows for Spring to execute SQL.

Additonally, if you are using HQL, please note that you refer to the fields by the object name, not by the table name.

So if you want to select an option_id from the table options_has_lessons, it will look like this:

SQL: @Query(value="SELECT option_id FROM options_has_lessons", nativeQuery=true)
HQL: @Query("SELECT options FROM OptionsHasLessons")

Upvotes: 3

Related Questions