How to paging a query in JPA with nativeQuery and Spring PagingAndSortingRepository

Question

A few days ago a colleague made this question: Order and group by with one column

It was resolved. A simple and clean query, very cool. The solution seemed to be worth it, except that we need to implement it in JPA.

As JPA does not accept subqueries in JOIN we had to do it as nativeQuery, but in doing so we have problems with paging, since JPA does not combine this with native queries. https://docs.spring.io/spring-data/jpa/docs/1.8.0.M1/reference/html/

Native queriesThe @Query annotation allows to execute native queries by setting the nativeQuery flag to true. Note, that we currently don’t support execution of pagination or dynamic sorting for native queries as we’d have to manipulate the actual query declared and we cannot do this reliably for native SQL.

We have no idea how to continue with this.

What we have to do: we have a series of records with phone number, user and date, and each user has been able to call N times. We need to obtain all the records grouped by phone number and sorted (DESC) by the most recent date of each group of numbers.

e.g.:

With this data:

+--------------+---------------------+-------------+---------------+
| phone_number | registered          | name        | first_surname |
+--------------+---------------------+-------------+---------------+
|    222005001 | 2019-05-10 10:01:01 | Alvaro      | Garcia        |
|    222004001 | 2019-05-13 16:14:21 | David       | Garcia        |
|    111003001 | 2019-05-13 16:14:43 | Roberto     | Martin        |
|    111001000 | 2019-05-13 16:14:50 | Juan Manuel | Martin        |
|    111001000 | 2019-05-13 16:14:50 | Maria       | Alonso        |
|    111001000 | 2019-05-13 16:14:50 | Roberto     | Martin        |
|    333006001 | 2019-05-13 16:14:55 | Benito      | Lopera        |
|    123456789 | 2019-05-13 16:15:00 | NULL        | NULL          |
|    987654321 | 2019-05-13 16:15:08 | NULL        | NULL          |
|    123456789 | 2019-05-13 16:15:13 | NULL        | NULL          |
|    666999666 | 2019-05-13 16:15:18 | NULL        | NULL          |
|    454545458 | 2019-05-13 16:15:27 | NULL        | NULL          |
|    333006001 | 2019-05-13 16:23:36 | Benito      | Lopera        |
|    987654321 | 2019-05-13 16:23:46 | NULL        | NULL          |
|    666999666 | 2019-05-13 16:23:50 | NULL        | NULL          |
|    454545458 | 2019-05-13 16:23:55 | NULL        | NULL          |
|    666999666 | 2019-05-13 16:24:03 | NULL        | NULL          |
|    222004001 | 2019-05-13 16:24:10 | David       | Garcia        |
+--------------+---------------------+-------------+---------------+

Sort them like this:

+--------------+---------------------+-------------+---------------+
| phone_number | registered          | name        | first_surname |
+--------------+---------------------+-------------+---------------+
|    222004001 | 2019-05-13 16:24:10 | David       | Garcia        |
|    222004001 | 2019-05-13 16:14:21 | David       | Garcia        |
|    666999666 | 2019-05-13 16:24:03 | NULL        | NULL          |
|    666999666 | 2019-05-13 16:23:50 | NULL        | NULL          |
|    666999666 | 2019-05-13 16:15:18 | NULL        | NULL          |
|    454545458 | 2019-05-13 16:23:55 | NULL        | NULL          |
|    454545458 | 2019-05-13 16:15:27 | NULL        | NULL          |
|    987654321 | 2019-05-13 16:23:46 | NULL        | NULL          |
|    987654321 | 2019-05-13 16:15:08 | NULL        | NULL          |
|    333006001 | 2019-05-13 16:23:36 | Benito      | Lopera        |
|    333006001 | 2019-05-13 16:14:55 | Benito      | Lopera        |
|    123456789 | 2019-05-13 16:15:13 | NULL        | NULL          |
|    123456789 | 2019-05-13 16:15:00 | NULL        | NULL          |
|    111001000 | 2019-05-13 16:14:50 | Maria       | Alonso        |
|    111001000 | 2019-05-13 16:14:50 | Roberto     | Martin        |
|    111001000 | 2019-05-13 16:14:50 | Juan Manuel | Martin        |
|    111003001 | 2019-05-13 16:14:43 | Roberto     | Martin        |
|    222005001 | 2019-05-10 10:01:01 | Alvaro      | Garcia        |
+--------------+---------------------+-------------+---------------+

It can be done with this query:

SELECT c.phone_number, c.registered, cl.name, cl.first_surname
FROM callers cl
    INNER JOIN callers_phones cp ON cl.caller_id = cp.caller_id
    RIGHT OUTER JOIN calls c ON c.phone_number = cp.phone_number
    JOIN (
        SELECT phone_number, MAX(registered) AS registered
        FROM calls
        GROUP BY phone_number) aux_c ON aux_c.phone_number = c.phone_number
WHERE c.answered = FALSE
    AND (null is null or null is null or c.registered between null and null)
    AND (null is null or c.phone_number = null)
    AND (null is null or cl.caller_id = null)
ORDER BY aux_c.registered DESC, c.registered DESC

These are the tables:

CREATE TABLE callers
(
    caller_id int NOT NULL UNIQUE AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    first_surname varchar(50) NOT NULL,
    CONSTRAINT callers_pkey PRIMARY KEY (caller_id)
);

CREATE TABLE callers_phones
(
    phone_id int NOT NULL UNIQUE AUTO_INCREMENT,
    caller_id int NOT NULL,
    phone_number int NOT NULL,
    CONSTRAINT callers_phones_pkey PRIMARY KEY (phone_id)
);

ALTER TABLE callers_phones
    ADD CONSTRAINT callers_phones_fkey_callers FOREIGN KEY (caller_id)
    REFERENCES callers (caller_id);

CREATE TABLE calls
(   
    call_id int NOT NULL UNIQUE AUTO_INCREMENT,
    phone_number int NOT NULL,
    answered boolean NOT NULL DEFAULT false,
    registered datetime NOT NULL,
    CONSTRAINT calls_pkey PRIMARY KEY (call_id)
);

The problem is that we have to implement it in JPA with paging, but subqueries do not work in JOIN clause, and paging does not work with nativeQuery.

This is what we have done:

@Entity:

import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.EntityResult;
import javax.persistence.FieldResult;
import javax.persistence.Id;
import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;

@SqlResultSetMapping (name = "MissedCallResult",
        entities = {
                     @EntityResult (entityClass = MissedCallEntity.class,
                             fields = {
                                        @FieldResult (name = "callId", column = "id"),
                                        @FieldResult (name = "phoneNumber", column = "pH"),
                                        @FieldResult (name = "registered", column = "reg"),
                                        @FieldResult (name = "callerName", column = "cN"),
                                        @FieldResult (name = "callerFirstSurname", column = "cFS")
                             })
        })
@NamedNativeQuery (name = "findMissedCalls",
        query = "select c.call_id as id, c.phone_number as pH, c.registered as reg, cl.name as cN, cl.first_surname as cFS "
                + "from callers cl "
                + "    inner join callers_phones cp on cl.caller_id = cp.caller_id "
                + "    right outer join calls c on c.phone_number = cp.phone_number "
                + "    join (select c2.phone_number, MAX(c2.registered) as registered "
                + "        from calls c2 "
                + "        group by c2.phone_number) aux_c on aux_c.phone_number = c.phone_number "
                + "where c.answered = false "
                + "    and (:startDate is null or :endDate is null or c.registered between :startDate and :endDate) "
                + "    and (:callerId is null or cl.caller_id = :callerId) "
                + "    and (:phoneNumber is null or c.phone_number = :phoneNumber) "
                + "order by aux_c.registered desc, c.registered desc",
        resultSetMapping = "MissedCallResult")
@Entity
public class MissedCallEntity
{
    @Id
    private Integer callId;
    private Integer phoneNumber;
    private Date registered;
    private String callerName;
    private String callerFirstSurname;
    private String callerSecondSurname;

...

}

@Repository:

import java.util.Date;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.data.rest.core.annotation.RepositoryRestResource;

import es.panel.domain.MissedCallEntity;

@RepositoryRestResource (path = "missedCalls", collectionResourceRel = "missedCalls")
public interface MissedCallRepository extends PagingAndSortingRepository<MissedCallEntity, Integer>
{
    @Query (nativeQuery = true, name = "findMissedCalls")
    Page<MissedCallEntity> findMissedCalls(@Param ("startDate") Date startDate,
                                           @Param ("endDate") Date endDate,
                                           @Param ("callerId") Integer callerId,
                                           @Param ("phoneNumber") Integer phoneNumber,
                                           Pageable page);
}

In @Service:

public Page<MissedCallEntity> getMissedCalls(Date startDate,
                                                 Date endDate,
                                                 Integer callerId,
                                                 Integer phoneNumber,
                                                 int actualPage,
                                                 int limit)
    {
        Page<MissedCallEntity> calls = mcRepository.findMissedCalls(
                startDate, endDate, callerId, phoneNumber, PageRequest.of(1, 5));

        return calls;
    }

Thanks in advance!

Alan Hay · Accepted Answer · 2019-05-16 15:15:04Z

1

A very simple solution is to create a database view based on your query for the calculated values i.e. counts and max values etc.

You can map this to the relevant entity using the JPA @SecondaryTable annotation which lets you map an entity to more than 1 table (or view).

With this is place you can the sort and filter using standard JPA/spring data functionality just as for any other field and you can pretty much remove all the code you have written.

I would elaborate further however it is not very clear what you are trying to achieve: you are asking about your attempted solution rather than the problem itself. Neither is MissedCall an entity. The entities in your system are users, calls, phones etc.

edited May 16, 2019 at 15:15

answered May 16, 2019 at 15:09

Alan Hay

23.3k4 gold badges64 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to paging a query in JPA with nativeQuery and Spring PagingAndSortingRepository

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related