Hi,
I am trying to write a Java Persistent Query. I would like to compute average price of some records based on a comparison between a Timestamp value and Time value. So I tried to add CURRENT_DATE to time value but I did not succeed:
Executing a query
Code:
SELECT avg(t.price*t.volume)/sum(t.volume)
FROM Transaction t
WHERE t.time>=CURRENT_DATE+?1
AND t.time<=CURRENT_DATE+?2
with java.sql.Time objects as parameters gives an exception:
Code:
org.hibernate.type.DoubleType - could not bind value '14:45:00' to parameter: 1; java.sql.Time
java.lang.ClassCastException: java.sql.Time
at org.hibernate.type.DoubleType.set(DoubleType.java:60)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:154)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:136)
at org.hibernate.param.NamedParameterSpecification.bind(NamedParameterSpecification.java:61)
at org.hibernate.loader.hql.QueryLoader.bindParameterValues(QueryLoader.java:514)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1589)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
..............................
I tried several different ways of writing this query eg. using "BETWEEN" or tried to subtract from t.time, but with the same unsuccessful results. It looks like Hibernate expects that addition operator is used between Double objects.
Certainly here I can make some workaround like using java.sql.Timestamp values as parameters by adding current date to java.sql.Time in JAVA language, or prepare some kind of database function to add time values, but it would be not portable.
So my question is:
Is adding of time values possible in JPQ Language ? How can I achieve this goal ?
Thanks in advance for any help
ps. a problem related to mine (casting time values):
https://forum.hibernate.org/viewtopic.php?f=1&t=1000851
Login
FAQ
Search