- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在开发一个 spring data jpa 和 thymeleaf 项目,并且遇到了连接查询问题。
我有 3 个表(多对多关系):
1. 类(class)
2. 选项
3. 连接表:options_has_lesson
我将执行联接查询,以从选项 ID + 与选项 ID 不相关的 Activity 类(class)中获取相关类(class)。
我的下面的请求有效,并且我在 phpMyAdmin 中有我想要的记录,但是当我将请求保存到我的存储库中时,Eclipse 立即显示异常。
我已经在 spring data 中进行了其他查询(但没有 join ),并且没有遇到此类问题。我想这是因为加入,甚至错误消息看起来“清晰”,我几乎无法解决它。
这是我的类(class)模型
@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**/
}
这是我的选项模型
@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**/
}
这是我的连接表
@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**/
}
这是我的 LessonRepository 和查询
@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);
}
这是一个异常(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
最佳答案
正如目前所述,您的查询是用 SQL 编写的。 Spring使用的默认语言是HQL。这是一种面向对象的语言,因此在使用 SQL 语法时会引发异常。
为了在 Spring 中使用 SQL,应按以下方式构建查询:
@Query(value=<SQL Query String>, nativeQuery=true)
这允许 Spring 执行 SQL。
此外,如果您使用 HQL,请注意,您通过对象名称引用字段,而不是通过表名称。
所以如果你想选择 option_id
从表中options_has_lessons
,它看起来像这样:
SQL:@Query(value="SELECT option_id FROM options_has_lessons", nativeQuery=true)
总部:@Query("SELECT options FROM OptionsHasLessons")
关于java - 连接查询导致 UnsatisfiedDependencyException,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/54020362/
我有三张 table 。表 A 有选项名称(即颜色、尺寸)。表 B 有选项值名称(即蓝色、红色、黑色等)。表C通过将选项名称id和选项名称值id放在一起来建立关系。 我的查询需要显示值和选项的名称,而
在mysql中,如何计算一行中的非空单元格?我只想计算某些列之间的单元格,比如第 3-10 列之间的单元格。不是所有的列...同样,仅在该行中。 最佳答案 如果你想这样做,只能在 sql 中使用名称而
关闭。这个问题需要多问focused 。目前不接受答案。 想要改进此问题吗?更新问题,使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
我正在为版本7.6进行Elasticsearch查询 我的查询是这样的: { "query": { "bool": { "should": [ {
关闭。这个问题需要多问focused 。目前不接受答案。 想要改进此问题吗?更新问题,使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
是否可以编写一个查询来检查任一子查询(而不是一个子查询)是否正确? SELECT * FROM employees e WHERE NOT EXISTS (
我找到了很多关于我的问题的答案,但问题没有解决 我有表格,有数据,例如: Data 1 Data 2 Data 3
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
我从 EditText 中获取了 String 值。以及提交查询的按钮。 String sql=editQuery.getText().toString();// SELECT * FROM empl
我有一个或多或少有效的查询(关于结果),但处理大约需要 45 秒。这对于在 GUI 中呈现数据来说肯定太长了。 所以我的需求是找到一个更快/更高效的查询(几毫秒左右会很好)我的数据表大约有 3000
这是我第一次使用 Stack Overflow,所以我希望我以正确的方式提出这个问题。 我有 2 个 SQL 查询,我正在尝试比较和识别缺失值,尽管我无法将 NULL 字段添加到第二个查询中以识别缺失
什么是动态 SQL 查询?何时需要使用动态 SQL 查询?我使用的是 SQL Server 2005。 最佳答案 这里有几篇文章: Introduction to Dynamic SQL Dynami
include "mysql.php"; $query= "SELECT ID,name,displayname,established,summary,searchlink,im
我有一个查询要“转换”为 mysql。这是查询: select top 5 * from (select id, firstName, lastName, sum(fileSize) as To
通过我的研究,我发现至少从 EF 4.1 开始,EF 查询上的 .ToString() 方法将返回要运行的 SQL。事实上,这对我来说非常有用,使用 Entity Framework 5 和 6。 但
我在构造查询来执行以下操作时遇到问题: 按activity_type_id过滤联系人,仅显示最近事件具有所需activity_type_id或为NULL(无事件)的联系人 表格结构如下: 一个联系人可
如何让我输入数据库的信息在输入数据 5 分钟后自行更新? 假设我有一张 table : +--+--+-----+ |id|ip|count| +--+--+-----+ |
我正在尝试搜索正好是 4 位数字的 ID,我知道我需要使用 LENGTH() 字符串函数,但找不到如何使用它的示例。我正在尝试以下(和其他变体)但它们不起作用。 SELECT max(car_id)
我有一个在 mysql 上运行良好的 sql 查询(查询 + 连接): select sum(pa.price) from user u , purchase pu , pack pa where (
我是一名优秀的程序员,十分优秀!