为什么这个SQL查询Java抛出一个异常?

Question

我迫切需要你帮我完成我的任务。下面的查询在执行我的Springboot项目的main()时抛出一个异常，因为显然查询失败了。

我试图将原始的findAllByKeyword()从示例代码调整到我的项目。不同之处在于，在示例项目中，主管有一个外键"id_keyword";而在我的项目中，教授没有。虽然我不太熟悉给定查询的语法，但我试图尽可能地适应它。

你可以在这篇文章的底部找到确切的错误信息。

使用此方法，我想找到给定Stichpunkt(德语中的意思是关键字)的所有对应的教授

@Query("SELECT DISTINCT p FROM Professor p " +
"WHERE p.id_professor = professor_hat_stichpunkt.id_professor " +
"AND professor_hat_stichpunkt.id_stichpunkt = stichpunkt.id_stichpunkt " +
"AND stichpunkt.id_stichpunkt = :stichpunkt" +
"ORDER BY p.nachname")
List<Professor> findAllByKeyword(@Param("stichpunkt") Stichpunkt stichpunkt);

DB形象教授班如下:

import javax.persistence.*;
import java.util.Objects;
@Entity
@Table(name = "professor")
public class Professor {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id_professor", nullable = false)
private Integer id;
@Column(name = "vorname", nullable = false)
private String vorname;

@Column(name = "nachname", nullable = false)
private String nachname;

@Column(name = "titel", nullable = false)
private String titel;
@Column(name = "mailadresse", nullable = false)
private String mailadresse;
@ManyToOne(targetEntity = Stichpunkt.class, optional = false)
@JoinColumn(name = "id_stichpunkt", referencedColumnName = "id_stichpunkt", nullable = false)
private Stichpunkt stichpunkt;

public void setStichpunkt(Stichpunkt stichpunkt) {
this.stichpunkt = stichpunkt;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Professor that = (Professor) o;
return Objects.equals(getId(), that.getId());
}
@Override
public int hashCode() {
return Objects.hash(getId());
}
@Override
public String toString() {
return nachname; // + " (" + (id == null ? "<null>" : id) + ')';
}

public String getVorname() {
return vorname;
}
public String getNachname() {
return nachname;
}
public Integer getId() {
return id;
}
}

---

Stichpunkt类如下:

import javax.persistence.*;
import java.util.Objects;

@Entity
@Table(name = "stichpunkt")
public class Stichpunkt {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id_stichpunkt", nullable = false)
private Integer id;
@Column(name = "titel", nullable = false)
private String titel;
@Column(name = "beschreibung", nullable = false)
private String beschreibung;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTitel() {
return titel;
}
@Override
public String toString() {
return titel + " (" + (id == null ? "<null>" : id) + ')';
}
}

---

ProfessorHatStichpunkt类:

import javax.persistence.*;
import java.io.Serializable;
import java.util.Objects;
@Entity
@Table(name = "professor_hat_stichpunkt")
public class ProfessorHatStichpunkt {
@EmbeddedId
private Id id;
public ProfessorHatStichpunkt() {
}
public ProfessorHatStichpunkt(Professor professor, Stichpunkt stichpunkt) {
this.id = new Id(professor, stichpunkt);
}
public Id getId() {
return id;
}
public void setId(Id id) {
this.id = id;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
ProfessorHatStichpunkt that = (ProfessorHatStichpunkt) o;
return Objects.equals(getId(), that.getId());
}
@Override
public int hashCode() {
return Objects.hash(getId());
}
@Override
public String toString() {
return "SupervisorHasKeyword{id=" + (id == null ? "<null>" : id) + '}';
}
@Embeddable
public static class Id implements Serializable {
@ManyToOne(targetEntity = Professor.class, optional = false)
@JoinColumn(name = "id_professor", referencedColumnName = "id_professor", nullable = false)
private Professor professor;
@ManyToOne(targetEntity = Stichpunkt.class, optional = false)
@JoinColumn(name = "id_stichpunkt", referencedColumnName = "id_stichpunkt", nullable = false)
private Stichpunkt stichpunkt;
public Id() {
}
public Id(Professor professor, Stichpunkt stichpunkt) {
this.professor = professor;
this.stichpunkt = stichpunkt;
}
public Professor getProfessor() {
return professor;
}
public void setProfessor(Professor professor) {
this.professor = professor;
}
public Stichpunkt getStichpunkt() {
return stichpunkt;
}
public void setStichpunkt(Stichpunkt stichpunkt) {
this.stichpunkt = stichpunkt;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Id id = (Id) o;
return Objects.equals(getProfessor(), id.getProfessor()) && Objects.equals(getStichpunkt(), id.getStichpunkt());
}
@Override
public int hashCode() {
return Objects.hash(getProfessor(), getStichpunkt());
}
@Override
public String toString() {
return String.format("%s has %s", professor, stichpunkt);
}
}
}

---

事情是这建立在一个给定的样本项目的工作，我只是适应了代码。工作示例代码如下:

public interface SupervisorRepository extends JpaRepository<Supervisor, Integer> {
@Query("SELECT DISTINCT s FROM Supervisor s " +
"INNER JOIN SupervisorHasKeyword shk ON shk.id.supervisor = s " +
"WHERE s.keyword = :keyword " +
"OR shk.id.keyword = :keyword " +
"ORDER BY s.name")
List<Supervisor> findAllByKeyword(@Param("keyword")Keyword keyword);
}

示例项目DB

Supervisor类如下:

import javax.persistence.*;
import java.util.Objects;
@Entity
@Table(name = "supervisor")
public class Supervisor {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id_supervisor", nullable = false)
private Integer id;
@Column(name = "name", nullable = false)
private String name;
@ManyToOne(targetEntity = Keyword.class, optional = false)
@JoinColumn(name = "id_keyword", referencedColumnName = "id_keyword", nullable = false)
private Keyword keyword;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Keyword getKeyword() {
return keyword;
}
public void setKeyword(Keyword keyword) {
this.keyword = keyword;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Supervisor that = (Supervisor) o;
return Objects.equals(getId(), that.getId());
}
@Override
public int hashCode() {
return Objects.hash(getId());
}
@Override
public String toString() {
return name + " (" + (id == null ? "<null>" : id) + ')';
}
}

---

关键字类:

import javax.persistence.*;
import java.util.Objects;
@Entity
@Table(name = "keyword")
public class Keyword {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id_keyword", nullable = false)
private Integer id;
@Column(name = "text", nullable = false)
private String text;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getText() {
return text;
}
public void setText(String text) {
this.text = text;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Keyword keyword = (Keyword) o;
return Objects.equals(getId(), keyword.getId());
}
@Override
public int hashCode() {
return Objects.hash(getId());
}
@Override
public String toString() {
return  text + " (" + (id == null ? "<null>" : id) + ')';
}
}

---

SupervisorHasKeyword类:

import javax.persistence.*;
import java.io.Serializable;
import java.util.Objects;
@Entity
@Table(name = "supervisor_has_keyword")
public class SupervisorHasKeyword {
@EmbeddedId
private Id id;
public SupervisorHasKeyword() {
}
public SupervisorHasKeyword(Supervisor supervisor, Keyword keyword) {
this.id = new Id(supervisor, keyword);
}
public Id getId() {
return id;
}
public void setId(Id id) {
this.id = id;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
SupervisorHasKeyword that = (SupervisorHasKeyword) o;
return Objects.equals(getId(), that.getId());
}
@Override
public int hashCode() {
return Objects.hash(getId());
}
@Override
public String toString() {
return "SupervisorHasKeyword{id=" + (id == null ? "<null>" : id) + '}';
}
@Embeddable
public static class Id implements Serializable {
@ManyToOne(targetEntity = Supervisor.class, optional = false)
@JoinColumn(name = "id_supervisor", referencedColumnName = "id_supervisor", nullable = false)
private Supervisor supervisor;
@ManyToOne(targetEntity = Keyword.class, optional = false)
@JoinColumn(name = "id_keyword", referencedColumnName = "id_keyword", nullable = false)
private Keyword keyword;
public Id() {
}
public Id(Supervisor supervisor, Keyword keyword) {
this.supervisor = supervisor;
this.keyword = keyword;
}
public Supervisor getSupervisor() {
return supervisor;
}
public void setSupervisor(Supervisor supervisor) {
this.supervisor = supervisor;
}
public Keyword getKeyword() {
return keyword;
}
public void setKeyword(Keyword keyword) {
this.keyword = keyword;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Id id = (Id) o;
return Objects.equals(getSupervisor(), id.getSupervisor()) && Objects.equals(getKeyword(), id.getKeyword());
}
@Override
public int hashCode() {
return Objects.hash(getSupervisor(), getKeyword());
}
@Override
public String toString() {
return String.format("%s has %s", supervisor,  keyword);
}
}
}

---

Error:

Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'professorService' defined in file [C:Users...camundadatabaseserviceProfessorService.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'professorRepository' defined in de.ostfalia.bips.ws22.camunda.database.repository.ProfessorRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List de.ostfalia.bips.ws22.camunda.database.repository.ProfessorRepository.findAllByKeyword(de.ostfalia.bips.ws22.camunda.database.domain.Stichpunkt); Reason: Validation failed for query for method public abstract java.util.List de.ostfalia.bips.ws22.camunda.database.repository.ProfessorRepository.findAllByKeyword(de.ostfalia.bips.ws22.camunda.database.domain.Stichpunkt)!; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List de.ostfalia.bips.ws22.camunda.database.repository.ProfessorRepository.findAllByKeyword(de.ostfalia.bips.ws22.camunda.database.domain.Stichpunkt)!

Answer 1

因为显然查询失败

查询本身没有失败，它在你得到异常的时候还没有被执行。异常是告诉您教授库不能被实例化，因为您在它的构造函数中引用的bean要么不存在，要么不是托管bean，但请查看构造函数中的第一个参数("通过构造函数参数0表示的未满足的依赖;")