正文

JPA+Postgresql+Spring Data Page分页失败

IP云
IP云V管理员/3 阅读
09/22
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

按照示例进行如下代码编写

Repository

  1. Page<DeviceEntity> findByTenantId(int tenantId, Pageable pageable);
service

  1. @Override
  2. public List<DeviceEntity> getDevices(int count, int page, int teantid) {
  3. Page<DeviceEntity> devices=deviceEntityRepository.findByTenantId(teantid,
  4. new PageRequest(page,count, Sort.Direction.ASC,"id"));
  5. return null;
  6. }
会出现如下错误

  1. 2017-04-25 17:12:09.541 ERROR 6136 --- [nio-9090-exec-1] c.alibaba.druid.filter.stat.StatFilter : merge sql error, dbType postgresql, sql :
  2. select deviceenti0_.id as id1_0_, deviceenti0_.dev_id as dev_id2_0_, deviceenti0_.dev_manufacture as dev_manu3_0_, deviceenti0_.dev_note as dev_note4_0_, deviceenti0_.dev_position1 as dev_posi5_0_, deviceenti0_.dev_position2 as dev_posi6_0_, deviceenti0_.dev_sn as dev_sn7_0_, deviceenti0_.dev_type as dev_type8_0_, deviceenti0_.emp_id as emp_id9_0_, deviceenti0_.tenant_id as tenant_10_0_, deviceenti0_.user_id as user_id11_0_ from public.device deviceenti0_ where deviceenti0_.tenant_id=? order by deviceenti0_.id asc limit ?, ?
  4. com.alibaba.druid.sql.parser.ParserException: syntax error, error in :' asc limit ?, ?',expect COMMA, actual COMMA limit
  5. at com.alibaba.druid.sql.parser.SQLParser.printError(SQLParser.java:239) ~[druid-1.0.26.jar:1.0.26]
  6. at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:431) ~[druid-1.0.26.jar:1.0.26]
  7. ...

多方查找，没有类似结果。查看debug中构造的sql语句，构造的sql在navicat for postgresql执行，也会返回如下错误。
原因是pg数据库中的查询语句应该是

  1. SELECT select_list
  2. FROM table_expression
  3. [LIMIT { number | ALL }] [OFFSET number]
有JPA构造的查询语句是mysql格式的。

解决方法是，在Repository中，自己写Query，即可
这里注意postgres需要使用本地sql语句

  1. @Query(value="select * from device where tenant_id=?1 order by id limit ?2 offset ?3 ",nativeQuery = true)
  2. List<DeviceEntity> findByTenantId(int tenantId, int size, int page);

另需要在druid配置中加入制定数据库类型。我是采用@Config方式配置的，语句如下

  1. @Configuration
  2. @EnableConfigurationProperties({DruidDataSourceProperties.class})
  3. public class DruidConfiguration {
  4. @Autowired
  5. private DruidDataSourceProperties properties;
  7. @Bean
  8. @ConditionalOnMissingBean
  9. public DataSource druidDataSource() {
  11. DruidDataSource druidDataSource = new DruidDataSource();
  12. ...........
  13. druidDataSource.setDbType("postgresql");
  14. .....

null