Announcement Announcement Module
No announcement yet.
Issues with getHibernateTemplate().find() method Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Issues with getHibernateTemplate().find() method

    I have a simple method in my DAO class as follows:
    public List getAll() {
    return getHibernateTemplate().find("FROM ADVISOR_CONVERSION");
    If I enable the show_sql property as true
    <prop key="hibernate.show_sql">true</prop>
    This is the SQL I get in the logs:
    Hibernate: select from
    And this obviously generates this exception:
    Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: Could not execute query; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-00936: missing expression

  • #2
    I would ensure you are using the correct object name defined in the Hibernate hbm for the query. Is it possible to see the hbm and the applicationContext.xml?


    • #3
      hbm and the applicationContext.xml

      hbm and the applicationContext.xml

      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "">
      <hibernate-mapping auto-import="true">
      <class name="chase.ibc.globalpipeline.model.Advisor" table="ADVISOR_CONVERSION">
      <id name="advisorId" type="java.lang.Long" column="ADVISOR_ID">
      <generator class="assigned"/>
      <property column="LEGACY_NAME" name="legacyName" type="java.lang.String"/>

      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "spring-beans.dtd">
      <!-- Data source beans -->
      <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
      <property name="driverClassName"><value>oracle.jdbc.driver.O racleDriver</value></property>
      <property name="url"><value>jdbc:oracle:thin:@cmc4asff8.ny.j</value></property>
      <property name="username"><value>gp_user</value></property>
      <property name="password"><value>gp_user</value></property>
      <bean id="transactionManager" class="org.springframework.orm.hibernate.Hibernate TransactionManager">
      <property name="sessionFactory"><ref local="sessionFactory"/></property>
      <bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSess ionFactoryBean">
      <property name="dataSource"><ref local="dataSource"/></property>
      <property name="mappingResources">
      <property name="hibernateProperties">
      <prop key="hibernate.dialect">org.hibernate.dialect.Orac leDialect</prop>
      <prop key="hibernate.max_fetch_depth">3</prop>
      <prop key="hibernate.show_sql">true</prop>
      <bean id="advisorDao" class="chase.ibc.globalpipeline.database.dao.Hiber nateAdvisorDao">
      <property name="sessionFactory"><ref local="sessionFactory"/></property>


      • #4
        You need to use the class name in the query not the table name.
        public List getAll() {
            return getHibernateTemplate().find("from Advisor");


        • #5
          Use Advisor

          If you are using method find you have to use Advisor instead of ADVISOR_CONVERSION. Hql is using name of hibernate objects not table names. So use return getHibernateTemplate().find("FROM Advisor").

          If you are using sql query api then you have to use actual table names of database. Example session.createSQLQuery().


          • #6
            Works but...

            Works with your suggestions with a caveat.
            This is the query I see in the log:

            Hibernate: select advisor_co0_.ADVISOR_ID as ADVISOR_ID, advisor_co0_.LEGACY_NAME as LEGACY_N2_ from Advisor_Conversion advisor_co0_

            This query runs fine in SQL Navigator. However, Spring throws the following exception:

            Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: Could not execute query; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-00942: table or view does not exist


            • #7
              Well I would guess the exception is right. Either the table or view just does not exist, or you don't have the right permissions to access it, hence you can't see it.


              • #8
                The database design is fixed and I cannot change it. All the tables have an underscore in them e.g. ADVISOR_CONVERSION and the columns in them also have an underscore in their names e.g. ADVISOR_ID and LEGACY_NAME. What should be the corresponding POJO for these tables? Is this correct?
                public class Advisor_Conversion {
                private java.lang.Long advisor_id;
                private String legacy_name;

                public Advisor_Conversion() {

                public java.lang.Long getadvisor_id() {
                return this.advisor_id;

                public String getlegacy_name() {
                return this.legacy_name;

                public void setadvisor_id(java.lang.Long id) {
                this.advisor_id = id;

                public void setlegacy_name(String name) {
                this.legacy_name = name;


                • #9
                  I don't think this has anything to do with the entity. It's simply that Hibernate can't see the table. Are you logging in with the same username and password? Are you connecting to the same tablespace/schema? Do you have permission to see the table?


                  • #10
                    You are right. I was using a different schema to check the sql query Hibernate generated. I changed the id and password in applicationContext.xml and it works now. Thanks for all your help.


                    • #11
                      Not a problem, glad it helped! If in doubt always use the same details.