spring数据jpa可分页排序错误

Question

我使用了spring boot(1.3.5)、spring-data、spring-data-jpa、JPA（hibernate/hsqldb）。

代码：

<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>

<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>

<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>

@Bean
public PageableHandlerMethodArgumentResolver pageableHandlerMethodArgumentResolver(
        SortHandlerMethodArgumentResolver sortHandlerMethodArgumentResolver) {

    PageableHandlerMethodArgumentResolver phmar = new PageableHandlerMethodArgumentResolver(
            sortHandlerMethodArgumentResolver);
    phmar.setOneIndexedParameters(false);
    phmar.setPageParameterName("page");
    phmar.setSizeParameterName("size");
    phmar.setMaxPageSize(20);
    return phmar;
}

@Bean
public SortHandlerMethodArgumentResolver sortHandlerMethodArgumentResolver() {

    SortHandlerMethodArgumentResolver shmar = new SortHandlerMethodArgumentResolver();
    shmar.setSortParameter("sort");
    return shmar;
}

控制器：

@RequestMapping(value = { "/List", "" })
public String list(Model model, @RequestParam(required = false) String searchString,
        @SortDefault(sort = "code", direction = Direction.ASC) @PageableDefault(page = 0, size = 20) Pageable pageable) {

我试着

@PageableDefault(page = 0, size = 20, sort = "code", direction = Direction.ASC)

也是，但不起作用。

Page<T> page;
if (!isEmpty(searchString))
   page = service.search(searchString, pageable); // <-- ERROR
else
   page = service.findAll(pageable);  // <-- OK

@Override
@Transactional(readOnly = true)
public Page<T> search(String str, Pageable pageable) {
    return repository.search(str, pageable);
}

@Repository
public interface EntityRepository extends JpaRepository<T, Integer> {
(...)
@Query(value = "SELECT a FROM #{#entityName} a WHERE a.code LIKE CONCAT('%', :str, '%') OR UPPER(a.name) LIKE UPPER(CONCAT('%', :str, '%'))")
Page<T> search(@Param("str") String str, Pageable pageable);

@Query(value = "SELECT a FROM #{#entityName} a WHERE a.code LIKE CONCAT('%', :str, '%') OR UPPER(a.name) LIKE UPPER(CONCAT('%', :str, '%'))")
List<T> search(@Param("str") String str);

浏览器输出：

Whitelabel Error Page
This application has no explicit mapping for /error, so you are seeing this as a fallback.
Wed May 25 15:16:24 CEST 2016
There was an unexpected error (type=Internal Server Error, status=500).
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: : near line 1, column 151 [SELECT a FROM prueba.entity.AccountType a WHERE a.code LIKE CONCAT('%', :str, '%') OR UPPER(a.name) LIKE UPPER(CONCAT('%', :str, '%')) order by a.code: ASC asc]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: : near line 1, column 151 [SELECT a FROM prueba.entity.AccountType a WHERE a.code LIKE CONCAT('%', :str, '%') OR UPPER(a.name) LIKE UPPER(CONCAT('%', :str, '%')) order by a.code: ASC asc]

SQL无效！额外的“：”和重复的“ASC ASC”。

控制台输出：

Hibernate: 
    select
        count(accounttyp0_.id) as col_0_0_ 
    from
        account_type accounttyp0_ 
    where
        accounttyp0_.code like ('%'||?||'%') 
        or upper(accounttyp0_.name) like upper(('%'||?||'%'))
mo.h.hql.internal.ast.ErrorCounter       line 1:151: unexpected token:     :
mo.h.hql.internal.ast.ErrorCounter       line 1:151: unexpected token:     :

antlr.NoViableAltException: unexpected token: :
    at         org.hibernate.hql.internal.antlr.HqlBaseParser.atom(HqlBaseParser.java:3694)     [hibernate-core-4.3.11.Final.jar:4.3.11.Final]

(more and more)

near line 1, column 151 [SELECT a FROM prueba.entity.AccountType a     WHERE a.code LIKE CONCAT('%', :str, '%') OR UPPER(a.name) LIKE     UPPER(CONCAT('%', :str, '%')) order by a.code: ASC asc]; nested exception     is java.lang.IllegalArgumentException:     org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: :     near line 1, column 151 [SELECT a FROM prueba.entity.AccountType a WHERE     a.code LIKE CONCAT('%', :str, '%') OR UPPER(a.name) LIKE     UPPER(CONCAT('%', :str, '%')) order by a.code: ASC asc]] with root cause

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token:     : near line 1, column 151 [SELECT a FROM     prueba.entity.AccountType a WHERE a.code LIKE CONCAT('%', :str, '%') OR     UPPER(a.name) LIKE UPPER(CONCAT('%', :str, '%')) order by a.code: ASC asc]
    at     org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxExcept    ion.java:91) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]

Answer 1

对于使用spring-data-jpa进行分页，您必须实现PagingAndSortingRepository接口。

您必须编写一个方法，它将参数作为Pageable page as并且可以将new PageRequest(0,size）作为参数传递给Pageable，而size是您想要获取的记录数。

不要使用@query，而是可以编写简单的方法，并且可以在spring-data-jpa中使用所有支持的关键字。

http://docs.spring.io/spring-data/jpa/docs/1.4.3.release/reference/html/jpa.repositories.html