因此,我已经看到类似主题上堆栈溢出的所有线程,但我没有找到解决我的问题的方法。
我正在尝试创建一个 Criteria 查询,并得到以下 SQL(第一个 SQL,简化版):
SELECT latitude FROM stations WHERE (ABS(latitude - 45.893227) <= 0.2)
但它给了我这个错误:
java.sql.SQLDataException: The resulting value is outside the range for the data type DECIMAL/NUMERIC(31,31).
那是因为我的纬度是 varchar(25) 类型。所以这解决了它(第二个SQL):
SELECT latitude FROM stations WHERE (ABS(CAST(latitude AS DECIMAL) - 45.893227) <= 0.2)
但现在我需要用 Criteria 语法来完成它。这是我的方法:
public List<Stations> searchStations(Float distance, List<Address> addresses) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Stations> cq = cb.createQuery(Stations.class);
Root<Stations> stations = cq.from(Stations.class);
//this <Float> cast actually does nothing:
Expression<Float> Lat = stations.get("latitude");
Expression<Float> Lon = stations.get("longitude");
//make a list of conditions
List<Predicate> predicates = new ArrayList<>();
for (Address a : addresses) {
Float aLat = Float.valueOf(a.getLatitude());
Float aLon = Float.valueOf(a.getLongitude());
//condition: |station.Lat - address.Lat| <= distance
//le() = lessThan, abs() = absolute, diff() = subtraction
Predicate condLat = cb.le(cb.abs(cb.diff(Lat, aLat)), distance);
Predicate condLon = cb.le(cb.abs(cb.diff(Lon, aLon)), distance);
//if I do: Lat.as(Float.class) it won't cast on runtime!
predicates.add(condLat);
predicates.add(condLon);
}
//add the array of conditions to the WHERE expression, connected with AND:
cq.where(cb.and(predicates.toArray(new Predicate[predicates.size()])));
TypedQuery<Stations> q = em.createQuery(cq);
return q.getResultList();
}
通过该代码,我得到了第一个 SQL,并且我想要第二个 SQL。我只是无法获得运行时转换功能。我尝试过的 Criteria 类型转换函数在运行时不起作用。我该如何解决这个问题?