Geek asks and answers

How can I use typcasting inside a JPQL statement?

I have two columns Integerin the database (derby and db2). I need to share them with each other inside JPQL.

Both type columns Integerreturn zero if the remainder is a decimal number, for example, 0.25becomes 0, etc. and understandable, since type is int.

In SQLI could have this for example

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

but that is equivalent JPQL.

One option may be (I have not tried it yet) is to have a method @Transientin essence that returns a decimal type, and do this calculation there and pass this value to JPQL, but I would rather let SQLthis work do.

Mysql does not require casting at the database level. Thus, the behavior for different DBMS is different from the rest. But what should JPQL do without requiring the use of its own query in order to know that decimal conversion is required for this operation.

Adding a dialect <property name="openjpa.jdbc.DBDictionary" value="derby"/>also did not correct it.

Please note that this is JPA1

+5
source share
3 answers

You have basically three options.

  • Use postload and let java do it.
  • New Operator Select JPQL datatypes, SQL, , , index.More .
  • , Idanmo.
+2

, , . MySQL div / , JPQL , Java.

:

select (column1 * 1.0) / column2 from Sometable;
+3

AFAIK JPQL.

JPA, SQL-, 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();
+2
source

More articles:


All Articles