JPA criteria API. A query with an sql function call that takes parameters

Question

I am trying to build this query using type APIs:

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(); 

I get this exception:

 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] 

Does the JPA querying logic seem to forget to separate the params with commas, or do I need to do it myself somehow?

Answer 1

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