9

I have two Integer columns in the database (derby and db2). I need to divide them with each other inside a JPQL.

Both columns being of type Integer return zero if remainder is a decimal number e.g 0.25 becomes 0 etc and understandably so since type is int.

In SQL I could have this for example

select CAST(column1 as decimal(6,2))/CAST(column2 as decimal(6,2))from Sometable;

but what is JPQL equivalent .

One option might be (I have not tried yet) is to have a @Transient method in the entity returning the Decimal type and doing this calculation there and pass that to JPQL but I would rather let SQL do this work.

Mysql does not require casting at database level . So Behaviour for different RDBMS is different which is fine . But what should JPQL do with out needing to use a native query to know that cast to decimal is needed for this operation.

Adding dialect <property name="openjpa.jdbc.DBDictionary" value="derby"/> did not fix it either.

Please note it is JPA1

Shahzeb
  • 4,745
  • 4
  • 27
  • 40

3 Answers3

8

You have basically three options.

  1. Use postload and let java do it .
  2. Use a third Class and use New Operator in Select statement of JPQL to call constructor and inside that calls you can manage the datatypes which will be passed to SQL but you will get more then one kind of objects back which is fine just get the right one from right array index.More on this here.
  3. Third use Native Query like Idanmo said.
Java Ka Baby
  • 4,880
  • 11
  • 39
  • 51
7

That the division operator performs integer division on integer arguments is a feature. In MySQL you have to explicitly choose div for integer division and / for floating point division, whereas in JPQL the choice is made automatically just like in Java.

So I suggest:

select (column1 * 1.0) / column2 from Sometable;
Old Pro
  • 24,624
  • 7
  • 58
  • 106
  • 1
    I found that in JPA (at least 2.0) the value returned when multiplication is used in the JPQL query winds up being a `BigDecimal`, which may not be what is expected (e.g. `Double`). However, I found a great solution for this issue here: https://stackoverflow.com/questions/34786148/jpa-native-query-returns-double-or-bigdecimal – Sometimes_Confused Jun 14 '18 at 22:48
3

AFAIK there isn't a way to do this kind of cast in JPQL.

I suggest using JPA's native query execution which lets you run an SQL query like you would if you were using JDBC.

For instance:

Query query = em.createNativeQuery("select CAST(column1 as decimal(6,2))/CAST(column2 as decimal(6,2)) from Sometable");

Double result = (Double) query.getSingleResult();

or

List<Double> results = (List<Double>) query.getResultList();
John Flatness
  • 32,469
  • 5
  • 79
  • 81
idanmo
  • 81
  • 7
  • I would have expected div / operator to return a decimal without needing a cast but thats not happening .Also setting up the dialect would/should have taken care of this may be but not the case either. I am leaning towards nativeQuery as well but I am still not convinced that it is not only option . If it is then thats kind of sad really . +1. – Shahzeb May 14 '12 at 00:38
  • Can you please elaborate why you expect dialect to automatically do the casting? – Victor May 17 '12 at 17:11
  • @Kaushik because division of two integers produce a different datatype depending on underlying database. For example `Mysql` returns a decimal where as `Derby` and `DB2` integer which changes `0.25` to `0` for example . There fore dialect must know the behavior and provide a machinism especially given that option of a cast is not available in `JPQL'. – Shahzeb May 17 '12 at 21:59