记一个报错(粗心大意所致):背景是项目数据库从Oracle切换成PostgreSQL,使用Spring Data Jpa提供的SimpleJpaRepository做查询。下面是引起报错的查询示例:
findTopByParam1AndParam2(param1, param2);
报错信息:
[WARN] [http-nio-8483-exec-3] [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] 129 - SQL Error: 0, SQLState: 42601
[ERROR] [http-nio-8483-exec-3] [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] 131 - 错误: FROM 中的子查询必须有一个别名
Caused by: org.postgresql.util.PSQLException: 错误: FROM 中的子查询必须有一个别名
Hint: 例如, FROM (SELECT ...) [AS] foo.
Position: 15
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:106)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2830)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2827)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:182)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:228)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
... 153 common frames omitted
通过打印出查询SQL可观察到,jpa查询拼接的sql不是PostgreSQL要求的样式,因此导致报错。
后面发现原因是配置文件中jpa配置的数据库方言没有从Oracle的方言改成PostgreSQL的方言。也就是以下配置的属性值没改,改成与PostgreSQL版本对应的dialect即可:
spring:
jpa:
# 之前使用的是:org.hibernate.dialect.Oracle10gDialect
database-platform: org.hibernate.dialect.PostgreSQL95Dialect