Here is my query snippet using sqlProjection,


Projections.sqlProjection("to_char(DATE_OF_BIRTH) as DOB", new String [] {"dateOfBirth"}, new Type [] {Hibernate.STRING} ))

DATE_OF_BIRTH - column name in User table
dateOfBirth - property name in User.class

The following sqlProjection constructs a query
select ....., to_char(DATE_OF_BIRTH) as DOB from ...... inner join ...

the query when executed in sqlconsole it produces the correct output, but when iterating the scrollableresult set I get the below error. What I don't understand here is the right syntax for Projections.sqlProjection(String sql, String [] colalias, Type [] types). What is the colalias pointing to ? How do I get the column alias here ?

the alias I specify in session.createCriteria(Xxx.class, "xx).createAlias("userMap", "usermap") ....

I want to substitute the alias usermap in sqlProjection like
Projections.sqlProjection("to_char(usermap.DATE_OF_BIRTH) as DOB, new String [] {"userMap"}, new Type [] {Hibernate.String}).

Is it correct ?



20:27:50,274 [Servlet.Engine.Transports : 0] ERROR com.ebcbs.enterprise.broker.sar.HibernateUtil:199 - Exception occured :
org.hibernate.exception.SQLGrammarException: could not read next row of results
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.loadSingleRow(Loader.java:248)
at org.hibernate.impl.ScrollableResultsImpl.prepareCurrentRow(ScrollableResultsImpl.java:477)
at org.hibernate.impl.ScrollableResultsImpl.next(ScrollableResultsImpl.java:106)


Caused by: java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:5971)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1527)
at oracle.jdbc.driver.ScrollableResultSet.findColumn(ScrollableResultSet.java:1797)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1482)
at org.hibernate.type.StringType.get(StringType.java:16)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:94)
at org.hibernate.loader.criteria.CriteriaLoader.getResultColumnOrRow(CriteriaLoader.java:123)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:329)
at org.hibernate.loader.Loader.loadSingleRow(Loader.java:238)
... 44 more

I did not realise the columnalias is corresponding to the alias specified in sqlString. once I changed it to

Projections.sqlProjection("to_char(DATE_OF_BIRTH) as DOB",
new String [] {"DOB"}, new Type [] {Hibernate.STRING} )

it worked.

if suppose I want to apply groupProperty on sql projection then how can apply

with above line I want to apply groupProperty , please anyone help me