Spring + Hibernate + named SQL queries

[md]

Hi,

I am using Spring 1.2.3 and Hibernate 3.0.5

I am trying to call a named sql query, following the example
in the official hibernate docs:
http://www.hibernate.org/hib_docs/v3...l-namedqueries


However I can't even get the simplest example to work.
Here is the part in the mapping file:

<sql-query name="mySqlQuery">
<return class="foo.Survey"
alias="survey"/>
SELECT STYLE as {survey.style} FROM t_survey
</sql-query>


And this is how I call this query:
getHibernateTemplate().findByNamedQuery("mySqlQuer y");


This is the error message I am getting:


[junit] Testcase: testGetSurveysByNamedSQL(de.jato.siemens.survey.pe rsistence.SurveyDAOTest): Caused an ERROR
[junit] Hibernate operation: could not execute query; bad SQL grammar [SELECT STYLE as style33_0_ FROM t_survey]; nested exception is java.sql.SQLException: Ungültiger Spaltenname
[junit] org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar [SELECT STYLE as style33_0_ FROM t_survey]; nested exception is java.sql.SQLException: Ungültiger Spaltenname
[junit] java.sql.SQLException: Ungültiger Spaltenname
[junit] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:169)
[junit] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:211)
[junit] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:274)
[junit] at oracle.jdbc.driver.OracleStatement.get_column_inde x(OracleStatement.java:4541)
[junit] at oracle.jdbc.driver.OracleResultSetImpl.findColumn( OracleResultSetImpl.java:666)
[junit] at oracle.jdbc.driver.OracleResultSet.getLong(OracleR esultSet.java:1432)
[junit] at org.apache.commons.dbcp.DelegatingResultSet.getLon g(DelegatingResultSet.java:239)
[junit] at org.hibernate.type.LongType.get(LongType.java:26)
[junit] at org.hibernate.type.NullableType.nullSafeGet(Nullab leType.java:77)
[junit] at org.hibernate.type.NullableType.nullSafeGet(Nullab leType.java:68)
[junit] at org.hibernate.loader.Loader.getKeyFromResultSet(Lo ader.java:759)
[junit] at org.hibernate.loader.Loader.getRowFromResultSet(Lo ader.java:292)
[junit] at org.hibernate.loader.Loader.doQuery(Loader.java:41 2)
[junit] at org.hibernate.loader.Loader.doQueryAndInitializeNo nLazyCollections(Loader.java:218)
[junit] at org.hibernate.loader.Loader.doList(Loader.java:159 3)
[junit] at org.hibernate.loader.Loader.list(Loader.java:1577)
[junit] at org.hibernate.loader.custom.CustomLoader.list(Cust omLoader.java:112)
[junit] at org.hibernate.impl.SessionImpl.listCustomQuery(Ses sionImpl.java:1414)
[junit] at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl. java:153)
[junit] at org.springframework.orm.hibernate3.HibernateTempla te$32.doInHibernate(HibernateTemplate.java:833)
[junit] at org.springframework.orm.hibernate3.HibernateTempla te.execute(HibernateTemplate.java:315)
[junit] at org.springframework.orm.hibernate3.HibernateTempla te.findByNamedQuery(HibernateTemplate.java:824)
[junit] at org.springframework.orm.hibernate3.HibernateTempla te.findByNamedQuery(HibernateTemplate.java:816)



So the column name I specified is not valid. But I've checked it 1000 times?!?

When I get rid of the alias attribute and select all columns, I receive a correct result.

<sql-query name="mySqlQuery">
<return class="foo.Survey"/>
SELECT * FROM t_survey
</sql-query>

But as soon as I search for a single column, I receive the above error message again.


Does somebody have experience with named SQL queries (Hibernate + Spring).
I don't think that this is an issue when using Hibernate by itself (although I haven't tried it).
Can someone pls explain to me what I am doing wrong here?

Cheers,
MD

[Rod Johnson]

Spring doesn't change any of the SQL generation Hibernate does, so I'd be very surprised if it's changed anything here. Do your mappings work perfectly otherwise? Can you execute SELECT STYLE as style33_0_ FROM t_survey through SQL*Plus? Are you using the correct Hibernate dialect for your version of Oracle?