问题描述
所以我看到堆栈上的所有线程都在类似主题上溢出,但我找不到解决问题的方法。
So I've seen all threads on stack overflow on similar subjects but I've found no solution to my problem.
我正在尝试创建一个Criteria查询,我得到这个SQL(第一个SQL,简化):
I'm trying to create a Criteria query and I get this SQL (1st SQL, simplified):
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):
That's because my latitude is of type varchar(25). So this fixes it (2nd SQL):
SELECT latitude FROM stations WHERE (ABS(CAST(latitude AS DECIMAL) - 45.893227) <= 0.2)
但现在我需要在Criteria语法中执行此操作。这是我的方法:
But now I need to do it in Criteria syntax. This is my method:
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,我想要第二个one。我只是无法获得运行时强制转换功能。我试过的Criteria类型转换函数在运行时不起作用。我如何解决这个问题?
With that code I'm getting the 1st SQL, and I want the 2nd one.I just can't get that runtime cast functionality. The Criteria type cast functions that I've tried don't work on runtime. How do I get around this problem?
推荐答案
经过一些搜索,我发现了一种使用Java derby强制运行时使用CriteriaBuilder函数进行运行的方法DOUBLE函数:
After some searching I found a way to force runtime casts with CriteriaBuilder function using java derby DOUBLE function:
Expression Lat = stations.get("latitude");
Expression LatCast = cb.function("DOUBLE", Float.class, Lat);
我得到这个SQL查询:
And I get this SQL query cast:
DOUBLE(LATITUDE)
参考文献:, http://www.objectdb.com/api/java/jpa/criteria/CriteriaBuilder/function_String_Class__Expression__
它适用于大多数支持的数据库函数。
It works with most supported database functions.
这篇关于JPA2 Criteria API运行时从varchar(25)转换为十进制的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!