Spring JPA, Entity Manager and Custom Query

When it comes to Spring JPA, usually until you have a really complex relationship in your tables its quite simple and straight forward to work on it.

I am going to explain a scenario where you want to get a result set which directly does not map to the entity you have defined.

Suppose you are a laundry service provider and you have a table which contains the details about the delivery offices which can do home delivery.

------------------------------ TABLE: laundry_office ----------------------------
CREATE TABLE `laundry_office` (
  `id` bigint(20) NOT NULL,
  `address_1` varchar(255) DEFAULT NULL,
  `address_2` varchar(255) DEFAULT NULL,
  `address_3` varchar(255) DEFAULT NULL,
  `address_4` varchar(255) DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,

  `home_delivery_indicator` bit NOT NULL DEFAULT 0,
`name` varchar(255) DEFAULT NULL,
  `postcode` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

------------Corresponding Entity-----------------------------

Here we have used SqlResultSetMapping as we are trying to get extra data  from the query which can't be held by the Entity object of LaundryOffice . (distance_in_miles in this case)

Pay attention to : columns={@ColumnResult(name="distance_in_miles")}

@Entity@Table(name = "laundry_office",

         indexes = {@Index(name = "geo_index",  columnList="latitude,longitude")})

@SqlResultSetMapping(
        name = "mySqlResultSetMapping",
        entities = @EntityResult(
                entityClass = LaundryOffice.class,
                fields = {
                        @FieldResult(column = "id", name = "id"),
                        @FieldResult(column = "name", name = "name"),
                        @FieldResult(column = "address_1", name = "address1"),
                        @FieldResult(column = "address_2", name = "address2"),
                        @FieldResult(column = "address_3", name = "address3"),
                        @FieldResult(column = "address_4", name = "address4"),
                        @FieldResult(column = "postcode", name = "postcode"),
                        @FieldResult(column = "longitude", name = "longitude"),
                        @FieldResult(column = "latitude", name = "latitude"),
                        @FieldResult(column = "home_delivery_indicator", name = "homeDeliveryIndicator")}),

                        columns={@ColumnResult(name="distance_in_miles")}

)
public class LaundryOffice {

    @Id    @Column(name = "id")
    private long id;

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

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

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

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

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

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

    @Column(name = "longitude")
    private Double longitude;

    @Column(name = "latitude")
    private Double latitude;

   
   @Column(name = "home_delivery_indicator", columnDefinition="bit NOT NULL DEFAULT 0")
   private boolean homeDeliveryIndicator;
}

----------- Java code -----------------------

Firing simple query is straight forward, but for complex queries, we may have to use Entity Manager. 

Refer SpringJPA for simple  queries.

@PersistenceContext

private EntityManager entityManager;

// Creating a parameterized custom query.
private static final String FIND_ALL_LAUNDRY_OFFICE_BY_LAT_LONG_AND_RADIUS =

        //To return km instead of miles, swap the ‘3959’ below statement with ‘6371’)        "select (\n" +
                "     3959 * acos (\n" +
                "       cos ( radians(:latitude) )\n" +
                "       * cos( radians( latitude ) )\n" +
                "       * cos( radians( longitude ) - radians(:longitude) )\n" +
                "       + sin ( radians(:latitude) )\n" +
                "       * sin( radians( latitude ) )\n" +
                "     )\n" +
                "   ) AS distance_in_miles, laundry_office.*\n" +

                " FROM laundry_office\n" +
                " HAVING distance_in_miles < :radius\n" +

" ORDER BY distance_in_miles\n";

Note, how we are getting all the laundry_office data and distance_in_miles in above query.


// Pass parameters to the above queries. 
List<Object[]> results = entityManager.createNativeQuery(FIND_ENQUIRY_OFFICE_BY_LOCAL_COLLECT, "mySqlResultSetMapping")
.setParameter("latitude", latitude)
.setParameter("longitude", longitude)
.setParameter("radius", radius)
.setMaxResults(10) // act as limit in sql query
.getResultList();

//Extract both Entity and extra parameter out of the result sets returned. 

results.stream().forEach((record) -> {
    LaundryOffice lo = (LaundryOffice) record[0];
    double distance_in_miles = (double) record[1];  

   System.out.println("LaundryOffice: " + lo.getId() );

   System.out.println("distance_in_miles: " + distance_in_miles );

});

Hope this example helps you to when creating complex custom queries

Enjoy!! :)