JPA nativeQuery with UNION does not support pagination

Question

Consider the following tables:

TABLE A

id	sys_time	user_id	rent_time

TABLE B

id	sys_time	occur_time

I would like to use a UNION query in MYSQL to have this table and put data from both tables row by row with sys_time order:

TABLE AB

id	sys_time	user_id	occur_time	rent_time

I use the following query:

select id, sys_time, user_id, null as occur_time, rent_time from open_close
union
select id, sys_time, null as user_id, occur_time, null as rent_time from periodic 
order by sys_time desc;

Now I define an @Entity with the following structure:

...
@Data
@Entity
@NamedNativeQuery(
        name="TotalEntity.getTotal"
        , query="select id, sys_time, user_id, null as occur_time, rent_time from open_close\r\n"
                + "union\r\n"
                + "select id, sys_time, null as user_id, occur_time, null as rent_time from periodic \r\n"          + "order by sys_time desc;"
        , resultClass=TotalEntity.class
    ) 
...
// Entity Fields and so on

and the corresponding Repository:

@Repository
public interface TotalRepository extends JpaRepository<TotalEntity, BigInteger> {

     @Query(nativeQuery = true)
     public List<TotalEntity> getTotal();

}

Everything is OK up to now.

Now I want to add pagination:

 @Repository
 public interface TotalRepository extends JpaRepository<TotalEntity, BigInteger> {

      @Query(nativeQuery = true)
      public Page<TotalEntity> getTotal(Pageable page);
}

and use this:

...
private TotalRepository tr;
...


Pageable pageable = PageRequest.of(page, size,
                direction.toUpperCase().equals("ASC") ? Sort.by(sort).ascending() : Sort.by(sort).descending());

        Optional<Page<TotalEntity>> pe = Optional.ofNullable(tr.getTotal(pageable));

The following exception is thrown:

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 20' at line 4

It seems that Hibernate can not modify the nativeQuery to add pagination statements. And I know that JPQL and JPA does not supprt UNIONs. Is there any workaround for this?

Does this answer your question? [Spring Data and Native Query with pagination](https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination) — Nikolai Shevchenko, Mar 30 '21 at 17:07
@NikolaiShevchenko I don't have a problem with pagination itself, the union is making some issues. — Iman H, Mar 30 '21 at 17:09
As a small workaround idea, you could define a database view with the "select union section" and execute the query on the view. — lzagkaretos, Mar 30 '21 at 17:14
@lzagkaretos Nice Idea. It worked. Thanks! You may submit this as an answer. I think. — Iman H, Mar 30 '21 at 17:23

score 2 · Accepted Answer · answered Mar 30 '21 at 17:53

A workaround would be to introduce a view in the database layer with the union clause, and then execute the query upon the view.

This way, you can hide the union from JPA layer and queries can be executed using pageable as usual.

CREATE VIEW view_name AS
select id, sys_time, user_id, null as occur_time, rent_time from open_close
union
select id, sys_time, null as user_id, occur_time, null as rent_time from periodic

And modify the JpaRepository to query upon the view using prefered way, like native queries, column projections etc.

score 0 · Answer 2 · answered Jul 01 '22 at 09:32

If you are not planning to use other functionalities from Pageable interface like getting the total records count, number of current slice etc., then try using offset and limit keywords in the query directly for the pagination.

Note : offeset corresponds to page and limit corresponds to size of content in the page

JPA nativeQuery with UNION does not support pagination

2 Answers2