I'm using Spring Data JPA, and when I use @Query
to to define a query WITHOUT Pageable
, it works:
public interface UrnMappingRepository extends JpaRepository<UrnMapping, Long> {
@Query(value = "select * from internal_uddi where urn like %?1% or contact like %?1%",
nativeQuery = true)
List<UrnMapping> fullTextSearch(String text);
}
But if I add the second param Pageable
, the @Query
will NOT work, and Spring will parse the method's name, then throw the exception No property full found
. Is this a bug?
public interface UrnMappingRepository extends JpaRepository<UrnMapping, Long> {
@Query(value = "select * from internal_uddi where urn like %?1% or contact like %?1%",
nativeQuery = true)
Page<UrnMapping> fullTextSearch(String text, Pageable pageable);
}
This question is related to
java
spring
hibernate
jpa
spring-data-jpa
A similar question was asked on the Spring forums, where it was pointed out that to apply pagination, a second subquery must be derived. Because the subquery is referring to the same fields, you need to ensure that your query uses aliases for the entities/tables it refers to. This means that where you wrote:
select * from internal_uddi where urn like
You should instead have:
select * from internal_uddi iu where iu.urn like ...
You can use pagination with a native query. It is documented here: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#_native_queries
"You can however use native queries for pagination by specifying the count query yourself: Example 59. Declare native count queries for pagination at the query method using @Query"
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);
}
Please reference :Spring Data JPA @Query, if you are using Spring Data JPA version 2.0.4 and later. Sample like below:
@Query(value = "SELECT u FROM User u ORDER BY id")
Page<User> findAllUsersWithPagination(Pageable pageable);
I tried all above solution and non worked , finally I removed the Sorting from Pagination and it worked
With @Query , we can use pagination as well where you need to pass object of Pageable class at end of JPA method
For example:
Pageable pageableRequest = new PageRequest(page, size, Sort.Direction.DESC, rollNo);
Where,
page = index of page (index start from zero)
size = No. of records
Sort.Direction = Sorting as per rollNo
rollNo = Field in User class
UserRepository repo
repo.findByFirstname("John", pageableRequest);
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM USER WHERE FIRSTNAME = :firstname)
Page<User> findByLastname(@Param("firstname") String firstname, Pageable pageable);
}
I had the same issue - without Pageable
method works fine.
When added as method parameter - doesn't work.
After playing with DB console and native query support came up to decision that method works like it should. However, only for upper case letters.
Logic of my application was that all names
of entity starts from upper case letters.
Playing a little bit with it. And discover that IgnoreCase
at method name do the "magic" and here is working solution:
public interface EmployeeRepository
extends PagingAndSortingRepository<Employee, Integer> {
Page<Employee> findAllByNameIgnoreCaseStartsWith(String name, Pageable pageable);
}
Where entity looks like:
@Data
@Entity
@Table(name = "tblEmployees")
public class Employee {
@Id
@Column(name = "empID")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@NotEmpty
@Size(min = 2, max = 20)
@Column(name = "empName", length = 25)
private String name;
@Column(name = "empActive")
private Boolean active;
@ManyToOne
@JoinColumn(name = "emp_dpID")
private Department department;
}
I found it works different among different jpa versions, for debug, you'd better add this configurations to show generated sql, it will save your time a lot !
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
for spring boot 2.1.6.RELEASE, it works good!
Sort sort = new Sort(Sort.Direction.DESC, "column_name");
int pageNumber = 3, pageSize = 5;
Pageable pageable = PageRequest.of(pageNumber - 1, pageSize, sort);
@Query(value = "select * from integrity_score_view " +
"where (?1 is null or data_hour >= ?1 ) " +
"and (?2 is null or data_hour <= ?2 ) " +
"and (?3 is null or ?3 = '' or park_no = ?3 ) " +
"group by park_name, data_hour ",
countQuery = "select count(*) from integrity_score_view " +
"where (?1 is null or data_hour >= ?1 ) " +
"and (?2 is null or data_hour <= ?2 ) " +
"and (?3 is null or ?3 = '' or park_no = ?3 ) " +
"group by park_name, data_hour",
nativeQuery = true
)
Page<IntegrityScoreView> queryParkView(Date from, Date to, String parkNo, Pageable pageable);
you DO NOT write order by
and limit
, it generates the right sql
Considering that the UrnMapping
class is mapped to the internal_uddi
table, I would suggest this:
@Repository
public interface UrnMappingRepository extends JpaRepository<UrnMapping, Long> {
@Query(value = "select iu from UrnMapping iu where iu.urn like %:text% or iu.contact like %:text%")
Page<UrnMapping> fullTextSearch(@Param("text") String text, Pageable pageable);
}
Please note that you might have to turn off native queries with dynamic requests.
Rewrite your query to:
select iu from internal_uddi iu where iu.urn....
description: http://forum.spring.io/forum/spring-projects/data/126415-is-it-possible-to-use-query-and-pageable?p=611398#post611398
Declare native count queries for pagination at the query method by using @Query
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);
}
Hope this helps
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods
Source: Stackoverflow.com