CODEWITHSUNDEEP
javaoracle-databasehibernatehql
Samantha
Samantha

Reputation: 167

Round to 2 decimal places in hibernate query language

Hi can someone help me how to round to 2 decimal places in hql?
I can't find anything online. Below is my query:

Select p.amount as amt,p.desc from pay p, register r where r.type=?1 and r.code=?2;

I would be glad if someone can help on this.

Technology used: hibernate, spring, java, primefaces 4.0, oracle database

Upvotes: 2

Views: 13835

Answers (3)

Nicolás Corthorn Errázuriz
Nicolás Corthorn Errázuriz

Reputation: 123

This worked for me

cast(value as decimal(9,2))

Upvotes: 1

arcasys
arcasys

Reputation: 41

I have struggled with this a lot an finally found the following solution:

If you want to be more DB independent and specifically want to support Postgres or Oracle DB you can use the floor function for rounding which is pretty generic and does not leave much room for different implementations, meaning this will probably work with all databases (tested with Postgres and MySQL).

The snippet performs rounding to 2 digits and rounds by the absolute value. You can easily adapt this to your needs.

Regard the division by 100 using 100.0. This will insure that you always get a double as a result which is particularly important if you build a sum over the rounded values.

floor(abs(value) * 100 + 0.5)/100.0 * sign(value)

Upvotes: 2

Karibasappa G C
Karibasappa G C

Reputation: 2732

use FORMAT function on property to 2 decimal point

Select FORMAT(p.amount,2) as amt,p.desc from pay p, register r where r.type=?1 and r.code=?2;

alternatively you can specify the same in mapping file as well like below

 <property name="amount">
        <column name="amount" scale="15" precision="3" sql-type="number(15,2)"/>
    </property>

Upvotes: 0

Related Questions