Cast function for Hibernate

views:

934

answers:

Cast function for Hibernate

Hi, all

I have tried to cast to float numbers from string in the database fields to compare with another numbers. The field in the database was String type. I have tried to use BETWEEN criteria using cast() as " cast(field, float) BETWEEN 1.003 AND 100.00)" in the where statement. however, it does not help.

however, when I tried to execute the regular query directly to Database without Hibernate, it works fine as "SELECT * FROM table WHERE cast(field as float) BETWEEN 1.003 AND 100.00"

I have tried ".. WHERE cast(field as float) > 1.003 AND cast(field as float) < 100", however it does not work on Hibernate either.

I found several blogs or forms, but it does not help.

https://forum.hibernate.org/viewtopic.php?p=2399159

Do you have any idea what was wrong or any opinion ?

I will appreciate about that if you give some directions.

Thanks

tiger

+1 A:

Are you getting an illegal syntax exception from Hibernate or a database error? Can you post it?
What database are you using? Does it support cast() internally?
Have you tried doing your comparison without cast()? Some (most, actually) databases support implicit type conversion

ChssPly76 2009-11-04 19:30:02

1. it does not throw any exception or any error from Hibernate. it showed off wrong list data.2. PostgreSQL 8.3. In the PostgreSQL , it works directly using cast(). On the hibernate, it does not work and did (1) above.3. I did withour cast(), it does not go through the result. It showed "operator does not exist: character varying > numeric".Pleae, let me know if you need any further information

Tiger 2009-11-04 19:44:54

I see. `BETWEEN` is actually not `< and >` but rather `<= and >=`. Secondly, Hibernate's Postgres dialect maps "float" to "float4" which can be rather tricky to compare due to it being floating point; consider casting it as numeric instead. Finally, can you turn on the SQL output in Hibernate and check what query is actually generated? If the discrepancy is not caused by inclusive vs exclusive greater / less than it must be due to something happening to the query.

ChssPly76 2009-11-04 19:59:12

The problem was Hibernate throws NullPointerException when I used cast as numeric. so I created a new PostgreSQLDialect that registers FLOAT type as as float not as float4. It now works properly. Thank you. :)

Tiger 2009-11-05 16:41:36

ansaurus

tags:

views:

answers:

Cast function for Hibernate

related questions