Java Postgres JDBC 驱动程序：PSQLException：返回处或附近出现语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24636120/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres JDBC driver: PSQLException: syntax error at or near RETURNING
提问by tty
For some reason the JDBC PostgreSQL driver is adding: RETURNING *to the end of select statements. Why?
出于某种原因，JDBC PostgreSQL 驱动程序将：RETURNING *添加到 select 语句的末尾。为什么？
Code:
代码：
protected static final String AUTH_QUERY = "SELECT \"SECRET\" FROM \"user\" WHERE \"NAME\" = :name";
String password = sql2o.open().createQuery(AUTH_QUERY).addParameter("name", username).executeScalar(String.class);
Exception:
例外：
org.postgresql.util.PSQLException: ERROR: syntax error at or near "RETURNING"
Position: 47
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.sql2o.Query.executeScalar(Query.java:533)
at org.sql2o.Query.executeScalar(Query.java:577)
at org.sql2o.Query.executeScalar(Query.java:568)
Data source (JNDI):
数据源（JNDI）：
<Configure id="wac" class="org.eclipse.jetty.webapp.WebAppContext">
<New id="mydb" class="org.eclipse.jetty.plus.jndi.Resource">
<Arg></Arg>
<Arg>jdbc/mydb</Arg>
<Arg>
<New class="com.mchange.v2.c3p0.ComboPooledDataSource">
<Set name="driverClass">org.postgresql.Driver</Set>
<Set name="jdbcUrl">jdbc:postgresql://localhost:5432/mydb</Set>
<Set name="user">user</Set>
<Set name="password">pass</Set>
</New>
</Arg>
</New>
</Configure>
PostgreSQL JDBC driver version
PostgreSQL JDBC 驱动版本
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.3-1101-jdbc41</version>
</dependency>
Packet capture
抓包
https://postimg.cc/image/gbl2dq4zx/
https://postimg.cc/image/gbl2dq4zx/
No. Time Source Destination Protocol Length Info
12 0.175636000 127.0.0.1 127.0.0.1 PGSQL 182 >P/B/D/E/S
Frame 12: 182 bytes on wire (1456 bits), 182 bytes captured (1456 bits) on interface 0
PostgreSQL
Type: Parse
Length: 69
Statement:
Query: SELECT "SECRET" FROM "user" WHERE "NAME" = RETURNING *
Parameters: 1
Type OID: 1043
采纳答案by Nathan Hughes
This looks like a problem with sql2o. The comments on the bug report say:
Whe using PostgreSQL, all SELECT statements will fail with message: org.postgresql.util.PSQLException: ERROR: syntax error at or near "RETURNING"
Seems to be related to this issue
This has been fixed with version 1.1.2.
The fix requires the QuirkMode enum flag to be set to PostgreSQL when creating a new instance of sql2o. It changes default behaviour of queries to NOT fetch generated keys by default. When it is needed to fetch generated keys, the returnGeneratedKeys parameter in the generateQuery method should be set.
使用 PostgreSQL 时，所有 SELECT 语句都将失败并显示消息：org.postgresql.util.PSQLException: ERROR: syntax error at or near "RETURNING"
似乎与此问题有关
这已在 1.1.2 版中修复。
该修复要求在创建 sql2o 的新实例时将 QuirkMode 枚举标志设置为 PostgreSQL。它将查询的默认行为更改为默认情况下不获取生成的键。当需要获取生成的密钥时，应该设置 generateQuery 方法中的 returnGeneratedKeys 参数。
Since Sql2o 1.6.0, include the sql2o-postgres dependency and use new PostgresQuirks()instead of QuirksMode.
由于Sql2o 1.6.0，包括sql2o，Postgres的依赖和使用new PostgresQuirks()代替QuirksMode。
回答by Ariloum
The easiest way I've done this was adding ";--" at the end of sql code:
我完成此操作的最简单方法是在 sql 代码末尾添加“;--”：
String sql = "INSERT INTO testTable(var1, var2) values ("1","2"), ("1","2") RETURNING id;--";
PreparedStatement ps = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
回答by Oleg Galushko
In my case occurred when u tried to insert empty list of objects.
在我的情况下，当您尝试插入空的对象列表时发生。
