Announcement Announcement Module
Collapse
No announcement yet.
HibernateTemplate generates incorrect SQL Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • HibernateTemplate generates incorrect SQL

    Note: I do have a post on the Hibernate Forum, but I think this is a Spring issue..see below

    Hibernate version:3.current
    Spring Version: 1.2.1

    Mapping documents:
    Note: I do not know if the key is just AREA_ID or AREA and AREA_ID, but the key regardless isn't anything auto-generated.
    <hibernate-mapping>

    <class
    name="test.bo.AreaBO"
    table="LOCATIONS_AREA_T"
    dynamic-update="false"
    dynamic-insert="false"
    >

    <id
    name="areaID"
    column="AREA_ID"
    type="string"
    unsaved-value="0"
    >
    <generator class="native"/>
    </id>

    <property
    name="area"
    type="string"
    update="false"
    insert="false"
    column="AREA"
    not-null="true"
    unique="true"
    lazy="true"
    />

    </class>

    </hibernate-mapping>


    My call to Hibernate though Spring is: AreaBO area = (AreaBO) getHibernateTemplate().load(AreaBO.class, "11A");

    No errors, unless I do a specific query on the "AREA" field in which I get an org.hibernate.hql.Parser error saying "AREA" isn't mapped, but it is.

    Database: Oracle 9.2.0.6.0 (WSAD 5.1.x)

    The generated SQL select areabo0_.AREA_ID as AREA1_0_ from FORM4248_AREA areabo0_ where areabo0_.AREA_ID=?

    The sql SHOULD be: select areabo0_.AREA_ID as AREA1_0_, areabo0_.AREA as AREA1_0_ from FORM4248_AREA areabo0_ where areabo0_.AREA_ID=?

    The table/row I am quering looks like
    AREA: Southern Illinois
    AREA_ID: 11A

    And my problem is that Hibernate is only asking for the area_id, when in fact I am asking for the whole object which I understand to be the WHOLE record that I specified in my mapping xml file. Amazingly when my AreaBO gets created the area and areaID are the same.

    It seems to just ignore my AREA column. If I take Spring out of the situation and call Hibernate directly, which defeats why I am using Spring, then everything works as expected and I get an AreaBO object with the right data. If I try a List list = getHibernateTemplate().loadAll(AreaBO.class) I still get bad SQL.

    Since the same setup works with Hibernate, I'm thinking I have everything configured right and setup regarding Hibernate. Maybe I found some odd bug in Spring but I would SERIOUSLY doubt something of this caliber slipped though the cracks. Any ideas?

  • #2
    Potential (major) Spring/Hibernate bug?

    I modified my earlier code to:
    Code:
    getHibernateTemplate&#40;&#41;.find&#40;"from AreaBO area where area.area = ?", "Western"&#41;
    Which AFAIK is the correct way to say "get me a list of AreaBO objects that have an area value of Western"

    Hibernate output sql:
    Code:
    Hibernate&#58; select areabo0_.AREA_ID as AREA1_ from FORM4248_AREA areabo0_ where areabo0_.AREA=?
    Which is incorrect.

    Following Spring's HibernateTemplate source, my above find call gets "translated" into the below Hibernate code, almost to the variable name:
    Code:
    	Query queryObject = session.createQuery&#40;"from AreaBO area where area.area = ?"&#41;; 
    	queryObject.setParameter&#40;0, "Western"&#41;; 
    	List list = queryObject.list&#40;&#41;;
    If I run this code, which is from my understanding how Spring executes my find(), in the same exact environment (same db, same objects) but outside of Spring and manually manage Hibernate Sessions, I get the expected results and the generated sql is correct.

    Hibernate output sql:
    Code:
    Hibernate&#58; select area0_.AREA_ID as AREA_ID, area0_.AREA as AREA from FORM4248_AREA area0_ where &#40;area0_.AREA=? &#41;
    I'm don't know or understand what is going on but it looks like there is a bug somewhere in the Spring or Hibernate framework that is throwing things off. I can't explain it any other way. I don't know why the code inside Spring isn't working but the same code outside does.

    Any takers before I fill out bug reports?

    Comment


    • #3
      Re: HibernateTemplate generates incorrect SQL

      Originally posted by travism
      <property
      name="area"
      type="string"
      update="false"
      insert="false"
      column="AREA"
      not-null="true"
      unique="true"
      lazy="true"
      />
      Note the lazy="true".

      Comment


      • #4
        Re: HibernateTemplate generates incorrect SQL

        Originally posted by dejanp
        Note the lazy="true".
        Yes, I've tried it with and without the lazy line. It doesn't change anything I've described.

        Code:
        <hibernate-mapping>
        
        	<class name="gov.usps.test.bo.AreaBO" table="FORM4248_AREA">
        		<id name="area_id" type="string" column="AREA_ID" >		
        			<generator class="assigned"/>
        		</id>
        		<property name="area" column="AREA" lazy="false" />
        
        	</class>
        	
        </hibernate-mapping>
        Is another mapping example that worked perfect in regular Hibernate but it again generated bad sql run from within Spring...

        Comment


        • #5
          Most probably Spring is loading a wrong set of hibernate configuration files. With lazy="true" the result you are getting is the correct one.

          Comment


          • #6
            I'm very thankful for the help given so far dejanp. I really do appreciate this.

            I don't understand
            Originally posted by dejanp
            Most probably Spring is loading a wrong set of hibernate configuration files.
            Why wouldn't spring load the right files?

            Here is my Spring stuff in web.xml
            Code:
            	<context-param>
            		<param-name>contextConfigLocation</param-name>
            		<param-value>/WEB-INF/applicationContext-hibernate.xml</param-value>		
            	</context-param>
            	
            	<context-param>
            		<param-name>log4jConfigLocation</param-name>
            		<param-value>/WEB-INF/log4j.xml</param-value>
            	</context-param>	
            	
            	<listener>
            		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
            	</listener>
            	
            	<servlet>
            	  <servlet-name>SpringContextServlet</servlet-name>
            	  <servlet-class>org.springframework.web.context.ContextLoaderServlet</servlet-class>	  
            	  <load-on-startup>0</load-on-startup>
            	</servlet>
            Here is my applicationContext-hibernate.xml file
            Code:
            <?xml version="1.0" encoding="UTF-8"?>
            <!DOCTYPE beans SYSTEM "/WEB-INF/spring-beans.dtd">
            
            <beans>	 
            
            
            	<bean id="myDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
            	  <property name="jndiName"><value>jdbc/oracledb</value></property>
            	</bean>
            	
            	<bean id="myTransactionManager" class="org.springframework.transaction.jta.WebSphereTransactionManagerFactoryBean"/>
            
            	<bean id="myJTATransactionManager" class="org.springframework.transaction.jta.JtaTransactionManager"> 
            		<property name="transactionManager"><ref local="myTransactionManager"/></property> 
            	</bean>
            	
            	
            	<bean id="mySessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
            		<property name="mappingResources">
            		  <list>
            		    <value>gov/usps/test/bo/AreaBO.hbm.xml</value>
            		  </list>
            		</property>
            		<property name="hibernateProperties">
            		  <props>
            		    <prop key="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</prop>
            		    <prop key="hibernate.show_sql">true</prop>
            		  	<prop key="hibernate.transaction.manager_lookup_class">org.hibernate.transaction.WebSphereTransactionManagerLookup</prop>
            		    <prop key="hibernate.transaction.factory_class">org.hibernate.transaction.CMTTransactionFactory</prop>
            		    <prop key="cache.use_query_cache">false</prop>
            			<prop key="cache.use_minimal_puts">false</prop>
            		  </props>
            		</property>
            		<property name="dataSource"><ref local="myDataSource"/></property>
            	</bean>	
            	
            	<!-- ========================= BUSINESS DEFINITIONS ========================= -->
            
            
            	<!-- ***** AREA SERVICE *****-->
            	<bean id="areaService" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">	
            		<!--<property name="transactionManager"><ref local="myTransactionManager"/></property>-->
            		<property name="transactionManager"><ref local="myJTATransactionManager"/></property>
            		<property name="target"><ref local="areaTarget"/></property>
            		<property name="transactionAttributes">
            			<props>
            				<prop key="find*">PROPAGATION_REQUIRED,readOnly,-SomethingIsWrong</prop>
            			</props>
            		</property>
            	</bean>
            	
            	<!-- DAO object&#58; Hibernate implementation -->
            	<bean id="areaDAO" class="test.service.dao.hibernate.AreaHibernateDAO">
            		<property name="sessionFactory"><ref local="mySessionFactory"/></property>
            	</bean>	
            		
            	
            	
            	<!-- AreaTarget primary business object implementation -->
            	<bean id="areaTarget" class=".test.service.spring.AreaServiceSpringImpl">
            		<property name="areaDAO"><ref local="areaDAO"/></property>
            	</bean>
            	
            
            	<!-- Add more services/DAOs here -->
            	
            </beans>
            [/code]

            Comment


            • #7
              The spring might load wrong set of files if you have multiple version of them floating around. Check your classpath and/or jar/wars carefully and find out exactly which files are used for hibernate configuration.

              Comment

              Working...
              X