HQL queries using HibernateTemplate (Spring)

Question

This is my first post so please bear with me. I'm trying to execute a HQL query from within a method using HibernateTemplate. The reason I'm doing this way is because the actual table has many more columns but I'm only interested in only two. The result of the query is used in performing updateOrSave operation later.

So the class structure looks like:

class XYZDAO { ... ... public void createReview(....) { ... ... class Temp { private final float rating; private final int count; @SuppressWarnings("unused") public Temp(float rating, int count) { this.rating = rating; this.count = count; } public float getRating() { return rating; } public int getCount() { return count; } } List<Temp> avgRatingWrapper = getHibernateTemplate().find("SELECT new Temp(AVG(RATING), COUNT(*)) FROM RATINGS WHERE ADVENTURE_ID = ?", Integer.parseInt(adventureId)); ... ... } }

When I run the code following exception occurs:

Caused by: org.hibernate.hql.ast.QuerySyntaxException: RATINGS is not mapped [SELECT new Temp(AVG(RATING), COUNT(*)) FROM RATINGS WHERE ADVENTURE_ID = ?]

I already have a full blown mapping hibernate mapping for the RATINGS table:

<hibernate-mapping> <class name="com.xyz.abc.dao.hibernate.Ratings" table="ADV_ADMN.RATINGS"> <id name="id" type="int" column="ID"> <generator class="seqhilo"> <param name="sequence">ADV_ADMN.RATINGS_ID_SEQ</param> <param name="allocationSize">1</param> </generator> </id> <version name="timestamp" type="timestamp"> <column name="TIMESTAMP" length="19" not-null="true" /> </version> <property name="adventureId" type="int"> <column name="ADVENTURE_ID" not-null="true" /> </property> <property name="reviewer" type="string"> <column name="REVIEWER" length="45" not-null="true" /> </property> <property name="rating" type="java.lang.Float"> <column name="RATING" not-null="true" /> </property> </class> </hibernate-mapping>

Now i understand that I need to perform some kind of mapping either in the hibernate.hbm.xml files or provide annotation for the Temp class to map the RATINGS table. I was wondering if there is any other way to get around the problem. I figured if you use Session.createSqlQuery(....) then you can add entities to it which can circumvent the problem. But I'm not sure is there is a way to do that in HibernateTemplate.

Any help/pointers are greatly appreciated.

Jordi · Accepted Answer · 2012-04-12 21:37:03Z

Code it's not tested, but I think you mean something like this. Create the SQLQUery, and define the scalars as Strings, and use the getHibernateTemplate().execute() function.

return getHibernateTemplate().execute(new HibernateCallback<List>() { public String doInHibernate(Session s) throws HibernateException, SQLException { SQLQuery sql=s.createSQLQuery("SELECT AVG(RATING) as r, COUNT(*) as c FROM RATINGS WHERE ADVENTURE_ID = ?"); sql.setParameter(0, adventureId); sql.addScalar("r"); sql.addScalar("c"); return sql.list(); } });

If it's HQL and getHibernateTemplate().find what you want to use, then use "select ... from com.xyz.abc.dao.hibernate.Ratings"

Although this solves the problem I was trying to avoid this messy way of doing it and wanted to keep it simple using HQL. I guess the other of doing it would be to create views and map a class to that view which again is somewhat messier.

axtavt · Accepted Answer · 2012-04-12 20:31:48Z

HibernateTemplate.find() doesn't take SQL query, in takes HQL query. As long as you can express your query in HQL in would be better to do so.

Otherwise you need to use Session.createSqlQuery() inside a callback passed to HibernateTemplate.executeFind().

See also:

Thanks for the reply. I made changes to my original post where I took out Native SQL and changed it to HQL.
Although i mentioned Native SQL in the original question but I have been using HQL ("SELECT new Temp(AVG(RATING), COUNT(*)) FROM RATINGS WHERE ADVENTURE_ID = ?"). It passes compilation by the HQL parser as well.

Collectives™ on Stack Overflow

HQL queries using HibernateTemplate (Spring)

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related