CODEWITHSUNDEEP
javamysqlhibernate
Rigg97
Rigg97

Reputation: 113

Hibernate - Query Works in MySQL WorkBench But Hibernate Throws SQLException

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;
    }
}

Upvotes: 0

Views: 156

Answers (1)

SternK
SternK

Reputation: 13041

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);

Upvotes: 1

Related Questions