JPA2 Criteria API运行时从varchar（25）转换为十进制 [英] JPA2 Criteria API runtime cast from varchar(25) to decimal

问题描述

所以我看到堆栈上的所有线程都在类似主题上溢出，但我找不到解决问题的方法。

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.javadb.net/double-function.html ， 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）转换为十进制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！