[FIXED] JPA nativeQuery with UNION does not support pagination

Issue

Consider the following tables:

TABLE A

id	sys_time	user_id	rent_time

TABLE B

class="s-table">

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?

Solution

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.

Answered By - lzagkaretos
Answer Checked By - Pedro (JavaFixing Volunteer)