Hibernate: Unable to query a database table using a Foreign Key

Question

I am trying to fire the following Hibernare quarry.

 Query query = session.createSQLQuery("from Rating as rating where rating.organization.idorganization = :idorganization");

I always ended up with the error

hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from Rating as rating where rating.organization.idorganization = 65' at line 1
org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Below is my Rating bean

public class Rating  implements java.io.Serializable {


     private Integer idrating;
     private Organization organization;
     private User user;
     private double rating;
     private Date dateCreated;
     private Date lastUpdated;

    public Rating() {
    }

    
    public Rating(Organization organization, User user, double rating) {
        this.organization = organization;
        this.user = user;
        this.rating = rating;
    }
    public Rating(Organization organization, User user, double rating, Date dateCreated, Date lastUpdated) {
       this.organization = organization;
       this.user = user;
       this.rating = rating;
       this.dateCreated = dateCreated;
       this.lastUpdated = lastUpdated;
    }
   
    public Integer getIdrating() {
        return this.idrating;
    }
    
    public void setIdrating(Integer idrating) {
        this.idrating = idrating;
    }
    public Organization getOrganization() {
        return this.organization;
    }
    
    public void setOrganization(Organization organization) {
        this.organization = organization;
    }
    public User getUser() {
        return this.user;
    }
    
    public void setUser(User user) {
        this.user = user;
    }
    public double getRating() {
        return this.rating;
    }
    
    public void setRating(double rating) {
        this.rating = rating;
    }
    public Date getDateCreated() {
        return this.dateCreated;
    }
    
    public void setDateCreated(Date dateCreated) {
        this.dateCreated = dateCreated;
    }
    public Date getLastUpdated() {
        return this.lastUpdated;
    }
    
    public void setLastUpdated(Date lastUpdated) {
        this.lastUpdated = lastUpdated;
    }


}

Below is the Rating.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- Generated Sep 19, 2020 8:15:23 PM by Hibernate Tools 4.3.1 -->
<hibernate-mapping>
    <class name="beans.Rating" table="rating" catalog="autocircle" optimistic-lock="version">
        <id name="idrating" type="java.lang.Integer">
            <column name="idrating" />
            <generator class="identity" />
        </id>
        <many-to-one name="organization" class="beans.Organization" fetch="select">
            <column name="idorganization" not-null="true" />
        </many-to-one>
        <many-to-one name="user" class="beans.User" fetch="select">
            <column name="iduser" not-null="true" />
        </many-to-one>
        <property name="rating" type="double">
            <column name="rating" precision="22" scale="0" not-null="true" />
        </property>
        <property name="dateCreated" type="timestamp">
            <column name="date_created" length="0" />
        </property>
        <property name="lastUpdated" type="timestamp">
            <column name="last_updated" length="0" />
        </property>
    </class>
</hibernate-mapping>

Why am I getting this error?

Answer 1

Since you are using createSQLQuery method, the SQL Statement should be used. In this case, it should be

"select * from Rating as rating where rating.organization.idorganization = :idorganization"

Or use the HQL Method and appropriate HQL Query