Hibernate – 查询在MySQL WorkBench中有效,但在Hibernate中引发SQLException。

huangapple go评论71阅读模式
英文:

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 &#39;focusCategory&#39; 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 = &quot;SELECT DISTINCT F.focusID FROM Focus F &quot; +
&quot;JOIN FrameworkFoci FF on FF.focusID = F.focusID &quot; +
&quot;JOIN FocusGroups FG on FF.frameworkID = FG.frameworkID &quot; +
&quot;JOIN GroupMembers GM on FG.groupID = GM.groupID &quot; +
&quot;JOIN Users U on GM.userID = U.userID &quot; +
&quot;WHERE U.userID = :userID&quot;, nativeQuery = true)
List&lt;Focus&gt; findByUserID(@Param(&quot;userID&quot;) 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 = &quot;focus&quot;)
private Set&lt;Rating&gt; ratings;
@ManyToMany
@JoinTable(name = &quot;FrameworkFoci&quot;,
joinColumns = @JoinColumn(
name = &quot;focusID&quot;),
inverseJoinColumns = @JoinColumn(
name = &quot;frameworkID&quot;))
private Set&lt;Framework&gt; 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&lt;Rating&gt; 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 = &quot;SELECT DISTINCT F.focusID FROM Focus F &quot; +
      &quot;JOIN FrameworkFoci FF on FF.focusID = F.focusID &quot; +
      &quot;JOIN FocusGroups FG on FF.frameworkID = FG.frameworkID &quot; +
      &quot;JOIN GroupMembers GM on FG.groupID = GM.groupID &quot; +
      &quot;JOIN Users U on GM.userID = U.userID &quot; +
      &quot;WHERE U.userID = :userID&quot;, nativeQuery = true)
List&lt;Long&gt; findByUserID(@Param(&quot;userID&quot;) Long userID);

huangapple
  • 本文由 发表于 2020年10月20日 08:22:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/64436803.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定