The problem I am having is that although Spring is constructing a net.sf.hibernate.dialect.PostgreSQLDialect object it does not appear to be making proper use of it. net.sf.hibernate.dialect.Dialect.openQuote() is not called, and the resulting SELECT query does not quote the table name. How is it possible that a query can be formulated without using the hibernate.dialect?

Alternatively, does anyone else have Spring with Hibernate and PostgreSQL working? If so, could they post a working configuration file?

Thanks for your time.

Hibernate version: 2.1.8

Spring version: 1.1.5

Spring application context document:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName"><value>org.postgresql.Driver</value></property>
<property name="url"><value>jdbc:postgresql://localhost/TestDb</value></property>
<property name="username"><value>postgres</value></property>
<property name="password"><value>xxx</value></property>
</bean>

<bean id="hibernateService" class="es.novasoft.prototype.model.HibernateService">
<property name="sessionFactory"><ref local="sessionFactory"/></property>
</bean>

<!-- Hibernate SessionFactory -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">net.sf.hibernate.dialect.PostgreSQLDialect</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
<property name="dataSource"><ref local="dataSource"/></property>
<property name="mappingResources">
<list>
<value>es/novasoft/model/hibernate/MentMenu.hbm.xml</value>
<value>es/novasoft/model/hibernate/MenMenu.hbm.xml</value>
<value>es/novasoft/model/hibernate/IdiIdioma.hbm.xml</value>
</list>
</property>
</bean>
</beans>

Hibernate Mapping documents: (Document: MenMenu.hbm.xml)
<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping
>
<class
name="es.novasoft.model.hibernate.MenMenu"
table="MEN_MENU"
dynamic-update="false"
dynamic-insert="false"
select-before-update="false"
>

<id
name="menPk"
column="MEN_PK"
type="java.lang.Integer"
>
<generator class="assigned">
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-MenMenu.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>

<property
name="tmnPk"
type="java.lang.Integer"
update="true"
insert="true"
access="property"
column="TMN_PK"
length="4"
/>

<property
name="menNivel"
type="java.lang.Integer"
update="true"
insert="true"
access="property"
column="MEN_NIVEL"
length="4"
/>

<property
name="menOrden"
type="java.lang.Integer"
update="true"
insert="true"
access="property"
column="MEN_ORDEN"
length="4"
/>

<property
name="menHojaSn"
type="java.lang.Short"
update="true"
insert="true"
access="property"
column="MEN_HOJA_SN"
length="2"
/>

<property
name="menDestacado"
type="java.lang.Short"
update="true"
insert="true"
access="property"
column="MEN_DESTACADO"
length="2"
/>

<many-to-one
name="menMenu"
class="es.novasoft.model.hibernate.MenMenu"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
>
<column
name="MEN_MEN_PK"
/>
</many-to-one>

<set
name="menMenus"
lazy="true"
inverse="true"
cascade="none"
sort="unsorted"
>

<key
column="MEN_MEN_PK"
>
</key>

<one-to-many
class="es.novasoft.model.hibernate.MenMenu"
/>

</set>

<set
name="mentMenus"
lazy="true"
inverse="false"
cascade="none"
sort="unsorted"
>

<key
column="MEN_PK"
>
</key>

<one-to-many
class="es.novasoft.model.hibernate.MentMenu"
/>

</set>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-MenMenu.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>

Code used to query


Full stack trace of any exception that occurs:
org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in task 'Hibernate operation'; nested exception is java.sql.SQLException: ERROR: relation "men_menu" does not exist
java.sql.SQLException: ERROR: relation "men_menu" does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:388)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:329)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:239)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:273)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
at net.sf.hibernate.loader.Loader.list(Loader.java:1054)
at net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:118)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3660)
at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:238)
at org.springframework.orm.hibernate.HibernateTemplate$5.doInHibernate(HibernateTemplate.java:415)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:312)
at org.springframework.orm.hibernate.HibernateTemplate.loadAll(HibernateTemplate.java:411)
at es.novasoft.prototype.model.HibernateService.getRootMenuItem(HibernateService.java:24)
at es.novasoft.prototype.controller.HibernateServiceTest.testService(HibernateServiceTest.java:41)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:436)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:311)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)


Name and version of the database you are using:
PostgreSQL 8.0.2

The generated SQL (show_sql=true):
select this.MEN_PK as MEN_PK1_, this.TMN_PK as TMN_PK1_, this.MEN_NIVEL as MEN_NIVEL1_, this.MEN_ORDEN as MEN_ORDEN1_, this.MEN_HOJA_SN as MEN_HOJA5_1_, this.MEN_DESTACADO as MEN_DEST6_1_, this.MEN_MEN_PK as MEN_MEN_PK1_, menmenu1_.MEN_PK as MEN_PK0_, menmenu1_.TMN_PK as TMN_PK0_, menmenu1_.MEN_NIVEL as MEN_NIVEL0_, menmenu1_.MEN_ORDEN as MEN_ORDEN0_, menmenu1_.MEN_HOJA_SN as MEN_HOJA5_0_, menmenu1_.MEN_DESTACADO as MEN_DEST6_0_, menmenu1_.MEN_MEN_PK as MEN_MEN_PK0_ from MEN_MENU this left outer join MEN_MENU menmenu1_ on this.MEN_MEN_PK=menmenu1_.MEN_PK where 1=1

Ok, without using Spring, I get the same problem. This must be a silly configuration blunder..

Desired query:
select menmenu0_."MEN_PK" as MEN_PK, menmenu0_."TMN_PK" as TMN_PK, menmenu0_."MEN_NIVEL" as MEN_NIVEL, menmenu0_."MEN_ORDEN" as MEN_ORDEN, menmenu0_."MEN_HOJA_SN" as MEN_HOJA5_, menmenu0_."MEN_DESTACADO" as MEN_DEST6_, menmenu0_."MEN_MEN_PK" as MEN_MEN_PK from "MEN_MENU" menmenu0_

hibernate.cfg.xml
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="show_sql">true</property>
<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.connection.url">jdbc:postgresql://localhost/TestDb</property>
<property name="hibernate.connection.username">postgres</property>
<property name="hibernate.connection.password">xxx</property>
<property name="hibernate.connection.dialect">net.sf.hibernate.dialect.PostgreSQLDialect</property>
<property name="hibernate.connection.pool_size">4</property>
<mapping resource="es/novasoft/model/hibernate/MentMenu.hbm.xml"/>
<mapping resource="es/novasoft/model/hibernate/MenMenu.hbm.xml"/>
<mapping resource="es/novasoft/model/hibernate/IdiIdioma.hbm.xml"/>
</session-factory>
</hibernate-configuration>

Query code:

The generated SQL (show_sql=true): (not the same as the one generated via the Spring framework..)
select menmenu0_.MEN_PK as MEN_PK, menmenu0_.TMN_PK as TMN_PK, menmenu0_.MEN_NIVEL as MEN_NIVEL, menmenu0_.MEN_ORDEN as MEN_ORDEN, menmenu0_.MEN_HOJA_SN as MEN_HOJA5_, menmenu0_.MEN_DESTACADO as MEN_DEST6_, menmenu0_.MEN_MEN_PK as MEN_MEN_PK from MEN_MENU menmenu0_[/b]

I have not foudn the correct way of addressing this problem. Instead I have resorted to modifying each and every hbm.xml file and quote the table and column names. This just can't be right. Anyway, here's a handy ant script to automate the process:

build.xml
<?xml version="1.0"?>

<project default="fix">
<property name="gen.src.dir" value="../../gen-src"/>

<target name="fix">
<replaceregexp byline="true">
<regexp pattern="(.*table=&quot;)([^&amp;]*)(&quot;.*)"/>
<substitution expression="\1&amp;quot;\2&amp;quot;\3"/>
<fileset dir="${gen.src.dir}">
<include name="**/*.hbm.xml"/>
</fileset>
</replaceregexp>

<replaceregexp byline="true">
<regexp pattern="(.*column=&quot;)([^&amp;]*)(&quot;.*)"/>
<substitution expression="\1&amp;quot;\2&amp;quot;\3"/>
<fileset dir="${gen.src.dir}">
<include name="**/*.hbm.xml"/>
</fileset>
</replaceregexp>
</target>
</project>