HibernateTemplate generates incorrect SQL

[travism]

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?

[travism]

I modified my earlier code to:

getHibernateTemplate().find("from AreaBO area where area.area = ?", "Western")

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:

Hibernate: 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:

	Query queryObject = session.createQuery("from AreaBO area where area.area = ?"); 
	queryObject.setParameter(0, "Western"); 
	List list = queryObject.list();

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:

Hibernate: select area0_.AREA_ID as AREA_ID, area0_.AREA as AREA from FORM4248_AREA area0_ where (area0_.AREA=? )

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?

[dejanp]

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

Note the lazy="true".

[travism]

Note the lazy="true".

Yes, I've tried it with and without the lazy line. It doesn't change anything I've described.

<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...

[dejanp]

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

[travism]

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

I don't understand

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

	<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

<?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]

[dejanp]

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.