使用 Criteria api 查找半径内的坐标

Question

我需要使用 CriteriaBuilder 从数据库中获取（纬度和经度）距离给定纬度和经度（用户位置）一定距离（例如：10 公里）内的所有行。

在第三个 if 中，我必须获取结果并将其添加到谓词中。

        CriteriaBuilder cb = em.getCriteriaBuilder();

        CriteriaQuery<Issue> cq = cb.createQuery(Issue.class);

        Root<Issue> issue = cq.from(Issue.class);

        List<Predicate> predicates = new ArrayList<>();

        if (filters.getCategoryId() != null) {

            Category issueCategory = categoryRepository.findById(filters.getCategoryId())

                    .orElseThrow(() -> new ResourceNotFoundException("Category not found for this id :: " +String.valueOf(filters.getCategoryId())));

            predicates.add(cb.equal(issue.get("category"), issueCategory));

        }

        if (filters.getPostedDays() != null) {

            Date startDate = issueUtility.getStartDateForFilterIssues(filters.getPostedDays());

            predicates.add(cb.between(issue.get("createdAt"), startDate,new Date()));

        }

        if (filters.getLatitude() != null && filters.getLongitude() != null) {

              // fetch by radius code comes here

        }

        cq.where(predicates.toArray(new Predicate[0]));```

Answer 1

得到了解决方案。&nbsp; &nbsp; &nbsp; &nbsp; CriteriaBuilder cb = em.getCriteriaBuilder();&nbsp; &nbsp; &nbsp; &nbsp; CriteriaQuery<Issue> cq = cb.createQuery(Issue.class);&nbsp; &nbsp; &nbsp; &nbsp; Root<Issue> issue = cq.from(Issue.class);&nbsp; &nbsp; &nbsp; &nbsp; List<Predicate> predicates = new ArrayList<>();&nbsp; &nbsp; &nbsp; &nbsp; if (filters.getCategoryId() != null) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Category issueCategory = categoryRepository.findById(filters.getCategoryId())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .orElseThrow(() -> new ResourceNotFoundException("Category not found for this id :: " +String.valueOf(filters.getCategoryId())));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; predicates.add(cb.equal(issue.get("category"), issueCategory));&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; if (filters.getPostedDays() != null) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date startDate = issueUtility.getStartDateForFilterIssues(filters.getPostedDays());&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; predicates.add(cb.between(issue.get("createdAt"), startDate,new Date()));&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; if (filters.getLatitude() != null && filters.getLongitude() != null) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // fetch by radius code comes here&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Expression<Issue> point1 = cb.function("point", Issue.class,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; issue.get("latitude"),issue.get("longitude"));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Expression<Double> point2 = cb.function("point", Double.class,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cb.literal(filters.getLatitude()),cb.literal(filters.getLongitude()));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Expression<Number> distance = cb.function("ST_Distance_Sphere", Number.class,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; point1,point2);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; predicates.add(cb.lt(distance,30000));&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; cq.where(predicates.toArray(new Predicate[0]));&nbsp; &nbsp; &nbsp; &nbsp; return em.createQuery(cq).getResultList();&nbsp; &nbsp; }