JPA 搜索附近的人功能st_distance_sphere

抄的@三三两两酱 的这篇文章

JPA根据坐标点的经纬度查询半径范围内的数据

生成的主要sql

st_distance_sphere(point(104.054503,30.639993),point(spot3_.longitude,spot3_.latitude))<=1000.0 

java代码

import org.hibernate.query.criteria.internal.CriteriaBuilderImpl;
import org.hibernate.query.criteria.internal.Renderable;
import org.hibernate.query.criteria.internal.compile.RenderingContext;
import org.hibernate.query.criteria.internal.expression.LiteralExpression;
import org.hibernate.query.criteria.internal.expression.function.BasicFunctionExpression;

import javax.persistence.criteria.Expression;
import java.io.Serializable;

/**
 * 用于JPA链接MYSQL的funtion
 */
public class PointFunction extends BasicFunctionExpression<String> implements Serializable {

    public static final String NAME = "point";
    private final Expression<Double> longitude;
    private final Expression<Double> latgitude;

    Expression<Double> getLatgitude(){
        return this.latgitude;
    }

    Expression<Double> getLongitude(){
        return this.longitude;
    }


    public PointFunction(CriteriaBuilderImpl criteriaBuilder, Expression<Double> longitude, Expression<Double> latgitude) {
        super(criteriaBuilder, String.class, "point");
        this.longitude = longitude;
        this.latgitude = latgitude;
    }

    public PointFunction(CriteriaBuilderImpl criteriaBuilder, Double longitude, Double latgitude) {
        this(criteriaBuilder, new LiteralExpression<>(criteriaBuilder, longitude), new LiteralExpression<>(criteriaBuilder, latgitude));
    }

    @Override
    public String render(RenderingContext renderingContext) {
        renderingContext.getFunctionStack().push(this);

        String var3;
        try {
            StringBuilder buffer = new StringBuilder();
            buffer.append("point(")
                    .append(
                        ((Renderable) this.getLongitude()).render(renderingContext)
                    )
                    .append(',')
                    .append(
                        ((Renderable) this.getLatgitude()).render(renderingContext)
                    );
            var3 = buffer.append(')').toString();
        } finally {
            renderingContext.getFunctionStack().pop();
        }
        return var3;
    }
}

使用

List<Predicate> listAnd = new ArrayList<>();
listAnd.add(
    criteriaBuilder.lessThanOrEqualTo(
        criteriaBuilder.function("st_distance_sphere", Double.class,
            new PointFunction(
                (CriteriaBuilderImpl) criteriaBuilder,
                Double.valueOf(queryFormDTO.getLng() ),
                Double.valueOf(queryFormDTO.getLat())),
            new PointFunction(
                (CriteriaBuilderImpl) criteriaBuilder,
                root.get("spot").get("longitude"),
                root.get("spot").get("latitude"))
        ),queryFormDTO.getRadius() * 1000
    )
);