Hibernate 2.1 with Spring Framework on jboss 3.2.3 with mysql 4.0.15

Im trying to generate a Native SQL something like
getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(net.sf.hibernate.Session session) throws net.sf.hibernate.HibernateException, SQLException
{
String sql = "select {project}.PROJECT_ID from PROJECT {project}, " +
" PROJECT_PROJECTGROUP {grp}" +
" where {project}.PROJECT_ID = {grp}.PROJECT_GROUP_ID" +
" and {project}.PROJECT_ID = " + id;
String[] aliases = {"project","grp"};
Class[] clazzes = new Class[] {Project.class, Project.class};
Query sqlQuery = session.createSQLQuery(sql, aliases, clazzes);
return sqlQuery.list();
}

}

This returns a SQL Exception:
org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [null] in task 'HibernateAccessor'; nested exception is java.sql.SQLException: Column 'PROJECT_ID0_' not found.
java.sql.SQLException: Column 'PROJECT_ID0_' not found.
at com.mysql.jdbc.ResultSet.findColumn(Unknown Source)
at com.mysql.jdbc.ResultSet.getInt(Unknown Source)
at net.sf.hibernate.type.IntegerType.get(IntegerType.java:18)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:347)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:198)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:128)
at net.sf.hibernate.loader.Loader.list(Loader.java:918)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:71)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3628)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:50)
at net.collab.dao.ProjectServicesHibernate$1.doInHibernate(ProjectServicesHibernate.java:104)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:150)
at net.collab.dao.Pr

There is no suffix generated in this case as im explictly called {project}.PROJECT_ID in my native SQL but still its looking for a PROJECT_ID0_ alias name which doesnt exist. Is this a bug? Any workarounds?

Thanks
Raja

The problem is that you have two references to the same alias/class.

This bug is fixed in v21branch of the cvs.

2.1.4 solve that

This is my code:


I am using postgresql 7.3.6 and hibernate 2.1.4 (and the nightly tar on June 03) and it appears I am getting this same problem. I have successfully changed my query to HQL using session.find() to get it to work, but if I use native SQL it gives me the following error. Same problem if I used named queries (I'm assuming it uses the same calls). Please help.

that is two very different queries!

The second is is a simple .* query which is junit-tested to death, so we at least need the mapping file and the code that shows the bug...

Should I start a new thread and post the mapping file there ? I'm new to this forum so not too sure if I was going to get into trouble creating a new thread for a similar looking problem. I'm new to hibernate so I didn't know it was a totally different problem. Thanks for the pointers though

up to you ;)

just post here as the subject matches your problem - the cause of the error is just not the same.

From Section 13.2 of the Hibernate Documentation,
<quote>
Note: if you list each property explicitly, you must include all properties of the class and its subclasses
</quote>
So if my PROJECT Table is like
PROJECT
===========
PROJECT_ID
PROJECT_NAME
DESCRIPTION

and a native SQL Like

select proj.PROJECT_ID as {proj.projectID}," +
" proj.PROJECT_NAME as {proj.projectName} " +
" from PROJECT {proj} "

it throws me an error saying DESCRIPTION_3_0 is not found as i havent *explicitly* included that in my native SQL. Any ideas of implementing such a behaviour? I know this is overkill for native SQL and is easily represented by the OR mapping but we are trying it out for a complex SQL Query and wanted to see if we can extract specific fields of a table when doing native SQL.

Any pointers greatly appreciated for implementing such a feature

Thanks
Raja