Spring Data and Native Query with pagination
SpringSpring DataSpring Data-JpaSpring Problem Overview
In a web project, using latest spring-data (1.10.2) with a MySQL 5.6 database, I'm trying to use a native query with pagination but I'm experiencing an org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException
at startup.
Spring 2.0.4 For those still interested or stuck with older versions check the related answers and comments for workarounds.
UPDATE: 20180306 This issue is now fixed inAccording to Example 50 at Using @Query from spring-data documentation this is possible specifying the query itself and a countQuery, like this:
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
nativeQuery = true)
Page<User> findByLastname(String lastname, Pageable pageable);
}
Out of curiosity, In NativeJpaQuery
class I can see that it contains the following code to check if it's a valid jpa query:
public NativeJpaQuery(JpaQueryMethod method, EntityManager em, String queryString, EvaluationContextProvider evaluationContextProvider, SpelExpressionParser parser) {
super(method, em, queryString, evaluationContextProvider, parser);
JpaParameters parameters = method.getParameters();
boolean hasPagingOrSortingParameter = parameters.hasPageableParameter() || parameters.hasSortParameter();
boolean containsPageableOrSortInQueryExpression = queryString.contains("#pageable") || queryString.contains("#sort");
if(hasPagingOrSortingParameter && !containsPageableOrSortInQueryExpression) {
throw new InvalidJpaQueryMethodException("Cannot use native queries with dynamic sorting and/or pagination in method " + method);
}
}
My query contains a Pageable
parameter, so hasPagingOrSortingParameter
is true
, but it's also looking for a #pageable
or #sort
sequence inside the queryString
, which I do not provide.
I've tried adding #pageable
(it's a comment) at the end of my query, which makes validation to pass but then, it fails at execution saying that the query expects one additional parameter: 3 instead of 2.
Funny thing is that, if I manually change containsPageableOrSortInQueryExpression
from false
to true
while running, the query works fine so I don't know why it's checking for that string to be at my queryString
and I don't know how to provide it.
Any help would be much appreciated.
Update 01/30/2018 It seems that developers at spring-data project are working on a fix for this issue with a PR by Jens Schauder
Spring Solutions
Solution 1 - Spring
My apologies in advance, this is pretty much summing up the original question and the comment from Janar, however...
I run into the same problem: I found the Example 50 of Spring Data as the solution for my need of having a native query with pagination but Spring was complaining on startup that I could not use pagination with native queries.
I just wanted to report that I managed to run successfully the native query I needed, using pagination, with the following code:
@Query(value="SELECT a.* "
+ "FROM author a left outer join mappable_natural_person p on a.id = p.provenance_id "
+ "WHERE p.update_time is null OR (p.provenance_name='biblio_db' and a.update_time>p.update_time)"
+ "ORDER BY a.id \n#pageable\n",
/*countQuery="SELECT count(a.*) "
+ "FROM author a left outer join mappable_natural_person p on a.id = p.provenance_id "
+ "WHERE p.update_time is null OR (p.provenance_name='biblio_db' and a.update_time>p.update_time) \n#pageable\n",*/
nativeQuery=true)
public List<Author> findAuthorsUpdatedAndNew(Pageable pageable);
The countQuery (that is commented out in the code block) is needed to use Page<Author>
as the return type of the query, the newlines around the "#pageable" comment are needed to avoid the runtime error on the number of expected parameters (workaround of the workaround). I hope this bug will be fixed soon...
Solution 2 - Spring
This is a hack for program using Spring Data JPA before Version 2.0.4.
Code has worked with PostgreSQL and MySQL :
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 ORDER BY ?#{#pageable}",
countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
nativeQuery = true)
Page<User> findByLastname(String lastname, Pageable pageable);
}
ORDER BY ?#{#pageable}
is for Pageable
.
countQuery
is for Page<User>
.
Solution 3 - Spring
Just for the record, using H2 as testing database, and MySQL at runtime, this approach works (example is newest object in group):
@Query(value = "SELECT t.* FROM t LEFT JOIN t AS t_newer " +
"ON t.object_id = t_newer.object_id AND t.id < t_newer.id AND o_newer.user_id IN (:user_ids) " +
"WHERE t_newer.id IS NULL AND t.user_id IN (:user_ids) " +
"ORDER BY t.id DESC \n-- #pageable\n",
countQuery = "SELECT COUNT(1) FROM t WHERE t.user_id IN (:user_ids) GROUP BY t.object_id, t.user_id",
nativeQuery = true)
Page<T> findByUserIdInGroupByObjectId(@Param("user_ids") Set<Integer> userIds, Pageable pageable);
Spring Data JPA 1.10.5, H2 1.4.194, MySQL Community Server 5.7.11-log (innodb_version 5.7.11).
Solution 4 - Spring
Try this:
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 ORDER BY /*#pageable*/",
countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
nativeQuery = true)
Page<User> findByLastname(String lastname, Pageable pageable);
}
("/* */"
for Oracle notation
)
Solution 5 - Spring
I have exact same symptom like @Lasneyx. My workaround for Postgres native query
@Query(value = "select * from users where user_type in (:userTypes) and user_context='abc'--#pageable\n", nativeQuery = true)
List<User> getUsersByTypes(@Param("userTypes") List<String> userTypes, Pageable pageable);
Solution 6 - Spring
I use oracle database and I did not get the result but an error with generated comma which d-man speak about above.
Then my solution was:
Pageable pageable = new PageRequest(current, rowCount);
As you can see without order by when create Pagable.
And the method in the DAO:
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 /*#pageable*/ ORDER BY LASTNAME",
countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
nativeQuery = true)
Page<User> findByLastname(String lastname, Pageable pageable);
}
Solution 7 - Spring
I am adding this answer just as a placeholder for those users who are using more recent versions of Spring Boot. On Spring Boot 2.4.3, I observed that none of the workaround were necessary, and the following code worked straight out of the box for me:
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value="SELECT * FROM USERS WHERE LASTNAME = ?1", nativeQuery=true)
Page<User> findByLastname(String lastname, Pageable pageable);
}
The countQuery
definition was not necessary, and a call to Page#getTotalElements()
in fact already was returning the correct count, as returned by JPA's own internal count query.
The above code is extremely powerful, offering pagination made via a native query, yet which return results into actual Java entities (rather than the ugly and bulky List<Object[]>
, which sometimes is necessary).
Solution 8 - Spring
I could successfully integrate Pagination in
> spring-data-jpa-2.1.6
as follows.
@Query(
value = “SELECT * FROM Users”,
countQuery = “SELECT count(*) FROM Users”,
nativeQuery = true)
Page<User> findAllUsersWithPagination(Pageable pageable);
Solution 9 - Spring
Both the following approaches work fine with MySQL for paginating native query. They doesn't work with H2 though. It will complain the sql syntax error.
- ORDER BY ?#{#pageable}
- ORDER BY a.id \n#pageable\n
Solution 10 - Spring
Using "ORDER BY id DESC \n-- #pageable\n " instead of "ORDER BY id \n#pageable\n" worked for me with MS SQL SERVER
Solution 11 - Spring
This worked for me (I am using Postgres) in Groovy:
@RestResource(path="namespaceAndNameAndRawStateContainsMostRecentVersion", rel="namespaceAndNameAndRawStateContainsMostRecentVersion")
@Query(nativeQuery=true,
countQuery="""
SELECT COUNT(1)
FROM
(
SELECT
ROW_NUMBER() OVER (
PARTITION BY name, provider_id, state
ORDER BY version DESC) version_partition,
*
FROM mydb.mytable
WHERE
(name ILIKE ('%' || :name || '%') OR (:name = '')) AND
(namespace ILIKE ('%' || :namespace || '%') OR (:namespace = '')) AND
(state = :state OR (:state = ''))
) t
WHERE version_partition = 1
""",
value="""
SELECT id, version, state, name, internal_name, namespace, provider_id, config, create_date, update_date
FROM
(
SELECT
ROW_NUMBER() OVER (
PARTITION BY name, provider_id, state
ORDER BY version DESC) version_partition,
*
FROM mydb.mytable
WHERE
(name ILIKE ('%' || :name || '%') OR (:name = '')) AND
(namespace ILIKE ('%' || :namespace || '%') OR (:namespace = '')) AND
(state = :state OR (:state = ''))
) t
WHERE version_partition = 1
/*#{#pageable}*/
""")
public Page<Entity> findByNamespaceContainsAndNameContainsAndRawStateContainsMostRecentVersion(@Param("namespace")String namespace, @Param("name")String name, @Param("state")String state, Pageable pageable)
The key here was to use: /*#{#pageable}*/
It allows me to do sorting and pagination. You can test it by using something like this: http://localhost:8080/api/v1/entities/search/namespaceAndNameAndRawStateContainsMostRecentVersion?namespace=&name=&state=published&page=0&size=3&sort=name,desc
Watch out for this issue: https://stackoverflow.com/questions/51459684/spring-pageable-does-not-translate-column-name
Solution 12 - Spring
It does work as below:
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "select * from (select (@rowid\\:=@rowid+1) as RN, u.* from USERS u, (SELECT @rowid\\:=0) as init where LASTNAME = ?1) as total"+
"where RN between ?#{#pageable.offset-1} and ?#{#pageable.offset + #pageable.pageSize}",
countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
nativeQuery = true)
Page<User> findByLastname(String lastname, Pageable pageable);
}
Solution 13 - Spring
For me below worked in MS SQL
@Query(value="SELECT * FROM ABC r where r.type in :type ORDER BY RAND() \n-- #pageable\n ",nativeQuery = true)
List<ABC> findByBinUseFAndRgtnType(@Param("type") List<Byte>type,Pageable pageable);
Solution 14 - Spring
I'm using the code below. working
@Query(value = "select * from user usr" +
"left join apl apl on usr.user_id = apl.id" +
"left join lang on lang.role_id = usr.role_id" +
"where apl.scr_name like %:scrname% and apl.uname like %:uname and usr.role_id in :roleIds ORDER BY ?#{#pageable}",
countQuery = "select count(*) from user usr" +
"left join apl apl on usr.user_id = apl.id" +
"left join lang on lang.role_id = usr.role_id" +
"where apl.scr_name like %:scrname% and apl.uname like %:uname and usr.role_id in :roleIds",
nativeQuery = true)
Page<AplUserEntity> searchUser(@Param("scrname") String scrname,@Param("uname") String uname,@Param("roleIds") List<Long> roleIds,Pageable pageable);
Solution 15 - Spring
Removing \n#pageable\n from both query and count query worked for me. Springboot version : 2.1.5.RELEASE DB : Mysql
Solution 16 - Spring
You can use below code for h2 and MySQl
@Query(value = "SELECT req.CREATED_AT createdAt, req.CREATED_BY createdBy,req.APP_ID appId,req.NOTE_ID noteId,req.MODEL model FROM SUMBITED_REQUESTS req inner join NOTE note where req.NOTE_ID=note.ID and note.CREATED_BY= :userId "
,
countQuery = "SELECT count(*) FROM SUMBITED_REQUESTS req inner join NOTE note WHERE req.NOTE_ID=note.ID and note.CREATED_BY=:userId",
nativeQuery = true)
Page<UserRequestsDataMapper> getAllRequestForCreator(@Param("userId") String userId,Pageable pageable);
Solution 17 - Spring
You can achieve it by using following code,
@Query(value = "SELECT * FROM users u WHERE ORDER BY ?#{#pageable}", nativeQuery = true)
List<User> getUsers(String name, Pageable pageable);
Simply use ORDER BY ?#{#pageable} and pass page request to your method.
Enjoy!
Solution 18 - Spring
- Create your custom repository:
public interface ProductsCustomRepository extends JpaRepository<ProductResultEntity, Long> {
@Query(
value = "select tableA.id, tableB.bank_name from tableA join tableB on tableA.id = tableB.a_id where tableA.id = :id
and (:fieldX is null or tableA.fieldX LIKE :fieldX)",
countQuery = "select count(*) from tableA join tableB on tableA.id = tableB.a_id where tableA.id = :id
and (:fieldX is null or tableA.fieldX LIKE :fieldX)",
nativeQuery = true
)
Page<ProductResultEntity> search(@Param("id") Long aId,
@Param("fieldX") String keyword, Pageable pageable
);
}
- Create View as query of:
create view zzz as select * from tableA join tableB on tableA.id = tableB.a_id
- Generate ProductResultEntity from that zzz view (and delete view zzz when have done)
- Test call function and enjoy it:
productsRepository.search("123", "%BANK%", PageRequest.of(0, 5, Sort.by(Sort.Direction.ASC, "id")));
- Entity:
@Entity
public class ProductResultEntity {
private Long id;
private String bank;
@Id
@Column(name = "id", nullable = false)
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
@Column(name = "bank_name", nullable = false)
public String getBank() {
return bank;
}
public void setBank(String bank) {
this.bank = bank;
}
}
Solution 19 - Spring
Replacing /#pageable/ with ?#{#pageable} allow to do pagination. Adding PageableDefault allow you to set size of page Elements.