I have asked in stackoverflow the following question:
spring boot (1.3.5), spring-data, spring-data-jpa, JPA (hibernate / hsqldb).
POM:
<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>
The configuration code:
@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;
}
The controller:
@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) {
// I've also tried it with @PageableDefault (page = 0, size = 20, sort="code", direction = Direction.ASC) but it does not work.
Page<T> page;
if (!isEmpty(searchString))
page = service.search(searchString, pageable); // <-- ERROR
else
page = service.findAll(pageable); // <-- OK
The service (simplified):
@Override
@Transactional(readOnly = true)
public Page<T> search(String str, Pageable pageable) {
return repository.search(str, pageable);
}
The repository (simplified):
@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);
}
I've tried it with the same @Query but with return List<> instead of Page<> and without the parameter Pageable pageable , and it works.
// con esta @Query de prueba:
@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);
When I call findAll(pageable) it works but when I call search(str, pageable) (str="AT") it does not work.
Browser output:
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 is invalid! Extra ":" and duplicate "ASC asc".
Console output:
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]