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/re ... medqueries


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("mySqlQuery");


This is the error message I am getting:


[junit] Testcase: testGetSurveysByNamedSQL(de.jato.siemens.survey.persistence.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(DBError.java:169)
[junit] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:211)
[junit] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:274)
[junit] at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:4541)
[junit] at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:666)
[junit] at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:1432)
[junit] at org.apache.commons.dbcp.DelegatingResultSet.getLong(DelegatingResultSet.java:239)
[junit] at org.hibernate.type.LongType.get(LongType.java:26)
[junit] at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
[junit] at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
[junit] at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:759)
[junit] at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:292)
[junit] at org.hibernate.loader.Loader.doQuery(Loader.java:412)
[junit] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
[junit] at org.hibernate.loader.Loader.doList(Loader.java:1593)
[junit] at org.hibernate.loader.Loader.list(Loader.java:1577)
[junit] at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
[junit] at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
[junit] at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
[junit] at org.springframework.orm.hibernate3.HibernateTemplate$32.doInHibernate(HibernateTemplate.java:833)
[junit] at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:315)
[junit] at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQuery(HibernateTemplate.java:824)
[junit] at org.springframework.orm.hibernate3.HibernateTemplate.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).
Can someone pls explain to me what I am doing wrong here?

Cheers,
MD

Try something like this (fill in type attr for style):



You can't use <return class=.../> because you aren't selecting the whole object, just a property. <return-scalar> should be used instead.