Hi,

I have created view and created the mapping document as shown below. When i tried to retrieve data from the view its giving the strange error.

But the same code worked fine when i changed i dropped the view and created the table with the same name, columns, data types and the same data.

What might be the problem?

Hibernate version: 3.2

Mapping documents:
<class name="com.cmcltd.afpis.dbsystem.dbobjects.GeographyMaster" table="vw_geography_master"
catalog="f7tms">
<id name="geographyId" type="java.lang.Integer">
<column name="GEOGRAPHY_ID" />
</id>
<property name="geographyName" type="java.lang.String">
<column name="GEOGRAPHY_NAME" length="25" />
</property>
</class>

Code between sessionFactory.openSession() and session.close():
log.debug("finding all GeographyMaster instances");
try {
String queryString = "from GeographyMaster";
return getHibernateTemplate().find(queryString);
} catch (RuntimeException re) {
log.error("find all failed", re);
throw re;
}
Full stack trace of any exception that occurs:
Exception in thread "main" org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:615)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:917)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:909)
at com.cmcltd.afpis.dbsystem.dao.GeographyMasterDAO.findAll(GeographyMasterDAO.java:75)
at com.cmcltd.afpis.dbsystem.dao.GeographyMasterDAO.main(GeographyMasterDAO.java:89)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:926)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
... 5 more
Caused by: java.sql.SQLException: Column 'GEOGRAPHY1_14_' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:987)
at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:2749)
at org.apache.commons.dbcp.DelegatingResultSet.getInt(DelegatingResultSet.java:237)
at org.hibernate.type.IntegerType.get(IntegerType.java:28)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1097)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:565)
at org.hibernate.loader.Loader.doQuery(Loader.java:701)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 14 more
Name and version of the database you are using: MySQL 5.1

The generated SQL (show_sql=true):
Hibernate: select geographym0_.GEOGRAPHY_ID as GEOGRAPHY1_14_, geographym0_.GEOGRAPHY_NAME as GEOGRAPHY2_14_ from f7tms.vw_geography_master geographym0_

Please help me.

Thanks in advance,
Sridhar Reddy .R

Are you sure that the actual table column which the view column GEOGRAPHY_ID references is int type only? Can you post the table and view DDL?

Yes it is the same column.
There is no type mistake. I Checked multiple times.
More over this code is generated by MyEclipse tool.

The same problem is being repeated for every view

Can you execute the SQL query generated by Hibernate directly in the database? With view and table?

Its perfectly executing

Try using long instead of integer for the id column type