java 如何在 JPQL 语句中使用类型转换？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10544307/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I use typcasting inside a JPQL statement?
提问by Shahzeb
I have two Integercolumns in the database (derby and db2). I need to divide them with each other inside a JPQL.
我Integer在数据库中有两列（derby 和 db2）。我需要在一个JPQL.
Both columns being of type Integerreturn zero if remainder is a decimal number e.g 0.25becomes 0etc and understandably so since type is int.
Integer如果余数是十进制数，则两个类型的列都返回零，例如0.25变成0等并且可以理解，因为类型是 int。
In SQLI could have this for example
例如，SQL我可以有这个
select CAST(column1 as decimal(6,2))/CAST(column2 as decimal(6,2))from Sometable;
select CAST(column1 as decimal(6,2))/CAST(column2 as decimal(6,2))from Sometable;
but what is JPQLequivalent .
但什么是JPQL等价的。
One option might be (I have not tried yet) is to have a @Transientmethod in the entity returning the Decimal type and doing this calculation there and pass that to JPQLbut I would rather let SQLdo this work.
一种选择可能是（我还没有尝试过）是@Transient在实体中使用一种方法返回 Decimal 类型并在那里进行计算并将其传递给JPQL但我宁愿让SQL做这项工作。
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.
Mysql 不需要在数据库级别进行转换。所以不同 RDBMS 的行为是不同的，这很好。但是 JPQL 应该做什么而不需要使用本机查询来知道此操作需要转换为十进制。
Adding dialect <property name="openjpa.jdbc.DBDictionary" value="derby"/>did not fix it either.
添加方言<property name="openjpa.jdbc.DBDictionary" value="derby"/>也没有解决它。
Please note it is JPA1
请注意它是 JPA1
采纳答案by Java Ka Baby
You have basically three options.
你基本上有三个选择。
- Use postload and let java do it .
- 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.
- Third use Native Query like Idanmo said.
- 使用 postload 并让 java 来做。
- 使用第三个类并在 JPQL 的 Select 语句中使用 New Operator 来调用构造函数，在该调用中，您可以管理将传递给 SQL 的数据类型，但是您将获得不止一种对象，只要得到正确的对象就行了从右边的数组索引。更多关于这个here。
- 第三，像 Idanmo 所说的那样使用 Native Query。
回答by Old Pro
That the division operator performs integer division on integer arguments is a feature. In MySQL you have to explicitly choose divfor integer division and /for floating point division, whereas in JPQL the choice is made automatically just like in Java.
除法运算符对整数参数执行整数除法是一个特性。在 MySQL 中，您必须明确选择div整数除法和/浮点除法，而在 JPQL 中，选择就像在 Java 中一样自动进行。
So I suggest:
所以我建议：
select (column1 * 1.0) / column2 from Sometable;
回答by idanmo
AFAIK there isn't a way to do this kind of cast in JPQL.
AFAIK 没有办法在 JPQL 中进行这种转换。
I suggest using JPA's native query execution which lets you run an SQL query like you would if you were using JDBC.
我建议使用 JPA 的本机查询执行，它可以让您像使用 JDBC 一样运行 SQL 查询。
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();
