Assigning values to a returned object from query

[Chris Boldon]

I'm fairly new to Hibernate, my exposure has been very abstracted ( I've used hibernate through SpringTemplate calls). I now have a need to make a HQL query, which some of the board members helped me get right. The query works find, however the Collection it returns is an Object[]. This presents a problem when trying to iterate the values of an object in Struts. How do I assign these values, I need a collection of an existing value object?

DAO method:

public Collection getPortfolios() {
Session session = getHibernateTemplate().getSessionFactory()
.openSession();
Transaction tx = session.beginTransaction();
Collection list = session
.createQuery(
"select projectOrganization, count(projectOrganization) "
+ "from ProjectImpl group by projectOrganization").list();
tx.commit();
session.close();
return list;

Note: I am using Count, which is not a value in my database, or hibernate mapping

Mapping:

<hibernate-mapping>
<class name="com.x.pmo.vo.ProjectImpl" table="PROJECT" schema="PMO">
<id name="projectId" type="string">
<column name="project_id" length="15" />
<generator class="assigned" />
</id>
<property name="projectName" type="string">
<column name="project_name" length="45" />
</property>
<property name="projectOrganization" type="string">
<column name="project_organization" length="45" />
</property>
</class>
</hibernate-mapping>

[Halcon Guatemala]

Hi Chris, since my little experience I think You can use the

select new myObject(Field1, Field2, ...)
from ...
where ...

to get an easy solution

I hope it helps

[Chris Boldon]

I guess I'm a little more hibernate and sql newb than i thought and might need a little more hand holding:-).

I see how that would work, but when I try and modify my query to fit that model i get query errors.

[Halcon Guatemala]

Ok Chris, don't worry I'm newbie too, but could You post the error message?

Thanks

[Chris Boldon]

The error I'm getting is:

06 Nov 2006 16:19:36:187, main, WARN, org.hibernate.hql.ast.ErrorCounter.reportWarning:46, *** WARNING: Keyword 'count' is being intepreted as an identifier due to: expecting IDENT, found 'count'
06 Nov 2006 16:19:36:202, main, ERROR, org.hibernate.hql.ast.ErrorCounter.reportError:35, *** ERROR: line 1:66: unexpected token: new
06 Nov 2006 16:19:36:218, main, ERROR, org.hibernate.hql.ast.ErrorCounter.reportError:35, *** ERROR: line 1:104: expecting OPEN, found ')'
06 Nov 2006 16:19:36:234, main, WARN, org.hibernate.hql.ast.HqlParser.processEqualityExpression:193, processEqualityExpression() : No expression to process!
06 Nov 2006 16:19:36:265, main, ERROR, org.hibernate.hql.ast.ErrorCounter.reportError:35, *** ERROR: line 1:168: unexpected token: as

And the HQl query I'm trying is:

Collection list = session
.createQuery(
"select projectOrganization, count(projectOrganization) as count, new PortfolioCountImpl(portfolio,count) "
+ "from ProjectImpl group by projectOrganization as organization").list();

I'm sure I'm doing something wrong with the query. Thanks for the help!

[Halcon Guatemala]

Hi again Chris, can You test changing second "count":

select projectOrganization, count(projectOrganization) as [B]count[/B]

for another alias? It is a "reserved word".

I hope it helps

[Chris Boldon]

I replaced the last 2 instances of count with inc

06 Nov 2006 16:30:49:249, main, WARN, net.sf.ehcache.config.Configurator.configure:126, No configuration found. Configuring ehcache from ehcache-failsafe.xml found in the classpath: jar:file:/C:/dev/pmo/WebRoot/WEB-INF/lib/ehcache-1.1.jar!/ehcache-failsafe.xml
06 Nov 2006 16:30:50:233, main, ERROR, org.hibernate.hql.ast.ErrorCounter.reportError:35, *** ERROR: line 1:64: unexpected token: new
Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxError: unexpected token: new near line 1, column 64 [select projectOrganization, count(projectOrganization) as inc, new PortfolioCountImpl(portfolio,inc) from com.x.pmo.vo.ProjectImpl group by projectOrganization as organization]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:834)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at com.x.pmo.dao.ProjectDAOImpl.getPortfolios(ProjectDAOImpl.java:61)
at com.x.pmo.test.Test.test(Test.java:44)
at com.x.pmo.test.Test.main(Test.java:31)
Caused by: line 1:64: unexpected token: new
at org.hibernate.hql.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2032)
at org.hibernate.hql.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:1937)
at org.hibernate.hql.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:1901)
at org.hibernate.hql.antlr.HqlBaseParser.expression(HqlBaseParser.java:1663)
at org.hibernate.hql.antlr.HqlBaseParser.aliasedExpression(HqlBaseParser.java:1853)
at org.hibernate.hql.antlr.HqlBaseParser.selectedPropertiesList(HqlBaseParser.java:1096)
at org.hibernate.hql.antlr.HqlBaseParser.selectClause(HqlBaseParser.java:997)
at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:735)
at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:611)
at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:263)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:150)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:209)
... 9 more

[Halcon Guatemala]

Hi Chris, is your PortfolioCountImpl class in root dir? else you must write an entire path for this class.

I hope this helps

[Chris Boldon]

Thanks for your help, that didn't seem to fix the problem...although I'm sure it would have had it been working:-). I'm going to think on this for a day and see if I can figure out the cause since I can feel the beginning of a headache:-). If I can't think of a solution I will post back here sometime tomorrow morning. Or if I do solve it I'll post back so future users don't have the same problem.