java – Postgres JDBC驱动程序：PSQLException：RETURNING或附近的语法错误

由于某些原因,JDBC Postgressql驱动程序将：RETURNING *添加到select语句的末尾.
有人有什么想法吗

码：

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);

例外：

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)

资料来源(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驱动程序版本

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.3-1101-jdbc41</version>
</dependency>

数据包捕获

http://postimg.org/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" = $1 RETURNING *
    Parameters: 1
        Type OID: 1043

解决方法

这看起来像一个 problem with sql2o.对错误报告的评论说：

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 07001

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.

由于sql2o 1.6.0,包括 sql2o-postgres依赖,并使用新的PostgresQuirks()而不是QuirksMode.

java – Postgres JDBC驱动程序：PSQLException：RETURNING或附近的语法错误

解决方法

猜你在找的Java相关文章