CODEWITHSUNDEEP
javaspring-bootspring-data-jpacoordinatescriteria
Alex Man
Alex Man

Reputation: 4886

Find Nearest Drivers using Spring Data JPA Criteria Query

I'm have driver details like as shown below.

[
  {
    "name": "Martin",
    "age": 37,
    "status": "online",
    "deleted": false,
    "place": {
      "name": "Mali",
      "lat": 18.341002,
      "lon": -1.700659
    }
  },
  {
    "name": "John",
    "age": 27,
    "status": "offline",
    "deleted": false,
    "place": {
      "name": "Oregon",
      "lat": 18.393135,
      "lon": -1.810474
    }
  }
  :
  :
  :
]

I have stored the details into my DB using JPA 2.0. In many blogs I have seen people using @Embedded and which the place coordinates entity is defined as @Embeddedand @Embeddable for storing the GPS coordinates (I don't know what advantages it will), so I thought of doing the same in my Driver like as shown below

Driver.java

@Entity
@Table(
    name = "driver",
    uniqueConstraints = @UniqueConstraint(name = "uc_drivers_id", columnNames = {"id"})
)
public class Driver implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Id
    @Column(name="name", nullable = false)
    private String name;

    @Embedded
    private City city;

    @Column(name="age")
    private Integer age;

    @Column(name="status")
    private String status;

    @Column(name="deleted")
    private Boolean deleted;

    :
    :
    :
}

Place.java

@Embeddable
public class Place {

    @Column(name = "location")
    private final Point location;

    @Column(name = "name")
    private String name;

    @JsonProperty
    public double getLatitude()
    {
        return this.location.getY();
    }

    @JsonProperty
    public double getLongitude()
    {
        return this.location.getX();
    }
    :
    :
    :
}

Scenario

I want to find all the drivers that matches the below condition

  1. Drivers whose age is greater than 20
  2. Drivers who are online
  3. Drivers who are 30 km nearest to my location coordinates (let say 18.226253, -1.980688)

I have achieved the first two condition like as shown below using Criteria but dont know how I can achieve the third one

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Driver> criteriaQuery = criteriaBuilder.createQuery(Driver.class);
Root<Driver> entity= criteriaQuery.from(Driver.class);
Predicate agePredicate = criteriaBuilder.greaterThan(entity.get("age"), 20);
Predicate jobPredicate = criteriaBuilder.equal(entity.get("status"), "online");
Predicate wherePredicate = criteriaBuilder.and(agePredicate, jobPredicate);
TypedQuery<Driver>  query = entityManager.createQuery(criteriaQuery.select(entity).where(wherePredicate));
return query.getResultList();

Question

  1. What is the advantage of using @Embeddedand @Embeddable for storing the GPS coordinates
  2. How to find the nearest drivers using coordinates

I am using Spring Boot 2, Spring Data JPA 2 and MySQL

Upvotes: 2

Views: 1583

Answers (1)

L&#225;szl&#243; Stahorszki
L&#225;szl&#243; Stahorszki

Reputation: 1216

That's a feature, that not all databases can handle nicely (by default). If you have a MySQL Server, than it's capable of storing spatial coordinates out of the box, but for example PostgreSQL needs an extension.

You tagged spring-data-jpa, so I'll assume that you use Hibernate as your JPA Provider. Hibernate needs an extension as well to work nicely with Spatial coordinates.

Have a look at this article

Upvotes: 1

Related Questions