JPA: How to combine a @NamedStoredProcedureQuery with a @NamedQuery to perform a perimeter search?

Question

I have some entities B and C inheriting from a parent entity A. Hence, I have a joined, multiple inheritance structure in my database. Furthermore, I have defined some @NamedQueries on these entitites which work well.
I intend to have a @NamedStoredProcedureQuery which is able to find some POIs in a perimeter. I have already implemented a stored procedure which performs a SELECT on the parent table, getting longitude,latitude and radius as parameter and a CALL returns the correct records. The columns to perform the perimeter search are all in the parent table/entity.

Now I want to call this stored procedure from Java using JPA related to the inherited entity. This means that a perimeter search for entities of class B shall return all POIs of class B within the perimeter.

Is it sufficient to define the @NamedStoredProcedureQuery in the parent entity class?
How can I call such a @NamedStoredProcedureQuery from within a @NamedQuery in a subclass?

A JPQL query cannot call a stored procedure, therefore a NamedQuery cannot call a NamedStoredProcedureQuery. You invoke them yourself using the JPA API — Neil Stockton, Jul 22 '14 at 13:01

du-it · Answer 1 · 2014-07-25T07:19:24.977

I created a stored procedure with the necessary IN parameters as well as a further IN parameter for the table to be used.

DELIMITER $$
CREATE PROCEDURE `perimeterSearch`(IN lon double, IN lat double, IN radius double, 
                                IN poiTable varchar(45))
BEGIN
SET @perimeterSearch = CONCAT(
'SELECT
   -- poiId,
   -- latitude,
   -- longitude,
    *
FROM
    ', 'mySchema.', poiTable, ' pt ', 

' 
LEFT JOIN mySchema.pois p ON p.poiId = pt.poiId 
HAVING
    -- distance <= radius
(
        6371 * acos(
            cos(
                radians( p.latitude )
            ) * cos(
                radians( ', lat, ' ) 
            ) * cos(
                radians( p.longitude ) - radians( ', lon, ' ) 
            ) + sin(
                radians( ', lat, ' ) 
            ) * sin(
                radians( p.latitude )
            )
        )
    ) <= ', radius, ' 
');
-- ORDER BY
--    distance ASC;
PREPARE stmt FROM @perimeterSearch;
EXECUTE stmt;
END

In my DAO implementation I execute

public List<MyPoiPoiEntity> findMyPoisInPerimeter(final double longitude, final double latitude, final double radius){
        em = factory.createEntityManager();
        final StoredProcedureQuery ppQuery =
                em.createStoredProcedureQuery("perimeterSearch", MyPoiEntity.class) 
                .registerStoredProcedureParameter("longitude", double.class, ParameterMode.IN)
                .setParameter("longitude", longitude)
                .registerStoredProcedureParameter("latitude", double.class, ParameterMode.IN)
                .setParameter("latitude", latitude)
                .registerStoredProcedureParameter("radius", double.class, ParameterMode.IN)
                .setParameter("radius", radius)
                .registerStoredProcedureParameter("poiTable", String.class, ParameterMode.IN)
                .setParameter("poiTable", MyPoiEntity.class.getAnnotation(Table.class).name());
        final List<MyPoiEntity> cpEntities = ppQuery.getResultList();
        em.close();
        return cpEntities;      
    }

The trick to use the correct table ist done by

.setParameter("poiTable", MyPoiEntity.class.getAnnotation(Table.class).name());

This way I am sure that the correct entity table is used in opposite to use a string literal.

JPA: How to combine a @NamedStoredProcedureQuery with a @NamedQuery to perform a perimeter search?

1 Answers1