java – Postgres JDBC驱动程序：PSQLException：RETURNING或附

发布时间：2020-12-15 00:00:26 所属栏目：Java 来源：网络整理

导读：由于某些原因,JDBC PostgresSQL驱动程序将：RETURNING *添加到select语句的末尾. 有人有什么想法吗码： protected static final String AUTH_QUERY = "SELECT "SECRET" FROM "user" WHERE "NAME" = :name";String password = sql2o.open().createQuer

由于某些原因,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.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!