PostgreSQL函数回合和JPA/休眠 [英] PostgreSQL function round and JPA/Hibernate

问题描述

我有一个从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.

错误:不存在函数舍入(双精度，整数)提示:没有函数与给定的名称和参数类型匹配.你可能会 需要添加显式类型强制转换.

ERROR: function round(double precision, integer) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

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.

java.lang.IllegalArgumentException:org.hibernate.QueryException:无法解析CAST的请求类型:数字

也尝试过Query query = getEntityManager().createNativeQuery(hql); 这会导致新的错误.

Also tried Query query = getEntityManager().createNativeQuery(hql); This results in a new error.

org.hibernate.engine.jdbc.spi.SqlExceptionHelper-错误:位置"或附近的语法错误

如果我进行调试，则在执行以下行时会出错.

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).

两个建议:

1. 使用BETWEEN并维护jpql映射
2. 写一个NativeQuery-> Query query = em.createNativeQuery(queryString);

1. Use BETWEEN and maintain jpql-mapping
2. Write a NativeQuery -> Query query = em.createNativeQuery(queryString);

您的queryString只需根据您的参数进行更改.

Your queryString just has to be altered by your parameters.

这篇关于PostgreSQL函数回合和JPA/休眠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！