How to make a native sql query that return pojos?

tchize · **Joined:** Tue May 10, 2005 9:00 am **Posts:** 125

Hibernate version: 3.0.5

Mapping documents:

Code:

  <class name="ResourcePersistence" table="shark_resource">
      <id column="objectId" type="long">
         <generator class="sequence">
            <param name="sequence">shark_sequence</param>
         </generator>
      </id>
      <property name="deleted" />
      <property name="name"/>
      <property name="username" not-null="true" unique="true"/>
  </class>

Code between sessionFactory.openSession() and session.close():

query="select * from shark_resource where deleted = 0 ;"
SQLQuery q =session.createSQLQuery(query);
q.addEntity("a",ResourcePersistence.class);

Full stack trace of any exception that occurs:

Code:

 Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
   at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:1596)
   at org.hibernate.loader.Loader.list(Loader.java:1577)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
   at be.rmi.shark.hibernate.persistence.PersistenceManager.nativeList(PersistenceManager.java:189)
   ... 41 more
Caused by: java.sql.SQLException: Nom de colonne non valide
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
   at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3295)
   at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1914)
   at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:1575)
   at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getLong(DelegatingResultSet.java:239)
   at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getLong(DelegatingResultSet.java:239)
   at org.hibernate.type.LongType.get(LongType.java:26)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
   at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:759)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:292)
   at org.hibernate.loader.Loader.doQuery(Loader.java:412)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
   at org.hibernate.loader.Loader.doList(Loader.java:1593)

Name and version of the database you are using:
Oracle 9
The generated SQL (show_sql=true):
select * from shark_resource where deleted = 0 ;

Hello,

trying to execcute the above mentionned native sql query, to get hibernate object (as mentionned in documentation: http://www.hibernate.org/hib_docs/refer ... rysql.html ), i get this sql error. "invalid column name". Upon further analysis, it seems that hibernate is trying to find an "objectId0_" column, which doesn't exist in the query. There is no mention of such behaviour in documentation.

Can someone give me information on how to create native sql query to return hibernate pojos? I need to use native query in this case, because the where part of query is provided by caller, and the caller is independant on hibernate (it just rely on a database storing implementation), and this caller use sql where statement in some calls.

ethnarch · **Joined:** Tue Aug 01, 2006 9:24 pm **Posts:** 120

Did you read through the article you posted?

it gives this example

Quote:

16.1.2. Entity queries

The above queries were all about returning scalar values, basically returning the "raw" values from the resultset. The following shows how to get entity objects from a native sql query via addEntity().

sess.createSQLQuery("SELECT * FROM CATS").addEntity(Cat.class);
sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").addEntity(Cat.class);
This query specified:

the SQL query string

the entity returned by the query

Assuming that Cat is mapped as a class with the columns ID, NAME and BIRTHDATE the above queries will both return a List where each element is a Cat entity.

If the entity is mapped with a many-to-one to another entity it is required to also return this when performing the native query, otherwise a database specific "column not found" error will occur. The additional columns will automatically be returned when using the * notation, but we prefer to be explicit as in the following example for a many-to-one to a Dog:

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, DOG_ID FROM CATS").addEntity(Cat.class);
This will allow cat.getDog() to function properly.

latha1119 · **Posted:** Thu Feb 26, 2009 3:09 pm

One of the requirements like this we have done this:-

StringBuffer query = new StringBuffer("from Activity activity where activity.type.type in (" +dynamic data+")";

java.lang.Object[] inputValues = new java.lang.Object[1];
inputValues[0] = new String();//comma separated dynamic data.

NullableType[] types = new NullableType[];
types[0] = Hibernate.Integer; //appropriate Hibernate type

List<Activity> result = session.find(query.toString(), inputValues, types);

Hope this helps,
latha.

tchize · **Joined:** Tue May 10, 2005 9:00 am **Posts:** 125

ethnarch wrote:

Did you read through the article you posted?

it gives this example

yes, and if you read my question, i did as in example (except there is no addEntity(Class) in the api anymore) and i get sql errors.

@latha1119
thank you. Unfortunately, session.find is an hibernate classis deprecated api, in favor of session.createQuery.find, which does an hql query. I need an sqlquery, because, for some pojo, the caller will give criterias using table columns, which are not the same name as corresponding mapped entity properties

latha1119 · **Posted:** Thu Feb 26, 2009 6:35 pm

Sorry, as you can see I'm not an expert. But trying to learn more..

I have a question for you is it a POJO or an Entity?
If its a POJO you might want to try this:-
List<MyPOJO> testSPs=session.createSQLQuery("select..").setResultTransformer(Transformers.aliasToBean(MyPOJO.class)).list();

Hope this helps,
latha.

ethnarch · **Joined:** Tue Aug 01, 2006 9:24 pm **Posts:** 120

Can you post your entire stack trace. As a standard practice you should always post the exact message.

Also, this isn't much help but this seems to be a similar problem.
http://forum.springframework.org/archiv ... 40486.html

ethnarch · **Joined:** Tue Aug 01, 2006 9:24 pm **Posts:** 120

actually maybe this is the problem from this link I see they are aliasing the class

http://www.java2s.com/Code/Java/Hibernate/SelectSQL.htm

Quote:

String sql = "select {supplier.*} from Supplier supplier";

SQLQuery query = session.createSQLQuery(sql);
query.addEntity("supplier", Supplier.class);
List results = query.list();

So maybe try your sql like this
query="select {a.*} from shark_resource where deleted = 0 ;"
SQLQuery q =session.createSQLQuery(query);
q.addEntity("a",ResourcePersistence.class);

tchize · **Joined:** Tue May 10, 2005 9:00 am **Posts:** 125

thanks for the help.

i will try your suggestion as soon as possible if possible (code is currently under heavy refactoring). As for the stacktrace, i only included the HibernateException and it's root causes. I did not output the rest as it is mere encapsulation of the hibernate into several layers of buisness level exception that have nothing to do with root cause and would make the post kilometric.