JPA+Postgresql+Spring Data Page分页失败

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

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

转载于:https://www.cnblogs.com/tilv37/p/6763306.html