Hello!
I'll try to be short... I have a problem trying to perform a select from stocks table using aggregate function sum over the product of two columns.
Code:
select stocks.editor.name,
sum(stocks.noItems * stocks.priceOld)
from stocks in class org.vasiliada.dbo.Stock
where stocks.editor.editorId = 5
group by stocks.editor.editorId
The problem might be connected with the fact that i'm using the * character. Runing
Code:
sum(colA+colB)
goes fine. It seems that
in case product hibernate counts one extra column so when geting data from resultset an exception ocures:
Code:
Column 'x2_0_' not found
.
Do you know what the answer might be?
Thank you very much!
Hibernate version: 2.1.8
Mapping documents:In Stock.hbm.xml:
Code:
<many-to-one name="editor"
class="org.vasiliada.dbo.Editor"
column="EDITOR_ID"/>
In Editor.hbm.xml:
Code:
<set name="stocks" inverse="true" lazy="true" cascade="delete-orphan" >
<key column="EDITOR_ID" foreign-key="EDITOR_ID"/>
<one-to-many class="org.vasiliada.dbo.Stock"/>
</set>
Code between sessionFactory.openSession() and session.close():Code:
Query query = session.createQuery(
"select
stocks.editor.name,
sum(stocks.noItems * stocks.priceOld)
from stocks in class org.vasiliada.dbo.Stock
where stocks.editor.editorId = 5 group by stocks.editor.editorId");
ScrollableResults result = query.scroll();
assertNotNull(result);
result.beforeFirst();
System.out.println("result is on " + result.getRowNumber() + " position");
result.last();
System.out.println("result is on " + result.getRowNumber() + " position");
result.close();
Full stack trace of any exception that occurs:error processing current row
[list=]net.sf.hibernate.exception.GenericJDBCException: error processing current row
at net.sf.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:90)
at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:79)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at net.sf.hibernate.impl.ScrollableResultsImpl.convert(ScrollableResultsImpl.java:442)
at net.sf.hibernate.impl.ScrollableResultsImpl.prepareCurrentRow(ScrollableResultsImpl.java:426)
at net.sf.hibernate.impl.ScrollableResultsImpl.last(ScrollableResultsImpl.java:80)
at org.vasiliada.dao.TestBookDao.testHQL(TestBookDao.java:70)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
Caused by: java.sql.SQLException: Column 'x2_0_' not found.
at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:2679)
at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:1453)
at net.sf.hibernate.type.IntegerType.get(IntegerType.java:21)
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:1005)
at net.sf.hibernate.loader.Loader.getRowFromResultSet(Loader.java:243)
at net.sf.hibernate.loader.Loader.loadSingleRow(Loader.java:155)
at net.sf.hibernate.hql.QueryTranslator.loadSingleRow(QueryTranslator.java:182)
at net.sf.hibernate.impl.ScrollableResultsImpl.prepareCurrentRow(ScrollableResultsImpl.java:412)
... 17 more[/list]
Name and version of the database you are using:MySQL 4.0.18
The generated SQL (show_sql=true):select editor0_.NAME as x0_0_, sum(stocks.NO_ITEMS*stocks.PRICE_OLD) as x1_0_ from stocks stocks, editors editor0_ where stocks.EDITOR_ID=editor0_.EDITOR_ID and
((stocks.EDITOR_ID=5 )) group by stocks.EDITOR_ID
Debug level Hibernate log excerpt: Code:
[size=7][/size]
Code:
Login
FAQ
Search