-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 
  Previous topic | Next topic 
Author Message
EdwardZ
 Post subject: Problem of using multiplication sign (*) in hql expression
PostPosted: Mon Nov 08, 2004 7:05 am 
Newbie

Joined: Mon Nov 08, 2004 6:38 am
Posts: 2
hi,

When i execute the following hql statement:
Code:
    hql = "SELECT " +
        "a.employee.fname," +
        "AVG([b]a.scoreItemA * a.coefficient[/b])," +
        "SUM(a.sumItemB)," +
        "SUM(a.sumItemC) " +
        "FROM AssessRecord a " +
        "GROUP BY a.employee.fname";

hibernate throw an exception.

but when i change the '*' to '+' or '-' or '/', eg:
Code:
    hql = "SELECT " +
        "a.employee.fname," +
        "AVG([b]a.scoreItemA + a.coefficient[/b])," +
        "SUM(a.sumItemB)," +
        "SUM(a.sumItemC) " +
        "FROM AssessRecord a " +
        "GROUP BY a.employee.fname";

every thing is ok.

So the question is: is hibernate's expression does not support '*'?
(of course it's impossible.)

My hibernate version is 2.1.6,
DBMS is ms sql server 2000,
JDBC driver is jTDS 0.9,
App Server is tomcat 4.1.30

thank's for your help

Edward
Top
 Profile  
 
EdwardZ
 Post subject: Sorry for my careless, the topic corrected as below
PostPosted: Mon Nov 08, 2004 7:10 am 
Newbie

Joined: Mon Nov 08, 2004 6:38 am
Posts: 2
When i execute the following hql statement:
Code:
    hql = "SELECT " +
        "a.employee.fname," +
        "AVG(a.scoreItemA * a.coefficient)," +
        "SUM(a.sumItemB)," +
        "SUM(a.sumItemC) " +
        "FROM AssessRecord a " +
        "GROUP BY a.employee.fname";

hibernate throw an exception.

but when i change the '*' to '+' or '-' or '/', eg:
Code:
    hql = "SELECT " +
        "a.employee.fname," +
        "AVG(a.scoreItemA + a.coefficient)," +
        "SUM(a.sumItemB)," +
        "SUM(a.sumItemC) " +
        "FROM AssessRecord a " +
        "GROUP BY a.employee.fname";

every thing is ok.

So the question is: is hibernate's expression does not support '*'?
(of course it's impossible)

My hibernate version is 2.1.6,
DBMS is ms sql server 2000,
JDBC driver is jTDS 0.9,
App Server is tomcat 4.1.30
Top
 Profile  
 
haumic
 Post subject:
PostPosted: Mon Nov 15, 2004 12:43 pm 
Newbie

Joined: Mon Nov 15, 2004 12:27 pm
Posts: 4
Location: Aschaffenburg, Germany
hi,

i have similar problems. My hibernate query is:

select avg(o.totalPrice * c.exchangeRate)
from LightOrder o, Currency c
where c.isoCurrency = o.currencyIso AND
o.createdTs >= '1096934400000'
AND o.createdTs < '1097020800000'
AND o.testStatus = 0
AND o.orderStatus = 1
AND ( o.mandantId = 19 OR o.mandantId = 35 )
GROUP BY c.exchangeRate


It will be translated to following SQL Query:

select avg(lightorder0_.total_price*currency1_.exchange_rate)
as x0_0_ from orders lightorder0_, currency currency1_
where (currency1_.iso_currency=lightorder0_.currency_iso )
AND(lightorder0_.created_TS>='1096934400000' )
AND(lightorder0_.created_TS<'1097020800000' )
AND(lightorder0_.test_status=0 )
AND(lightorder0_.order_status=1 )
AND((lightorder0_.mandant_id=19 )OR(lightorder0_.mandant_id=35 ))
group by currency1_.exchange_rate

This SQL _DOES_ perform on my Database as expected.
But Hibernate has problems to examine the ResultSet.

Code:
2004-11-15 16:35:53,415 WARN  [main] (JDBCExceptionReporter.java:38) - SQL Error: 0, SQLState: S0022
2004-11-15 16:35:53,416 ERROR [main] (JDBCExceptionReporter.java:46) - Column 'x1_0_' not found.
2004-11-15 16:35:53,418 WARN  [main] (JDBCExceptionReporter.java:38) - SQL Error: 0, SQLState: S0022
2004-11-15 16:35:53,419 ERROR [main] (JDBCExceptionReporter.java:46) - Column 'x1_0_' not found.
2004-11-15 16:35:53,448 ERROR [main] (JDBCException.java:38) - Could not execute query
java.sql.SQLException: Column 'x1_0_' not found.
   at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:2278)
   at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:1289)
   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.hql.QueryTranslator.getResultColumnOrRow(QueryTranslator.java:985)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:222)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
   at net.sf.hibernate.loader.Loader.doList(Loader.java:950)
   at net.sf.hibernate.loader.Loader.list(Loader.java:941)
   at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:834)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1512)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1491)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1483)
   at com.maxviva.siteadmin.hibernate.base._BaseRootDAO.find(_BaseRootDAO.java:224)
   at com.maxviva.siteadmin.hibernate.base._BaseRootDAO.find(_BaseRootDAO.java:212)
   at com.maxviva.siteadmin.hibernate.dao.LightOrderDAO.getAvgBooking(LightOrderDAO.java:128)
   at com.maxviva.siteadmin.stats.OrderStatsManager.getOrderStatistic(OrderStatsManager.java:505)
   at com.maxviva.siteadmin.stats.OrderStatsManager.getDailyOrderStatistic(OrderStatsManager.java:492)
   at com.maxviva.siteadmin.test.OrderStatsManagerTest.testGetOrders(OrderStatsManagerTest.java:74)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:324)
   at junit.framework.TestCase.runTest(TestCase.java:154)
   at junit.framework.TestCase.runBare(TestCase.java:127)
   at junit.framework.TestResult$1.protect(TestResult.java:106)
   at junit.framework.TestResult.runProtected(TestResult.java:124)
   at junit.framework.TestResult.run(TestResult.java:109)
   at junit.framework.TestCase.run(TestCase.java:118)
   at junit.framework.TestSuite.runTest(TestSuite.java:208)
   at junit.framework.TestSuite.run(TestSuite.java:203)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)


Any Idea?

Thanks in advace for Help.
Michael
Top
 Profile  
 
christian
 Post subject:
PostPosted: Mon Nov 15, 2004 12:46 pm 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
It's very unlikely we will make much enhancements to the old (2.1) HQL query parser. A quick workaround for this is a "derived property", ie. a <property> element with a formula attribute mapping to a read-only Java property. You can then use this property easily in your queries:

<property name="totalInclTax" formular="TOTAL * TAX_RATE + TOTAL"/>

This translates into a SQL select clause (path|subselect) expression.

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.

Top
 Profile  
 
haumic
 Post subject:
PostPosted: Mon Nov 15, 2004 1:34 pm 
Newbie

Joined: Mon Nov 15, 2004 12:27 pm
Posts: 4
Location: Aschaffenburg, Germany
Christian, thanks a lot for your quick reply!

Is there a way with that property you described to add something to the where clause ?
Code:
<property
name="averageTurning"
type="string"
formula="avg( orders.total_price * currency.exchange_rate )
                      where  currency.iso_currency=order.currency_iso ">

does result something like this:
Code:
select lightorder0_.id as id,...., avg( orders.total_price * currency.exchange_rate )  where  currency.iso_currency=order.currency_iso  ..... where (lightorder0_.created_TS>='1096934400000' ) .....

ooops!

thx,
michael
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.