JPA标准API.使用带有参数的sql函数调用查询 [英] JPA Criteria API. Query with sql function call which takes parameters

问题描述

我正在尝试使用Criteria typesafe API构造此查询:

I'm trying to construct this query using Criteria typesafe API:

select * from xxx_table xxx where CALC_DISTANCE(xxx.latitude, xxx.longitude, :lat, :lng) < :dist

CALC_DISTANCE definded PL/SQL function:  
FUNCTION calc_distance(  
 pLat1 NUMBER,  
 pLon1 NUMBER,  
 pLat2 NUMBER,  
 pLon2 NUMBER)  
 RETURN NUMBER  


CriteriaBuilder builder = JpaHandle.get().getCriteriaBuilder();  
CriteriaQuery<XXX> criteria = builder.createQuery(XXX.class);  
Root<XXX> xxxRoot = criteria.from(XXX.class);  

ParameterExpression<Double> latParam = builder.parameter( Double.class );  
ParameterExpression<Double> lngParam = builder.parameter( Double.class );  
ParameterExpression<Double> distParam = builder.parameter( Double.class );  

Expression<Double> distFunction = builder.function("CALC_DISTANCE", Double.class, xxxRoot.get(XXX_.latitude), xxxRoot.get(XXX_.longitude), latParam, lngParam);  

criteria.where(builder.greaterThan(distFunction, distParam));  
TypedQuery<XXX> q = em.createQuery(criteria);  
q.setParameter(latParam, latitude);  
q.setParameter(lngParam, longitude);  
q.setParameter(distParam, new Double(distance.toString()));  
return q.getResultList();  

我收到此异常:

Caused by: org.hibernate.QueryException: could not resolve property:  
latitudegeneratedAlias0 of: domain.XXX  
[select generatedAlias0 from domain.XXX as generatedAlias0 where  
CALC_DISTANCE(generatedAlias0.latitudegeneratedAlias0.longitude:param0:param1)>:param2]  

似乎JPA查询构造逻辑忘记了用逗号分隔参数，还是我自己有办法做到这一点?

Seems like JPA query construction logic forgets to separate params with commas, or do I have somehow to do it myself?

推荐答案

看起来您定义的PL/SQL函数需要4个参数，而只显示2个参数.

It looks like your defined PL/SQL function takes 4 arguments and you only have 2 arguments shown.

这篇关于JPA标准API.使用带有参数的sql函数调用查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！