How to resolve Hibernate Table or view does not exist?

Question

I am able to view a list of results from R_States table using createSQLQuery.

  public List<String> getStates() throws Exception {    
    try 
    {
    Session session = sessionFactory.openSession();
    return session.createSQLQuery("select * from R_States").list();
    }
    catch(Exception e)
    {
     //Logging
    }
    finally
    {
        if(session !=null && session.isOpen())
        {
          session.close();
          session=null;
        }
    }
    return null;
}

But when I try to use HQL createQuery method I get a ORA-00942: table or view does not exist error.

   return session.createQuery("from States").list();

This is my session factory (JNDI Datasource).

<bean id="sessionFactory"
class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">

<property name="packagesToScan">
<list>
       <value>folder.dao</value>
       <value>folder.domain.States</value>
   </list>

</property>


<property name="hibernateProperties">
    <props>
        <prop key="hibernate.dialect">org.hibernate.dialect.Oracle9iDialect</prop>
        <prop key="hibernate.show_sql">true</prop>
        <prop key="hibernate.hbm2ddl.auto">update</prop>
        <prop key="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</prop>
        <prop key="hibernate.connection.datasource">java:/comp/env/jdbc/r</prop>
        <prop key="hibernate.current_session_context_class">thread</prop>

        </props>
        </property>  
    </bean>

This is my domain class. What am i missing?

@Entity 
@Table(name = "R_STATE")  
public class States {  


 @Column(name = "SCode")  
 private String stateCode;   


@Column(name = "SName")  
 private String stateName;  

 public String getStateCode() {
        return stateCode;
    }

    public void setStateCode(String stateCode) {
        this.stateCode = stateCode;
    }

    public String getStateName() {
        return stateName;
    }

    public void setStateName(String stateName) {
        this.stateName = stateName;
    }

Answer 1

   <value>folder.domain.States</value>

This is a class, not a package, so Hibernate probably won't find your mapped entity. Try changing with this:

   <value>folder.domain</value>

Answer 2

Had the same problem. Speaking just from my own experience, the problem was that I was not treating the query as a native SQL query against Oracle (the back end in my case). I also had to be sure to specify the schema. The query, once written to reference the actual field names and table name (with schema), finally ran fine. Example:

String sql = "Select fieldname as alias from myschema.mytablename where fieldname = 'somevalue'"; // This is all raw SQL, not Hibernate SQL
List<Double> dblList = null; // In my case, 'fieldname' is a NUMBER col type, so Hibernate returns a Double.  If it were a VARCHAR, it'd be a String.
try {
  dblList = session.createSQLQuery(sql).addScalar("alias", StandardBasicTypes.DOUBLE ).list(); // org.hibernate.session, org.hibernate.type.StandardBasicTypes
   } catch (Exception e) {
    e.printStackTrace();
  }

  int valuetoworkwith = 0;
  if(dblList != null && dblList.size() > 0) {
    valuetoworkwith = (dblList.get(0)).intValue();
  } else
  {
    // ... If desired, something to alert the user that no value was returned so the default of '0' is being used, or throw an exception, or do nothing in particular.
  }

Also see: https://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/querysql.html#querysql-creating