java jpa page,java-spring data jpa @query和pageab

java-spring data jpa @query和pageab

我正在使用Spring Data JPA，当我使用Pageable来定义一个没有@Query的查询时，它可以工作：

public interface UrnMappingRepository extends JpaRepository {

@Query(value = "select * from internal_uddi where urn like %?1% or contact like %?1%",

nativeQuery = true)

List fullTextSearch(String text);

}

但是，如果我添加第二个参数Pageable，则@Query将不起作用，Spring将解析该方法的名称，然后引发异常No property full found。这是错误吗？

public interface UrnMappingRepository extends JpaRepository {

@Query(value = "select * from internal_uddi where urn like %?1% or contact like %?1%",

nativeQuery = true)

Page fullTextSearch(String text, Pageable pageable);

}

6个解决方案

37 votes

您可以对本机查询使用分页。 它记录在这里：[https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#_native_queries]

“不过，您可以通过自己指定计数查询来使用本机查询进行分页：例子59.使用@Query在查询方法中声明本机计数查询以进行分页

public interface UserRepository extends JpaRepository {

@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",

countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",

nativeQuery = true)

Page findByLastname(String lastname, Pageable pageable);

}

RemusS answered 2020-01-05T08:50:02Z

29 votes

在Spring论坛上提出了类似的问题，指出要应用分页，必须派生第二个子查询。 由于子查询引用的是相同的字段，因此您需要确保查询对引用的实体/表使用别名。 这意味着您在哪里写了：

select * from internal_uddi where urn like

您应该改为：

select * from internal_uddi iu where iu.urn like ...

Steve answered 2020-01-05T08:49:37Z

8 votes

考虑到UrnMapping类已映射到internal_uddi表，我建议这样做：

@Repository

public interface UrnMappingRepository extends JpaRepository {

@Query(value = "select iu from UrnMapping iu where iu.urn like %:text% or iu.contact like %:text%")

Page fullTextSearch(@Param("text") String text, Pageable pageable);

}

请注意，您可能必须关闭具有动态请求的本机查询。

Dimitri Hautot answered 2020-01-05T08:50:27Z

1 votes

将查询重写为：

select iu from internal_uddi iu where iu.urn....

说明：[http://forum.spring.io/forum/spring-projects/data/126415-is-it-possible-to-use-query-and-pageable?p=611398#post611398]

Martin Baumgartner answered 2020-01-05T08:50:51Z

0 votes

如果您使用的是Spring Data JPA 2.0.4及更高版本，请参考：Spring Data JPA @Query。 样例如下：

@Query(value = "SELECT u FROM User u ORDER BY id")

Page findAllUsersWithPagination(Pageable pageable);

shenyu1997 answered 2020-01-05T08:51:12Z

0 votes

我发现它在不同的jpa版本之间的工作方式有所不同，对于调试，您最好添加此配置以显示生成的sql，它将节省大量时间！

spring.jpa.show-sql=true

spring.jpa.properties.hibernate.format_sql=true

对于春季靴2.1.6.RELEASE，效果很好！

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 queryParkView(Date from, Date to, String parkNo, Pageable pageable);

您不要编写order by和limit，它会生成正确的sql

kite answered 2020-01-05T08:51:41Z