问题描述
我有一个从Java应用程序执行的查询,如下所示:
I have a query which is executed from java application like this:
Query query = getEntityManager().createQuery(hql);
查询如下:
String hql = "select * from table a where round(column1, 3) = round(parameter, 3)";
此处column1
是Double
类型.它拥有的值类似于143.02856666
.我需要保持原样的价值,但是对于某些业务逻辑,只需要四舍五入并进行比较.
Here column1
is of type Double
. The value it holds is like 143.02856666
. I need to retain the value as it is, but for some business logic just need to round and compare.
配置的初始数据库为H2,并且工作正常.现在,数据库已更改为Postgres,并且此查询现在出错了.
The initial database configured was H2 and this worked fine. Now the database has been changed to Postgres and this query now errors out.
Postgres中的round()
函数采用数字数据类型,并且需要进行强制转换.
The round()
function in Postgres takes a numeric datatype and needs a cast.
下面的查询如果直接在Postgres控制台中执行,则工作正常.
The below query works fine if executed directly in Postgres console.
select * from table a where round(cast(column1 as numeric), 3) = round(cast(parameter as numeric), 3);
同样是从Java应用程序中出错的.
The same from java application errors out.
也尝试过Query query = getEntityManager().createNativeQuery(hql);
这会导致新的错误.
Also tried Query query = getEntityManager().createNativeQuery(hql);
This results in a new error.
如果我进行调试,则在执行以下行时会出错.
If I debug, this errors out when the below line is executed.
List resultList = query.getResultList();
如何重写查询,使其对Postgres有效?
How do I rewrite the query so that it works against Postgres ?
推荐答案
使用Query query = getEntityManager().createQuery(hql);
所做的就是调用jpql
-查询,该查询不支持round(v numeric, s integer)
之类的所有数据库功能.
What you are doing with Query query = getEntityManager().createQuery(hql);
is calling a jpql
-query, which does not support all db-functions like round(v numeric, s integer)
.
两个建议:
- 使用
BETWEEN
并维护jpql映射 - 写一个NativeQuery->
Query query = em.createNativeQuery(queryString);
- Use
BETWEEN
and maintain jpql-mapping - Write a NativeQuery ->
Query query = em.createNativeQuery(queryString);
您的queryString
只需根据您的参数进行更改.
Your queryString
just has to be altered by your parameters.
这篇关于PostgreSQL函数回合和JPA/休眠的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!