英文:
Hibernate - Query Works in MySQL WorkBench But Hibernate Throws SQLException
问题
以下是翻译好的部分:
查询:
@Query(value = "SELECT DISTINCT F.focusID FROM Focus F " +
"JOIN FrameworkFoci FF on FF.focusID = F.focusID " +
"JOIN FocusGroups FG on FF.frameworkID = FG.frameworkID " +
"JOIN GroupMembers GM on FG.groupID = GM.groupID " +
"JOIN Users U on GM.userID = U.userID " +
"WHERE U.userID = :userID", nativeQuery = true)
List<Focus> findByUserID(@Param("userID") Long userID);
Focus 实体:
@Entity
public class Focus {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long focusID;
private String focusCategory;
private String focusName;
private String focusExplanation;
@OneToMany(mappedBy = "focus")
private Set<Rating> ratings;
@ManyToMany
@JoinTable(name = "FrameworkFoci",
joinColumns = @JoinColumn(
name = "focusID"),
inverseJoinColumns = @JoinColumn(
name = "frameworkID"))
private Set<Framework> frameworks;
protected Focus(){}
public Focus(String focusName, String focusCategory, String focusExplanation) {
this.focusCategory = focusCategory;
this.focusName = focusName;
this.focusExplanation = focusExplanation;
}
public Focus(String focusCategory, String focusName, String focusExplanation, Set<Rating> ratings){
this.focusCategory = focusCategory;
this.focusName = focusName;
this.focusExplanation = focusExplanation;
this.ratings = ratings;
}
public Long getFocusId() {
return focusID;
}
public void setFocusId(Long focusID) {
this.focusID = focusID;
}
public String getFocusCategory() {
return focusCategory;
}
public void setFocusCategory(String focusCategory) {
this.focusCategory = focusCategory;
}
}
英文:
My query works fine in workbench however hibernate throws the error java.sql.SQLException: Column 'focusCategory' not found.
focusCategory is mapped correctly and it's not even part of the statements, I can't think of why this would be happening. The Focus table has not had any problems up until this point
Query:
@Query(value = "SELECT DISTINCT F.focusID FROM Focus F " +
"JOIN FrameworkFoci FF on FF.focusID = F.focusID " +
"JOIN FocusGroups FG on FF.frameworkID = FG.frameworkID " +
"JOIN GroupMembers GM on FG.groupID = GM.groupID " +
"JOIN Users U on GM.userID = U.userID " +
"WHERE U.userID = :userID", nativeQuery = true)
List<Focus> findByUserID(@Param("userID") Long userID);
Focus Entity:
@Entity
public class Focus {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long focusID;
private String focusCategory;
private String focusName;
private String focusExplanation;
@OneToMany(mappedBy = "focus")
private Set<Rating> ratings;
@ManyToMany
@JoinTable(name = "FrameworkFoci",
joinColumns = @JoinColumn(
name = "focusID"),
inverseJoinColumns = @JoinColumn(
name = "frameworkID"))
private Set<Framework> frameworks;
protected Focus(){}
public Focus(String focusName, String focusCategory, String focusExplanation) {
this.focusCategory = focusCategory;
this.focusName = focusName;
this.focusExplanation = focusExplanation;
}
public Focus(String focusCategory, String focusName, String focusExplanation, Set<Rating> ratings){
this.focusCategory = focusCategory;
this.focusName = focusName;
this.focusExplanation = focusExplanation;
this.ratings = ratings;
}
public Long getFocusId() {
return focusID;
}
public void setFocusId(Long focusID) {
this.focusID = focusID;
}
public String getFocusCategory() {
return focusCategory;
}
public void setFocusCategory(String focusCategory) {
this.focusCategory = focusCategory;
}
}
答案1
得分: 1
你的原生查询在 ResultSet
中只返回一个名为 focusID
的列,但你尝试将其映射到 Focus
实体。我在这个 SQL 中没有看到你需要使用原生 SQL 的任何原因。因此,我认为在这里更可取的方式是使用 HQL 或 JPQL。
以下查询将按预期工作:
@Query(value = "SELECT DISTINCT F.focusID FROM Focus F " +
"JOIN FrameworkFoci FF on FF.focusID = F.focusID " +
"JOIN FocusGroups FG on FF.frameworkID = FG.frameworkID " +
"JOIN GroupMembers GM on FG.groupID = GM.groupID " +
"JOIN Users U on GM.userID = U.userID " +
"WHERE U.userID = :userID", nativeQuery = true)
List<Long> findByUserID(@Param("userID") Long userID);
英文:
Your native query returns in the ResultSet
only one column focusID
but you try to map it to the Focus
entity. I do not see in this sql any reason why you should use here native sql. So, more preferable way as for me here is hql or jpql.
The following query will work as you expected:
@Query(value = "SELECT DISTINCT F.focusID FROM Focus F " +
"JOIN FrameworkFoci FF on FF.focusID = F.focusID " +
"JOIN FocusGroups FG on FF.frameworkID = FG.frameworkID " +
"JOIN GroupMembers GM on FG.groupID = GM.groupID " +
"JOIN Users U on GM.userID = U.userID " +
"WHERE U.userID = :userID", nativeQuery = true)
List<Long> findByUserID(@Param("userID") Long userID);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论