Hi there !

I am in the process of converting a simple java tutorial from plain JDBC to Hibernate. This is some introductory material for some students. Everything worked fine until now, where I have the problem with a simple SQL statement that I just cannot convert to HQL.



The problem lies in the mathematical operation in the select statement, where amount ('anzahl') and price ('price') are multiplied. I tried a lot of things, but it seems like mathematical expressions are only allowed in the where clause (see Expressions). I could not find anything about that. I even tried with () or [] or {}. Or is there perhaps a mapping to SQL functions (like sum or count)?

So I decided to use named queries and put the SQL statement into the mapping files. I called the query directly by the name referenced in the mapping file and the query is read correctly. See the output:

Query1


The query was executed without errors, but a call to Query.list() or Query.uniqueResult() only returned an empty Object[] (Object[0]). Putting the same string into my SQL client returned a list of results.

I tried the same thing with simpler queries, but I always get an empty Object[]

Query2


Query3


Could someone help me with that? Is there a possiblitiy to put arithmetic operations into the SELECT part of the query?

Any help is welcome :)

Kariem


Versions: Hibernate 2.1.8, PostgreSQL 7.2.1

In case I need special libraries I put all the stuff I found into my library path. Something missing perhaps? I did not get any error message though, but just to be sure, here is my list.

c3p0-0.8.4.5.jar
cglib-full-2.0.2.jar
commons-collections-2.1.1.jar
commons-dbcp-1.2.1.jar
commons-lang-2.0.jar
commons-logging-1.0.4.jar
commons-pool-1.2.jar
concurrent-1.3.3.jar
connector.jar
dom4j-1.4.jar
ehcache-0.9.jar
hibernate2.jar
hsqldb-1.7.1.jar
jaas.jar
jcs-1.0-dev.jar
jdbc2_0-stdext.jar
jdom.jar
jgroups-2.2.7.jar
jta.jar
log4j-1.2.8.jar
odmg-3.0.jar
oscache-2.0.jar
proxool-0.8.3.jar
swarmcache-1.0rc2.jar
xerces.jar
xml-apis.jar

Seems like that problem is too simple for you :)

I just found the error: I had <sql-query> instead of <query> in my mappings. And because there were no returns defined, the statement was executed without returning anything except Object[0].

Still, the problem with a multiplication in the select statement remains. With sql-query I can only return fully mapped objects. I found some more possiblities with the new API, but did not want to try that out, as they are not yet marked 'for production'.

For the current implementation, I fetch a list of all objects and do the processing myself (instead of the server doing that). It is a bit awkward, as I try to fill a table model with the results, but at least it works.

Are there any tips that could improve the situation or could make a simple mathematical operation possible within the select statement?

Thanks,
Kariem